13 Replies Latest reply on May 26, 2006 2:14 PM by SafariTECH

    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
        • 1. Re: Outputting 7 day calendar
          SafariTECH Level 1
          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.
          • 2. Re: Outputting 7 day calendar
            Dan Bracuk Level 5
            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.
            • 3. Re: Outputting 7 day calendar
              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/./..
              • 4. Re: Outputting 7 day calendar
                Botman63 Level 1
                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
                • 5. Re: Outputting 7 day calendar
                  SafariTECH Level 1
                  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.

                  • 6. Re: Outputting 7 day calendar
                    elDonrico Level 1
                    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.
                    • 7. Re: Outputting 7 day calendar
                      elDonrico Level 1
                      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.
                      • 8. Re: Outputting 7 day calendar
                        elDonrico Level 1
                        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.
                        • 9. Re: Outputting 7 day calendar
                          elDonrico Level 1
                          whoops sorry... dont know how that happend.
                          • 10. Re: Outputting 7 day calendar
                            Botman63 Level 1
                            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?
                            • 11. Re: Outputting 7 day calendar
                              SafariTECH Level 1
                              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.

                              • 12. Re: Outputting 7 day calendar
                                Botman63 Level 1
                                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
                                • 13. Re: Outputting 7 day calendar
                                  SafariTECH Level 1
                                  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.