April 19, 2006 at 2:30 am
Hi All
I have a problem trying to loop through my calendar table which has all dates and public holidays eg.
I want to include all records if it is a monday since Friday if this query is run the same would happend if it is a public holiday/s but if it was a normal weekday for example a tuesday then i only want to inlude everything since yesterday 7:00am in this case the monday.
snip on the calendar:
dt isWeekday isHoliday Y FY Q M D DW monthname dayname W HolidayDescription
------------------------------------------------------ --------- --------- ------ ------ ---- ---- ---- ---- --------- --------- ---- --------------------------------
2000-01-02 00:00:00 0 0 2000 2000 1 1 2 1 January Sunday 2 NULL
2000-01-03 00:00:00 1 0 2000 2000 1 1 3 2 January Monday 2 NULL
2000-01-04 00:00:00 1 0 2000 2000 1 1 4 3 January Tuesday 2 NULL
Here is the code
DECLARE @lastWorkingDay datetime
SELECT @lastWorkingDay = getdate()
WHILE (SELECT dbo.CALENDAR.isWeekday
FROM dbo.CALENDAR
WHERE CONVERT(varchar(8), dbo.CALENDAR.dt, 112) =CONVERT(varchar(8),getdate(), 112)) <> 1
BEGIN
SELECT COUNT(*) AS [COUNT],
dbo.DEC_TXN.CUSTOMER,
dbo.DEC_TXN.STATUS,
dbo.STATUS.STATUS_DESC
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON CONVERT(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = CONVERT(varchar(8), dbo.CALENDAR.dt, 112) INNER JOIN
dbo.STATUS ON dbo.DEC_TXN.STATUS = dbo.STATUS.STATUS
WHERE (dbo.DEC_TXN.DATE_LOAD >= DATEADD(hh, - 17, CONVERT(datetime, CAST(MONTH(@lastWorkingDay) AS char(2)) + '/' + CAST(DAY(@lastWorkingDay) AS char(2))
+ '/' + CAST(YEAR(@lastWorkingDay) AS char(4)), 101)))
GROUP BY dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS , dbo.STATUS.STATUS_DESC
ORDER by dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS
IF (SELECT dbo.CALENDAR.isWeekday
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON CONVERT(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = CONVERT(varchar(8), dbo.CALENDAR.dt, 112)
WHERE (CONVERT(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = CONVERT(varchar(8), GETDATE(), 112))) <> 0
BREAK
ELSE
SELECT @lastWorkingDay = @lastWorkingDay-1
CONTINUE
END
Any Help?
April 20, 2006 at 7:54 am
Hello,
You have to be a little more specific. What is it really you want to obtain as result? And what is your input? From your example query (that I assume does not work as you want it to) it is very hard to try to decipher what you ask us to do...
Happy hunting,
Hanslindgren
April 21, 2006 at 1:31 am
There seems to be something wrong i here i replied thrice already and nothing went through.
I seemed to have solved the problem halfway.
I removed the SELECT from the while loop and put it out side then i only put the criteria set on onside which increments the loop counting days backward until it reaches the first weekday, then it breaks to the query outside.
Only problem is that it now includes one day to much but works fine for normal week days.
Eg.Easter weekend here includes monday and friday as a holiday
DECLARE @today datetime
SELECT @today = '04/17/2006'
DECLARE @lastWorkingDay datetime
SELECT @lastWorkingDay ='04/16/2006'
WHILE ( (SELECT dbo.CALENDAR.isWeekday
FROM dbo.CALENDAR
WHERE CONVERT(varchar(8), dbo.CALENDAR.dt, 112) = CONVERT(varchar(8),@lastWorkingDay, 112)) <> 1 OR
(SELECT dbo.CALENDAR.isHoliday
FROM dbo.CALENDAR
WHERE CONVERT(varchar(8), dbo.CALENDAR.dt, 112) = CONVERT(varchar(8),@lastWorkingDay, 112)) <> 0)
The above code checks if today is not a weekday
then moves on to increment to the previous day in the loop
BEGIN
SET @today = @today-1
IF ( (SELECT dbo.CALENDAR.isWeekday
FROM dbo.CALENDAR
WHERE CONVERT(varchar(8), dbo.CALENDAR.dt, 112) = CONVERT(varchar(8),@today, 112)) = 1 AND
(SELECT dbo.CALENDAR.isHoliday
FROM dbo.CALENDAR
WHERE CONVERT(varchar(8), dbo.CALENDAR.dt, 112) = CONVERT(varchar(8),@today, 112)) = 0)
BREAK
ELSE
CONTINUE
END
The problem is that the loop needs to break out just before the last working it looped back to, it includes everything from the wed 07:00am before easter which it should not becasue this report is run daily. It was run for wed already the thursday before easter.
Its kinda difficult to wrap ur mind around this, just remember that if the query is run every working day it includes evrything from previous day 07:00am
hence:
SELECT @today
SELECT COUNT(*) AS [COUNT],
dbo.DEC_TXN.CUSTOMER,
dbo.DEC_TXN.STATUS,
dbo.STATUS.STATUS_DESC
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON CONVERT(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = CONVERT(varchar(8), dbo.CALENDAR.dt, 112) INNER JOIN
dbo.STATUS ON dbo.DEC_TXN.STATUS = dbo.STATUS.STATUS
WHERE (dbo.DEC_TXN.DATE_LOAD between DATEADD(hh, - 17, CONVERT(datetime, CAST(MONTH(@toDay) AS char(2)) + '/' + CAST(DAY(@today) AS char(2))
+ '/' + CAST(YEAR(@toDay) AS char(4)), 101))AND GETDATE())
GROUP BY dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS , dbo.STATUS.STATUS_DESC
ORDER by dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS
so i now need assistance to play around with either the variables or the timing of the calendar so that the weekends and the holidays are included.
it must only start from 7:00am the previous working day if it is a normal working day
AND
it must start including records from 7:00am the day before easter Friday (Thursday)
At the moment my query is including everthing from the previous wed. 07:00am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply