May 4, 2018 at 12:33 am
Hi All,
I have a statement returning data from today up to 28 days ahead, but it is including weekends. I only want business days, how do I achieve this ?
select [MFLITM],[MFMCU]
,CASE WHEN SUM([MFUORG]) > 0
THEN SUM([MFUORG]) END as [Forecast]
FROM [dbo].[OOS_Asia_Forecast]
WHERE Normal_Date >=DATEADD(day, DATEDIFF(day,0,GETDATE()),0)
AND Normal_Date <= DATEADD(day, DATEDIFF(day,0,GETDATE())+28,0)
GROUP BY [MFLITM],[MFMCU]
Thanks for any assistance.
May 4, 2018 at 1:37 am
This query filters out the weekends
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 100;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3)
,SAMPLE_DATA AS
(
SELECT
NM.N AS SD_RID
,DATEADD(DAY,NM.N,GETDATE() - 15) AS SD_DATE
,1 + (ABS(CHECKSUM(NEWID())) % 3 ) AS SD_GROUP
,ABS(CHECKSUM(NEWID())) % 1000 AS SD_VALUE
FROM NUMS NM
)
SELECT
SD.SD_GROUP
,SUM(SD.SD_VALUE) AS [Forecast]
--,SD.SD_RID
--,SD.SD_DATE
FROM SAMPLE_DATA SD
WHERE SD.SD_DATE >= CONVERT(DATE,GETDATE(),0)
AND SD.SD_DATE < CONVERT(DATE,GETDATE() + 29,0)
AND (DATEDIFF(DAY,0,SD.SD_DATE) % 7) < 5
GROUP BY SD.SD_GROUP;
May 4, 2018 at 4:45 pm
Thanks for the reply, but how do i apply your cte's to my statement?
May 5, 2018 at 12:57 am
ringovski - Friday, May 4, 2018 4:45 PMThanks for the reply, but how do i apply your cte's to my statement?
The CTE is only there to provide sample data, it is the WHERE clause that does the trick.
😎
WHERE SD.SD_DATE >= CONVERT(DATE,GETDATE(),0)
AND SD.SD_DATE < CONVERT(DATE,GETDATE() + 29,0)
AND (DATEDIFF(DAY,0,SD.SD_DATE) % 7) < 5
May 5, 2018 at 3:34 am
Thanks a lot for the script. Can you explain the logic behind it so i can understand it?
May 5, 2018 at 3:47 am
ringovski - Saturday, May 5, 2018 3:34 AMThanks a lot for the script. Can you explain the logic behind it so i can understand it?
The logic for the weekdays is that date 0 is a Monday, modulus 7 of the number of dates from date 0 will return 5 for Saturdays and 6 for Sundays, hence limit the results to the set where that number is lower than 5 will return working (non weekend) days.
The conversion of getdate to a date removes the time part which simplifies the temporal framing of the query and adding 29 days allows us to use less than rather than less or equal to, slightly better performance.
😎
May 5, 2018 at 11:24 am
Eirikur Eiriksson - Saturday, May 5, 2018 3:47 AMringovski - Saturday, May 5, 2018 3:34 AMThanks a lot for the script. Can you explain the logic behind it so i can understand it?The logic for the weekdays is that date 0 is a Monday, modulus 7 of the number of dates from date 0 will return 5 for Saturdays and 6 for Sundays, hence limit the results to the set where that number is lower than 5 will return working (non weekend) days.
The conversion of getdate to a date removes the time part which simplifies the temporal framing of the query and adding 29 days allows us to use less than rather than less or equal to, slightly better performance.
😎
This part of Eirikur's code replaces a WHILE Loop Counter using the "Pseudo Cursor" behind every SELECT to produce a sequence of numbers. The sample size variable limits its scope.
DECLARE @SAMPLE_SIZE INT = 100;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3)
To learn more about this powerful tool that is used as a bit of a high performance "Swiss Army Knife of SQL", please see the following article. Eirikur is using the "Cascading CTE" method that was (to the best of my knowledge), first published by Itzik Ben-Gan.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
The reason why he generates the dates instead of the dates available in the original table is to cover the eventuality of having a weekday date with no entries, which should be reported as having a "zero sum".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2018 at 5:41 am
Do you need "business days" or "week days"?
There are public holidays which are not business days, and might not fall on weekends.
_____________
Code for TallyGenerator
May 6, 2018 at 7:26 am
mmm good point, didn't think of that not sure I will find out tomorrow.
May 6, 2018 at 7:39 am
If you need to include holidays on top of the weekends, you will need to incorporate a calendar table.
😎
May 7, 2018 at 8:28 am
And having a calendar table in your possession, you don't need to count Sat-Sun switches, you simply count the days marked as business days in the calendar.
_____________
Code for TallyGenerator
May 7, 2018 at 1:21 pm
In any contiguous 28-day period, there will always be exactly 20 non-weekend dates and 8 weekend dates.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 8, 2018 at 12:23 am
The business can't give me a clear answer on what they are doing. Old spreadsheets they have using for so many years and no one knows 🙂 Thanks for all the replies
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply