I am retrieving data from a view for a dashboard and I need some help with my query to retrieve yesterday's data where the following conditions meet:
When 'Monday' retrieve JUST friday's data. When 'Tuesday' retrieve data from Saturday until Monday, When 'other days' JUST retrieve previous day,
But there is another condition: Exclude 'previous day' data WHERE "DESP...' is empty. (as this will skew the percentages)
This is what I have so far:
SELECT CORD_DocumentCode
,OpenDate
,datedue
,DESP_PostedDate
,COUNT(CORD_DocumentCode) AS Order_Count
,SUM(CASE WHEN InFullAndOneTime = 2 THEN 1 ELSE 0 END) AS Difot_count
,SUM(CASE WHEN InFullAndOneTime = 2 THEN 1 ELSE 0 END) * 1.0 /COUNT(CORD_DocumentCode) AS DIFOT
FROM DIFOTIS_View
WHERE (OpenDate >= CASE WHEN DATENAME(dw, CONVERT(CHAR(8), GETDATE(), 112)) LIKE 'Monday' THEN CONVERT(CHAR(8), DATEADD(dd, - 2, GETDATE()), 112)
WHEN DATENAME(dw, CONVERT(CHAR(8), GETDATE(), 112)) LIKE 'Tuesday' THEN CONVERT(CHAR(8), DATEADD(dd, - 3, GETDATE()), 112) ELSE CONVERT(CHAR(8),
DATEADD(dd, - 1, GETDATE()), 112) END) --AND) (OpenDate < CONVERT(CHAR(8), GETDATE(), 112))
Group by OpenDate,CORD_DocumentCode, datedue, DESP_PostedDate
order by OpenDate
This will show you yesterday's data as today is 'Wednesday' and the result is 15.4 % (or 4/26) whereas I would like it to show 80% (or 4 / 5)
I have tried adding
((Datediff(day,Opendate,getdate())=1) and DESP_PostedDate is not null)
but it shows then only data from the 21Oct.
What do you suggest I should do? Please find below Data from friday for your perusal.
All help is appreciated. Thanks
Eric
CORD_DocumentCode OpenDate datedue DESP_PostedDate Order_Count Difot_count DIFOT
CASW92195 2014-10-19 2014-10-19 2014-10-20 1 1 1.000000000000
CASW92196 2014-10-19 2014-10-19 2014-10-20 1 1 1.000000000000
CASW92197 2014-10-19 2014-10-19 2014-10-20 1 1 1.000000000000
CASW92198 2014-10-19 2014-10-19 2014-10-20 1 1 1.000000000000
CASW92199 2014-10-19 2014-10-19 2014-10-20 1 1 1.000000000000
CASW92200 2014-10-19 2014-10-19 NULL 1 0 0.000000000000
CASW92201 2014-10-20 2014-10-20 2014-10-20 1 1 1.000000000000
CASW92202 2014-10-20 2014-10-20 2014-10-20 1 1 1.000000000000
CASW92203 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CASW92204 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CASW92205 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CASW92206 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CORD37188 2014-10-20 2014-10-20 NULL 1 0 0.000000000000
CORD37189 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37190 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37191 2014-10-20 2014-10-20 2014-10-21 1 0 0.000000000000
CORD37192 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37193 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37195 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37196 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37197 2014-10-20 2014-10-20 2014-10-21 1 0 0.000000000000
CORD37198 2014-10-20 2014-10-20 2014-10-21 1 0 0.000000000000
CORD37199 2014-10-20 2014-10-20 2014-10-20 1 0 0.000000000000
CORD37200 2014-10-20 2014-10-20 NULL 1 0 0.000000000000
CORD37211 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CORD37216 2014-10-20 2014-10-20 2014-10-21 1 1 1.000000000000
CASW92207 2014-10-21 2014-10-21 2014-10-21 1 1 1.000000000000
CASW92208 2014-10-21 2014-10-21 2014-10-21 1 1 1.000000000000
CASW92209 2014-10-21 2014-10-21 2014-10-21 1 1 1.000000000000
CASW92210 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CASW92211 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CASW92212 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CASW92213 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37218 2014-10-21 2014-10-21 2014-10-21 1 1 1.000000000000
CORD37220 2014-10-21 2014-10-20 2014-10-21 1 0 0.000000000000
CORD37221 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37222 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37225 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37227 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37228 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37229 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37230 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37231 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37232 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37233 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37234 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37235 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37236 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37237 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37238 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37239 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
CORD37241 2014-10-21 2014-10-21 NULL 1 0 0.000000000000
It took me a while but I got this sorted out with the help of Shiv. from another forum. I am posting the answer here for future reference.
/*Main Query */
declare @ReportDate datetime = getdate()
SELECT COUNT(CORD_DocumentCode) as [count], SUM(CASE WHEN InFullAndOneTime = 2 THEN 1 ELSE 0 END) AS Difot_count,
SUM(CASE WHEN InFullAndOneTime = 2 THEN 1 ELSE 0 END) * 1.0 / COUNT(CORD_DocumentCode) AS DIFOT
FROM DIFOTIS_View
WHERE cast(datepart(dw,OpenDate) as varchar) in
(SELECT * FROM dbo.CSVToTable(Case
when datepart(dw,@ReportDate) =7 and DESP_PostedDate is NOT null then '6'
when datepart(dw,@ReportDate) =6 and DESP_PostedDate is NOT null then '5'
when datepart(dw,@ReportDate) =5 and DESP_PostedDate is NOT null then '4'
when datepart(dw,@ReportDate) =4 and DESP_PostedDate is NOT null then '3'
when datepart(dw,@ReportDate) =2 and DESP_PostedDate is NOT null then '6'
when datepart(dw,@ReportDate) =3 and DESP_PostedDate is NOT null then '7,1,2'
when datepart(dw,@ReportDate) =1 and DESP_PostedDate is NOT null then '7' Else NULL END)) and opendate>=DATEADD(day, -7,@ReportDate)
/* Function Creation*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id int not null)
AS
BEGIN
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END
GO
Cheers H