We help IT Professionals succeed at work.

Providing Reporting for ColdFusion records by dates.

traport
traport asked
on
I have a field with a date in timestamp format: 12/6/2011 12:39:33 PM. That field is dateEntered.

I need to provide a dynamic report of Records by Month and YTD.

Example:

2011

Jan: 2
Feb: 4
March: 5
...

Total in 2011: 64

I know I somehow need to incorporate the splitting up of the dates by month and year... Something like:

              <cfquery name="qry_getRecords" datasource="#request.DSN#">
          SELECT dateEntered from sa_Requests
                          ORDER BY dateEntered
         </cfquery>


                     <cfset myDate = qry_getRecords.dateEntered>
      <cfset theDay = listGetAt(myDate,1,"-")>
      <cfset theMonth = listGetAt(myDate,2,"-")>
      <cfset theYear = listGetAt(myDate,3,"-")>

But I can't figure out if I do this somehow on the query side, or the output side (and if so, how to group). I'd love some suggestions/examples/advice. Thanks so much.
Comment
Watch Question

Most Valuable Expert 2015

Commented:
If all you need to display is a count of records, you can do that in sql.  Just group the results by month and year number. The syntax is db specific but something like

SELECT  month(dateEntered) AS monthNum,
              year(dateEntered) AS YearNum, COUNT(*) AS TotalRecords
FROM     sa_Requests
GROUP BY month(dateEntered) AS monthNum,
              year(dateEntered) AS YearNum
 ORDER BY YearNum, MonthNum

Most Valuable Expert 2015
Commented:
> ORDER BY YearNum, MonthNum

Since they're ordered by Year and Month, you can use "group"d output to display and calculate the counts by year.

<cfoutput query="qryName" group="YearNum">
        <!--- display year header once --->
      <strong>#YearNum#</strong><br>
      
         <!--- reinitialize totals --->
      <cfset yearTotal = 0>
      <cfoutput>
            #MonthAsString(MonthNum)#: #TotalRecords#<br>
                <!--- add this month to the total --->
            <cfset yearTotal = yearTotal + TotalRecords>
      </cfoutput>

        <!--- display year footer once --->
      <strong>Total for #YearNum#:</strong> #yearTotal#<br>
</cfoutput>

Author

Commented:
THANK YOU!

I'm getting a SQL error "Incorrect syntax near the keyword 'AS'"

Can't figure out where it is - I'm trying to locate.
Most Valuable Expert 2015

Commented:
Sorry, my bad.  I forgot to remove the alias from the GROUP BY clause. It should be:

        GROUP BY month(dateEntered), year(dateEntered)

Author

Commented:
What you have suggested works! YAH! I can accept it at that but I have a part II question as I just had another element added to this. Should I open as another question?

The records that come out of the above query have AMOUNTS associated with them amountAwarded - and they also want to know how much was awarded by monthNum by yearNum.

So - they would want an output like:

2011
October: 1 Total: $50
December: 2 Total: $200
Total for 2011: 3
 
Most Valuable Expert 2015

Commented:
It should be as simple as adding a SUM to the select list. Then output the new column as usual

SELECT  month(dateEntered) AS monthNum
             , year(dateEntered) AS YearNum
            , SUM(Amount) AS TotalAmount
              , COUNT(*) AS TotalRecords
....

Author

Commented:
Hmmm... I'm not sure I know how to incorporate this:

<cfquery name="qry_getRecords" datasource="#request.DSN#">
        SELECT  month(awardDateNeeded) AS monthNum
             , year(awardDateNeeded) AS YearNum
            , SUM(awardAmount) AS TotalAmount
              , COUNT(*) AS TotalRecords

        FROM sa_Requests
                GROUP BY awardAmount, month(awardDateNeeded), year(awardDateNeeded)
                ORDER BY YearNum, MonthNum, AwardAmount
    </cfquery>

with the same output as above but adding totalAmount

Gives:

2011
October: 1 | 50
December: 1 | 100
Total for 2011: 2

--------------------------------------------------------------------------------
2012
January: 1 | 50
January: 1 | 100
Total for 2012: 2

Which isn't what I want.
Most Valuable Expert 2015

Commented:
   > Which isn't what I want.

Okay, what result do you want? :) I must be missing something, because the results above look pretty similar to what you said you wanted here http:#a37245649 - other than the text before the amount:  "Total: "
Most Valuable Expert 2015
Commented:
    > GROUP BY awardAmount, month(awardDateNeeded), year(awardDateNeeded)

Oh wait a minute.... don't add any columns to the GROUP BY. All you should be changing is the select list (only)

SELECT  month(dateEntered) AS monthNum
             , year(dateEntered) AS YearNum
            , SUM(Amount) AS TotalAmount
             , COUNT(*) AS TotalRecords
FROM     sa_Requests
GROUP BY month(dateEntered), year(dateEntered)
ORDER BY YearNum, MonthNum

Author

Commented:
I really thank you for your help with this. This would have taken me endless hours. I always feel like I learn something from you. I was able to arrive at the yearly total amount from using your code, too. Thanks a lot.
Most Valuable Expert 2015

Commented:
That's great! Glad I could help.