Query to make a 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

Query to make a report

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2012-02-28 : 04:48:30

I have table view with columns BRANCH, CREATION_DATE, VIOLATION_COUNT,VIOLATION_AMOUNT.

Here I have to create a report on monthly and day wise like

The heading will be like

Month---------------BRANCH1----------------------------BRANCH2-----------------COUNT_TOTAL---AMOUNT_TOTAL
----------Violation_Count--Violation_Amount---Violation_Count-Violation_Amount

Here under each branch there should be two sub heading as mentioned Violation_Count & Violation_Amount.

end of heading

Display of data will be
Jan 1-----------50---------------450-----------------15--------------4542---------------65--------4992
Jan 2-----------10---------------102-----------------30--------------1570---------------40--------1672
Jan 3-----------450--------------12------------------498-------------45-----------------948--------57
etc
etc up to January 31

End of data display

[url][/url][url][/url][url][/url][url][/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 12:30:37

do you've calendar table containing all the dates? you need to LEFT JOIN your tables to this
not sure how you will get the rest of values. can you show some sample data from tableS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2012-02-29 : 06:06:26

I got the query something like

select to_char(creation_date,'Mon') Month, to_char(creation_date,'DD') Day,
sum(case when branch='R-16' then violation_count end) Moraba_Count, sum(case when branch='R-16' then violation_amount end) Moraba_Amount,
sum(case when branch='R-21' then violation_count end) Rawabi_Count, sum(case when branch='R-21' then violation_amount end) Rawabi_Amount,
sum(violation_count) Total_Count, sum(violation_amount) Total_Amount
from cm_violation_totals where month_name = 'JAN'
group by to_char(creation_date,'Mon'),to_char(creation_date,'DD') order by day

now I need to find sum on column wise. Row wise sum is working fine.
So anyone can help me to alter with the above query to find the sum of each columns.

Regards
Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:56:32

quote:
Originally posted by shajimanjeri

I got the query something like

select to_char(creation_date,'Mon') Month, to_char(creation_date,'DD') Day,
sum(case when branch='R-16' then violation_count end) Moraba_Count, sum(case when branch='R-16' then violation_amount end) Moraba_Amount,
sum(case when branch='R-21' then violation_count end) Rawabi_Count, sum(case when branch='R-21' then violation_amount end) Rawabi_Amount,
sum(violation_count) Total_Count, sum(violation_amount) Total_Amount
from cm_violation_totals where month_name = 'JAN'
group by to_char(creation_date,'Mon'),to_char(creation_date,'DD') order by day

now I need to find sum on column wise. Row wise sum is working fine.
So anyone can help me to alter with the above query to find the sum of each columns.

Regards
Shaji

Looks like Oracle code
Please keep in mind that this is a ms sql server forum so solutions given here are mostly sql server specific
In sql server your requirement can be achieved using CUBE or ROLLUP functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources