Not getting the distinct record

Wednesday, February 22, 2012 10:30 AM

0

On what basis you are assuming your records as distinct ?

Thanks and regards, Rishabh , Microsoft Community Contributor

Wednesday, February 22, 2012 11:09 AM

0

See firstly i have a query

select  (sum(convert(int,(A.quantity)) *A.rate)/sum(convert(int,(A.quantity))))  as aggree  
 , Row_number() over(order by B.product desc)  as sno,A.id,A.client_id,A.product_id,B.product + ' ' + B.weight as product,A.quantity + ' ' +(SELECT data FROM [dbo].[Split] (B.weight,' ') where id=2) as weight,A.rate as purchaserate,A.TotalCost,B.rates,convert(decimal(38,2),((select   case when A.rate < B.rates then (B.rates-A.rate)*A.quantity else 0 end)))as Profit,convert(decimal(38,2),((select   case when A.rate > B.rates then (A.rate-B.rates)*A.quantity else 0 end)))as Loss from Commodity_orders as A inner join Commodity_Market as B on A.product_id=B.id where A.client_id=3 group by A.product_id,B.product,A.id,A.client_id,A.product_id,B.product,B.weight,A.quantity,A.rate,A.TotalCost,B.rates

Now I have to calculate average of purchase rate so for that i have written query like

select B.product, sum(convert(int,(A.quantity)) *A.rate)/sum(convert(int,(A.quantity))) as aggree from Commodity_orders as A inner join Commodity_Market as B on A.product_id=B.id where A.client_id=3 group by A.product_id,B.product

This query gives me result

Now what i am trying to do that merging this last query in Ist one.. But i am not getting right result..I think now you got it

kshama

Wednesday, February 22, 2012 11:20 AM

0

Check this. Not sure but try this if it works.

SELECT (
           SUM(CONVERT(INT ,(A.quantity))*A.rate)/SUM(CONVERT(INT ,(A.quantity)))
       ) AS aggree
      ,ROW_NUMBER() OVER(ORDER BY B.product DESC) AS sno
      ,(Select sum(convert(int,(A.quantity)) *A.rate)/sum(convert(int,(A.quantity))) as aggree from Commodity_orders c 
				WHERE A.product_id=c.id and A.client_id=3 AND b.product = c.product)
      ,A.id
      ,A.client_id
      ,A.product_id
      ,B.product+' '+B.weight AS product
      ,A.quantity+' '+(
           SELECT DATA
           FROM   [dbo].[Split] (B.weight ,' ')
           WHERE  id = 2
       ) AS WEIGHT
      ,A.rate AS purchaserate
      ,A.TotalCost
      ,B.rates
      ,CONVERT(
           DECIMAL(38 ,2)
          ,(
               (
                   SELECT CASE 
                               WHEN A.rateB.rates THEN (A.rate-B.rates)*A.quantity
                               ELSE 0
                          END
               )
           )
       ) AS Loss
FROM   Commodity_orders AS A
       INNER JOIN Commodity_Market AS B
            ON  A.product_id = B.id
WHERE  A.client_id = 3
GROUP BY
       A.product_id
      ,B.product
      ,A.id
      ,A.client_id
      ,A.product_id
      ,B.product
      ,B.weight
      ,A.quantity
      ,A.rate
      ,A.TotalCost
      ,B.rates

We can use CTE later, if this works.

Planet Earth is at risk. Global warming is on a high tide.
Take Responsibility. Plant Trees. Keep your City Clean and Green.

Mark all Helping Posts and Close your Threads. Keep the Forum Green.
- Arun Kumar Allu

Edited by arun.passioniway Wednesday, February 22, 2012 11:21 AM edit

Wednesday, February 22, 2012 11:50 AM

0

I made some minor correction in your Query but getting the result which was I already from my query

I want distinct record Like this.. I am not writing the whole fields

and also weight should come 1000 kgs

kshama

Wednesday, February 22, 2012 12:31 PM

0

Hmmm.... for your weight to be 1000 kgs, you should not GROUP By "Commodity_orders.id".

With that you won't get a value of 1000 unless you use OVER - PARTITION BY.

What are column that you need in your output.

Planet Earth is at risk. Global warming is on a high tide.
Take Responsibility. Plant Trees. Keep your City Clean and Green.

Mark all Helping Posts and Close your Threads. Keep the Forum Green.
- Arun Kumar Allu

Wednesday, February 22, 2012 12:45 PM

0

see this is my ouput coming

I need all the columns except in place of Purchase rate column, i need purchase aggregate which is coming in aggegate column and also i wants only single row i.e distinct and weight should be 1000 kgs

kshama

Wednesday, February 22, 2012 1:02 PM

0

If you want in One row,

then your id would not be shown.
TotalCost will be added/aggregated (as well as profit and loss values will be changed)

Is that fine.

Planet Earth is at risk. Global warming is on a high tide.
Take Responsibility. Plant Trees. Keep your City Clean and Green.

Mark all Helping Posts and Close your Threads. Keep the Forum Green.
- Arun Kumar Allu

Wednesday, February 22, 2012 1:02 PM

0

Hi Kshama,

Please exclude(remove) sno, id, columns from your select list and group by clause of the sql query.
And pug aggregation on Weight i.e. SUM(Weight) and remove Weight from group by clause.

Or please send us your current query, also mention list of columns you require in ouput.

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Wednesday, February 22, 2012 1:06 PM

0

I need all the columns except in place of Purchase rate column, i need purchase aggregate which is coming in aggegate column and also i wants only single row i.e distinct and weight should be 1000 kgs

kshama

Impossible. You can't have id=39 and id=40 in one row. Either omit this column from select list or aggregate it some way. Same Profit, TotalCost columns.

Serg

Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Wednesday, February 22, 2012 2:51 PM

Wednesday, February 22, 2012 1:08 PM

0

Let me know if this does work.

SELECT (
           SUM(CONVERT(INT ,(A.quantity))*A.rate)/SUM(CONVERT(INT ,(A.quantity)))
       ) AS aggree
      ,ROW_NUMBER() OVER(ORDER BY B.product DESC) AS sno
--      ,A.id
      ,A.client_id
      ,A.product_id
      ,B.product+' '+B.weight AS product
      ,A.quantity+' '+(
           SELECT DATA
           FROM   [dbo].[Split] (B.weight ,' ')
           WHERE  id = 2
       ) AS WEIGHT
      ,A.rate AS purchaserate
      ,A.TotalCost
      ,B.rates
      ,CONVERT(
           DECIMAL(38 ,2)
          ,(
               (
                   SELECT CASE 
                               WHEN A.rate<B.rates THEN (B.rates-A.rate)*A.quantity
                               ELSE 0
                          END
               )
           )
       ) AS Profit
      ,CONVERT(
           DECIMAL(38 ,2)
          ,(
               (
                   SELECT CASE 
                               WHEN A.rate>B.rates THEN (A.rate-B.rates)*A.quantity
                               ELSE 0
                          END
               )
           )
       ) AS Loss
FROM   Commodity_orders AS A
       INNER JOIN Commodity_Market AS B
            ON  A.product_id = B.id
WHERE  A.client_id = 3
GROUP BY
       A.product_id
      ,B.product
--      ,A.id
      ,A.client_id
      ,A.product_id
      ,B.product
      ,B.weight
      ,A.quantity
      ,A.rate
      ,A.TotalCost
      ,B.rates

Provide the table creation and sample data scripts so that we can work on it.

Planet Earth is at risk. Global warming is on a high tide.
Take Responsibility. Plant Trees. Keep your City Clean and Green.

Mark all Helping Posts and Close your Threads. Keep the Forum Green.
- Arun Kumar Allu

Wednesday, February 22, 2012 1:11 PM

0

Ok i am sending my query and see i need all the columns

See I need all the columns with some modification like weight should be 1000 kgs , Purchase rate should be aggregate according to weights, Total Cost should also be calculated and lastly profit and loss should comes finally, If clent was in loss then column name should be loss with Loss amount and same as for profit..

PLz help as i am trying since morning..

kshama

Wednesday, February 22, 2012 1:13 PM

0

I have replied to Suresh PLZ see and if possible then plz help

kshama

Wednesday, February 22, 2012 1:23 PM

0

I have replied to Suresh PLZ see and if possible then plz help

kshama

I wish the forums had a check out button so that I could check out the forum when suresh was replying.

Dude Just see the time when I replied and then shout.

Planet Earth is at risk. Global warming is on a high tide.
Take Responsibility. Plant Trees. Keep your City Clean and Green.

Mark all Helping Posts and Close your Threads. Keep the Forum Green.
- Arun Kumar Allu

Wednesday, February 22, 2012 1:27 PM

0

ok just leave the id column

kshama

Wednesday, February 22, 2012 1:27 PM

0

Please send the query, not the screenshot. As screenshot is not helpful to edit/modify.

As I said in my last reply, Id and Sr No columns are causing to return multiple(two) rows as output of your query.

Please send your query and table structure.

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Wednesday, February 22, 2012 1:35 PM

0

No thats Not working thats again bringing two rows plz run this script in your database..

In this script i have two tables

/****** Object:  Table [dbo].[Commodity_orders]    Script Date: 02/22/2012 19:03:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Commodity_orders]') AND type in (N'U'))
DROP TABLE [dbo].[Commodity_orders]
GO
/****** Object:  Table [dbo].[Commodity_Market]    Script Date: 02/22/2012 19:03:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Commodity_Market]') AND type in (N'U'))
DROP TABLE [dbo].[Commodity_Market]
GO
/****** Object:  Table [dbo].[Commodity_Market]    Script Date: 02/22/2012 19:03:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Commodity_Market]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Commodity_Market](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[product] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[weight] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[city] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[rates] [decimal](38, 2) NULL,
 CONSTRAINT [PK_Commodity Market] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Commodity_Market] ON
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (1, N'ALMOND', N'1 KGS', N'DELHI', CAST(368.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (2, N'ALUMINI', N'1 KGS', N'MUMBAI', CAST(105.25 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (3, N'ALUMINIUM', N'1 KGS', N'MUMBAI', CAST(105.25 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (4, N'BARLEY', N'100 KGS', N'JAIPUR', CAST(1177.20 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (5, N'BRENTCRUDE', N'1 BBL', N'MUMBAI', CAST(5916.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (6, N'CARDAMOM', N'1 KGS', N'VANDANMEDU', CAST(706.20 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (7, N'CASTORSD RJK', N'100 KGS', N'RAJKOT', CAST(3855.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (8, N'CFI', N'1 MT', N'MUMBAI', CAST(584.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (9, N'CHANA', N'100 KGS', N'DELHI', CAST(3613.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (10, N'COCONUTOIL', N'100 KGS', N'KOCHI', CAST(6604.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (11, N'COPPER', N'1 KGS', N'MUMBAI', CAST(411.65 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (12, N'COPPERM', N'1 KGS', N'MUMBAI', CAST(411.65 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (13, N'COTTON', N'1 BALES', N'RAJKOT', CAST(17250.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (14, N'CRUDEOIL', N'1 BBL', N'MUMBAI', CAST(5039.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (15, N'GASOLINE', N'1 USGLN', N'MUMBAI', CAST(150.10 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (16, N'GNUTOILEXP', N'10 KGS', N'RAJKOT', CAST(1090.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (17, N'GOLDHNI', N'10 GRMS', N'AHMEDABAD', CAST(27369.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (18, N'GUARSEED', N'100 KGS', N'BIKANER', CAST(15626.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (19, N'HEATINGOIL', N'1 USGLN', N'MUMBAI', CAST(158.10 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (20, N'JUTE', N'100 KGS', N'KOLKATA', CAST(2488.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (21, N'KAPASKHALI', N'100 KGS', N'AKOLA', CAST(1188.30 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (22, N'LEAD', N'1 KGS', N'MUMBAI', CAST(98.95 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (23, N'LEADMINI', N'1 KGS', N'MUMBAI', CAST(98.95 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (24, N'MAIZE', N'100 KGS', N'NIZAMABAD', CAST(1161.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (25, N'MASUR', N'100 KGS', N'INDORE', CAST(3036.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (26, N'MUSTARD OIL', N'10 KGS', N'JAIPUR', CAST(732.90 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (27, N'MUSTRDSD JPR', N'20 KGS', N'JAIPUR', CAST(678.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (28, N'NATURALGAS', N'1 mmBtu', N'HAZIRA', CAST(126.40 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (29, N'NICKEL', N'1 KGS', N'MUMBAI', CAST(987.60 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (30, N'NICKELM', N'1 KGS', N'MUMBAI', CAST(987.60 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (31, N'PLATINUM', N'1 GRMS', N'MUMBAI', CAST(2575.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (32, N'RUBBER', N'100 KGS', N'KOCHI', CAST(18610.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (33, N'SESAMESEED', N'100 KGS', N'RAJKOT', CAST(6100.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (34, N'SILVERHNI', N'1 KGS', N'AHMEDABAD', CAST(52841.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (35, N'SUGAR S', N'100 KGS', N'KOLHAPUR', CAST(2820.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (36, N'SUGARMDEL', N'100 KGS', N'DELHI', CAST(3017.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (37, N'SUGARMKOL', N'100 KGS', N'KOLHAPUR', CAST(2900.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (38, N'TCOAL', N'1 MT', N'CHENNAI', CAST(5814.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (39, N'TIN', N'1 KGS', N'MUMBAI', CAST(1191.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (40, N'TURMERIC', N'100 KGS', N'NIZAMABAD', CAST(5006.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (41, N'WHEAT', N'100 KGS', N'DELHI', CAST(1265.80 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (42, N'ZINC', N'1 KGS', N'MUMBAI', CAST(97.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (43, N'ZINCMINI', N'1 KGS', N'MUMBAI', CAST(97.50 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (44, N'ATF', N'1 BBL', N'MUMBAI', CAST(7078.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (45, N'FLAKEMENTH', N'1 KGS', N'SAMBHAL', CAST(1940.40 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (46, N'GOLD', N'10 GRMS', N'AHMEDABAD', CAST(27870.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (47, N'GOLDGUINEA', N'8 GRMS', N'AHMEDABAD', CAST(22385.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (48, N'GOLDPETAL', N'1 GRMS', N'MUMBAI', CAST(2804.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (49, N'GOLDPTLDEL', N'1 GRMS', N'DELHI', CAST(2800.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (50, N'KAPAS', N'20 KGS', N'SURENDRANAGAR', CAST(818.40 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (51, N'MENTHAOIL', N'1 KGS', N'CHANDAUSI', CAST(1813.70 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (52, N'REFSOYOIL', N'10 KGS', N'INDORE', CAST(703.05 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (53, N'SILVERMIC', N'1 KGS', N'AHMEDABAD', CAST(55673.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_Market] ([id], [product], [weight], [city], [rates]) VALUES (54, N'SOYABEAN', N'100 KGS', N'INDORE', CAST(2512.00 AS Decimal(38, 2)))
SET IDENTITY_INSERT [dbo].[Commodity_Market] OFF
/****** Object:  Table [dbo].[Commodity_orders]    Script Date: 02/22/2012 19:03:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Commodity_orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Commodity_orders](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[client_id] [int] NULL,
	[product_id] [int] NULL,
	[quantity] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[rate] [decimal](38, 2) NULL,
	[dateofpurchase] [datetime] NULL,
	[TotalCost] [decimal](38, 2) NULL,
 CONSTRAINT [PK_Commodity_orders] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Commodity_orders] ON
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (32, 2, 54, N'550', CAST(2314.00 AS Decimal(38, 2)), CAST(0x00009FFF00000000 AS DateTime), CAST(13816.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (34, 2, 7, N'23', CAST(3855.00 AS Decimal(38, 2)), CAST(0x0000A00400000000 AS DateTime), CAST(888.38 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (36, 2, 1, N'2', CAST(368.50 AS Decimal(38, 2)), CAST(0x00009FEB00000000 AS DateTime), CAST(737.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (37, 2, 1, N'2', CAST(390.50 AS Decimal(38, 2)), CAST(0x00009FEB00000000 AS DateTime), CAST(781.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (38, 2, 46, N'20', CAST(27970.00 AS Decimal(38, 2)), CAST(0x00009FF100000000 AS DateTime), CAST(55740.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (39, 3, 2, N'800', CAST(81.00 AS Decimal(38, 2)), CAST(0x00009FF100000000 AS DateTime), CAST(64800.00 AS Decimal(38, 2)))
INSERT [dbo].[Commodity_orders] ([id], [client_id], [product_id], [quantity], [rate], [dateofpurchase], [TotalCost]) VALUES (40, 3, 2, N'200', CAST(91.05 AS Decimal(38, 2)), CAST(0x0000A15F00000000 AS DateTime), CAST(18210.00 AS Decimal(38, 2)))
SET IDENTITY_INSERT [dbo].[Commodity_orders] OFF

kshama

Wednesday, February 22, 2012 2:22 PM

0

Hi Kshama,

Please see the query below, try it and let me know if you need any modifications.

Post the query back if you modify at your end and do not get desired output.

 SELECT 
  A.CLIENT_ID,
 A.PRODUCT_ID,B.PRODUCT + ' ' + B.WEIGHT AS PRODUCT 
 --, A.QUANTITY + ' ' + (SELECT DATA FROM [DBO].[SPLIT] (B.WEIGHT,' ') WHERE ID=2) AS WEIGHT
 --,A.RATE AS PURCHASERATE
 , SUM(CONVERT(INT,(A.QUANTITY))) QTY
 , B.WEIGHT
 , SUM(A.TOTALCOST) AS TOTALCOST
 , B.RATES 
 ,SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END))))AS PROFIT
 ,SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))AS LOSS 
 , SUM(CONVERT(INT,(A.QUANTITY)) * A.RATE)/SUM(CONVERT(INT,(A.QUANTITY)) ) AGGREE

 FROM COMMODITY_ORDERS AS A
  INNER JOIN COMMODITY_MARKET AS B ON A.PRODUCT_ID=B.ID 
  WHERE A.CLIENT_ID=3 
  GROUP BY A.PRODUCT_ID,B.PRODUCT
  ,A.CLIENT_ID,A.PRODUCT_ID,B.PRODUCT,B.WEIGHT,B.RATES

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Marked As Answer by nikijain Thursday, February 23, 2012 5:40 AM

Thursday, February 23, 2012 5:39 AM

0

Thats Great. it works fine..Thanks so much for your helpful reply..But one more problem i have..In Image the first output is by Your Query and in second Output I am showing total purcase of that client

Now what i wants that in your Output one column should come say as Total Profit/Loss and by calulating it should come loss of 48960 as seeing Second Output..

kshama

Thursday, February 23, 2012 5:55 AM

0

Hi,

Please add this in select clause of the query I sent earlier. Even you can add other calculations too if you need anything more

, (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
   AS TOTAL_PROFIT_LOSS

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Marked As Answer by nikijain Thursday, February 23, 2012 6:26 AM

Thursday, February 23, 2012 6:13 AM

0

Again It works Great

TTotal_Profit_Loss is coming but see the grid where i am showing these records i have two columns one is Profit and One is Loss.. Now what i want that if user Got profit then amount of profit should come in profit column and Loss column should be Null and same Should happen if User got Loss Profit column shoul Null

kshama

Thursday, February 23, 2012 6:31 AM

0

Hi,

Please make use of CASE statement on profit loss caclulation. Refer the code below for the same,

, 
   (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
	- (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
   AS TOTAL_PROFIT_LOSS
, 
CASE WHEN (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
			- (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
          ) > 0
     THEN  
         (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
			- (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
          )    
    END
   AS TOTAL_PROFIT
, 
CASE WHEN (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
			- (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
          ) < 0
     THEN  
         (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
			- (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
          )    
    END
   AS TOTAL_LOSS

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Marked As Answer by nikijain Thursday, February 23, 2012 6:57 AM

Thursday, February 23, 2012 6:57 AM

0

Thanks for Reply See amount is coming fine but that amount is of Loss So it should come in Loss Column Because calcultaing his Profit/loss user is getting loss

You can calculate from second result

kshama

Thursday, February 23, 2012 7:17 AM

0

Hi,

Please see the code below,

, 
  (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
   AS TOTAL_PROFIT_LOSS
, 
CASE WHEN (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
			   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
          ) > 0
     THEN  
         (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
			   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
          )    
    END
   AS TOTAL_PROFIT
, 
CASE WHEN (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
			   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
          ) < 0
     THEN  
         (
			(SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE < B.RATES THEN (B.RATES-A.RATE)*A.QUANTITY ELSE 0 END)))))
			   - (SUM(CONVERT(DECIMAL(38,2),((   CASE WHEN A.RATE > B.RATES THEN (A.RATE-B.RATES)*A.QUANTITY ELSE 0 END))))) 
          )    
    END
   AS TOTAL_LOSS

If the issue is related to data its very hard to give exact query. Please try with minor changes in the query if required before you revert back.

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Marked As Answer by nikijain Thursday, February 23, 2012 7:23 AM

Thursday, February 23, 2012 7:23 AM

0

Great Work... Now I got the result finally what I need..Thanks Suresh so much For your help and for your Support..I have never returned without result from this forum..Thanks

kshama

Marked As Answer by nikijain Thursday, February 23, 2012 7:25 AM
Unmarked As Answer by nikijain Thursday, February 23, 2012 7:25 AM

Not getting the distinct record

All Replies