Hi
I currently when i run my query i get the following output.
Current
year
Weeks
Won
Lost
2017
48
4
NULL
2017
49
NULL
2
2017
49
7
NULL
2017
50
NULL
8
2017
50
19
NULL
As you can see,the WON and LOST result of the same week falls on different rows and I would like them to appear in one row and have and additional column for total for that week than an average column of WON/Total
Query
SELECT DATEPART(yy,SignalDATE) AS year,DATEPART( wk, Signaldate) as Weeks,
CASE
WHEN Result='Won' THEN
count(result)
END AS Won,
CASE
WHEN Result='Lost' THEN
count(result)
END AS Lost
FROM [BinarySignals]
JOIN Assets ON Assets.AssetId=BinarySignals.AssetId
JOIN Results ON Results.ResultId=BinarySignals.ResultId
GROUP BY DATEPART( wk, Signaldate),DATEPART(yy,SignalDATE),result
ORDER BY DATEPART(yy,SignalDATE),DATEPART( wk, Signaldate)
Desired: Won/Lost to appear in one row if they are in the same week, add total column then divide Won by total to get percentage
Try this (I'm guessing SignalDATE is located in table BinarySignals):
SELECT DATEPART(yy,s.SignalDATE) AS year
,DATEPART(wk,s.SignalDATE) as Weeks
,CASE WHEN r.Result='Won' THEN 1 ELSE 0 END AS Won
,CASE WHEN r.Result='Lost' THEN 1 ELSE 0 END AS Lost
FROM BinarySignals AS s
INNER JOIN Assets AS a
ON a.AssetId=s.AssetId
INNER JOIN Results AS r
ON r.ResultId=s.ResultId
GROUP BY DATEPART(yy,s.SignalDATE)
,DATEPART(wk,s.SignalDATE)
ORDER BY DATEPART(yy,s.SignalDATE)
,DATEPART(wk,s.SignalDATE)
;
create table harish_temp
(
year int null,
weeks int null,
won int null,
Lost int null
)
go
INSERT INTO harish_temp VALUES (2017,48,4,null)
INSERT INTO harish_temp VALUES (2017,49,null,2)
INSERT INTO harish_temp VALUES (2017,49,7,NULL)
INSERT INTO harish_temp VALUES (2017,50,NULL,8)
INSERT INTO harish_temp VALUES (2017,50,19,NULL)
go
My Solution
SELECT year,
weeks,
Min(won)
AS won,
Isnull(Min(lost), 0)
AS lost,
Min(won) + Isnull(Min(lost), 0)
AS total,
Cast(( Min(won) * 1.0 / ( Min(won) + Isnull(Min(lost), 0) ) ) * 100 AS
INT) AS
AVG
FROM harish_temp
GROUP BY year,
weeks
SELECT DATEPART(yy,SignalDATE) AS year,DATEPART( wk, Signaldate) as Weeks,
SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) AS Won,
SUM(CASE WHEN Result='Lost' THEN 1 ELSE 0 END) AS Lost,
SUM(1) AS Total,
CAST(SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) * 100.0 / SUM(1) AS decimal(4, 1)) AS Avg
FROM [BinarySignals]
JOIN Assets ON Assets.AssetId=BinarySignals.AssetId
JOIN Results ON Results.ResultId=BinarySignals.ResultId
GROUP BY DATEPART( wk, Signaldate),DATEPART(yy,SignalDATE),result
ORDER BY DATEPART(yy,SignalDATE),DATEPART( wk, Signaldate)