SQL Reporting - splitting a column value to other

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 Reporting - splitting a column value to other

Author

Topic

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-17 : 10:37:13

Hi

I am working on a report and my report should look like in the format
Application Priority Total Pending Executed Failed Completed
ILOnline High 4 4 0 0 0
ILOnline Med 0 0 0 0 0
ILOnline Low 0 0 0 0 0

I have prepared a query like below which yields data as below:

use [migration_ccg_bau_db0]
select ts_User_07 as [Project No], ts_user_09 as Priority, ts_exec_status as [Execution Status],
count(ts_exec_status) as [Count] from td.Test where ts_user_07 in ('P0018075', 'P0019812', 'P0019922',
'P0019947', 'P0019974', 'P0019975', 'P0020085', 'P0020275', 'P0020291','P0020380', 'P0020545', 'P0020620',
'P0020679') and ts_user_01 in ('R2.09', '2.09') group by ts_User_07, ts_user_09, ts_exec_status
order by ts_User_07

Excel format data
Application Priority Status Count
FM 1-High No Run 1
FM 2-Medium Failed 1
FM 2-Medium No Run 4
FM 3-Low Failed 2
HULA 1-High No Run 8
HULA 1-High Passed 1
ILOL NULL No Run 1
ILOL 1-High No Run 13
ILOL 1-High Passed 9
ILOL 2-Medium No Run 37
ILOL 2-Medium Not Completed 2
ILOL 2-Medium Passed 6
ILOL 3-Low Failed 1

The problem is like for the application name, and priority I am not able to generate sql query to separate the status and put that into a different column.
I tried using the cursors but it fetching details with repeated headers (results repeated for all iterations). Can you please help me on this. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:49:58

your status values in excel data and output are not same. Also how did you get count as 4? can you explain?

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-17 : 10:58:50

Thank you for quick enquiry.

Here the above is just a format in which the business wanted. The no 4 is just a sample format given by them
Here are the details:
Total - Count of (Pending+Executed+Failed+Completed)
No in DB the Status is a single column which includes all statuses like (Pending, Executed, Failed and Completed) now the request is that I should report them with grouped on 1.'Project Name' 2.Priority and for each project and priority i should get the count for 'Passed' 'Failed' 'Executed' 'Completed'

Ex: just for understanding for application AOL it has low, medium priorities then i need like this

AOL--Medium--Total-Passed--Failed--Executed--Completed--Pending
AOL--Low--Total-Passed--Failed--Executed--Completed--Pending

I hope I am not making your confused by this.

quote:
Originally posted by visakh16

your status values in excel data and output are not same. Also how did you get count as 4? can you explain?

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-17 : 11:03:03

Here in my code 'Count' -- Count of entries with that status (and grouped first by 1.project name 2.priority)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:05:37

ok. then what you need is this


SELECT Application,Priority,
SUM([Count]) AS Total,
COUNT(CASE WHEN Status='Pending' THEN [Count] ELSE NULL END) AS Pending,
COUNT(CASE WHEN Status='Executed' THEN [Count] ELSE NULL END) AS Executed,
COUNT(CASE WHEN Status='Failed' THEN [Count] ELSE NULL END) AS Failed,
COUNT(CASE WHEN Status='Completed' THEN [Count] ELSE NULL END) AS Completed
FROM Table
GROUP BY Application,Priority

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-17 : 11:30:49

thank you so much for the excellent reply. I think we are almost there. No I am getting the results as below

Project No Priority Total Passed Failed Executed Pending
P0019922 2-Medium 8 1 0 1 1
P0019947 2-Medium 12 1 1 1 1
P0019947 3-Low 2 0 1 0 0
P0019974 1-High 7 1 1 0 0
P0019974 2-Medium 9 1 0 1 0
P0019975 2-Medium 24 1 0 0 0
P0020085 2-Medium 31 1 0 1 1
P0020085 3-Low 1 1 0 0 0
P0020275 1-High 11 1 0 1 0
P0020275 2-Medium 28 1 0 1 0
P0020275 3-Low 1 0 0 1 0
P0020291 1-High 13 1 0 1 1
P0020291 2-Medium 24 1 0 1 0
P0020545 1-High 1 1 0 0 0
P0020545 2-Medium 9 1 0 1 0
P0020620 1-High 5 1 0 1 0
P0020620 2-Medium 86 1 1 1 1
P0020620 3-Low 2 0 1 0 0
P0020679 2-Medium 2 0 0 1 0

all 'Passed', 'Failed' etc are showing only either 1 or 0. (if Total is 10 then all (pass+Fail+exected+pending) sshould sum up to 10). Can you please help on this. Thank you

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:37:49

May be there are more Status apart from the ones you have taken care of. To check, run ..

select distinct Status from Table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:39:04

change count to sum then


SELECT Application,Priority,
SUM([Count]) AS Total,
SUM(CASE WHEN Status='Pending' THEN [Count] ELSE 0 END) AS Pending,
SUM(CASE WHEN Status='Executed' THEN [Count] ELSE 0 END) AS Executed,
SUM(CASE WHEN Status='Failed' THEN [Count] ELSE 0 END) AS Failed,
SUM(CASE WHEN Status='Completed' THEN [Count] ELSE 0 END) AS Completed
FROM Table
GROUP BY Application,Priority

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-17 : 11:52:33

You are AWESOME Visakh. Thank you so much for help. I have been searching for this so long.

Thank you soooo much

Finally this is the one that worked:
SELECT ProjectNo, Priority,
SUM(Number) AS Total,
sum(CASE WHEN ExecutionStatus='Passed' THEN Number ELSE NULL END) AS Passed,
sum(CASE WHEN ExecutionStatus='Failed' THEN Number ELSE NULL END) AS Failed,
sum(CASE WHEN ExecutionStatus='No Run' THEN Number ELSE NULL END) AS Pending,
sum(CASE WHEN ExecutionStatus='Not Completed' THEN Number ELSE NULL END) AS NotCompleted
FROM #ByProject
GROUP BY ProjectNo,Priority
order by ProjectNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:09:33

welcome

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-18 : 07:45:17

One more help required from you

I am trying to put these into an excel file. When I am trying for a dsn connection to sql server 2000 from MS Excel 2003 I keep getting the error Login failed for 'dsmtic\vpatlol'.
I tried creating through VBA code or through menu options in excel but both dint work. Any suggestions. Below is my VBa code

Sub DataExtract()
' Create a connection object.
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "Provider=SQLOLEDB;Data Source=10.2.1.41,1433;Initial Catalog= migration_ccg_bau_db0;uid=dsmtic\vpatlol;pwd=******;"

'Now open the connection.
conn.Open strConn

' Create a recordset object.
Dim rsconn As ADODB.Recordset
Set rsconn = New ADODB.Recordset

With rsPubs

' Assign the Connection object.
.ActiveConnection = conn
' Extract the required records.
.Open "use [migration_ccg_bau_db0] select ts_User_07 as [Project No], ts_user_09 as Priority, ts_exec_status as [Execution Status], count(ts_exec_status) as [Count] from td.Test where ts_user_07 in ('P0018075', 'P0019812', 'P0019922','P0019947', 'P0019974', 'P0019975', 'P0020085', 'P0020275', 'P0020291','P0020380', 'P0020545', 'P0020620','P0020679') and ts_user_01 in ('R2.09', '2.09') group by ts_User_07, ts_user_09, ts_exec_status order by ts_User_07"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsconn

' Tidy up
.Close
End With

conn.Close
Set rsconn = Nothing
Set conn = Nothing

End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources