Sql Query for Monthly 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

SQL Server 2008 Forums

Transact-SQL (2008)

Sql Query for Monthly report

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-28 : 20:40:58

Hi i am working in sqlserver 2008 R2. I have the requirement as like below. I am building monthly report and will showing in asp.net gridview. I will be passing month and year from application. My table structure as follows.

TableName: Cust_Data
Columns : id, shop_name, date_records_in, noofrecords

everyday once i will be getting records for this table. so based on month and year i will have to fetch the shop name, date and records count and will have to show.

for example :

If i send month as 08 and year as 2013 i will have fetch all the data(shopname, date and records count) falls under this month of year and display as in the format :

shopname 08/1 08/02 08/03 08/04.......... 08/31

wallmart 103 105 209 305 ......... 207
amazon 204 340 333 345 .......... 204

can anyone please show me some sample query / procedure for this requirement.

Thanks in advance

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-29 : 00:27:21

Here date_records_in should be in [MM\D] format....
SELECT *
FROM ( SELECT shop_name, date_records_in, noofrecords FROM WHERE <condition for Month & Year > ) p
PIVOT (SUM(noofrecords) FOR date_records_in IN ( [08/1], [08/2], ................)) pvt

If you are not able to sort out, post us some sample data....
Let us know that the date_records_in data format....

--
Chandu

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 01:54:01

Try this one http://sqlfiddle.com/#!3/b5c3b/14

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:35:59

for getting dates in required format you might have to use logic like

CONVERT(varchar(5),datefield,101)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 07:56:39

Hello everyone,

Thanks for the reply and AS I said in my earlier thread i am getting data everyday for this table.i have to generate the column as (mm/dd) as for full month.

My table sample data :

Id Shopname date_records_in noofrecords

1 wallmart 2013-08-01 103
2 wallmart 2013-08-02 203
3 wallmart 2013-08-03 107
4 wallmart 2013-08-04 100
5 wallmart 2013-08-05 106
6 wallmart 2013-08-06 108
7 wallmart 2013-08-07 203
.
.
.
31 Amazon 2013-08-31 300
32 Amazon 2013-08-01 103
33 Amazon 2013-08-02 203
34 Amazon 2013-08-03 107
35 Amazon 2013-08-04 100
36 Amazon 2013-08-05 106
37 Amazon 2013-08-06 108
38 Amazon 2013-08-07 203
.
.
.
62 Amazon 2013-08-31 300

my sample query to fetch the exact records:

SELECT shop_name, date_records_in, noofrecords from Cust_Data where DATEPART(MM, date_records_in) = 8 and DATEPART(yy, date_records_in) = 2013

out come of this query is correct results. but how can i make it as like pivot bellow.

shopname 08/1 08/02 08/03 08/04.......... 08/31

wallmart 103 105 209 305 ......... 207
amazon 204 340 333 345 .......... 204

can u please help me

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 00:31:09

[code]SELECT *
FROM (
SELECT shop_name, noofrecords
,REPLACE(CONVERT(VARCHAR(5), date_records_in, 10), '-', '\') AS date_records_in
from Cust_Data
where DATEPART(MM, date_records_in) = 8 and DATEPART(yy, date_records_in) = 2013) P
PIVOT (MAX(noofrecords) FOR date_records_in IN ([08\01],[08\02],[08\03],[08\04],[08\05],[08\06],[08\07],[08\08],[08\09],[08\10],[08\11],[08\12],[08\13],[08\14],[08\15],[08\16],[08\17],[08\18],[08\19],[08\20],[08\21],[08\22],[08\23],[08\24],[08\25],[08\26],[08\27],[08\29],[08\30],[08\31]))pvt[/code]

NOTE: if you wish to have dynamic values as part of PIVOT columns
1) declare a variable
2) get the list of comma separated values in the form od [MM\DD]
3) prepare above Pivot query dynamically...

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:57:41

whats was the problem with bandis suggestion? did you try it at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-30 : 12:10:54

On Bandi's sugestion "FOR date_records_in" IN shouldn't be hard coded. instead based on month and year the dates should as dynamic



SELECT * 
FROM (
SELECT shop_name, noofrecords
	,REPLACE(CONVERT(VARCHAR(5), date_records_in, 10), '-', '\') AS date_records_in
from Cust_Data 
where DATEPART(MM, date_records_in) = 8 and DATEPART(yy, date_records_in) = 2013) P
PIVOT (MAX(noofrecords) FOR date_records_in IN ( Dynamic and not to be hard coded))pvt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 05:07:00

quote:
Originally posted by sqllover

On Bandi's sugestion "FOR date_records_in" IN shouldn't be hard coded. instead based on month and year the dates should as dynamic

SELECT *
FROM (
SELECT shop_name, noofrecords
,REPLACE(CONVERT(VARCHAR(5), date_records_in, 10), '-', '\') AS date_records_in
from Cust_Data
where DATEPART(MM, date_records_in) = 8 and DATEPART(yy, date_records_in) = 2013) P
PIVOT (MAX(noofrecords) FOR date_records_in IN ( Dynamic and not to be hard coded))pvt

see how you can make it dynamic here

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-03 : 06:39:27

Thanks visakh and am done with my requirement. appreciate your consideration on my thread.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-03 : 09:19:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources