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 2005 Forums
 Transact-SQL (2005)
 Lunch hours

Author  Topic 

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 11:49:27
OK Im somewhat new at this so excuse my ignorance. With that said here is my dilemma. I have a view which returns the first OUT and last IN w/in a specified time range. Now what Id like to do is w/in that same view get the difference of the EVENTIME for ea EMPID listed.
View:

SELECT     TOP (100) PERCENT SERIALNUM, EVENTIME, EMPID, CASE WHEN DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status
FROM         dbo.EVENTS
WHERE     (SERIALNUM IN
                          (SELECT     MIN(SERIALNUM) AS Expr1
                            FROM          dbo.[VW-InOut] AS EVENTS_1
                            WHERE      (EVENTIME > '11:15AM') AND (EVENTIME < '2:15PM') AND (Status = 'IN')
                            GROUP BY EMPID))
UNION
SELECT     TOP (100) PERCENT SERIALNUM, EVENTIME, EMPID, CASE WHEN DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status
FROM         dbo.EVENTS AS EVENTS_2
WHERE     (SERIALNUM IN
                          (SELECT     MIN(SERIALNUM) AS Expr1
                            FROM          dbo.[VW-InOut] AS EVENTS_1
                            WHERE      (EVENTIME > '11:15AM') AND (EVENTIME < '2:15PM') AND (Status = 'OUT')
                            GROUP BY EMPID))
ORDER BY EMPID


Sample data returned:
SERIALNUM EVENTIME EMPID Status
1241082260 5/11/2009 11:20:16 AM 9 OUT
1241082295 5/11/2009 11:23:37 AM 9 IN

Data Id like returned:
SERIALNUM Lunch EMPID
1241082260 00:03:21 9

Any suggestions?
TIA,
Stue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-11 : 11:55:36
[code]
SELECT v.SERIALNUM,CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,v.EVENTTIME,v1.EVENTTIME),0),108),v.EMPID
FROM YourView v
CROSS APPLY (SELECT TOP 1 EVENTTIME
FROM YourView
WHERE EMPID=v.EMPID
AND EVENTTIME > v.EVENTTIME
AND Status='IN'
ORDER BY EVENTTIME)v1
WHERE v.Status='OUT'
[/code]
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 12:10:14
visakh16,
Thanks for the quick response! Is there anyway to concatenate this into one view or am I going to have to stick to 2 views to get the same results?
Thanks again,
Stue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-11 : 12:34:21
you can stick it in a single view. can you show how the data will be in your original table for an empid?
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 13:11:47
Im sorry visakh16 I dont understand. Did you want a sample set from the EVENTS table?
TIA,
Stue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-11 : 13:39:07
yup. thats what i asked for
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 13:52:18
OK, here is a small sample:

SERIALNUM	EVENTIME			EMPID	DEVID
1241080798	5/11/2009 9:37:52 AM	46	5
1241080804	5/11/2009 9:37:56 AM	93	14
1241080817	5/11/2009 9:38:57 AM	26	12
1241080835	5/11/2009 9:40:31 AM	17	22
1241080836	5/11/2009 9:40:56 AM	17	5
1241080846	5/11/2009 9:41:21 AM	59	15
1241080855	5/11/2009 9:41:37 AM	6	21
1241080881	5/11/2009 9:41:58 AM	59	15
1241080903	5/11/2009 9:45:15 AM	18	5
1241080910	5/11/2009 9:45:21 AM	8	5
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 14:12:34
Also, on a tangent, have you ever encountered an error stating: "The CROSS APPLY SQL construct or statement is not supported." I have never used that operator and now when I run the new view on occasion I get that error. After I click OK the view will still be executed successfully.
Have you come across that?
Just Curious,
Steve
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-11 : 14:21:04
[code]SELECT MAX(CASE WHEN Status='OUT' THEN SERIALNUM ELSE NULL END) AS SERIALNUM,
DATEADD(ss,DATEDIFF(ss,MAX(CASE WHEN Status='OUT' THEN EVENTIME ELSE NULL END),MAX(CASE WHEN Status='IN' THEN EVENTIME ELSE NULL END)),0) AS Lunch,
EMPID
FROM
(
SELECT SERIALNUM,
EMPID,
Status,
ROW_NUMBER() OVER (PARTITION BY EMPID,Status ORDER BY SERIALNUM) AS Seq
FROM dbo.[VW-InOut] AS EVENTS_1
WHERE (EVENTIME > '11:15AM') AND (EVENTIME < '2:15PM')
)t
WHERE Seq=1
GROUP BY EMPID
[/code]
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-11 : 14:44:39
Invalid column name 'EVENTIME'
I have to run so Ill have to play more with it later. Thanks for all your help and getting me on the right track visakh16!
Stue
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-05-12 : 01:36:09
That query should be


SELECT MAX(CASE WHEN Status='OUT' THEN SERIALNUM ELSE NULL END) AS SERIALNUM,
DATEADD(ss,DATEDIFF(ss,MAX(CASE WHEN Status='OUT' THEN EVENTIME ELSE NULL END),MAX(CASE WHEN Status='IN' THEN EVENTIME ELSE NULL END)),0) AS Lunch,
EMPID
FROM
(
SELECT SERIALNUM,
EMPID,
Status,
EVENTIME,
ROW_NUMBER() OVER (PARTITION BY EMPID,Status ORDER BY SERIALNUM) AS Seq
FROM dbo.[VW-InOut] AS EVENTS_1
WHERE (EVENTIME > '11:15AM') AND (EVENTIME < '2:15PM')
)t
WHERE Seq=1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-12 : 11:00:53
Thanks madhivanan but Im not getting the correct data. I changed the time from 11:15 to 10:15 in order to get some data back. here are the results:

SERIALNUM	Lunch				EMPID
NULL		NULL				30
NULL		NULL				36
NULL		NULL				40
NULL		NULL				43
1241090414	12/31/1899 11:47:00 PM	49
NULL		NULL				50
1241090472	1/1/1900 12:10:00 AM		54

Thanks again,
Stue
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-13 : 09:38:24
Still at a dead end. Anyone have any other ideas?
TIA,
Stue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-13 : 10:06:38
quote:
Originally posted by madhivanan

That query should be


SELECT MAX(CASE WHEN Status='OUT' THEN SERIALNUM ELSE NULL END) AS SERIALNUM,
DATEADD(ss,DATEDIFF(ss,MAX(CASE WHEN Status='OUT' THEN EVENTIME ELSE NULL END),MAX(CASE WHEN Status='IN' THEN EVENTIME ELSE NULL END)),0) AS Lunch,
EMPID
FROM
(
SELECT SERIALNUM,
EMPID,
Status,
EVENTIME,
ROW_NUMBER() OVER (PARTITION BY EMPID,Status ORDER BY SERIALNUM) AS Seq
FROM dbo.[VW-InOut] AS EVENTS_1
WHERE (EVENTIME > '11:15AM') AND (EVENTIME < '2:15PM')
)t
WHERE Seq=1


Madhivanan

Failing to plan is Planning to fail

why you remove group by? i'm using EMPID in select list should we group by it?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-13 : 10:08:05
quote:
Originally posted by Biscuithead

Thanks madhivanan but Im not getting the correct data. I changed the time from 11:15 to 10:15 in order to get some data back. here are the results:

SERIALNUM	Lunch				EMPID
NULL		NULL				30
NULL		NULL				36
NULL		NULL				40
NULL		NULL				43
1241090414	12/31/1899 11:47:00 PM	49
NULL		NULL				50
1241090472	1/1/1900 12:10:00 AM		54

Thanks again,
Stue

dont you have records with status 'IN','OUT' for each EMPID?
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-13 : 10:30:08
This is the code for the view:

SELECT     MAX(CASE WHEN Status = 'OUT' THEN SERIALNUM ELSE NULL END) AS SERIALNUM, DATEADD(ss, DATEDIFF(ss, 
                      MAX(CASE WHEN Status = 'OUT' THEN EVENTIME ELSE NULL END), MAX(CASE WHEN Status = 'IN' THEN EVENTIME ELSE NULL END)), 0) AS Lunch, EMPID
FROM         (SELECT     SERIALNUM, EMPID, Status, EVENTIME, ROW_NUMBER() OVER (PARTITION BY EMPID, Status
                       ORDER BY SERIALNUM) AS Seq
FROM         dbo.[VW-InOut] AS EVENTS_1
WHERE     (EVENTIME > '01:15AM') AND (EVENTIME < '2:15PM')) t
WHERE     Seq = 1
GROUP BY EMPID

Results are as posted above. No Status column is listed.

Just for reference here is the only other table you dont have a sample set from. The data below comes from the VW-InOut view.

EVENTIME  LASTNAME	FIRSTNAME EXT	READERDESC	DEVID	Staus	EMPID	NAME			SERIALNUM	DeptID
12:26PM   Robbs	Robert	75756	NW Glass Dr	14	IN	119	Inside Sales Group	1241083210	13
11:12AM   Applet     Davie	75759	NE Glass Doors 5	IN	113	Inside Sales Group	1241082159	13
10:28AM   Robbs	Robert	75756	NW Glass Dr	14	IN	119	Inside Sales Group	1241081485	13
11:41AM   Carr	Eva	75719	NE Glass Doors 5	IN	93	Creative Department	1241082554	14
11:29AM   Applet     Davie	75759	out Front Dr  24	OUT	113	Inside Sales Group	1241082389	13

TIA,
Stue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-13 : 10:50:12
your posted data does not have a status of OUT. does that me your wont have OUT records for all employees?
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-13 : 10:56:40
Sorry about that :( I threw an out in the sample set. There will be OUTs as a result of that view. Of course the first few I selected happened to be all INs.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-13 : 10:57:09
so where does you get values for status? or is it a derived column?
Go to Top of Page

Biscuithead
Starting Member

30 Posts
Posted - 2009-05-13 : 11:11:27
Here is the code for my VW-InOut view, I know its prob pretty ugly but like I said before Im still a grub, no wings yet when it comes to SQL programming.

SELECT     TOP (100) PERCENT SUBSTRING(CONVERT(VARCHAR(20), dbo.EVENTS.EVENTIME, 100), 13, 7) AS EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, 
                      dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID, CASE WHEN dbo.EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status, 
                      dbo.EVENTS.EMPID, dbo.DEPT.NAME, dbo.EVENTS.SERIALNUM, dbo.DEPT.ID AS DeptID
FROM         dbo.READER INNER JOIN
                      dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
                      dbo.UDFEMP INNER JOIN
                      dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID INNER JOIN
                      dbo.DEPT ON dbo.UDFEMP.DEPT = dbo.DEPT.ID
WHERE     (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0) AND (dbo.EMP.LASTNAME <> 'TEMP')
ORDER BY EVENTIME

HTH,
Stue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-13 : 11:18:02
try like this

SELECT MAX(CASE WHEN Status='OUT' THEN SERIALNUM ELSE NULL END) AS SERIALNUM,
DATEADD(ss,DATEDIFF(ss,MAX(CASE WHEN Status='OUT' THEN EVENTIME ELSE NULL END),MAX(CASE WHEN Status='IN' THEN EVENTIME ELSE NULL END)),0) AS Lunch,
EMPID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY EMPID,Status ORDER BY SERIALNUM) AS Seq,*
FROM
(
SELECT     SUBSTRING(CONVERT(VARCHAR(20), dbo.EVENTS.EVENTIME, 100), 13, 7) AS EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, 
                      dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID, CASE WHEN dbo.EVENTS.DEVID IN ('23', '24', '25', '26') THEN 'OUT' ELSE 'IN' END AS Status, 
                      dbo.EVENTS.EMPID, dbo.DEPT.NAME, dbo.EVENTS.SERIALNUM, dbo.DEPT.ID AS DeptID
FROM         dbo.READER INNER JOIN
                      dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
                      dbo.UDFEMP INNER JOIN
                      dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID INNER JOIN
                      dbo.DEPT ON dbo.UDFEMP.DEPT = dbo.DEPT.ID
WHERE     (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0) AND (dbo.EMP.LASTNAME <> 'TEMP')
)t
)r
WHERE Seq=1
GROUP BY EMPID
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site