Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Group By | Remove NULL Puzzle ] – In this puzzle we have to group by data using Service Type and UOM. Per say for a service type if we have NULL Uom then we have drop that row and add their Cost with the Non blank service type and take max Usage from both of them . Please check out the sample input and expected output for details.

Sample Input

ID ServiceType UOM Cost Usage
7 Electric Therms 10 100
8 Electric NULL 20 0
9 Water Litre 30 100
10 Water Gallons 40 100
11 Water NULL 25 0
12 Water Gallon 20 130

Expected Output

UOM ServiceType Cost Usage
Therms Electric 30 100
Gallon Water 20 130
Gallons Water 40 100
Litre Water 55 100

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--


CREATE TABLE UOMs
(
ID INT IDENTITY(1,1)
,ServiceType VARCHAR(100)
,UOM VARCHAR(10) NULL
,Cost INT
,Usage INT
)
GO

INSERT INTO UOMs(ServiceType,UOM,Cost,Usage)
VALUES
('Electric','Therms',10,100),
('Electric',NULL,20,0),
('Water','Litre',30,100),
('Water','Gallons',40,100),
('Water',NULL,25,0),
('Water','Gallon',20,130)

----

UPDATE – 20-Apr-2015 – Solution 1


--


--Solution 1

SELECT Ar.NewUOM UOM , ServiceType, SUM(Ar.Cost) Cost , SUM(Ar.Usage) Usage FROM 
(
       SELECT u.ID, u.Cost,u.ServiceType,u.Usage
       ,CASE WHEN UOM IS NULL THEN 
				( SELECT TOP 1 UOM FROM UOMs u1 WHERE u.ServiceType = u1.ServiceType AND u1.UOM IS NOT NULL ) 
			 ELSE
			    UOM 
	   END NewUOM 
       FROM UOMs u
) Ar
GROUP BY Ar.NewUOM , ServiceType

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements