Group over WEEK, MONTH & YEAR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group over WEEK, MONTH & YEAR

Author

Topic

chilling
Starting Member

1 Post

Posted - 2009-05-21 : 05:05:53

I would like to show a table with the with the following headings:

WeekNo, MonthNo, Year, WeekNetAmount, MonthNetAmount, YearNetAmount

where the NetAmount columns show the accumulated NetAmount from the beginning of Week, Month, Year to today's date for the table GDax_AuditInvoiceItem which has the fields TRANS_DATE and NETAMOUNT.

This query sums only the NetAmount for the week in all three colums:

SELECT
  DATEPART(wk, TRANS_DATE) AS WeekNo, 
  DATEPART(month, TRANS_DATE) AS MonthNo, 
  DATEPART(year, TRANS_DATE) AS Year, 
  SUM(CASE WHEN DATEPART(wk, dbo.GDax_AuditInvoiceItem.TRANS_DATE) 
          = DATEPART(wk, GETDATE()) 
         AND DATEPART(year, dbo.GDax_AuditInvoiceItem.TRANS_DATE) 
          = DATEPART(year, GETDATE()) 
       THEN dbo.GDax_AuditInvoiceItem.NET_AMOUNT 
       ELSE 0 
      END) 
      AS WeekNetAmount, 
  SUM(CASE WHEN DATEPART(month, dbo.GDax_AuditInvoiceItem.TRANS_DATE) 
          = DATEPART(month, GETDATE()) 
         AND DATEPART(year, dbo.GDax_AuditInvoiceItem.TRANS_DATE) 
          = DATEPART(year, GETDATE()) 
       THEN dbo.GDax_AuditInvoiceItem.NET_AMOUNT 
       ELSE 0 
      END) 
      AS MonthNetAmount, 
  SUM(CASE WHEN DATEPART(year, dbo.GDax_AuditInvoiceItem.TRANS_DATE)
          = DATEPART(year, GETDATE()) 
       THEN dbo.GDax_AuditInvoiceItem.NET_AMOUNT 
       ELSE 0 
      END) 
      AS YearNetAmount
FROM dbo.GDax_AuditInvoiceItem
GROUP BY 
  DATEPART(wk, TRANS_DATE), 
  DATEPART(month, TRANS_DATE), 
  DATEPART(year, TRANS_DATE)
ORDER BY Year, WeekNo

Suggestions as to how to get the correct month and year totals - thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:09:05

[code]
SELECT WeekNo, MonthNo, [Year], WeekNetAmount, MonthNetAmount,YearNetAmount
FROM
(
SELECT DATEPART(wk,TRANS_DATE) AS WeekNo,MONTH(TRANS_DATE) AS MonthNo,YEAR(TRANS_DATE) AS [Year]
FROM dbo.GDax_AuditInvoiceItem
GROUP BY DATEPART(wk,TRANS_DATE),MONTH(TRANS_DATE),YEAR(TRANS_DATE)
)m
CROSS APPLY (SELECT SUM(CASE WHEN DATEPART(wk,TRANS_DATE)>=m.WeekNo THEN NETAMOUNT ELSE 0 END) AS WeekNetAmount,
SUM(CASE WHEN MONTH(TRANS_DATE)>=m.MonthNo THEN NETAMOUNT ELSE 0 END) AS MonthNetAmount,
SUM(NETAMOUNT) AS YearNetAmount
FROM dbo.GDax_AuditInvoiceItem
WHERE YEAR(TRANS_DATE)>=m.[Year]
AND TRANS_DATE<DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)
) NetAmt
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources