Reputation: 3301
I have data that looks like the following:
Cell Date Hr Min Value Kpi Exists
CELL1 20141112 8 45 1 KPI1 NULL
CELL1 20141112 8 45 2 KPI1 NULL
CELL1 20141112 8 45 7 KPI1 NULL
CELL1 20141112 8 45 7 KPI1 NULL
CELL2 20141112 8 0 5 KPI2 1
CELL2 20141112 8 15 2 KPI2 1
CELL2 20141112 8 30 9 KPI2 1
CELL2 20141112 8 45 7 KPI2 1
CELL3 20141112 8 0 1 KPI3 1
CELL3 20141112 8 15 3 KPI3 1
CELL3 20141112 8 30 4 KPI3 NULL
CELL3 20141112 8 45 6 KPI3 1
I would like to group by Cell, Date, HR, KPI in order to get SUM(Value), but I would like to somehow group column [Exists] so that if there's a NULL in the grouping, it will somehow differentiate the ones with NULLs from the ones without NULLs. In other words, if there's a NULL in any of the 4 quarters, then show it.
For example, the result would look something like this:
CELL3 20141112 8 14 NULL
CELL2 20141112 8 23 1
CELL1 20141112 8 17 NULL
This is the query I'm currently using to group the data, which works:
create table #temp
(
Cell varchar(10),
Date int,
Hr int,
Min int,
Value int,
Kpi varchar(10),
[Exists] bit
)
;
insert into #temp values ('CELL1' , 20141112 , 8 , 45 , 1 , 'KPI1' , NULL)
insert into #temp values ('CELL1' , 20141112 , 8 , 45 , 2 , 'KPI1' , NULL)
insert into #temp values ('CELL1' , 20141112 , 8 , 45 , 7 , 'KPI1' , NULL)
insert into #temp values ('CELL1' , 20141112 , 8 , 45 , 7 , 'KPI1' , NULL)
insert into #temp values ('CELL2' , 20141112 , 8 , 0 , 5 , 'KPI1' , 1 )
insert into #temp values ('CELL2' , 20141112 , 8 , 15 , 2 , 'KPI1' , 1 )
insert into #temp values ('CELL2' , 20141112 , 8 , 30 , 9 , 'KPI1' , 1 )
insert into #temp values ('CELL2' , 20141112 , 8 , 45 , 7 , 'KPI1' , 1 )
insert into #temp values ('CELL3' , 20141112 , 8 , 0 , 1 , 'KPI1' , 1 )
insert into #temp values ('CELL3' , 20141112 , 8 , 15 , 3 , 'KPI1' , 1 )
insert into #temp values ('CELL3' , 20141112 , 8 , 30 , 4 , 'KPI1' , NULL)
insert into #temp values ('CELL3' , 20141112 , 8 , 45 , 6 , 'KPI1' , 1 )
SELECT
Cell
, Date
, Hr
,sum(KPI1) as 'KPI1'
FROM #temp
PIVOT (SUM(Value)
FOR kpi in
(KPI1) ) AS pvt
GROUP BY Cell,
Date,
hr
Upvotes: 0
Views: 104
Reputation: 44931
Try this: case when min(coalesce([Exists],0)) = 0 then null else 1 end as [Exists]
The full query:
SELECT
Cell
,Date
,Hr
,sum(KPI1) as 'KPI1'
,case when min(coalesce([Exists],0)) = 0 then null else 1 end as [Exists]
FROM #temp
PIVOT (SUM(Value) FOR kpi in (KPI1)) AS pvt
GROUP BY Cell, Date, hr
With your sample data the output is:
Cell Date Hr KPI1 Exists
---------- ----------- ----------- ----------- -----------
CELL1 20141112 8 17 NULL
CELL2 20141112 8 23 1
CELL3 20141112 8 14 NULL
Upvotes: 2