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

Author

Topic

Zeinab
Starting Member

7 Posts

Posted - 2008-11-08 : 02:51:57

Hello Fiends,
I wrote this query and hereunder is the result. the result is just about 4 employees with userIds in this query. Now I want to have this result for all our 223 employees. How can I change this query?
Thank you in advance.
-------------------------------------------------
Issue D01007 D0262 D0753 D0384
-------------------------------------------------
Regiosteration 8737 1 0 0
CorrectionReg 48900 136 4562 1087
Swap 3972 0 9 0
Trasfer 23 0 0 0
Other 215 0 0 0

Query:

Select 'Regiosteration' Issue,*
From
(
Select UserId ,CASE WHEN [Text]='REG' THEN 1 ELSE 0 END as REG
From History
)as T
PIVOT
(
Sum(REG) for UserId in ([D1007 ],[D0262 ],[D0753 ],[D0384 ])
)as P
union
Select 'CorrectionReg' Issue, *
From
(
Select userID,CASE WHEN [Text]='Correction REG' THEN 1 ELSE 0 END as CorrectionReg
From History
)as T
PIVOT
(
Sum(CorrectionReg) for UserId in ([D1007 ],[D0262 ],[D0753 ],[D0384 ])
)as P
union
Select 'Swap' Issue, *
From
(
Select userID,CASE WHEN [Text]like '%SWAP%' THEN 1 ELSE 0 END as Swap
From History
)as T
PIVOT
(
Sum(Swap) for UserId in ([D1007 ],[D0262 ],[D0753 ],[D0384 ])
)as P
union
Select 'Trasfer' Issue,*
From
(
Select userID,CASE WHEN [Text] like '%Fer%' THEN 1 ELSE 0 END as Fer
From History
)as T
PIVOT
(
Sum(Fer) for UserId in ([D1007 ],[D0262 ],[D0753 ],[D0384 ])
)as P
union
Select 'Other' Issue,*
From
(
Select userID,CASE WHEN [Text] like '%Other%' THEN 1 ELSE 0 END as Other
From History
)as T
PIVOT
(
Sum(Other) for UserId in ([D1007 ],[D0262 ],[D0753 ],[D0384 ])
)as P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 04:06:30

will the employee detail be static?

Zeinab
Starting Member

7 Posts

Posted - 2008-11-08 : 05:05:07

yes, there is a table with 133 records.I have these IDs in both history table and Users in this DB.I want to see A record including Issue and 133 field with 133 UserId.like:
Issue D0000 D0088 D0114 D0116 D0118 D0126 D0127 ....
Registration 12 1 0 100 10 0 ...
-------------
UserId:
------
D0000
D0088
D0114
D0116
D0118
D0126
D0127
D0128
D0136
D0137
.
.
.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 05:19:11

if this for a reporting application, you can very easily get this done in reporting services application by using matrix container.
Alternatively to achieve this in T-sql, you need

SELECT Issue,
SUM(CASE WHEN UserId ='D1007' THEN 1 ELSE 0 END) AS [D1007],
SUM(CASE WHEN UserId ='D0262' THEN 1 ELSE 0 END) AS [D0262],
SUM(CASE WHEN UserId ='D0753' THEN 1 ELSE 0 END) AS [D0753],
SUM(CASE WHEN UserId ='D0384' THEN 1 ELSE 0 END) AS [D0384],
.......
FROM
(
SELECT CASE WHEN [Text]='REG' THEN 'REG'
            WHEN [Text]='Correction REG' THEN  'CorrectionReg'
            WHEN [Text]like '%SWAP%' THEN 'Swap'
            WHEN [Text] like '%Fer%' THEN 'Fer'
            WHEN [Text] like '%Other%' THEN 'Other'
       END AS Issue,
       userID
FROM History)t
GROUP BY Issue

but one limitation here is its not scalable, tommorow if you add a new user you've come back and add it to the above query.
If you want to avoid this, you need to go for dynamic cross tabing.

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

Zeinab
Starting Member

7 Posts

Posted - 2008-11-08 : 05:33:37

thanks for your reply
I want to show all userIDs in the result without writing them in the query.I want read the IDs from it's table and calculate REG,... for every userId.It means that we should have 133 userId and an issue in every single record.

regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 05:36:06

in that case go for second method, as first one requires you to give all 133 ids in query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 05:43:52

or another method would be to enhance yur current query as follows

DECLARE @userIDlist varchar(max),@Sql varchar(max)

SELECT @userIDlist='['+ REPLACE(LEFT(ul.UserIDList,LEN(ul.UserIDList)-1),',','],[') + ']'
FROM
(SELECT CAST(userID AS varchar(10)) + ','
 FROM History
 FOR XML PATH(''))ul(UserIDList)

SET @Sql='Select ''Registeration'' AS Issue,*
From 
(
Select UserId ,CASE WHEN [Text]=''REG'' THEN 1 ELSE 0 END as REG
From History 
)as T
PIVOT
(
Sum(REG) for UserId in ('+ @userIDlist +') 
)as P
union
Select ''CorrectionReg'' Issue, *
From 
(
Select userID,CASE WHEN [Text]=''Correction REG'' THEN 1 ELSE 0 END as CorrectionReg
From History
)as T
PIVOT
(
Sum(CorrectionReg) for UserId in ('+ @userIDlist +')
)as P
union
Select ''Swap'' Issue, *
From 
(
Select userID,CASE WHEN [Text]like ''%SWAP%'' THEN 1 ELSE 0 END as Swap
From History
)as T
PIVOT
(
Sum(Swap) for UserId in ('+ @userIDlist +')
)as P
union
Select ''Transfer'' Issue,*
From 
(
Select userID,CASE WHEN [Text] like ''%Fer%'' THEN 1 ELSE 0 END as Fer
From History
)as T
PIVOT
(
Sum(Fer) for UserId in ('+ @userIDlist +')
)as P
union
Select ''Other'' Issue,*
From 
(
Select userID,CASE WHEN [Text] like ''%Other%'' THEN 1 ELSE 0 END as Other
From History
)as T
PIVOT
(
Sum(Other) for UserId in ('+ @userIDlist +')
)as P'

EXEC (@Sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources