I'm a newbie, sorry if this seems stupid, but can anyone help me, Ive the following query:
select PBDate, Datename(WeekDay, PBDate) as MyDay, Station, sum(CASE WHEN NG IS NULL THEN bcount ELSE 0 END) as Good, sum(CASE WHEN NG is not null THEN 1 ELSE 0 END) as [No Good]
from ProcessDetails where PBDate between '07/16/2018' and '07/20/2018'
group by PBDate, STATION
Select
Station,
Sum([G_Monday]) As [Good Monday],
Sum([NG_Monday]) As [No Good Monday],
Sum([G_Tuesday]) As [Good Tuesday],
Sum([NG_Tuesday]) As [No Good Tuesday],
Sum([G_Wednesday]) As [Good Wednesday],
Sum([NG_Wednesday]) As [No Good Wednesday],
Sum([G_Thursday]) As [Good Thursday],
Sum([NG_Thursday]) As [No Good Thursday],
Sum([G_Friday]) As [Good Friday],
Sum([NG_Friday]) As [No Good Friday]
From
(
Select
'G_' + Datename(WeekDay, PBDate) as MyDayGood,
'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood,
Station,
CASE WHEN NG IS NULL THEN bcount ELSE 0 END as Good,
CASE WHEN NG is not null THEN 1 ELSE 0 END as [NoGood]
from @ProcessDetails
where PBDate between '07/16/2018' and '07/20/2018'
) PD
Pivot
(
Sum(Good) For MyDayGood In ([G_Monday],[G_Tuesday],[G_Wednesday],[G_Thursday],[G_Friday])
) PVT_GOOD
Pivot
(
Sum([NoGood]) For MyDayNoGood In ([NG_Monday],[NG_Tuesday],[NG_Wednesday],[NG_Thursday],[NG_Friday])
) PVT_NOGOOD
Group By Station
Wow! Thank you very much AndyC, you rock! The only thing I'm still trying to understand is why it is not returning 8 on station SMALL for the NG Monday, please check out the results:
Station
Good Monday
No Good Monday
Good Tuesday
No Good Tuesday
Good Wednesday
No Good Wednesday
Good Thursday
No Good Thursday
Good Friday
No Good Friday
SMALL
90
1
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
THEND
NULL
NULL
534
0
26
0
NULL
NULL
NULL
NULL
THONE
142
0
414
0
NULL
NULL
NULL
NULL
NULL
NULL
THWORK
NULL
NULL
75
0
NULL
NULL
NULL
NULL
NULL
NULL
If I run the the query:
Select
'G_' + Datename(WeekDay, PBDate) as MyDayGood,
'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood,
Station,
CASE WHEN NG IS NULL THEN bcount ELSE 0 END as Good,
CASE WHEN NG is not null THEN 1 ELSE 0 END as [NoGood]
from @ProcessDetails
where PBDate between '07/16/2018' and '07/20/2018'
The result is correct, 8 records with 1 on No Good, but it seems is not doing the sum!
Can you please share what it is happening, thank you so much for your kind help.
Select
'G_' + Datename(WeekDay, PBDate) as MyDayGood,
'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood,
Station,
sum(CASE WHEN NG IS NULL THEN bcount ELSE 0 END) as Good,
sum(CASE WHEN NG is not null THEN 1 ELSE 0 END) as [NoGood]
from @ProcessDetails
where PBDate between '07/16/2018' and '07/20/2018'
group by PBDate,station
If not, you might need to post some sample data (preferably as some SQL inserts) so we can see what data you're working with
I am practicing my SQL
Also I have given a different solution
If it helps GREAT
I have added a "rn" column in the create data
to order by the station
drop create data
use tempdb
go
drop table data
go
create table data
(
rn int,
PBDate date ,
MyDay varchar(100) ,
Station varchar(100) ,
Good int ,
NoGood int
)
go
insert into data select 1,'07/16/2018','Monday','THONE',142,0
insert into data select 2,'07/17/2018','Tuesday','THEND',534,0
insert into data select 3,'07/16/2018','Monday','SMALL',90,8
insert into data select 2,'07/18/2018','Wednesday','THEND',26,0
insert into data select 4,'07/17/2018','Tuesday','THWORK',75,0
insert into data select 1,'07/17/2018','Tuesday','THONE',414,0
go
select * from data
go
SQL
SELECT rn,
station,
Sum(( CASE
WHEN myday = 'Monday' THEN good
ELSE 0
END )) AS monday_good,
Sum(( CASE
WHEN myday = 'Monday' THEN nogood
ELSE 0
END )) monday_nogood,
Sum(( CASE
WHEN myday = 'Tuesday' THEN good
ELSE 0
END )) tuesday_good,
Sum(( CASE
WHEN myday = 'Tuesday' THEN nogood
ELSE 0
END )) tuesday_nogood,
Sum(( CASE
WHEN myday = 'Wednesday' THEN good
ELSE 0
END )) wednesaday_good,
Sum(( CASE
WHEN myday = 'Wednesday' THEN nogood
ELSE 0
END )) wednesday_nogood,
Sum(( CASE
WHEN myday = 'Thursday' THEN good
ELSE 0
END )) thursday_good,
Sum(( CASE
WHEN myday = 'Thursday' THEN nogood
ELSE 0
END )) thursday_nogood,
Sum(( CASE
WHEN myday = 'Friday' THEN good
ELSE 0
END )) friday_good,
Sum(( CASE
WHEN myday = 'Friday' THEN nogood
ELSE 0
END )) friday_nogood
FROM data
GROUP BY rn,
station
ORDER BY rn
go