Query for Last 20 Weekdays 2

[RJL1]

Hi,

I have a query I been working on and finnaly got it to pull the last 20 days, so the query returns from today 20 days back. My porblem is that I need the last 20 weekdays. Currently the script is counting weekend days so if I run the script today I get 13 weekdays (Mon -Fri) and I really need the last 20 weekdays so no matter when the script runs it should always return the last 20 days

Here isthe code I have so far

SELECT
CONVERT (VARCHAR(30),L.DATE_TIME_STAMP,101) AS 'LAUNCH_DATE',
DATENAME(DW,L.DATE_TIME_STAMP) AS 'WEEKDAY',
COUNT(DISTINCT SD.SHIPMENT_ID) AS 'ORDERS',
COUNT(SD.ERP_ORDER_LINE_NUM) AS 'LINES',
SUM(SD.TOTAL_QTY) AS 'UNITS',
SUM(dbo.adcMR_UWT_GetCaseQty(SD.ITEM,SD.QUANTITY_UM,SD.TOTAL_QTY)) AS 'PK2CS'
FROM
LAUNCH_STATISTICS L WITH(NOLOCK)
  JOIN SHIPMENT_DETAIL SD ON SD.LAUNCH_NUM = L.INTERNAL_LAUNCH_NUM
WHERE
CONVERT (VARCHAR(30),L.DATE_TIME_STAMP,101) >= DATEADD(DAY, -20, CONVERT (VARCHAR(30),GETDATE(),101))
GROUP BY
CONVERT (VARCHAR(30),L.DATE_TIME_STAMP,101),
DATENAME(DW,L.DATE_TIME_STAMP)
ORDER BY
CONVERT (VARCHAR(30),L.DATE_TIME_STAMP,101)

Thanks in advance for any help
RJL1

 

[RiverGuy]

There will really be no elegant way to accomplish this. I would recommend adding a Date table to your database. You'll have a much easier time with this and other queries.

Here's an example script to populate a sample Date table. Your real table would include more columns such as Year, Day of Week, Quarter, etc. But this sample gives you the Date, Month and Weekday/Weekend:

CREATE TABLE DateTable 
(TheDate DATETIME PRIMARY KEY,
 TheMonth VARCHAR(20),
IsWeekDay BIT)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '20000101'
SELECT @EndDate = '20201231'
WHILE @StartDate <= @EndDate
BEGIN
	INSERT INTO DateTable SELECT @StartDate, DATENAME(month, @StartDate) + ' ' + DATENAME(year, @StartDate),
	CASE
		WHEN DATENAME(DW, @StartDate) IN ('Saturday', 'Sunday') THEN 0
		ELSE 1
	END 
	SELECT @StartDate = DATEADD(day, 1, @StartDate )
END

With that example, you could easily get the first date in a 20-weekday-back scenario:

SELECT TOP 1 * FROM (SELECT TOP 20 TheDate FROM DateTable WHERE TheDate <= GETDATE() AND IsWeekDay = 1 ORDER BY TheDate DESC) a ORDER BY TheDate ASC

 

[simian101]

Pretty good article on a Calendar table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi