Run a query to create a desired output

1

Hello,

The following query creates a table name Demo_Table.

CREATE TABLE Demo_Table							
(								
Number nvarchar(20), Fa nvarchar(20), Qty numeric(19, 2), AvgPrice numeric(19, 2), Pr numeric(19, 2)								
)								
GO								
							
INSERT INTO Demo_Table								
VALUES								
(15, 'CMS',	1 ,	139.16,	266), (22, 'CMS',	2 ,	24.17,	35), (23, NULL,	10,	1.73,	11.25),				
(28, NULL,	1 ,	1.82,	11.25), (37, 'CMS',	1 ,	14.51,	38.5),					
(66, 'CMS',	1 ,	16.21,	38.5), (66, 'CMS',	3 ,	16.21,	38.5),					
(88, NULL,	10,	1.31,	8), (88, NULL,	50,	1.31,	8), (99, 'CMS',	1 ,	37.76,	74.5),					
(99, 'CMS',	2 ,	37.76,	74.5)					
GO

I am using the following query from the above table below..

SELECT  Number, Fa, SUM(Qty) AS Qty, SUM(AvgPrice) AS AvgPrice, SUM(Pr) AS PR FROM Demo_Table  GROUP BY Number , Fa ORDER BY Number								
GO

By running that query, the result is....

I need a query to give me the following result.

> Number	IfIsCMS(Y)	QtyIfIsCMS	2DaysAvgQtyForAll	Diff(C4 - C3)	AddCMS	AvgPrice	Pr
> 15	        Y	        1.00		0.50		        -0.50		--	139.16		266.00
> 22	        Y	        2.00		1.00		        -1.00		--	24.17		35.00
> 23	        N	        --		5.00		        --		Y	1.73		11.25
> 28	        N	        --		0.50		        --		Y	1.82		11.25
> 37	        Y	        1.00		0.50		        -0.50		--	14.51		38.50
> 66	        Y	        4.00		2.00		        -2.00		--	32.42		77.00
> 88	        N	        --		30.00              	--              Y	2.62		16.00
> 99	        Y	        3.00		1.50		        -1.50		--	75.52		149.00

Would be appreciated if you could help me.

--Praziani

2

@praziani

Here's the first 2 not sure what the calculation needs to be for the others.

DECLARE @Demo_Table table
   ( Number nvarchar(20)
   , Fa nvarchar(20)
   , Qty numeric(19, 2)
   , AvgPrice numeric(19, 2)
   , Pr numeric(19, 2)
   );								
							
INSERT INTO @Demo_Table
VALUES
     (15, 'CMS', 1, 139.16, 266)
   , (22, 'CMS', 2, 24.17, 35)  
   , (23, NULL, 10, 1.73, 11.25)
   , (28, NULL, 1, 1.82, 11.25) 
   , (37, 'CMS', 1, 14.51, 38.5)
   , (66, 'CMS', 1, 16.21, 38.5)
   , (66, 'CMS', 3, 16.21, 38.5)
   , (88, NULL, 10, 1.31, 8)    
   , (88, NULL, 50, 1.31, 8)    
   , (99, 'CMS', 1, 37.76, 74.5)
   , (99, 'CMS', 2, 37.76, 74.5);

SELECT 
   dt.Number
 , CASE WHEN dt.Fa ='CMS' THEN 'Y' ELSE 'N' END  [IfIsCMS(Y)]
 , CASE WHEN dt.Fa = 'CMS' THEN Convert(varchar(5), dt.Qty, 2) ELSE '--' END QtyIfIsCMS
 , dt.AvgPrice
 , dt.Pr
FROM
   @Demo_Table dt;