Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET dateformat dmy
- DECLARE @t TABLE (name VARCHAR(10), nilai INT, DateTime datetime)
- INSERT @t
- SELECT 'AB', 23,'05-02-2006 00:00:00'
- UNION ALL SELECT 'AB', 55,'05-02-2006 01:00:00'
- UNION ALL SELECT 'AB', 47,'05-02-2006 02:00:00'
- UNION ALL SELECT 'AB', 19,'05-02-2006 03:00:00'
- UNION ALL SELECT 'AB', 12,'05-02-2006 04:00:00'
- UNION ALL SELECT 'AB', 11,'06-02-2006 00:00:00'
- UNION ALL SELECT 'AB', 20,'06-02-2006 01:00:00'
- UNION ALL SELECT 'AB', 66,'06-02-2006 02:00:00'
- UNION ALL SELECT 'AB', 56,'06-02-2006 03:00:00'
- UNION ALL SELECT 'AB', 44,'06-02-2006 04:00:00'
- UNION ALL SELECT 'AB', 66,'06-02-2006 05:00:00'
- UNION ALL SELECT 'AB', 66,'07-02-2006 00:00:00'
- UNION ALL SELECT 'AB', 66,'07-02-2006 01:00:00'
- SELECT * FROM @t
- SELECT * FROM @t a WHERE DateTime = (
- SELECT dateadd(d, MAX(nilai), MIN(dateadd(d, -nilai, DateTime))) FROM @t
- WHERE name = a.name AND datediff(d, 0, DateTime) = datediff(d, 0, a.DateTime)
- )
- SET dateformat dmy
- DECLARE @l TABLE (name VARCHAR(10), nilai INT, DATE DATE, HOUR TIME)
- INSERT @l
- SELECT 'AB', 23,'05-02-2006','00:00:00'
- UNION ALL SELECT 'AB', 55,'05-02-2006','01:00:00'
- UNION ALL SELECT 'AB', 47,'05-02-2006','02:00:00'
- UNION ALL SELECT 'AB', 19,'05-02-2006','03:00:00'
- UNION ALL SELECT 'AB', 12,'05-02-2006','04:00:00'
- UNION ALL SELECT 'AB', 11,'06-02-2006','00:00:00'
- UNION ALL SELECT 'AB', 20,'06-02-2006','01:00:00'
- UNION ALL SELECT 'AB', 66,'06-02-2006','02:00:00'
- UNION ALL SELECT 'AB', 56,'06-02-2006','03:00:00'
- UNION ALL SELECT 'AB', 44,'06-02-2006','04:00:00'
- UNION ALL SELECT 'AB', 66,'06-02-2006','05:00:00'
- UNION ALL SELECT 'AB', 66,'07-02-2006','00:00:00'
- UNION ALL SELECT 'AB', 66,'07-02-2006','01:00:00'
- SELECT * FROM @l
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement