| View previous topic :: View next topic |
| Author |
Message |
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
 Posted: Thu May 27, 2004 10:56 am Post subject: Number of Saturdays in a MonthYear |
 |
|
Does anyone have a SQL Db2 solution for this ? The tricky part is the fact that this count changes based on the year .
The following data would be available :
1) Month Year
2) Calander days in a month
3) Start , end date of month
One way I can think of , which is probably not the most efficient way and that is to start with the First of the month date and using DAYOFWEEK() function look for 7 (for saturday). Do this repeatedly in a Loop .. add 7 to the start date and continue adding to subsequent dates until you reach the end of the month date. AFter having said this , am not even sure if it makes sense , as I havent tried this.
I was hoping if I could do this in just one pass .i.e a single execution of an SQL. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 11:07 am Post subject: |
|
|
Vini,
Assuming that you have a date field in your table,the following SQL will give you the desired results.
| Code: |
SELECT COUNT(*)
FROM TABLE
WHERE DAYOFWEEK(TAB_DATE) = 7
AND MONTH(TAB_DATE) = 1
AND YEAR(TAB_DATE) = 2004
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Thu May 27, 2004 12:33 pm Post subject: |
|
|
Kolusu ,
I think my question was not clear ... I will try to explain further .... the Input Data here is a variable YearMonth (YYYYMM) , which could be just any ...like 200012 ,200103, 200409 , 200510 ... and its available in a host variable ( WSH-YEARMONTH )
| Code: |
Variable Input(s) Expected Output (Total Saturdays in the month)
200012 5
200112 5
200212 4
200401 4
200305 5
|
Other data which are available to the program to aid in the solution , if need be , are the total calendar days in the month , the start and end date of month.
Vini |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu May 27, 2004 2:47 pm Post subject: |
|
|
If you can use dynamic SQL to set up the date, this might work.
Assume that the input date is 200308, then
| Code: |
SELECT
(DAY(DATE('08/01/2003') + 1 MONTH - 1 DAY) -- DAYS IN MONTH
+ DAYOFWEEK(DATE('08/01/2003')) -- ADJUST FOR 1ST DOW
)
/ 7 -- DIVIDE BY WEEKS
FROM SYSIBM.SYSDUMMY1
|
I ran a couple of tests and it seems to work. |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu May 27, 2004 2:48 pm Post subject: |
|
|
Actual code is:
| Code: |
SELECT
(DAY(DATE('08/01/2003') + 1 MONTH - 1 DAY)
+ DAYOFWEEK(DATE('08/01/2003'))
)
/ 7
FROM SYSIBM.SYSDUMMY1
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 3:03 pm Post subject: |
|
|
Bithead,
Excellent solution !!! I was able to solve it but it is way to complicated.See the bottom for my solution. It is ugly as hell  . However if you have db2 version 7.1 then you can use LAST_DAY function instead adding 1 month and subtraction 1 day.
| Code: |
SELECT
(DAY(LAST_DAY('2004-05-01'))
+ DAYOFWEEK(DATE('2004-05-01'))) / 7
FROM SYSIBM.SYSDUMMY1
;
|
My trial sql
| Code: |
SELECT
SUM(CASE WHEN B.D1 = 05 THEN 1 ELSE 0 END) +
SUM(CASE WHEN B.D2 = 05 THEN 1 ELSE 0 END) +
SUM(CASE WHEN B.D3 = 05 THEN 1 ELSE 0 END) +
SUM(CASE WHEN B.D4 = 05 THEN 1 ELSE 0 END) +
SUM(CASE WHEN B.D5 = 05 THEN 1 ELSE 0 END)
FROM (
SELECT MONTH(DATE('2004-05-01') + A.WNUM DAYS) AS D1
,MONTH(DATE('2004-05-01') + A.WNUM DAYS + 07 DAYS) AS D2
,MONTH(DATE('2004-05-01') + A.WNUM DAYS + 14 DAYS) AS D3
,MONTH(DATE('2004-05-01') + A.WNUM DAYS + 21 DAYS) AS D4
,MONTH(DATE('2004-05-01') + A.WNUM DAYS + 28 DAYS) AS D5
FROM (SELECT (CASE DAYOFWEEK('2004-05-01')
WHEN 1 THEN 6
WHEN 2 THEN 5
WHEN 3 THEN 4
WHEN 4 THEN 3
WHEN 5 THEN 2
WHEN 6 THEN 1
WHEN 7 THEN 0
END) AS WNUM
FROM SYSIBM.SYSDUMMY1) A ) B
;
|
Even though it gives the desired results, I wasn't happy with it at all.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu May 27, 2004 3:05 pm Post subject: |
|
|
| Do I get my missing 5 points back? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 3:08 pm Post subject: |
|
|
Bithead,
I would say get ready for your acceptance speech for "Member of the Month" award 
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Thu May 27, 2004 3:27 pm Post subject: |
|
|
Awesome 8) .just when I had given up hope , here is just what I was looking for . Thanks a ton !!!
|
|
| Back to top |
|
|
MAHADEVAN
Beginner
Joined: 28 May 2004
Posts: 1
Topics: 0
Location: India
|
| Posted: Fri May 28, 2004 6:04 am Post subject: |
|
|
Bithead,
Could anyone just explain how the above code works
Thanks in advance,
_________________
Lifes Like That... |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Fri May 28, 2004 7:18 am Post subject: |
|
|
Mahadevan,
The following calculates the days in the month:
(DAY(DATE('08/01/2003') + 1 MONTH - 1 DAY)
DATE('08/01/2003') + 1 MONTH will give 09/01/2003 so we need to subtract 1 day.
As Kolusu points out, you could simply use
(DAY(LAST_DAY('08/01/2003'))
As the DAYOFWEEK value for a Saturday is 7, we can simply divide the number of days in the month by 7 to get the number of full weeks in the month and hence the number of Saturdays. (This won't work for any other day of the week). This would be fine if every month started on a Sunday, but it doesn't, so we need to "bump" it along a few days by adding the DAYOFWEEK for the first day of the month.
+ DAYOFWEEK(DATE('08/01/2003'))
It helps if you also look at a calendar while thinking about this one. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
