July 31, 2011 at 10:26 pm
Hello,
I have a table for which structure is below:
nCounter numeric
vInOutTag varchar(1)
nEmpNo numeric
dtInOut datetime
I want to plot the data from this table horizontally so I wrote following sql statement
select nCounter, vInOutTag, nEmpNo,
case when day(dtinout) = 1 then dtinout end as col1,
case when day(dtinout) = 2 then dtinout end as col2,
case when day(dtinout) = 3 then dtinout end as col3,
case when day(dtinout) = 4 then dtinout end as col4,
case when day(dtinout) = 5 then dtinout end as col5,
case when day(dtinout) = 6 then dtinout end as col6,
case when day(dtinout) = 7 then dtinout end as col7,
case when day(dtinout) = 8 then dtinout end as col8,
case when day(dtinout) = 9 then dtinout end as col9,
case when day(dtinout) = 10 then dtinout end as col10,
case when day(dtinout) = 11 then dtinout end as col11,
case when day(dtinout) = 12 then dtinout end as col12,
case when day(dtinout) = 13 then dtinout end as col13,
case when day(dtinout) = 14 then dtinout end as col14,
case when day(dtinout) = 15 then dtinout end as col15,
case when day(dtinout) = 16 then dtinout end as col16,
case when day(dtinout) = 17 then dtinout end as col17,
case when day(dtinout) = 18 then dtinout end as col18,
case when day(dtinout) = 19 then dtinout end as col19,
case when day(dtinout) = 20 then dtinout end as col20,
case when day(dtinout) = 21 then dtinout end as col21,
case when day(dtinout) = 22 then dtinout end as col22,
case when day(dtinout) = 23 then dtinout end as col23,
case when day(dtinout) = 24 then dtinout end as col24,
case when day(dtinout) = 25 then dtinout end as col25,
case when day(dtinout) = 26 then dtinout end as col26,
case when day(dtinout) = 27 then dtinout end as col27,
case when day(dtinout) = 28 then dtinout end as col28,
case when day(dtinout) = 29 then dtinout end as col29,
case when day(dtinout) = 30 then dtinout end as col30,
case when day(dtinout) = 31 then dtinout end as col31
from tblInOutTime
This query gives me the data in slant chart type format i.e.
nCounter vInOut empno col1 col2 col3......col31
1 I 1 14:00 Null Null.......Null
1 O 1 18:00 Null Null.......Null
1 I 1 Null 14:00 Null.......Null
1 O 1 Null 18:00 Null.......Null
1 I 1 Null Null 14:00....Null
1 O 1 Null Null 18:00....Null
My query on this is, is it possible to get the in-out time of an employee on single row using sql query.
I can very well achieve this by using a stored procedure, but I want to avoid use of stored proc.
Pls help
Thanks,
-Milind Shevade
August 1, 2011 at 4:04 am
It would be great if you could provide us with the scripts for creating the table and sample data so that we can help you. the below mentioned link will help you on how to post data to get your answers quickly...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 1, 2011 at 7:39 am
Can you specify what your expected results should look like? Do you want to calculate how many hours employee did work each day or what?
DDL with table setup and some sample data inserts would help as well.
August 1, 2011 at 7:46 am
SELECT nCounter, vInOutTag, nEmpNo,
MAX(case when day(dtinout) = 1 then dtinout end) as col1,
MAX(case when day(dtinout) = 2 then dtinout end) as col2,
MAX(case when day(dtinout) = 3 then dtinout end) as col3,
MAX(case when day(dtinout) = 4 then dtinout end) as col4,
MAX(case when day(dtinout) = 5 then dtinout end) as col5,
MAX(case when day(dtinout) = 6 then dtinout end) as col6,
MAX(case when day(dtinout) = 7 then dtinout end) as col7,
MAX(case when day(dtinout) = 8 then dtinout end) as col8,
MAX(case when day(dtinout) = 9 then dtinout end) as col9,
MAX(case when day(dtinout) = 10 then dtinout end) as col10,
MAX(case when day(dtinout) = 11 then dtinout end) as col11,
MAX(case when day(dtinout) = 12 then dtinout end) as col12,
MAX(case when day(dtinout) = 13 then dtinout end) as col13,
MAX(case when day(dtinout) = 14 then dtinout end) as col14,
MAX(case when day(dtinout) = 15 then dtinout end) as col15,
MAX(case when day(dtinout) = 16 then dtinout end) as col16,
MAX(case when day(dtinout) = 17 then dtinout end) as col17,
MAX(case when day(dtinout) = 18 then dtinout end) as col18,
MAX(case when day(dtinout) = 19 then dtinout end) as col19,
MAX(case when day(dtinout) = 20 then dtinout end) as col20,
MAX(case when day(dtinout) = 21 then dtinout end) as col21,
MAX(case when day(dtinout) = 22 then dtinout end) as col22,
MAX(case when day(dtinout) = 23 then dtinout end) as col23,
MAX(case when day(dtinout) = 24 then dtinout end) as col24,
MAX(case when day(dtinout) = 25 then dtinout end) as col25,
MAX(case when day(dtinout) = 26 then dtinout end) as col26,
MAX(case when day(dtinout) = 27 then dtinout end) as col27,
MAX(case when day(dtinout) = 28 then dtinout end) as col28,
MAX(case when day(dtinout) = 29 then dtinout end) as col29,
MAX(case when day(dtinout) = 30 then dtinout end) as col30,
MAX(case when day(dtinout) = 31 then dtinout end) as col31
FROM dbo.tblInOutTime
--WHERE...
GROUP BY nCounter, vInOutTag, nEmpNo
August 3, 2011 at 5:00 am
Hello,
Following is the script
CREATE TABLE [dbo].[tblInOutTime](
[nCounter] [numeric](18, 0) NOT NULL,
[nCounterWorkPermit] [numeric](18, 0) NOT NULL,
[dtInOut] [datetime] NULL,
[vInOutTag] [varchar](1) NULL
)
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('1','1','7/29/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('2','1','7/29/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('3','1','7/30/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('4','1','7/30/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('5','1','7/31/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('6','1','7/31/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('7','1','8/1/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('8','1','8/1/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('9','1','8/2/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('10','1','8/2/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('11','1','8/3/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('12','1','8/3/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('13','1','8/4/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('14','1','8/4/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('15','1','8/5/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('16','1','8/5/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('17','1','8/6/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('18','1','8/6/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('19','1','8/7/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('20','1','8/7/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('21','1','8/8/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('22','1','8/8/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('23','1','8/9/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('24','1','8/9/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('25','1','8/10/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('26','1','8/10/2011 18:00','O')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('27','1','8/11/2011 14:00','I')
insert into tblInOutTime (nCounter,nCounterWorkPermit,dtInOut,vInOutTag) values ('28','1','8/11/2011 18:00','O')
The expected for the above data output should be
<-------------------- days in month max(31)---------------->
ContractorWorkPermit InOut 1 2 3 4 5 6 7 8 9 10........ 31
1 I 14:00 14:00 14:00 14:00 14:00 14:00 14:00.........
1 O 18:00 18:00 18:00 18:00 18:00 18:00 18:00.........
Thanks,
Milind
August 3, 2011 at 5:55 am
What's wrong with the pivot function I gave you?
What have you tried and where are you having problems?
August 3, 2011 at 6:06 am
Hi,
The output do not come as I expect.
What i want is all the InTime values of 31days should come in one row. All the OutTime values of 31 days should come on next row. Where as the output is only one value per row... i.e. there are 31 rows for 31 intime values, 31 rows for 31 Outtime values.
-Milind
August 3, 2011 at 6:31 am
Your group by is wrong. It's stopping the MAX from bringing it all to 1 row.
August 3, 2011 at 6:37 am
You are right... Now it has worked... Thanks a lot...
- Milind
August 3, 2011 at 6:50 am
Happy to help!
L8rs!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply