Outputting 7 day calendar

[Botman63]

I have a query that is getting information for the previous 7 days. What I need to know how to do is display it in a weekly calendar format, day of the week in the first row, then the result in the second row. I think that I need to use some sort of array but I am not sure.

Thanks,
Bruce

[SafariTECH]

Check through your help files regarding all the DATE functions.

You are already sorting by date, otherwise you wouldn't know if you had 7 days of info or not, so all you have to do is use the various date functions to label the days / dates etc.

The query is already technically an array, you just have to loop through it.

[Dan_Bracuk]

quote:

---

Originally posted by: SafariTECH
Check through your help files regarding all the DATE functions.
You are already sorting by date, otherwise you wouldn't know if you had 7 days of info or not, so all you have to do is use the various date functions to label the days / dates etc.
The query is already technically an array, you just have to loop through it.

---

It's not quite that simple because he wants the days of the week as column headers. This means he either comes up with a cross tab query, or builds a cold fusion array to rearrange his query results.

[elDonrico]

yea, i dont think this is as easy as when i first read through this... my question is ... you are getting the information for the previous 7 days... so this is not at all on a real calendar of days only "7 last days"? right?

today is thurs so you are looking for

THURS FRI SAT SUN MON TUES WED
X X X X X X X
is this right?

i think i can write this, but, not tonight,... i actually have a similar code at work (and i'm not there now.. )

you are going to do a <CFLOOP FROM="0" TO="6" STEP="1" INDEX="day"> and do a DateAdd('d', 1 , Now())>

how to really write it right now, i cant think about it, but, i think i can write (unless someone else comes up with a bright idea within the next 12hrs/./..

[Botman63]

That is what I am trying to do. I admittedly am not that great at arrays and such so I am fumbling around. And I am getting the previous 7 days. I currently have a scheduled report that runs every Monday and it gets totals for me for the previous day. So now the customer wants to get a day by day breakdown.

Thanks,

Bruce

[elDonrico]

you need a day by day break down, but, do you have to have it in a calendar "like" display... or can you just display the data with the date there too? see the trouble with the code would he to write the day of the week in the column above the data... its not really that hard, but, are you overthinking this? i mean if you want it to display linear instead of vertical that is a different story.

[Botman63]

I would like to display it as the days of the week in one row and the data in the next row.
What I am doing is this. I have several queries that are doing a count on one field in the table and getting that date from another field. Since this runs on Monday's, we will use that.
So on Monday, if there are a total of 8 record for that day, I want the date (5/15/2006) in the first cell and then 8 in the cell below. Continuing this through the rest of the previous week. I am putting one of the queries in this post. It does what I want regarding getting the information but I am having trouble getting it into the table. Also, it is getting mailed out via CFMAIL so I guess I have to put the loop into the CFMAIL?

[SafariTECH]

RE: CFMAIL

If you are going to use the result in multiple locations (web, email, ?), (or even if not), it likely would be best to run the loop inside a CFSAVECONTENT set and assign a variable. Then you just have to call the variable for wherever it is being used.

[Botman63]

Sometimes my level of stupidity even amazes me!. So, all this time all I had to do was redo the query by adding dateInserted, converting it to a char field and then doing a simple GROUP BY function. So now I get the data I want. The customer is not going to be too picky about how the data looks though so I do not need to output it to look like a one week calendar.

however, a new issue. he wants me to make sure to put 0 in the days that do not have any leads. Since I am grouping the data, if we only had 4 days that generated leads, and that is what is output, how to I make the other days default to zero? I am attaching my code to this message.

For the query I attached, the code only shows four rows since there are four days that had leads. I need to get the other three rows inserted with 0 in the TotalLeadRec field.

Thanks,

Bruce

[SafariTECH]

That one is going to cause you a few more headaches because now you can't rely solely on the query data.

You may actually need to loop through the date range and use query of query to decide if there is any valid data for that date, assigning a zero if there is not.

[elDonrico]

you need a day by day break down, but, do you have to have it in a calendar "like" display... or can you just display the data with the date there too? see the trouble with the code would he to write the day of the week in the column above the data... its not really that hard, but, are you overthinking this? i mean if you want it to display linear instead of vertical that is a different story.

[elDonrico]

you need a day by day break down, but, do you have to have it in a calendar "like" display... or can you just display the data with the date there too? see the trouble with the code would he to write the day of the week in the column above the data... its not really that hard, but, are you overthinking this? i mean if you want it to display linear instead of vertical that is a different story.

[elDonrico]

whoops sorry... dont know how that happend.

[SafariTECH]

I still think the array really won't be necessary.

He's grabbing the data based on date anyway. Sort the data by date ASC and Group the data in the CFOUTPUT by date as well.

You can then use the DayOfWeek function to figure out what day of the week that date is and assign it as the first column. Since the data is sorted by date it wil always be in the proper order.

If you only want the dayof week to show up once per group, then add a simple CF variable flag amongst the loop that will only change if the dayofweek changes and when it changes it writes the dayof week again.

I may take a moment to write this up and test it tomorrow, but I've done similar "groupings" in other apps this way.