June 24, 2016 at 1:41 am
hi
I have the sample data:Insert Into @Table (Id,Code, StartDate, EndDate, TimeDiff)
SELECT 1, 'DA6310', '2016-06-20 09:25:00.000', '2016-06-20 11:15:00.000', 110 Union All
SELECT 2, 'DA6310', '2016-06-20 18:40:00.000', '2016-06-21 20:00:00.000', 1520 Union All
Select 3, 'DA6310', '2016-06-21 09:15:00.000', '2016-06-21 12:05:00.000', 170 Union All
Select 4, 'DA6310', '2016-06-21 13:30:00.000', '2016-06-21 22:30:00.000', 540 Union All
Select 5, 'DA6310', '2016-06-22 01:30:00.000', '2016-06-22 08:00:00.000', 390 Union All
row 3 overlaps with row 2, meaning row 3 falls within start Date of row 2 and endDate of row 4
I want to build query where I can ignore row 3 and get the difference between row2 StartDate and row4 EndDate.
June 24, 2016 at 7:02 am
maybe....
--based on solution http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle
CREATE TABLE #testtable(
ID INT NULL
,Code VARCHAR(6) NULL
,Startdate DATETIME NULL
,Enddate DATETIME NULL
,Timediff INT NULL
);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (1,'DA6310','2016-06-20 09:25:00.000','2016-06-20 11:15:00.000',110);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (2,'DA6310','2016-06-20 18:40:00.000','2016-06-21 20:00:00.000',1520);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (3,'DA6310','2016-06-21 09:15:00.000','2016-06-21 12:05:00.000',170);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (4,'DA6310','2016-06-21 13:30:00.000','2016-06-21 22:30:00.000',540);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (5,'DA6310','2016-06-22 01:30:00.000','2016-06-22 08:00:00.000',390);
-- additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-03','2016-01-14');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-05','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-15','2016-01-20');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-07','2016-01-22');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-27','2016-01-31');
SELECT * FROM #testtable;
WITH C1
AS (
SELECT
code,
startdate AS dt,
1 AS type,
NULL AS e,
ROW_NUMBER() OVER(PARTITION BY code ORDER BY startdate) AS s
FROM #testtable
UNION ALL
SELECT
code,
Enddate AS dt,
-1 AS type,
ROW_NUMBER() OVER(PARTITION BY code ORDER BY enddate) AS e,
NULL AS s
FROM #testtable),
C2
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY code ORDER BY dt, type DESC) AS se
FROM C1),
C3
AS (SELECT *,
FLOOR((ROW_NUMBER() OVER(PARTITION BY code ORDER BY dt) - 1) / 2) + 1 AS grpnum
FROM C2
WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0)
SELECT code,
MIN(dt) AS startdate,
MAX(dt) AS enddate,
datediff(minute,MIN(dt),MAX(dt)) AS TimeDiff
FROM C3
GROUP BY code,grpnum
ORDER BY code,grpnum
DROP TABLE #testtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 27, 2016 at 1:24 am
thank you so much J Livingston SQL,
it worked perfectly!!!
June 27, 2016 at 8:22 am
Nomvula (6/27/2016)
thank you so much J Livingston SQL,it worked perfectly!!!
Since you're the one that needs to support it, the question now is... can you? Do you actually understand how it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2016 at 8:36 am
Jeff Moden (6/27/2016)
Nomvula (6/27/2016)
thank you so much J Livingston SQL,it worked perfectly!!!
Since you're the one that needs to support it, the question now is... can you? Do you actually understand how it works?
suggest you read this article as well as the one I pointed you to in the code
http://blogs.solidq.com/en/sqlserver/packing-intervals/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 4, 2016 at 6:43 am
Hi
Can you help me again, I added some additional data. It seems whenever there's a null end date, I'm not getting those values.
CREATE TABLE #testtable(
ID INT NULL
,Code VARCHAR(6) NULL
,Startdate DATETIME NULL
,Enddate DATETIME NULL
,Timediff INT NULL
);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (1,'DA6310','2016-06-20 09:25:00.000','2016-06-20 11:15:00.000',110);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (2,'DA6310','2016-06-20 18:40:00.000','2016-06-21 20:00:00.000',1520);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (3,'DA6310','2016-06-21 09:15:00.000','2016-06-21 12:05:00.000',170);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (4,'DA6310','2016-06-21 13:30:00.000','2016-06-21 22:30:00.000',540);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (5,'DA6310','2016-06-22 01:30:00.000','2016-06-22 08:00:00.000',390);
-- additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-03','2016-01-14');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-05','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-15','2016-01-20');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-07','2016-01-22');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-27','2016-01-31');
--additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 09:00:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 10:00:00.000','2016-01-31 22:30:00.000');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 12:30:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 07:00:00.000','2016-01-20 22:30:00.000');
in this instance I also should be getting this 'AL1125','2016-06-18 09:00:00.000',NULL and
'BD1188','2016-06-20 07:00:00.000',NULL
July 4, 2016 at 8:43 am
Nomvula (7/4/2016)
HiCan you help me again, I added some additional data. It seems whenever there's a null end date, I'm not getting those values.
CREATE TABLE #testtable(ID INT NULL
,Code VARCHAR(6) NULL
,Startdate DATETIME NULL
,Enddate DATETIME NULL
,Timediff INT NULL
);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (1,'DA6310','2016-06-20 09:25:00.000','2016-06-20 11:15:00.000',110);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (2,'DA6310','2016-06-20 18:40:00.000','2016-06-21 20:00:00.000',1520);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (3,'DA6310','2016-06-21 09:15:00.000','2016-06-21 12:05:00.000',170);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (4,'DA6310','2016-06-21 13:30:00.000','2016-06-21 22:30:00.000',540);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (5,'DA6310','2016-06-22 01:30:00.000','2016-06-22 08:00:00.000',390);
-- additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-03','2016-01-14');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-05','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-15','2016-01-20');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-07','2016-01-22');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-27','2016-01-31');
--additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 09:00:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 10:00:00.000','2016-01-31 22:30:00.000');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 12:30:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 07:00:00.000','2016-01-20 22:30:00.000');
in this instance I also should be getting this 'AL1125','2016-06-18 09:00:00.000',NULL and
'BD1188','2016-06-20 07:00:00.000',NULL
is it as simple as selecting the rows with NULL and then unioning thos rows to the end result?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 12:07 am
How do I go about doing that?
July 5, 2016 at 4:29 am
Nomvula (7/5/2016)
How do I go about doing that?
you need to exclude the rows with NULLS in cte "C1"
and then in the end select, use UNION with a select for rows with NULLS.
there is already code in my script that demonstrates UNION.
just to be sure I understand what you are looking for.....
what results would you expect from this sample
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-03','2016-01-14');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-05',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-15','2016-01-20');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-07','2016-01-22');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-27','2016-01-31');
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 6, 2016 at 2:00 am
Hi J
according to your sample data. the results I'm expecting is
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01',NULL)
this means the downtime is still open.
July 6, 2016 at 3:05 am
Nomvula (7/6/2016)
Hi Jaccording to your sample data. the results I'm expecting is
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01',NULL)
this means the downtime is still open.
so, are you ever going to have a situation where a "code" ie JLS in this instance...... has more than one row with a NULL enddate?
do you ever have NULL startdates?
edit: typo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 6, 2016 at 8:05 am
there will always be a startdate.
each code i.e jls will not have more than 1 null end date
July 6, 2016 at 8:16 am
Nomvula (7/6/2016)
there will always be a startdate.each code i.e jls will not have more than 1 null end date
ok so you will need to exclude all 'jls' rows from the cte scripts and then union the result set you want for 'jls' at the end.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 6, 2016 at 10:01 am
does this work for you?
CREATE TABLE #testtable(
ID INT NULL
,Code VARCHAR(6) NULL
,Startdate DATETIME NULL
,Enddate DATETIME NULL
,Timediff INT NULL
);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (1,'DA6310','2016-06-20 09:25:00.000','2016-06-20 11:15:00.000',110);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (2,'DA6310','2016-06-20 18:40:00.000','2016-06-21 20:00:00.000',1520);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (3,'DA6310','2016-06-21 09:15:00.000','2016-06-21 12:05:00.000',170);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (4,'DA6310','2016-06-21 13:30:00.000','2016-06-21 22:30:00.000',540);
INSERT INTO #testtable(ID,Code,Startdate,Enddate,Timediff) VALUES (5,'DA6310','2016-06-22 01:30:00.000','2016-06-22 08:00:00.000',390);
-- additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-03','2016-01-14');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-05',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-15','2016-01-20');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-07','2016-01-22');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-01','2016-01-10');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('jls','2016-01-27','2016-01-31');
--additional sample data
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 09:00:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('AL1125','2016-06-18 10:00:00.000','2016-01-31 22:30:00.000');
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 12:30:00.000',NULL);
INSERT INTO #testtable(Code,Startdate,Enddate) VALUES ('BD1188','2016-06-20 07:00:00.000','2016-01-20 22:30:00.000');
WITH xcodes as (
select DISTINCT code from #testtable where enddate IS NULL
)
, C1
AS (
SELECT
t.code,
t.startdate AS dt,
1 AS type,
NULL AS e,
ROW_NUMBER() OVER(PARTITION BY t.code ORDER BY startdate) AS s
FROM #testtable t
WHERE NOT EXISTS
(SELECT code from xcodes x WHERE x.code = t.code)
UNION ALL
SELECT
t.code,
t. Enddate AS dt,
-1 AS type,
ROW_NUMBER() OVER(PARTITION BY t.code ORDER BY enddate) AS e,
NULL AS s
FROM #testtable t
WHERE NOT EXISTS
(SELECT code from xcodes x WHERE x.code = t.code)
)
,
C2
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY code ORDER BY dt, type DESC) AS se
FROM C1),
C3
AS (SELECT *,
FLOOR((ROW_NUMBER() OVER(PARTITION BY code ORDER BY dt) - 1) / 2) + 1 AS grpnum
FROM C2
WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0)
SELECT code,
MIN(dt) AS startdate,
MAX(dt) AS enddate,
DATEDIFF(minute, MIN(dt), MAX(dt)) AS TimeDiff
FROM C3
GROUP BY code,grpnum
UNION ALL
SELECT t.Code,
MIN(t.Startdate),
NULL,
NULL
FROM #testtable AS t
WHERE EXISTS
( SELECT code FROM xcodes x WHERE x.code = t.code)
GROUP BY t.code
ORDER BY code;
DROP TABLE #testtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 6, 2016 at 10:07 am
Nomvula (7/5/2016)
How do I go about doing that?
I'm thinking that you didn't actually read the link that JLS provided so that you could actually support the code yourself.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply