Query to generate 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 2012 Forums

Transact-SQL (2012)

Query to generate report

Author

Topic

ivlnr76
Starting Member

3 Posts

Posted - 2013-07-04 : 07:35:47

Hi,

I need to generate below report which is priority wise status count report for each application. Format is like below:
-------------------------------------------------------------
Priority1 priority2 priority3
--------------------------------------------------------------
Open|Closed Open|Closed Open|Closed
---------------------------------------------------------------
App1
----------------------------------------------------------------
App2
--------------------------------------------------------------
App3

Please help with query.

Thanks
RLN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 07:43:42

without knowing tables, their structure and seeing some sample data, how do you expect someone to provide you with a solution? Post some sample data from tables along with structure and then somebody might be able to help

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

ivlnr76
Starting Member

3 Posts

Posted - 2013-07-04 : 08:40:50

Hi,

Sorry, Here is the sample format. Here HTML format is not enabled hence I have given like below:

All this data will be in single table only.
Name:Tbl_transaction
Columns:ID Numeric identity
Applicaiton varchar(50)
Priority varchar(50)
Status varchar(10)
Insert into tbl_transaction values (1,'app1','High','Open')
Insert into tbl_transaction values (2,'app1','Medium','Open')
Insert into tbl_transaction values (3,'app1','Medium','Closed')
Insert into tbl_transaction values (4,'app2','High','Open')
Insert into tbl_transaction values (5,'app2','Medium','Closed')
Insert into tbl_transaction values (6,'app2','Low','Closed')
Insert into tbl_transaction values (7,'app3','High','Open')
Insert into tbl_transaction values (8,'app3','Low','Closed')
Insert into tbl_transaction values (9,'app3','Low','Closed')

Sample format:
High Medium Low
---------------------------------------------
Open|Closed Open|Closed Open|Closed
----------------------------------------------------
App1 10 | 3 5 | 8 12|4
App2 34 |12 16 | 34 11|8
App3 76 |4 32 | 3 78|22
---------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 08:53:02

[code]
SELECT Application,
SUM(CASE WHEN Priority = 'High' AND Status ='Open' THEN 1 ELSE 0 END) AS High_Open,
SUM(CASE WHEN Priority = 'High' AND Status ='Closed' THEN 1 ELSE 0 END) AS High_Closed,
SUM(CASE WHEN Priority = 'Medium' AND Status ='Open' THEN 1 ELSE 0 END) AS Medium_Open,
SUM(CASE WHEN Priority = 'Medium' AND Status ='Closed' THEN 1 ELSE 0 END) AS Medium_Closed,
SUM(CASE WHEN Priority = 'Low' AND Status ='Open' THEN 1 ELSE 0 END) AS low_Open,
SUM(CASE WHEN Priority = 'Low' AND Status ='Closed' THEN 1 ELSE 0 END) AS Low_Closed
FROM table
GROUP BY Application
[/code]

the output format you need can be created in your front end application tool using controls

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

wasif
Starting Member

1 Post

Posted - 2013-07-04 : 15:18:13

Hi,
The way you need the result can be opted from Data Warehouse report, but from OLTP perspective Visakh16 has mentioned the right query to get the results.

Thanks

Wasif Muneer

ivlnr76
Starting Member

3 Posts

Posted - 2013-07-05 : 00:21:15

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 01:39:49

quote:
Originally posted by wasif

Hi,
The way you need the result can be opted from Data Warehouse report, but from OLTP perspective Visakh16 has mentioned the right query to get the results.

Thanks

Wasif Muneer

Whats datawarehouse report?
Do you mean SQL Server Reporting Services?

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