Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DateTime Rows to Columns Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:57 AM
Points: 104, Visits: 435
Hi

I have something like a table of users and other table with dates and working time for the users, i want to display the working hours for the users, but i need to see the date in columns.

I created a little script to show what i want, but i'm having a problem with the duplicate users.

DROP TABLE #Users
DROP TABLE #Working
CREATE TABLE #Users (Num INT, NAME NVARCHAR(5))
CREATE TABLE #Working (WorkDay SMALLDATETIME,Num INT, TimeWorked INT)


INSERT INTO #Users
SELECT 1,'User1'
UNION ALL
SELECT 2,'User2'
UNION ALL
SELECT 3,'User3'



INSERT INTO #Working
SELECT '01/01/2013',1,2
UNION ALL
SELECT '02/01/2013',1,0
UNION ALL
SELECT '03/01/2013',1,1
UNION ALL
SELECT '04/01/2013',1,4
UNION ALL
SELECT '05/01/2013',1,0
UNION ALL

SELECT '01/01/2013',2,0
UNION ALL
SELECT '02/01/2013',2,0
UNION ALL
SELECT '03/01/2013',2,20
UNION ALL
SELECT '04/01/2013',2,20
UNION ALL
SELECT '05/01/2013',2,20
UNION ALL

SELECT '01/01/2013',3,30
UNION ALL
SELECT '02/01/2013',3,30
UNION ALL
SELECT '03/01/2013',3,0
UNION ALL
SELECT '04/01/2013',3,0
UNION ALL
SELECT '05/01/2013',3,0


SELECT * FROM #Users
SELECT * FROM #Working


SELECT DISTINCT
U.Num,
U.NAME,
[1] = CASE WHEN MONTH(W.WorkDay) = 1 THEN W.TimeWorked ELSE 0 END,
[2] = CASE WHEN MONTH(W.WorkDay) = 2 THEN W.TimeWorked ELSE 0 END,
[3] = CASE WHEN MONTH(W.WorkDay) = 3 THEN W.TimeWorked ELSE 0 END,
[4] = CASE WHEN MONTH(W.WorkDay) = 4 THEN W.TimeWorked ELSE 0 END,
[5] = CASE WHEN MONTH(W.WorkDay) = 5 THEN W.TimeWorked ELSE 0 END
FROM #Users U INNER JOIN #Working W ON U.Num = W.Num

In the end the query will be built on the fly, with the help of the tally table, i just pass 2 paramteres to the sp, the year and the month...

Thanks
Post #1425648
Posted Friday, March 1, 2013 11:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
The request is a little unclear to me. Can you supply a sample of what you would expect from the resultset so we can understand further?
Post #1425661
Posted Friday, March 1, 2013 4:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:57 AM
Points: 104, Visits: 435
Hi
I have to reply with text, in this machine i don't have any sql server installed.

So if i run the above code i get something like this:
Num Name 1 2 3 4 5 ---> Column Names
1 User1 2 0 0 0 0
1 User1 0 0 0 0 0
1 User1 0 0 1 0 0
1 User1 0 0 0 4 0
1 User1 0 0 0 0 0
and something equal for the other users...

WHat i'm trying to achieve it's to only have one row for each user like this:
Num Name 1 2 3 4 5 ---> Column Names
1 User1 2 0 1 4 0
Post #1425789
Posted Friday, March 1, 2013 9:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 36,755, Visits: 31,211
You were SO close...

SELECT 
U.Num,
U.NAME,
[1] = SUM(CASE WHEN MONTH(W.WorkDay) = 1 THEN W.TimeWorked ELSE 0 END),
[2] = SUM(CASE WHEN MONTH(W.WorkDay) = 2 THEN W.TimeWorked ELSE 0 END),
[3] = SUM(CASE WHEN MONTH(W.WorkDay) = 3 THEN W.TimeWorked ELSE 0 END),
[4] = SUM(CASE WHEN MONTH(W.WorkDay) = 4 THEN W.TimeWorked ELSE 0 END),
[5] = SUM(CASE WHEN MONTH(W.WorkDay) = 5 THEN W.TimeWorked ELSE 0 END)
FROM #Users U INNER JOIN #Working W ON U.Num = W.Num
GROUP BY U.Num, U.Name;

You can learn more about this technique (cross tab) at the following link.
http://www.sqlservercentral.com/articles/T-SQL/63681/
And thank you VERY much for the test data setup and the sample code. It made helping you very easy to do. I wish more people would take the small bit of time.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425815
Posted Monday, March 4, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:57 AM
Points: 104, Visits: 435


End of the day of friday, hard week... dumb questions...

Now it's obvious that i need to sum the values...

Thanks
Post #1426245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse