Recipe9.7.Creating a Calendar
Recipe 9.7. Creating a CalendarProblemYou want to create a calendar for the current month. The calendar should be formatted like a calendar you might have on your desk seven columns across, (usually) five rows down. SolutionEach solution will look a bit different, but they all solve the problem the same way: return each day for the current month, and then pivot on the day of the week for each week in the month to create a calendar. There are different formats available for calendars. For example, the Unix cal command formats the days from Sunday to Saturday. The examples in this recipe are based on ISO weeks, so the Monday through Friday format is the most convenient to generate. Once you become comfortable with the solutions, you'll see that reformatting however you like is simply a matter of modifying the values assigned by the ISO week before pivoting.
DB2Use the recursive WITH clause to return every day in the current month. Then pivot on the day of the week using CASE and MAX: 1 with x(dy,dm,mth,dw,wk) 2 as ( 3 select (current_date -day(current_date) day +1 day) dy, 4 day((current_date -day(current_date) day +1 day)) dm, 5 month(current_date) mth, 6 dayofweek(current_date -day(current_date) day +1 day) dw, 7 week_iso(current_date -day(current_date) day +1 day) wk 8 from t1 9 union all 10 select dy+1 day, day(dy+1 day), mth, 11 dayofweek(dy+1 day), week_iso(dy+1 day) 12 from x 13 where month(dy+1 day) = mth 14 ) 15 select max(case dw when 2 then dm end) as Mo, 16 max(case dw when 3 then dm end) as Tu, 17 max(case dw when 4 then dm end) as We, 18 max(case dw when 5 then dm end) as Th, 19 max(case dw when 6 then dm end) as Fr, 20 max(case dw when 7 then dm end) as Sa, 21 max(case dw when 1 then dm end) as Su 22 from x 23 group by wk 24 order by wk OracleUse the recursive CONNECT BY clause to return each day in the current month. Then pivot on the day of the week using CASE and MAX: 1 with x 2 as ( 3 select * 4 from ( 5 select to_char(trunc(sysdate,'mm')+level-1,'iw') wk, 6 to_char(trunc(sysdate,'mm')+level-1,'dd') dm, 7 to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw, 8 to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth, 9 to_char(sysdate,'mm') mth 10 from dual 11 connect by level <= 31 12 ) 13 where curr_mth = mth 14 ) 15 select max(case dw when 2 then dm end) Mo, 16 max(case dw when 3 then dm end) Tu, 17 max(case dw when 4 then dm end) We, 18 max(case dw when 5 then dm end) Th, 19 max(case dw when 6 then dm end) Fr, 20 max(case dw when 7 then dm end) Sa, 21 max(case dw when 1 then dm end) Su 22 from x 23 group by wk 24 order by wk PostgreSQLUse the function GENERATE_SERIES to return every day in the current month. Then pivot on the day of the week using MAX and CASE: 1 select max(case dw when 2 then dm end) as Mo, 2 max(case dw when 3 then dm end) as Tu, 3 max(case dw when 4 then dm end) as We, 4 max(case dw when 5 then dm end) as Th, 5 max(case dw when 6 then dm end) as Fr, 6 max(case dw when 7 then dm end) as Sa, 7 max(case dw when 1 then dm end) as Su 8 from ( 9 select * 10 from ( 11 select cast(date_trunc('month',current_date) as date)+x.id, 12 to_char( 13 cast( 14 date_trunc('month',current_date) 15 as date)+x.id,'iw') as wk, 16 to_char( 17 cast( 18 date_trunc('month',current_date) 19 as date)+x.id,'dd') as dm, 20 cast( 21 to_char( 22 cast( 23 date_trunc('month',current_date) 24 as date)+x.id,'d') as integer) as dw, 25 to_char( 26 cast( 27 date_trunc('month',current_date) 28 as date)+x.id,'mm') as curr_mth, 29 to_char(current_date,'mm') as mth 30 from generate_series (0,31) x(id) 31 ) x 32 where mth = curr_mth 33 ) y 34 group by wk 35 order by wk MysolUse table T500 to return each day in the current month. Then pivot on the day of the week using MAX and CASE: 1 select max(case dw when 2 then dm end) as Mo, 2 max(case dw when 3 then dm end) as Tu, 3 max(case dw when 4 then dm end) as We, 4 max(case dw when 5 then dm end) as Th, 5 max(case dw when 6 then dm end) as Fr, 6 max(case dw when 7 then dm end) as Sa, 7 max(case dw when 1 then dm end) as Su 8 from ( 9 select date_format(dy,'%u') wk, 10 date_format(dy,'%d') dm, 11 date_format(dy,'%w')+1 dw 12 from ( 13 select adddate(x.dy,t500.id-1) dy, 14 x.mth 15 from ( 16 select adddate(current_date,-dayofmonth(current_date)+1) dy, 17 date_format( 18 adddate(current_date, 19 -dayofmonth(current_date)+1), 20 '%m') mth 21 from t1 22 ) x, 23 t500 24 where t500.id <= 31 25 and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth 26 ) y 27 ) z 28 group by wk 29 order by wk SQL ServerUse the recursive WITH clause to return every day in the current month. Then pivot on the day of the week using CASE and MAX: 1 with x(dy,dm,mth,dw,wk) 2 as ( 3 select dy, 4 day(dy) dm, 5 datepart(m,dy) mth, 6 datepart(dw,dy) dw, 7 case when datepart(dw,dy) = 1 8 then datepart(ww,dy)-1 9 else datepart(ww,dy) 10 end wk 11 from ( 12 select dateadd(day,-day(getdate())+1,getdate( )) dy 13 from t1 14 ) x 15 union all 16 select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth, 17 datepart(dw,dateadd(d,1,dy)), 18 case when datepart(dw,dateadd(d,1,dy)) = 1 19 then datepart(wk,dateadd(d,1,dy))-1 20 else datepart(wk,dateadd(d,1,dy)) 21 end 22 from x 23 where datepart(m,dateadd(d,1,dy)) = mth 24 ) 25 select max(case dw when 2 then dm end) as Mo, 26 max(case dw when 3 then dm end) as Tu, 27 max(case dw when 4 then dm end) as We, 28 max(case dw when 5 then dm end) as Th, 29 max(case dw when 6 then dm end) as Fr, 30 max(case dw when 7 then dm end) as Sa, 31 max(case dw when 1 then dm end) as Su 32 from x 33 group by wk 34 order by wk DiscussionDB2The first step is to return each day in the month for which you want to create a calendar. Do that using the recursive WITH clause (if you don't have WITH available, you can use a pivot table, such as T500, as in the MySQL solution). Along with each day of the month (alias DM) you will need to return different parts of each date: the day of the week (alias DW), the current month you are working with (alias MTH), and the ISO week for each day of the month (alias WK). The results of the recursive view X prior to recursion taking place (the upper portion of the UNION ALL) are shown below: select (current_date -day(current_date) day +1 day) dy, day((current_date -day(current_date) day +1 day)) dm, month(current_date) mth, dayofweek(current_date -day(current_date) day +1 day) dw, week_iso(current_date -day(current_date) day +1 day) wk from t1 DY DM MTH DW WK ----------- -- --- ---------- -- 01-JUN-2005 01 06 4 22 The next step is to repeatedly increase the value for DM (move through the days of the month) until you are no longer in the current month. As you move through each day in the month, you will also return the day of the week that each day is, and which ISO week the current day of the month falls into. Partial results are shown below: with x(dy,dm,mth,dw,wk) as ( select (current_date -day(current_date) day +1 day) dy, day((current_date -day(current_date) day +1 day)) dm, month(current_date) mth, dayofweek(current_date -day(current_date) day +1 day) dw, week_iso(current_date -day(current_date) day +1 day) wk from t1 union all select dy+1 day, day(dy+1 day), mth, dayofweek(dy+1 day), week_iso(dy+1 day) from x where month(dy+1 day) = mth ) select * from x DY DM MTH DW WK ----------- -- --- ---------- -- 01-JUN-2005 01 06 4 22 02-JUN-2005 02 06 5 22 … 21-JUN-2005 21 06 3 25 22-JUN-2005 22 06 4 25 … 30-JUN-2005 30 06 5 26 What you are returning at this point are: each day for the current month, the two-digit numeric day of the month, the two-digit numeric month, the one-digit day of the week (17 for SunSat), and the two-digit ISO week each day falls into. With all this information available, you can use a CASE expression to determine which day of the week each value of DM (each day of the month) falls into. A portion of the results is shown below: with x(dy,dm,mth,dw,wk) as ( select (current_date -day(current_date) day +1 day) dy, day((current_date -day(current_date) day +1 day)) dm, month(current_date) mth, dayofweek(current_date -day(current_date) day +1 day) dw, week_iso(current_date -day(current_date) day +1 day) wk from t1 union all select dy+1 day, day(dy+1 day), mth, dayofweek(dy+1 day), week_iso(dy+1 day) from x where month(dy+1 day) = mth ) select wk, case dw when 2 then dm end as Mo, case dw when 3 then dm end as Tu, case dw when 4 then dm end as We, case dw when 5 then dm end as Th, case dw when 6 then dm end as Fr, case dw when 7 then dm end as Sa, case dw when 1 then dm end as Su from x WK MO TU WE TH FR SA SU -- -- -- -- -- -- -- -- 22 01 22 02 22 03 22 04 22 05 23 06 23 07 23 08 23 09 23 10 23 11 23 12 As you can see from the partial output, every day in each week is returned as a row. What you want to do now is to group the days by week, and then collapse all the days for each week into a single row. Use the aggregate function MAX, and group by WK (the ISO week) to return all the days for a week as one row. To properly format the calendar and ensure that the days are in the right order, order the results by WK. The final output is shown below: with x(dy,dm,mth,dw,wk) as ( select (current_date -day(current_date) day +1 day) dy, day((current_date -day(current_date) day +1 day)) dm, month(current_date) mth, dayofweek(current_date -day(current_date) day +1 day) dw, week_iso(current_date -day(current_date) day +1 day) wk from t1 union all select dy+1 day, day(dy+1 day), mth, dayofweek(dy+1 day), week_iso(dy+1 day) from x where month(dy+1 day) = mth ) select max(case dw when 2 then dm end) as Mo, max(case dw when 3 then dm end) as Tu, max(case dw when 4 then dm end) as We, max(case dw when 5 then dm end) as Th, max(case dw when 6 then dm end) as Fr, max(case dw when 7 then dm end) as Sa, max(case dw when 1 then dm end) as Su from x group by wk order by wk MO TU WE TH FR SA SU -- -- -- -- -- -- -- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 OracleBegin by using the recursive CONNECT BY clause to generate a row for each day in the month for which you wish to generate a calendar. If you aren't running at least Oracle9i Database, you can't use CONNECT BY this way. Instead, you can use a pivot table, such as T500 in the MySQL solution. Along with each day of the month, you will need to return different bits of information for each day: the day of the month (alias DM), the day of the week (alias DW), the current month you are working with (alias MTH), and the ISO week for each day of the month (alias WK). The results of the WITH view X for the first day of the current month are shown below: select trunc(sysdate,'mm') dy, to_char(trunc(sysdate,'mm'),'dd') dm, to_char(sysdate,'mm') mth, to_number(to_char(trunc(sysdate,'mm'),'d')) dw, to_char(trunc(sysdate,'mm'),'iw') wk from dual DY DM MT DW WK ----------- -- -- ---------- -- 01-JUN-2005 01 06 4 22 The next step is to repeatedly increase the value for DM (move through the days of the month) until you are no longer in the current month. As you move through each day in the month, you will also return the day of the week for each day and the ISO week into which the current day falls. Partial results are shown below (the full date for each day is added below for readability): with x as ( select * from ( select trunc(sysdate,'mm')+level-1 dy, to_char(trunc(sysdate,'mm')+level-1,'iw') wk, to_char(trunc(sysdate,'mm')+level-1,'dd') dm, to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw, to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth, to_char(sysdate,'mm') mth from dual connect by level <= 31 ) where curr_mth = mth ) select * from x DY WK DM DW CU MT ----------- -- -- ---------- -- -- 01-JUN-2005 22 01 4 06 06 02-JUN-2005 22 02 5 06 06 … 21-JUN-2005 25 21 3 06 06 22-JUN-2005 25 22 4 06 06 … 30-JUN-2005 26 30 5 06 06 What you are returning at this point is one row for each day of the current month. In that row you have: the two-digit numeric day of the month, the two-digit numeric month, the one-digit day of the week (17 for SunSat), and the two-digit ISO week number. With all this information available, you can use a CASE expression to determine which day of the week each value of DM (each day of the month) falls into. A portion of the results is shown below: with x as ( select * from ( select trunc(sysdate,'mm')+level-1 dy, to_char(trunc(sysdate,'mm')+level-1,'iw') wk, to_char(trunc(sysdate,'mm')+level-1,'dd') dm, to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw, to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth, to_char(sysdate,'mm') mth from dual connect by level <= 31 ) where curr_mth = mth ) select wk, case dw when 2 then dm end as Mo, case dw when 3 then dm end as Tu, case dw when 4 then dm end as We, case dw when 5 then dm end as Th, case dw when 6 then dm end as Fr, case dw when 7 then dm end as Sa, case dw when 1 then dm end as Su from x WK MO TU WE TH FR SA SU -- -- -- -- -- -- -- -- 22 01 22 02 22 03 22 04 22 05 23 06 23 07 23 08 23 09 23 10 23 11 23 12 As you can see from the partial output, every day in each week is returned as a row, but the day number is in one of seven columns corresponding to the day of the week. Your task now is to consolidate the days into one row for each week. Use the aggregate function MAX and group by WK (the ISO week) to return all the days for a week as one row. To ensure the days are in the right order, order the results by WK. The final output is shown below: with x as ( select * from ( select to_char(trunc(sysdate,'mm')+level-1,'iw') wk, to_char(trunc(sysdate,'mm')+level-1,'dd') dm, to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw, to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth, to_char(sysdate,'mm') mth from dual connect by level <= 31 ) where curr_mth = mth ) select max(case dw when 2 then dm end) Mo, max(case dw when 3 then dm end) Tu, max(case dw when 4 then dm end) We, max(case dw when 5 then dm end) Th, max(case dw when 6 then dm end) Fr, max(case dw when 7 then dm end) Sa, max(case dw when 1 then dm end) Su from x group by wk order by wk MO TU WE TH FR SA SU -- -- -- -- -- -- -- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 PostgreSQLUse the GENERATE_SERIES function to return one row for each day in the month. If your version of PostgreSQL doesn't support GENERATE_SERIES, then query a pivot table as shown in the MySQL solution. For each day of the month, return the following information: the day of the month (alias DM), the day of the week (alias DW), the current month you are working with (alias MTH), and the ISO week for each day of the month (alias WK). The formatting and explicit casting makes this solution tough on the eyes, but it's really quite simple. Partial results from inline view X are shown below: select cast(date_trunc('month',current_date) as date)+x.id as dy, to_char( cast( date_trunc('month',current_date) as date)+x.id,'iw') as wk, to_char( cast( date_trunc('month',current_date) as date)+x.id,'dd') as dm, cast( to_char( cast( date_trunc('month',current_date) as date)+x.id,'d') as integer) as dw, to_char( cast( date_trunc('month',current_date) as date)+x.id,'mm') as curr_mth, to_char(current_date,'mm') as mth from generate_series (0,31) x(id) DY WK DM DW CU MT ----------- -- -- ---------- -- -- 01-JUN-2005 22 01 4 06 06 02-JUN-2005 22 02 5 06 06 … 21-JUN-2005 25 21 3 06 06 22-JUN-2005 25 22 4 06 06 … 30-JUN-2005 26 30 5 06 06 Notice that as you move through each day in the month, you will also return the day of the week and the ISO week number. To ensure you return days only for the month you are interested in, return only rows where CURR_MTH = MTH (the month each day belongs to should be the month the current date belongs to). What you are returning at this point is, for each day for the current month: the two-digit numeric day of the month, the two-digit numeric month, the one-digit day of the week (17 for Sun Sat), and the two-digit ISO week. Your next step is to use a CASE expression to determine which day of the week each value of DM (each day of the month) falls into. A portion of the results is shown below: select case dw when 2 then dm end as Mo, case dw when 3 then dm end as Tu, case dw when 4 then dm end as We, case dw when 5 then dm end as Th, case dw when 6 then dm end as Fr, case dw when 7 then dm end as Sa, case dw when 1 then dm end as Su from ( select * from ( select cast(date_trunc('month',current_date) as date)+x.id, to_char( cast( date_trunc('month',current_date) as date)+x.id,'iw') as wk, to_char( cast( date_trunc('month',current_date) as date)+x.id,'dd') as dm, cast( to_char( cast( date_trunc('month',current_date) as date)+x.id,'d') as integer) as dw, to_char( cast( date_trunc('month',current_date) as date)+x.id,'mm') as curr_mth, to_char(current_date,'mm') as mth from generate_series (0,31) x(id) ) x where mth = curr_mth ) y WK MO TU WE TH FR SA SU -- -- -- -- -- -- -- -- 22 01 22 02 22 03 22 04 22 05 23 06 23 07 23 08 23 09 23 10 23 11 23 12 As you can see from the partial output, every day in each week is returned as a row, and each day number falls into the column corresponding to its day of the week. Your job now is to collapse the days into one row for each week. To that end, use the aggregate function MAX and group the rows by WK (the ISO week). The result will be all the days for each week returned as one row as you would see on a calendar. To ensure the days are in the right order, order the results by WK. The final output is shown below: select max(case dw when 2 then dm end) as Mo, max(case dw when 3 then dm end) as Tu, max(case dw when 4 then dm end) as We, max(case dw when 5 then dm end) as Th, max(case dw when 6 then dm end) as Fr, max(case dw when 7 then dm end) as Sa, max(case dw when 1 then dm end) as Su from ( select * from ( select cast(date_trunc('month',current_date) as date)+x.id, to_char( cast( date_trunc('month',current_date) as date)+x.id,'iw') as wk, to_char( cast( date_trunc('month',current_date) as date)+x.id,'dd') as dm, cast( to_char( cast( date_trunc('month',current_date) as date)+x.id,'d') as integer) as dw, to_char( cast( date_trunc('month',current_date) as date)+x.id,'mm') as curr_mth, to_char(current_date,'mm') as mth from generate_series (0,31) x(id) ) x where mth = curr_mth ) y group by wk order by wk MO TU WE TH FR SA SU -- -- -- -- -- -- -- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 MySQLThe first step is to return a row for each day in the month for which you want to create a calendar. To that end, query against table T500. By adding each value returned by T500 to the first day of the month, you can return each day in the month. For each date, you will need to return the following bits of information: the day of the month (alias DM), the day of the week (alias DW), the current month you are working with (alias MTH), and the ISO week for each day of the month (alias WK). Inline view X returns the first day of the current month along with the two-digit numeric value for the current month. Results are shown below: select adddate(current_date,-dayofmonth(current_date)+1) dy, date_format( adddate(current_date, -dayofmonth(current_date)+1), '%m') mth from t1 DY MT ----------- -- 01-JUN-2005 06 The next step is to move through the month, starting from the first day and returning each day in the month. Notice that as you move through each day in the month, you will also return the corresponding day of the week and ISO week number. To ensure you return days only for the month you are interested in, return only rows where the month of the day returned is equal to the current month (the month each day belongs to should be the month the current date belongs to). A portion of the rows from inline view Y is shown below: select date_format(dy,'%u') wk, date_format(dy,'%d') dm, date_format(dy,'%w')+1 dw from ( select adddate(x.dy,t500.id-1) dy, x.mth from ( select adddate(current_date,-dayofmonth(current_date)+1) dy, date_format( adddate(current_date, -dayofmonth(current_date)+1), '%m') mth from t1 ) x, t500 where t500.id <= 31 and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth ) y WK DM DW -- -- ---------- 22 01 4 22 02 5 … 25 21 3 25 22 4 … 26 30 5 For each day for the current month you now have: the two-digit numeric day of the month (DM), the one-digit day of the week (DW), and the two-digit ISO week number (WK). Using this information, you can write a CASE expression to determine which day of the week each value of DM (each day of the month) falls into. A portion of the results is shown below: select case dw when 2 then dm end as Mo, case dw when 3 then dm end as Tu, case dw when 4 then dm end as We, case dw when 5 then dm end as Th, case dw when 6 then dm end as Fr, case dw when 7 then dm end as Sa, case dw when 1 then dm end as Su from ( select date_format(dy,'%u') wk, date_format(dy,'%d') dm, date_format(dy,'%w')+1 dw from ( select adddate(x.dy,t500.id-1) dy, x.mth from ( select adddate(current_date,-dayofmonth(current_date)+1) dy, date_format( adddate(current_date, -dayofmonth(current_date)+1), '%m') mth from t1 ) x, t500 where t500.id <= 31 and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth ) y ) z WK MO TU WE TH FR SA SU -- -- -- -- -- -- -- -- 22 01 22 02 22 03 22 04 22 05 23 06 23 07 23 08 23 09 23 10 23 11 23 12 As you can see from the partial output, every day in each week is returned as a row. Within each row, the day number falls into the column corresponding to the appropriate weekday. Now you need to consolidate the days into one row for each week. To do that, use the aggregate function MAX, and group the rows by WK (the ISO week). To ensure the days are in the right order, order the results by WK. The final output is shown below: select max(case dw when 2 then dm end) as Mo, max(case dw when 3 then dm end) as Tu, max(case dw when 4 then dm end) as We, max(case dw when 5 then dm end) as Th, max(case dw when 6 then dm end) as Fr, max(case dw when 7 then dm end) as Sa, max(case dw when 1 then dm end) as Su from ( select date_format(dy,'%u') wk, date_format(dy,'%d') dm, date_format(dy,'%w')+1 dw from ( select adddate(x.dy,t500.id-1) dy, x.mth from ( select adddate(current_date,-dayofmonth(current_date)+1) dy, date_format( adddate(current_date, -dayofmonth(current_date)+1), '%m') mth from t1 ) x, t500 where t500.id <= 31 and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth ) y ) z group by wk order by wk MO TU WE TH FR SA SU -- -- -- -- -- -- -- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 SQL ServerBegin by returning one row for each day of the month. You can do that using the recursive WITH clause. Or, if your version of SQL Server doesn't support recursive WITH, you can use a pivot table in the same manner as the MySQL solution. For each row that you return, you will need the following items: the day of the month (alias DM), the day of the week (alias DW), the current month you are working with (alias MTH), and the ISO week for each day of the month (alias WK). The results of the recursive view X prior to recursion taking place (the upper portion of the UNION ALL) are shown below: select dy, day(dy) dm, datepart(m,dy) mth, datepart(dw,dy) dw, case when datepart(dw,dy) = 1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk from ( select dateadd(day,-day(getdate())+1,getdate()) dy from t1 ) x DY DM MTH DW WK ----------- -- --- ---------- -- 01-JUN-2005 1 6 4 23 Your next step is to repeatedly increase the value for DM (move through the days of the month) until you are no longer in the current month. As you move through each day in the month, you will also return the day of the week and the ISO week number. Partial results are shown below: with x(dy,dm,mth,dw,wk) as ( select dy, day(dy) dm, datepart(m,dy) mth, datepart(dw,dy) dw, case when datepart(dw,dy) = 1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk from ( select dateadd(day,-day(getdate( ))+1,getdate( )) dy from t1 ) x union all select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth, datepart(dw,dateadd(d,1,dy)), case when datepart(dw,dateadd(d,1,dy)) = 1 then datepart(wk,dateadd(d,1,dy))-1 else datepart(wk,dateadd(d,1,dy)) end from x where datepart(m,dateadd(d,1,dy)) = mth ) select * from x DY DM MTH DW WK ----------- -- --- ---------- -- 01-JUN-2005 01 06 4 23 02-JUN-2005 02 06 5 23 … 21-JUN-2005 21 06 3 26 22-JUN-2005 22 06 4 26 … 30-JUN-2005 30 06 5 27 You now have, for each day in the current month: the two-digit numeric day of the month, the two-digit numeric month, the one-digit day of the week (17 for Sun Sat), and the two-digit ISO week number. Now, use a CASE expression to determine which day of the week each value of DM (each day of the month) falls into. A portion of the results is shown below: with x(dy,dm,mth,dw,wk) as ( select dy, day(dy) dm, datepart(m,dy) mth, datepart(dw,dy) dw, case when datepart(dw,dy) = 1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk from ( select dateadd(day,-day(getdate( ))+1,getdate( )) dy from t1 ) x union all select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth, datepart(dw,dateadd(d,1,dy)), case when datepart(dw,dateadd(d,1,dy)) = 1 then datepart(wk,dateadd(d,1,dy))-1 else datepart(wk,dateadd(d,1,dy)) end from x where datepart(m,dateadd(d,1,dy)) = mth ) select case dw when 2 then dm end as Mo, case dw when 3 then dm end as Tu, case dw when 4 then dm end as We, case dw when 5 then dm end as Th, case dw when 6 then dm end as Fr, case dw when 7 then dm end as Sa, case dw when 1 then dm end as Su from x WK MO TU WE TH FR SA SU -- -- -- -- -- -- -- -- 22 01 22 02 22 03 22 04 22 05 23 06 23 07 23 08 23 09 23 10 23 11 23 12 Every day in each week is returned as a separate row. In each row, the column containing the day number corresponds to the day of the week. You now need to consolidate the days for each week into one row. Do that by grouping the rows by WK (the ISO week) and applying the MAX function to the different columns. The results will be in calendar format as shown below: with x(dy,dm,mth,dw,wk) as ( select dy, day(dy) dm, datepart(m,dy) mth, datepart(dw,dy) dw, case when datepart(dw,dy) = 1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk from ( select dateadd(day,-day(getdate( ))+1,getdate( )) dy from t1 ) x union all select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth, datepart(dw,dateadd(d,1,dy)), case when datepart(dw,dateadd(d,1,dy)) = 1 then datepart(wk,dateadd(d,1,dy))-1 else datepart(wk,dateadd(d,1,dy)) end from x where datepart(m,dateadd(d,1,dy)) = mth ) select max(case dw when 2 then dm end) as Mo, max(case dw when 3 then dm end) as Tu, max(case dw when 4 then dm end) as We, max(case dw when 5 then dm end) as Th, max(case dw when 6 then dm end) as Fr, max(case dw when 7 then dm end) as Sa, max(case dw when 1 then dm end) as Su from x group by wk order by wk MO TU WE TH FR SA SU -- -- -- -- -- -- -- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
EAN: 2147483647
Pages: 235