Group Records by Date Range in SQL

[Españolita]

I have an Access Database. The payments table (tblPay) is:
pdID Autonumber (key)
pdStudent Long (links to Student Table)
pdClass Date/Time (holds date of class given)
pdPaid Integer (Amount paid for that class)

I have records going back to 2010.
I can easily obtain a list of records BETWEEN a range of dates, but that is not what I'm after.
I want to be able to produce a SQL statement that will give me the total paid in each Month or Year.
For example, the output I am after would be:

January 2010 1.050 €
February 2010 985 €
March 2010 1,190 €

or

2010 18.150 €
2011 21.410 €
2012 25.100 €

Unfortunately, after much searching on the web, I cannot find anything helpful. Maybe I was using the wrong search phrases but any guidance would be appreciated.

[TysonLPrice]

This is SQL Server. I think the sum is probably what you need. Just tweak the dates to what you want:

create table #VBForums(pdStudent int, pdClass DateTime, pdPaid decimal(6,2))
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)

select convert(varchar,pdClass,101) 'date' , sum(pdPaid)
from #VBForums
group by convert(varchar,pdClass,101)
drop table #VBFOrums

Output:

07/28/2012 60.20
07/29/2012 40.20
07/30/2012 20.20

[techgnome]

the output is still incorrect though.... it's still by date... not month/year...


Try this:

create table #VBForums(pdStudent int, pdClass DateTime, pdPaid decimal(6,2))
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)

--select convert(varchar,pdClass,101) 'date' , sum(pdPaid)
--from #VBForums
--group by convert(varchar,pdClass,101)

select DATENAME(month, pdClass) + ' ' + DATENAME(year, pdClass) as [Date], sum(pdPaid)
from #VBForums
group by DATENAME(month, pdClass) + ' ' + DATENAME(year, pdClass)

drop table #VBFOrums

Result:
July 2012 120.60

-tg

[Españolita]

tg,
yes, that is precisely the result I want. Unfortunately, when I used the code, it threw an error message "The function "DATENAME" is not defined in the expression"

I looked up the error message and found a site which said the code posted worked on SQL Server but not Oracle Server.

I don't know the server I'm using.

The database is accessed and opened by:
In Module

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Public Sub OpenFile()
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & App.Path & "\Student.mdb;Persist Security Info=False"
    cn.Open
    Set rs = New ADODB.Recordset
End Sub

In Form:

....
   If cboStats.ListIndex = 8 Then
      sSQL = "SELECT DATENAME(month, pdClass) + ' ' + DATENAME(year, pdClass) as [Date], sum(pdPaid)"
      sSQL = sSQL & " FROM tblPay"
      sSQL = sSQL & " GROUP BY DATENAME(month, pdClass) + ' ' + DATENAME(year, pdClass)"
   End If
   ShowData (sSQL)
.....


Private Sub ShowData(ByVal sSQL As String)
   OpenFile
   rs.CursorLocation = adUseClient
   rs.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
   Set dgStats.DataSource = rs
   dgStats.Refresh
End Sub

And the App has a reference to Microsoft ActiveX Data Objects 6.0 Library.

Hopefully this can tell you what server I'm using.

[techgnome]

That's because DATENAME is a SQL Server function... Looks like you are using Access... so try this:

create table #VBForums(pdStudent int, pdClass DateTime, pdPaid decimal(6,2))
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(1 , getdate() -2, 10.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(2 , getdate() -3, 20.10)

insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)
insert into #VBForums(pdStudent, pdClass, pdPaid) values(3 , getdate() -4, 30.10)

--select convert(varchar,pdClass,101) 'date' , sum(pdPaid)
--from #VBForums
--group by convert(varchar,pdClass,101)

select Format("MMMM YYYY", pdClass) as [Date], sum(pdPaid)
from #VBForums
group by Format("MMMM YYYY", pdClass)

drop table #VBFOrums

I think I got the parameters right for the FORMAT function...

-tg

[Españolita]

The format was almost right, it was actually the other way round: Format(pdClass, 'MMM YYYY'). I modified what you gave me slightly to give me:

      sSQL = "select Format(pdClass,'YYYY') as [Year],Format(pdClass,'MMMM') as [Month], sum(pdPaid) AS Total_Earned FROM tblPay"
      sSQL = sSQL & " group by Format(pdClass,'YYYY'), Format(pdClass,'MMMM') ORDER BY Format(pdClass,'YYYY'), Format(pdClass,'MMMM')"

You'll notice that I added an ORDER BY. The reason was that it sorted the output correctly for the years but the months were random.
August
December
July
June
November
October
September

I was attempting to get it ordered by year and Month but it didn't work. In fact, the output is exactly the same whether the ORDER BY is present or not.
How can I order the months as well. I've just noticed, the months ARE ordered, but alphabetically (in Spanish re my region settings), not logically. Is there a way to order them, January, February etc.

[techgnome]

use mm in the order by ... then it'll use the numeric month value.

-tg

[smendoza]

I would format the ORDER BY to convert the months to a numeric value instead of August, December and so on. Then you can order the months which would be 1-12 ASC or DESC

[Españolita]

I tried using 'mm' in the order by and it threw the error: You tried to run a search that doesn't include the specified expression 'FORMAT(pdClass,'MM') as part of an included function.

      sSQL = "SELECT FORMAT(pdClass,'YYYY') AS [Year], FORMAT(pdClass,'MMMM') AS [Month]"
      sSQL = sSQL & ", SUM(pdPaid) AS Total_Earned FROM tblPay"
      sSQL = sSQL & " GROUP BY FORMAT(pdClass,'YYYY'), FORMAT(pdClass,'MMMM')"
      sSQL = sSQL & " ORDER BY FORMAT(pdClass,'MM')"

So I messed around a bit and discovered that I need ORDER BY FORMAT(pdClass,'YYYY') as well.

      sSQL = "SELECT FORMAT(pdClass,'YYYY') AS [Year], FORMAT(pdClass,'MMMM') AS [Month]"
      sSQL = sSQL & ", SUM(pdPaid) AS Total_Earned FROM tblPay"
      sSQL = sSQL & " GROUP BY FORMAT(pdClass,'YYYY'), FORMAT(pdClass,'MMMM')"
      sSQL = sSQL & ", FORMAT(pdclass,'mm') ORDER BY FORMAT(pdClass,'YYYY'), FORMAT(pdClass,'MM')"
      ShowData (sSQL)

Thank you both for your help.