last 12 Business days starting from today

[sushmitha]

Can anybody help me in getting the last 12 business days data excluding weekends. Also the results should be group by each business day

I have a table with value field and date field
How can I find previous 12 days(only weekdays) value group by each day

 

[ByteMyzer]

Try something like the following (substitute highlighted table and field names as appropriate):

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]MyDateField[/i][/b] < CDate([Input Date])
AND T1.[b][i]MyDateField[/i][/b] >= CDate([Input Date])-17
AND Weekday(T1.[b][i]MyDateField[/i][/b]) NOT IN (1, 7)
ORDER BY T1.[b][i]MyDateField[/i][/b];

...where [Input Date] is the Input Parameter for the Date you wish to profile.

 

[raskew]

Hey ByteMyzer -

Nice solution!

However, you might want to take a look at that -17. It doesn't work across the board.

If you start on a Wednesday, Thursday or Friday, you'll use up a total of 16 days, whereas if you start on a Monday it'll be 18 or Tuesday, you'll exhaust 17 days.

[

[U]StartDay[/U]	[U]1stWk[/U]	[U]2ndWk[/U]	[U]3rdWk[/U]	[U]4thWk[/U]
Friday	4	5	3	0	         4+[COLOR="Blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+3 = 16
Thur	3	5	4	0	         3+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+4 = 16
Wed	2	5	5	0	         2+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+5 = 16
Tue	1	5	5	1	1+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+1 = 17
Mon	0	5	5	2	0+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+5+[COLOR="blue"]2[/COLOR]+2 = 18

Items in [COLOR="blue"]blue[/COLOR] = weekends

Best wishes - Bob

 

[ByteMyzer]

Good point. This revision should do the trick:

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]MyDateField[/i][/b] < CDate([Input Date])
AND T1.[b][i]MyDateField[/i][/b] >= CDate([Input Date])-
    Choose(Weekday(CDate([Input Date])), 17, 18, 18, 16, 16, 16, 16)
AND Weekday(T1.[b][i]MyDateField[/i][/b]) NOT IN (1, 7)
ORDER BY T1.[b][i]MyDateField[/i][/b];

 

[raskew]

Hi -

Looks good!

I'm embarassed by the formatting that was displayed. Created it in Notepad and it was perfect. Copied/pasted to this forum and it turned to crap.

If anyone has some ideas, I'm open to suggestion.

Best wishes - Bob