Help with Date Stuff
Ok I have a weekly report I have to run.Basically it's run every Sunday and brings back info for the previous week. If it's, as an example, the 3rd sunday of hte month it will bring back info for the last 3 weeks of the month (basically going back to the first). I have the report running fine however he wants me to have the display broken out to say Week 1, Week 2, Week 3, within the report and I cant figure out how to do that. Here is the code and where you see !--WEEK 1 HERE-- is where that would need to display. CFPARAM name=enddate default=08/17/04 CFPARAM name=startdate default=#dateformat(Createdate(year,month,1),'mm/dd/yy')# CFPARAM name=type default=1 CFPARAM name=displaydate default= CFMAIL type=HTML to= server= from= subject=Report for #startdate# - #enddate# font face=arial size=2 CFLOOP from=#type# to=3 step=1 index=count table cellpadding=5 cellspacing=0 border bordercolor=silver width=600 CFQUERY datasource=emailertemp name=test exec sp_emailreports '#startdate#','#enddate#','OSI',#count# /CFQUERY tr td colspan=5OSI CFIF type EQ 1Single StepCFELSEIF type eq 2Two Step Phase 1CFELSEIF type eq 3Two Step Phase 2/CFIF/td tr tdfont size=2Name/font/td tdfont size=2ID/font/td tdfont size=2Status/font/td tdfont size=2Type/font/td tdfont size=2Date/font/td /tr trtd colspan=5 !---Week 1 here (or week 2 or week 3 etc based on the date. Obviously since it's outside the query loop its not working right if i put it in the query loop it gets repeate etc.--- /td/tr CFIF Isdefined(test.recordcount) CFLOOP query=test CFIF count EQ 1 CFSET displaydate = #dateformat(test.ssd, 'mm/dd/yy')# CFELSEIF count eq 2 CFSET displaydate = #dateformat(test.ts1d, 'mm/dd/yy')# CFELSE CFSET displaydate = #dateformat(test.ts2d, 'mm/dd/yy')# /CFIF tr tdfont size=2#firstname# #lastname#/font/td tdfont size=2#ID#/font/td tdfont size=2#status#/font/td tdfont size=2#description#/font/td tdfont size=2#displaydate#/font/td /tr /CFLOOP /CFIF CFSET type=type+1 /table P/P /CFLOOP /CFMAIL ANy suggestions on how to do get it to group by Week 1, week 2 week 3? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Help with Date Stuff
The key is to figure out for each date what the week number is. Once you can do that reliably it'll be easy. I take it that each weeks starts on a Sunday, so that for September 2004 we're in week 2, and week 3 starts this coming Sunday, right? _ From: Kelly Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 9:23 a.m. To: CF-Talk Subject: Help with Date Stuff Ok I have a weekly report I have to run.Basically it's run every Sunday and brings back info for the previous week. If it's, as an example, the 3rd sunday of hte month it will bring back info for the last 3 weeks of the month (basically going back to the first). I have the report running fine however he wants me to have the display broken out to say Week 1, Week 2, Week 3, within the report and I cant figure out how to do that. Here is the code and where you see !--WEEK 1 HERE-- is where that would need to display. CFPARAM name=enddate default=08/17/04 CFPARAM name=startdate default=#dateformat(Createdate(year,month,1),'mm/dd/yy')# CFPARAM name=type default=1 CFPARAM name=displaydate default= CFMAIL type=HTML to= server= from= subject=Report for #startdate# - #enddate# font face=arial size=2 CFLOOP from=#type# to=3 step=1 index=count table cellpadding=5 cellspacing=0 border bordercolor=silver width=600 CFQUERY datasource=emailertemp name=test exec sp_emailreports '#startdate#','#enddate#','OSI',#count# /CFQUERY tr td colspan=5OSI CFIF type EQ 1Single StepCFELSEIF type eq 2Two Step Phase 1CFELSEIF type eq 3Two Step Phase 2/CFIF/td tr tdfont size=2Name/font/td tdfont size=2ID/font/td tdfont size=2Status/font/td tdfont size=2Type/font/td tdfont size=2Date/font/td /tr trtd colspan=5 !---Week 1 here (or week 2 or week 3 etc based on the date. Obviously since it's outside the query loop its not working right if i put it in the query loop it gets repeate etc.--- /td/tr CFIF Isdefined(test.recordcount) CFLOOP query=test CFIF count EQ 1 CFSET displaydate = #dateformat(test.ssd, 'mm/dd/yy')# CFELSEIF count eq 2 CFSET displaydate = #dateformat(test.ts1d, 'mm/dd/yy')# CFELSE CFSET displaydate = #dateformat(test.ts2d, 'mm/dd/yy')# /CFIF tr tdfont size=2#firstname# #lastname#/font/td tdfont size=2#ID#/font/td tdfont size=2#status#/font/td tdfont size=2#description#/font/td tdfont size=2#displaydate#/font/td /tr /CFLOOP /CFIF CFSET type=type+1 /table P/P /CFLOOP /CFMAIL ANy suggestions on how to do get it to group by Week 1, week 2 week 3? _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Help with Date Stuff
correct but the WEEK function brings back the week number for the YEAR not the MONTH. The key is to figure out for each date what the week number is. Once you can do that reliably it'll be easy. I take it that each weeks starts on a Sunday, so that for September 2004 we're in week 2, and week 3 starts this coming Sunday, right? _ From: Kelly Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, 8 September 2004 9:23 a.m. To: CF-Talk Subject: Help with Date Stuff Ok I have a weekly report I have to run.Basically it's run every Sunday and brings back info for the previous week. If it's, as an example, the 3rd sunday of hte month it will bring back info for the last 3 weeks of the month (basically going back to the first). I have the report running fine however he wants me to have the display broken out to say Week 1, Week 2, Week 3, within the report and I cant figure out how to do that. Here is the code and where you see !--WEEK 1 HERE-- is where that would need to display. CFPARAM name=enddate default=08/17/04 CFPARAM name=startdate default=#dateformat(Createdate(year,month,1),'mm/dd/yy')# CFPARAM name=type default=1 CFPARAM name=displaydate default= CFMAIL type=HTML to= server= from= subject=Report for #startdate# - #enddate# font face=arial size=2 CFLOOP from=#type# to=3 step=1 index=count table cellpadding=5 cellspacing=0 border bordercolor=silver width=600 CFQUERY datasource=emailertemp name=test exec sp_emailreports '#startdate#','#enddate#','OSI',#count# /CFQUERY tr td colspan=5OSI CFIF type EQ 1Single StepCFELSEIF type eq 2Two Step Phase 1CFELSEIF type eq 3Two Step Phase 2/CFIF/td tr tdfont size=2Name/font/td tdfont size=2ID/font/td tdfont size=2Status/font/td tdfont size=2Type/font/td tdfont size=2Date/font/td /tr trtd colspan=5 !---Week 1 here (or week 2 or week 3 etc based on the date. Obviously since it's outside the query loop its not working right if i put it in the query loop it gets repeate etc.--- /td/tr CFIF Isdefined(test.recordcount) CFLOOP query=test CFIF count EQ 1 CFSET displaydate = #dateformat(test.ssd, 'mm/dd/yy')# CFELSEIF count eq 2 CFSET displaydate = #dateformat(test.ts1d, 'mm/dd/yy')# CFELSE CFSET displaydate = #dateformat(test.ts2d, 'mm/dd/yy')# /CFIF tr tdfont size=2#firstname# #lastname#/font/td tdfont size=2#ID#/font/td tdfont size=2#status#/font/td tdfont size=2#description#/font/td tdfont size=2#displaydate#/font/td /tr /CFLOOP /CFIF CFSET type=type+1 /table P/P /CFLOOP /CFMAIL ANy suggestions on how to do get it to group by Week 1, week 2 week 3? _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Help with Date Stuff
I think this is the function you need but I haven't tested it.
function weekOfMonth(date, firstDOW) {
var DOM = day(date);
var m = month(date);
var y = year(date);
var firstDOM = dayOfWeek(createDate(y, m, 1));
var offset = ((firstDOM - firstDOW + 6) mod 7) + 1;
var WOM = (DOM + offset - 1) \ 7 + 1;
return WOM;
}
Do watch out for bugs. You give it the date and the number corresponding to
the day you consider the first of the week (i.e. 1 for Sunday - this would
be a constant for your). The function returns the week number of the month.
Hopefully.
Then you can either add a calculated column to your query and use cfoutput
group=. or you could simply test the week number on each loop and display
it only when it changes.
_
From: Kelly Matthews [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 8 September 2004 10:37 a.m.
To: CF-Talk
Subject: Re: Help with Date Stuff
correct but the WEEK function brings back the week number for the YEAR not
the MONTH.
The key is to figure out for each date what the week number is. Once you
can
do that reliably it'll be easy. I take it that each weeks starts on a
Sunday, so that for September 2004 we're in week 2, and week 3 starts this
coming Sunday, right?
_
From: Kelly Matthews [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 8 September 2004 9:23 a.m.
To: CF-Talk
Subject: Help with Date Stuff
Ok I have a weekly report I have to run.Basically it's run every Sunday
and brings back info for the previous week. If it's, as an example, the 3rd
sunday of hte month it will bring back info for the last 3 weeks of the
month (basically going back to the first). I have the report running fine
however he wants me to have the display broken out to say Week 1, Week 2,
Week 3, within the report and I cant figure out how to do that.
Here is the code and where you see !--WEEK 1 HERE-- is where that would
need to display.
CFPARAM name=enddate default=08/17/04
CFPARAM name=startdate
default=#dateformat(Createdate(year,month,1),'mm/dd/yy')#
CFPARAM name=type default=1
CFPARAM name=displaydate default=
CFMAIL type=HTML to= server= from= subject=Report for #startdate#
- #enddate#
font face=arial size=2
CFLOOP from=#type# to=3 step=1 index=count
table cellpadding=5 cellspacing=0 border bordercolor=silver
width=600
CFQUERY datasource=emailertemp name=test
exec sp_emailreports '#startdate#','#enddate#','OSI',#count#
/CFQUERY
tr
td colspan=5OSI CFIF type EQ 1Single StepCFELSEIF type eq 2Two Step
Phase 1CFELSEIF type eq 3Two Step Phase 2/CFIF/td
tr
tdfont size=2Name/font/td
tdfont size=2ID/font/td
tdfont size=2Status/font/td
tdfont size=2Type/font/td
tdfont size=2Date/font/td
/tr
trtd colspan=5
!---Week 1 here (or week 2 or week 3 etc based on the date. Obviously
since
it's outside the query loop its not working right if i put it in the query
loop it gets repeate etc.---
/td/tr
CFIF Isdefined(test.recordcount)
CFLOOP query=test
CFIF count EQ 1
CFSET displaydate = #dateformat(test.ssd, 'mm/dd/yy')#
CFELSEIF count eq 2
CFSET displaydate = #dateformat(test.ts1d, 'mm/dd/yy')#
CFELSE
CFSET displaydate = #dateformat(test.ts2d, 'mm/dd/yy')#
/CFIF
tr
tdfont size=2#firstname# #lastname#/font/td
tdfont size=2#ID#/font/td
tdfont size=2#status#/font/td
tdfont size=2#description#/font/td
tdfont size=2#displaydate#/font/td
/tr
/CFLOOP
/CFIF
CFSET type=type+1
/table
P/P
/CFLOOP
/CFMAIL
ANy suggestions on how to do get it to group by Week 1, week 2 week 3?
_
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
