how to query yearly report? - 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

how to query yearly report?

Author

Topic

zeyi
Starting Member

5 Posts

Posted - 2009-01-12 : 07:25:13

I have the table1 with every selling record, as:
customer_name, timestamp.

how can i get the table of yearly report including every month count?
customer_name, buying_time_200701, buying_time_200702.....sum_2007

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-12 : 07:30:43

Need more explanations regarding your table and your expected report.

zeyi
Starting Member

5 Posts

Posted - 2009-01-12 : 08:04:07

quote:
Originally posted by karthik_padbanaban

Need more explanations regarding your table and your expected report.

my table includes two columns, one is username, another is buying_time. ragarding to selling records, username could be show many times with different buying_time.

the report i want is the table including username, count of buying time in particular month, say January,2008, Feb,2008....in 12 different columns. then sum the number of buying time in this whole year as a colunm as well.

Is that clear? really appreciate if help!!

zeyi
Starting Member

5 Posts

Posted - 2009-01-12 : 08:10:05

like table1 which i have

username timestamp
AA 20080109
BB 20080307
CC 20080711
AA 20080111
AA 20080222
CC 20080714
BB 20081225

the table i expect like

username count_01 count_02 count_03...count_07 count_12 sum_2008
AA 2 1 0 0 0 3
BB 0 0 1 0 1 2
CC 0 0 0 2 0 2

Considering the records in table1 is millions, i need the effective query. thanks a lot.
waiting online!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-12 : 08:23:12

see Cross Tab Reporting in Books Online

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 08:54:42

quote:
Originally posted by zeyi

like table1 which i have

username timestamp
AA 20080109
BB 20080307
CC 20080711
AA 20080111
AA 20080222
CC 20080714
BB 20081225

the table i expect like

username count_01 count_02 count_03...count_07 count_12 sum_2008
AA 2 1 0 0 0 3
BB 0 0 1 0 1 2
CC 0 0 0 2 0 2

Considering the records in table1 is millions, i need the effective query. thanks a lot.
waiting online!

something like


SELECT username,
SUM(CASE WHEN MONTH(timestamp)=1 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Jan2008,
SUM(CASE WHEN MONTH(timestamp)=2 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Feb2008,
SUM(CASE WHEN MONTH(timestamp)=3 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Mar2008,
SUM(CASE WHEN MONTH(timestamp)=4 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Apr2008,
SUM(CASE WHEN MONTH(timestamp)=5 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS May2008,
....
FROM table1
GROUP BY username

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-12 : 08:56:57

or Search for Pivot queries in here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 08:59:39

if you want to do this dynamically use

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

zeyi
Starting Member

5 Posts

Posted - 2009-01-12 : 12:53:53

quote:
Originally posted by visakh16

quote:
Originally posted by zeyi

like table1 which i have

username timestamp
AA 20080109
BB 20080307
CC 20080711
AA 20080111
AA 20080222
CC 20080714
BB 20081225

the table i expect like

username count_01 count_02 count_03...count_07 count_12 sum_2008
AA 2 1 0 0 0 3
BB 0 0 1 0 1 2
CC 0 0 0 2 0 2

Considering the records in table1 is millions, i need the effective query. thanks a lot.
waiting online!

something like

SELECT username,
SUM(CASE WHEN MONTH(timestamp)=1 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Jan2008,
SUM(CASE WHEN MONTH(timestamp)=2 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Feb2008,
SUM(CASE WHEN MONTH(timestamp)=3 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Mar2008,
SUM(CASE WHEN MONTH(timestamp)=4 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS Apr2008,
SUM(CASE WHEN MONTH(timestamp)=5 AND YEAR(timestamp)=2008 THEN 1 ELSE 0 END) AS May2008,
....
FROM table1
GROUP BY username

hi, I don't think this query considering the usename issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:56:44

what username issue?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources