Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Server 2012 Analysis Services, SQL Server Data Tools, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
;with cte
as
(
select * ,ROW_NUMBER() over (partition by serviceType order by id) as rwnum
from #UOMs
)
,cte1
as
(
select a.ServiceType,a.Cost,a.Usage,coalesce(a.UOM,b.UOM)as UOM from cte a
join cte b
on a.ServiceType=b.ServiceType
where b.rwnum=1
)
select UOM,ServiceType,SUM(cost) as Cost,MAX(usage)as Usage
from cte1
group by UOM,ServiceType
LikeLiked by 1 person
Excellent Sanjay as usual !!
LikeLiked by 1 person
SELECT SERVICETYPE,UOM,COST,USAGE1 FROM UOMs WHERE UOM IS NULL;
SELECT * FROM (
WITH T AS (
SELECT SERVICETYPE,UOM,COST,USAGE1,ROW_NUMBER()OVER (PARTITION BY SERVICETYPE) AS RN FROM UOMs WHERE UOM IS NOT NULL
UNION
SELECT SERVICETYPE,UOM,COST,USAGE1,1 FROM UOMs WHERE UOM IS NULL)
SELECT UOM,SERVICETYPE,SUM(COST) OVER(PARTITION BY RN,SERVICETYPE) AS COST,
SUM(USAGE1) OVER(PARTITION BY RN,SERVICETYPE) AS USAGE1 FROM T) A WHERE A.UOM IS NOT NULL;
LikeLike