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 2008 Forums
 Transact-SQL (2008)
 datetime problem

Author  Topic 

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-02-07 : 10:17:50
Hi all,

I've got a workday from 8:00 till 16:00, but the employee gets different fee-amounts in the day, depending on some data.

This is my data:
level time_from time_to fee_amount
1 1900-01-01 09:00:00.000 1900-01-01 11:00:00.000 5
1 1900-01-01 13:00:00.000 1900-01-01 14:00:00.000 5
2 1900-01-01 08:00:00.000 1900-01-01 16:00:00.000 6
4 1900-01-01 08:00:00.000 1900-01-01 16:00:00.000 3

I need a query which gives me the following result:
level time_from time_to fee_amount
1 1900-01-01 09:00:00.000 1900-01-01 11:00:00.000 5
1 1900-01-01 13:00:00.000 1900-01-01 14:00:00.000 5
2 1900-01-01 08:00:00.000 1900-01-01 09:00:00.000 6
2 1900-01-01 11:00:00.000 1900-01-01 13:00:00.000 6
2 1900-01-01 14:00:00.000 1900-01-01 16:00:00.000 6

Difficult to explain but let me try:
In level 1 there's no fee for the 0800 - 0900, 1100 - 1300 and 1400 - 1600 so the query has to check if that 'open places' exists in the level 2 rows. If there are open places after querying level 2 the query needs to check in level 3 and so on.

Can somebody help me with a query?

Thanks

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-02-12 : 20:02:08
Like following?


CREATE TABLE #SOURCE (
level int not null,
time_from datetime not null,
time_to datetime not null,
fee_amount int not null
);

INSERT INTO #SOURCE VALUES
(1, '09:00:00', '11:00:00', 5),
(1, '13:00:00', '14:00:00', 5),
(2, '08:00:00', '16:00:00', 6),
(4, '08:00:00', '16:00:00', 3);

SELECT *
FROM #SOURCE;

WITH TIMEFROM AS (
SELECT time_from FROM #SOURCE
UNION
SELECT time_to FROM #SOURCE
),
TIMETO AS (
SELECT time_to FROM #SOURCE
UNION
SELECT time_from FROM #SOURCE
)
SELECT
level,
time_from,
time_to,
fee_amount
FROM (
SELECT
ROW_NUMBER() OVER(
PARTITION BY TIMEFROM.time_from
ORDER BY #SOURCE.level) ROWNUM,
#SOURCE.level,
#SOURCE.fee_amount,
TIMEFROM.time_from,
MIN(TIMETO.time_to) OVER(
PARTITION BY TIMEFROM.time_from) time_to
FROM TIMEFROM
INNER JOIN TIMETO
ON TIMEFROM.time_from < TIMETO.time_to
INNER JOIN #SOURCE
ON #SOURCE.time_from <= TIMEFROM.time_from
AND TIMETO.time_to <= #SOURCE.time_to
) TBL
WHERE ROWNUM = 1
ORDER BY level, time_from

DROP TABLE #SOURCE


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-02-14 : 03:09:29
Thanks Nagino, with a little alteration it did the trick!
Go to Top of Page
   

- Advertisement -