Help about SQL Query - 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

Help about SQL Query

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2008-06-04 : 11:39:04

Hello forum. I need some help about a query.

I have a table that contains information about the entrances and exits of empleyees when arrive and go out to/from job as:


USER_ID     STAMPING                   ENTRANCE/EXIT (true/false)*
1           1982-05-19 15:30:00.000    true
1           1982-05-19 16:30:00.000    false
1           1982-05-19 22:30:00.000    true
1           1982-05-19 23:59:59.000    false
1           1982-05-20 06:00:00.000    true
1           1982-05-21 06:30:00.000    true
(*) true represents entrances and false exits.

Otherwise, each user has assigned a calendar and each day's calendar has a "day type" and each day type has intervals and each interval has assigned a hour type (Normal/Extra/Nocturnal). Each one is retrieved from:

USER TABLE - (USER_ID | CALENDAR_ID)

example:


USER_ID     CALENDAR_ID
1           1

CALENDAR TABLE - (CALENDAR_ID)
CALENDAR_DAY - (CALENDAR_ID | DAY(datetime) | DAY_TYPE_ID)
example:


CALENDAR_ID   DAY                       DAY_TYPE_ID
1             1982-05-19 00:00:00.000   1
1             1982-05-20 00:00:00.000   1
1             1982-05-21 00:00:00.000   1

DAY_TYPE - (DAY_TYPE_ID)
DAY_INTERVALS - (DAY_INTERVAL_ID | DAY_TYPE_ID | FROM(long)* | TO(long)* | HOUR_TYPE_ID)
example:


DAY_INTERVAL_ID    DAY_TYPE_ID    FROM          TO             HOUR_TYPE_ID
1                  1              216000000000  504000000000   1
2                  1              504000000000  720000000000   2
3                  1              720000000000  840000000000   3       
(*) FROM and TO representes the time of one day

HOUR_TYPE - (HOUR_TYPE_ID | DESCRIPTION)
example:


HOUR_TYPE_ID    DESCRIPTION
1               Normal
2               Extra
3               Nocturnal

for example: One employee has a calendar which at 19/05/1982 has assigned an day_type (night shift) and this day_type has three intervals:
One from 06:00:00(216000000000) to 14:00:00(504000000000)
Other one from 14:00:00 to 22:00:00
The last one from 22:00:00 to 23:59:59
and each one of these "day intervals" has assigned a "hour type", that would represent extra, normal and noctural concept.

So, I would need to obtain how many hours has worked an employee using these tables. Using the sample data above I would need something that:


EMPLOYEE_ID   FROM                      TO                         NORMAL    EXTRA     NOCTURNAL
1             1982-05-19 15:30:00.000   1982-05-19 16:30:00.000    01:00:00  00:00:00  00:00:00
1             1982-05-19 22:30:00.000   1982-05-19 23:59:59.000    00:00:00  00:00:00  01:30:00
1             1982-05-20 06:00:00.000   NULL                       NULL      NULL      NULL
1             1982-05-21 06:30:00.000   NULL                       NULL      NULL      NULL

I've tried to explain me as well as I could.
Can you help me about this?
Thanks for your attention.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 12:43:48

If you provide this information in the form of DDL and DML statements (create table and insert statements for sample data) I think you'll get some help. Also, I'm probably being dense but how exactly is "216000000000" 6:00 AM ?

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 12:49:23

Number of microseconds?

E 12°55'05.25"
N 56°04'39.16"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-04 : 13:44:20

quote:
Originally posted by jeusdi

Hello forum. I need some help about a query.

I have a table that contains information about the entrances and exits of empleyees when arrive and go out to/from job as:

USER_ID STAMPING ENTRANCE/EXIT (true/false)*
1 1982-05-19 15:30:00.000 true
1 1982-05-19 16:30:00.000 false
1 1982-05-19 22:30:00.000 true
1 1982-05-19 23:59:59.000 false
1 1982-05-20 06:00:00.000 true
1 1982-05-21 06:30:00.000 true
(*) true represents entrances and false exits.

Otherwise, each user has assigned a calendar and each day's calendar has a "day type" and each day type has intervals and each interval has assigned a hour type (Normal/Extra/Nocturnal). Each one is retrieved from:

USER TABLE - (USER_ID | CALENDAR_ID)

example:

USER_ID CALENDAR_ID
1 1

CALENDAR TABLE - (CALENDAR_ID)
CALENDAR_DAY - (CALENDAR_ID | DAY(datetime) | DAY_TYPE_ID)
example:

CALENDAR_ID DAY DAY_TYPE_ID
1 1982-05-19 00:00:00.000 1
1 1982-05-20 00:00:00.000 1
1 1982-05-21 00:00:00.000 1

DAY_TYPE - (DAY_TYPE_ID)
DAY_INTERVALS - (DAY_INTERVAL_ID | DAY_TYPE_ID | FROM(long)* | TO(long)* | HOUR_TYPE_ID)
example:

DAY_INTERVAL_ID DAY_TYPE_ID FROM TO HOUR_TYPE_ID
1 1 216000000000 504000000000 1
2 1 504000000000 720000000000 2
3 1 720000000000 840000000000 3
(*) FROM and TO representes the time of one day

HOUR_TYPE - (HOUR_TYPE_ID | DESCRIPTION)
example:

HOUR_TYPE_ID DESCRIPTION
1 Normal
2 Extra
3 Nocturnal

for example: One employee has a calendar which at 19/05/1982 has assigned an day_type (night shift) and this day_type has three intervals:
One from 06:00:00(216000000000) to 14:00:00(504000000000)
Other one from 14:00:00 to 22:00:00
The last one from 22:00:00 to 23:59:59
and each one of these "day intervals" has assigned a "hour type", that would represent extra, normal and noctural concept.

So, I would need to obtain how many hours has worked an employee using these tables. Using the sample data above I would need something that:

EMPLOYEE_ID FROM TO NORMAL EXTRA NOCTURNAL
1 1982-05-19 15:30:00.000 1982-05-19 16:30:00.000 01:00:00 00:00:00 00:00:00
1 1982-05-19 22:30:00.000 1982-05-19 23:59:59.000 00:00:00 00:00:00 01:30:00
1 1982-05-20 06:00:00.000 NULL NULL NULL NULL
1 1982-05-21 06:30:00.000 NULL NULL NULL NULL

I've tried to explain me as well as I could.
Can you help me about this?
Thanks for your attention.

I wasn't sure on the time interval but went with my best guess:

IF OBJECT_ID('tempdb..#TIME') IS NOT NULL
	DROP TABLE #TIME
CREATE TABLE #TIME ( [USER_ID] INT, [STAMPING] DATETIME, [ENTRANCE/EXIT] BIT )
INSERT #TIME ( [USER_ID], [STAMPING], [ENTRANCE/EXIT] )
SELECT 1, '1982-05-19 15:30:00.000', 1 UNION ALL
SELECT 1, '1982-05-19 16:30:00.000', 0 UNION ALL
SELECT 1, '1982-05-19 22:30:00.000', 1 UNION ALL
SELECT 1, '1982-05-19 23:59:59.000', 0 UNION ALL
SELECT 1, '1982-05-20 06:00:00.000', 1 UNION ALL
SELECT 1, '1982-05-21 06:30:00.000', 1

IF OBJECT_ID('tempdb..#USER') IS NOT NULL
	DROP TABLE #USER
CREATE TABLE #USER ( [USER_ID] INT, [CALENDAR_ID] INT )
INSERT #USER ( [USER_ID], [CALENDAR_ID] )
SELECT 1, 1

IF OBJECT_ID('tempdb..#CALENDAR') IS NOT NULL
	DROP TABLE #CALENDAR
CREATE TABLE #CALENDAR ( [CALENDAR_ID] INT, [DAY] datetime, [DAY_TYPE_ID] INT )
INSERT #CALENDAR ( [CALENDAR_ID], [DAY], [DAY_TYPE_ID] )
SELECT 1, '1982-05-19 00:00:00.000', 1 UNION ALL
SELECT 1, '1982-05-20 00:00:00.000', 1 UNION ALL
SELECT 1, '1982-05-21 00:00:00.000', 1

-- assumed FROM and TO to be miliseconds and made corrections
IF OBJECT_ID('tempdb..#DAY_TYPE') IS NOT NULL
	DROP TABLE #DAY_TYPE
CREATE TABLE #DAY_TYPE ( [DAY_INTERVAL_ID] INT, [DAY_TYPE_ID] INT, [FROM] INT, [TO] INT, [HOUR_TYPE_ID] INT )
INSERT #DAY_TYPE ( [DAY_INTERVAL_ID], [DAY_TYPE_ID], [FROM], [TO], [HOUR_TYPE_ID] )
SELECT 1, 1, 21600000, 50400000, 1 UNION ALL
SELECT 2, 1, 50400000, 72000000, 2 UNION ALL
SELECT 3, 1, 72000000, 86399996, 3    

-- not used in solution other than as reference
IF OBJECT_ID('tempdb..#HOUR_TYPE') IS NOT NULL
	DROP TABLE #HOUR_TYPE
CREATE TABLE #HOUR_TYPE ( [HOUR_TYPE_ID] INT, [DESCRIPTION] VARCHAR(10) )
INSERT #HOUR_TYPE ( [HOUR_TYPE_ID], [DESCRIPTION] )
SELECT 1, 'Normal' UNION
SELECT 2, 'Extra' UNION
SELECT 3, 'Nocturnal'

SELECT  t1.[USER_ID] AS [EMPLOYEE_ID], t1.[STAMPING] AS [FROM],
        t2.[STAMPING] AS [TO],
        CASE WHEN t2.[STAMPING] IS NULL THEN NULL
             ELSE CONVERT(VARCHAR, DATEADD(ms,
                                           CASE WHEN dt1.[HOUR_TYPE_ID] = 1
                                                THEN DATEDIFF(ms, t1.[STAMPING], dt1.endDate)
                                                ELSE 0
                                           END
                                           + CASE WHEN dt2.[HOUR_TYPE_ID] = 1
                                                  THEN DATEDIFF(ms, dt2.startDate, t2.[STAMPING])
                                                  ELSE 0
                                             END, 0), 8)
        END AS [NORMAL],
        CASE WHEN t2.[STAMPING] IS NULL THEN NULL
             ELSE CONVERT(VARCHAR, DATEADD(ms,
                                           CASE WHEN dt1.[HOUR_TYPE_ID] = 2
                                                THEN DATEDIFF(ms, t1.[STAMPING], dt1.endDate)
                                                ELSE 0
                                           END
                                           + CASE WHEN dt2.[HOUR_TYPE_ID] = 2
                                                  THEN DATEDIFF(ms, dt2.startDate, t2.[STAMPING])
                                                  ELSE 0
                                             END, 0), 8)
        END AS [EXTRA],
        CASE WHEN t2.[STAMPING] IS NULL THEN NULL
             ELSE CONVERT(VARCHAR, DATEADD(ms,
                                           CASE WHEN dt1.[HOUR_TYPE_ID] = 3
                                                THEN DATEDIFF(ms, t1.[STAMPING], dt1.endDate)
                                                ELSE 0
                                           END
                                           + CASE WHEN dt2.[HOUR_TYPE_ID] = 3
                                                  THEN DATEDIFF(ms, dt2.startDate, t2.[STAMPING])
                                                  ELSE 0
                                             END, 0), 8)
        END AS [NOCTURNAL]
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY [USER_ID] ORDER BY [STAMPING] )
                    AS rn, *
          FROM      #TIME
        ) t1
LEFT OUTER JOIN ( SELECT    ROW_NUMBER() OVER ( PARTITION BY [USER_ID] ORDER BY [STAMPING] )
                            AS rn, *
                  FROM      #TIME
                ) t2 ON t1.[USER_ID] = t2.[USER_ID]
                        AND t1.rn + 1 = t2.rn
                        AND t2.[ENTRANCE/EXIT] = 0
JOIN    #USER u ON t1.[USER_ID] = u.[USER_ID]
JOIN    #CALENDAR c1 ON c1.[DAY] = DATEADD(dd, DATEDIFF(dd, 0, t1.[STAMPING]),
                                           0)
                        AND c1.[CALENDAR_ID] = u.[CALENDAR_ID]
LEFT OUTER JOIN #CALENDAR c2 ON c2.[DAY] = DATEADD(dd,
                                                   DATEDIFF(dd, 0,
                                                            t2.[STAMPING]), 0)
                                AND c2.[CALENDAR_ID] = u.[CALENDAR_ID]
JOIN    ( SELECT    c.[CALENDAR_ID],
                    DATEADD(ms, dt.[FROM], c.[DAY]) AS startDate,
                    DATEADD(ms, dt.[TO] - 2, c.[DAY]) AS endDate,
                    c.[DAY_TYPE_ID], dt.[HOUR_TYPE_ID]
          FROM      #CALENDAR c
          CROSS JOIN #DAY_TYPE dt
        ) dt1 ON t1.[STAMPING] BETWEEN dt1.startDate AND dt1.endDate
LEFT OUTER JOIN ( SELECT    c.[CALENDAR_ID],
                            DATEADD(ms, dt.[FROM], c.[DAY]) AS startDate,
                            DATEADD(ms, dt.[TO] - 2, c.[DAY]) AS endDate,
                            c.[DAY_TYPE_ID], dt.[HOUR_TYPE_ID]
                  FROM      #CALENDAR c
                  CROSS JOIN #DAY_TYPE dt
                ) dt2 ON t2.[STAMPING] BETWEEN dt2.startDate AND dt2.endDate
WHERE   t1.[ENTRANCE/EXIT] = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources