SQL Statement - 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

General SQL Server Forums

New to SQL Server Programming

SQL Statement

Author

Topic

sqlserver107
Starting Member

4 Posts

Posted - 2011-03-31 : 17:55:50

I have a table named DailyTransactionCount with columns AppId, LogDate and DailyCount.

I am trying to get total number of transactions per month in a given date range in this below format, so that I can display the data in my web application with very minimum code.

Any help would be appreciated. I needed to display total number of transactions per month for each application. Thanks.

LogMonth AppId1 AppId2 AppId3 AppId4 AppId5 AppId6

OCT-2010 20 29 12 35 72 34
NOV-2010 40 54 12 32 32 34
DEC-2010 20 24 32 30 22 32
JAN-2010 20 24 12 32 32 34
FEB-2010 10 24 12 32 92 30
MAR-2010 20 34 12 42 32 34

sqlserver107
Starting Member

4 Posts

Posted - 2011-03-31 : 18:00:47

I am using MS-SQL server 2005.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 18:34:24

Do it (or at least think about it) in two steps. In the first step, get the data aggregated by month. So you would do something like this:

select
   AppId,
   Datename(month,logdate) as Mnth,
   Year(logdate) as Yr,
   sum(dailycount) as MnthTotal
from
   DailyTransactionCount
group by
   AppId,
   Datename(month,logdate),
   Year(logdate)

This now gets you the data, but it is not in the correct form that you are looking for. So the second step would be pivoting this data to get it in the form you want.

If you know in advance how many applications you will have, then you can use SQL 2005's built in PIVOT. If you don't, you will need to use dynamic pivoting - see here:http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables or http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

If you are able to use the "static" pivot, it would be like this:

with CTE as
(
 -- insert the previous select that I showed above here
)
select 
   *
from
   CTE
PIVOT ( sum(MnthTotal)  for AppId in([AppId1],[AppId2],[AppId3])) P -- and may be more AppIds

sqlserver107
Starting Member

4 Posts

Posted - 2011-03-31 : 22:08:50

This is exactly what I am looking for. Thank you so much.

Based on your reply, I am trying to get weekly transaction count for each appId in a given date range.

If take the date range 11/17/2010 to 03/22/2011, I am trying to get the weekly count from every monday to

LogMonth AppId1 AppId2 AppId3 AppId4 AppId5 AppId6

17-NOV-2010 20 29 12 35 72 34
22-NOV-2010 40 54 12 32 32 34
29-NOV-2010 20 24 32 30 22 32
06-DEC-2010 20 24 12 32 32 34
13-DEC-2010 10 24 12 32 92 30
MAR-2010 20 34 12 42 32 34

sqlserver107
Starting Member

4 Posts

Posted - 2011-03-31 : 22:13:00

quote:
Originally posted by sqlserver107

This is exactly what I am looking for. Thank you so much.

Based on your reply, I am trying to get weekly transaction count on every Monday for each appId in a given date range.

If take the date range 11/17/2010 to 12/29/2010.

LogMonth AppId1 AppId2 AppId3 AppId4 AppId5 AppId6

17-NOV-2010 20 29 12 35 72 34
22-NOV-2010 40 54 12 32 32 34
29-NOV-2010 20 24 32 30 22 32
06-DEC-2010 20 24 12 32 32 34
13-DEC-2010 10 24 12 32 92 30
20-DEC-2010 20 34 12 42 32 34
27-DEC-2010 20 34 12 42 32 34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources