May 19, 2015 at 3:36 am
Hi everybody, i work on sqlserver 2008 and i would to check a recordset like this
for example
CREATE TABLE [dbo].[Intervalli](
[id] [smallint] NOT NULL,
[Dt1] [datetime2](0) NOT NULL,
[Dt2] [datetime2](0) NOT NULL,
CONSTRAINT [PK_Intervalli] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO intervalli(id,Dt1, Dt2) values (1, '2015-05-16 10:00:00.0000000', '2015-05-16 12:00:00.0000000');
INSERT INTO intervalli(id,Dt1, Dt2) values (2, '2015-05-16 15:00:00.0000000', '2015-05-16 18:00:00.0000000');
INSERT INTO intervalli(id,Dt1, Dt2) values (3, '2015-05-17 11:00:00.0000000', '2015-05-17 13:00:00.0000000');
INSERT INTO intervalli(id,Dt1, Dt2) values (4, '2015-05-17 12:30:00.0000000', '2015-05-17 16:30:00.0000000');
INSERT INTO intervalli(id,Dt1, Dt2) values (5, '2015-05-17 10:30:00.0000000', '2015-05-17 18:30:00.0000000');
for each record Dt2 is always grater then Dt1
record 1, 2, 3, is good for me, but i would to check situation like record 4 because Dt1 from record 4 is inside dt1 - dt2 record 3
And dt1 dt2 is inside dt1 - dt2 record 5
How can i check a possible no good situation (record 4, 5) width 1 query ?
May 19, 2015 at 4:26 am
Add check constraint to your table.
https://msdn.microsoft.com/en-IN/library/ms190377.aspx
____________________________________________________________
APMay 19, 2015 at 4:51 am
thanks but i want to check entire recordset.
i'am sure that dt2 > dt1 for each record
but i need to chech if dt1 of OTHER RECORD is inside to ANOTHER RECORD
and the same for dt2
May 19, 2015 at 5:22 am
record 1, 2, 3, is good for me, but i would to check situation like record 4 because Dt1 from record 4 is inside dt1 - dt2 record 3
And dt1 dt2 is inside dt1 - dt2 record 5
How can i check a possible no good situation (record 4, 5) width 1 query ?
Apologies, I missed this part.
I would suggest insert full record set into a stage table first. Then it would be easier to identify such records where Dt1 is not between the Dt1 - Dt2 of previous record.
____________________________________________________________
APMay 19, 2015 at 6:14 am
Yes, i think i must use CTE, and subtract somehow from next/previous record.
But i don't know how
May 19, 2015 at 6:32 am
i think this is works
;WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.dt1),
p.Dt1,p.Dt2
FROM Intervalli p
)
SELECT
CTE.dt1, CTE.Dt2,
nex.dt1 Ndt1,
nex.dt2 NDt2
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
WHERE nex.dt1 < CTE.Dt2
May 19, 2015 at 7:35 am
Alessandro Andreatta (5/19/2015)
i think this is works;WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.dt1),
p.Dt1,p.Dt2
FROM Intervalli p
)
SELECT
CTE.dt1, CTE.Dt2,
nex.dt1 Ndt1,
nex.dt2 NDt2
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
WHERE nex.dt1 < CTE.Dt2
when running your solution above with the data you provided...it returns Id (record) 3 and 5......I thought you wanted 4 & 5
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2015 at 8:02 am
yes you're right, depends on order by.
In my real case, i don't have table exacly like this, and i don't have primary Key.
Only have Dt1, and Dt2
for explan my problem (1st is translate in english :)) and indicate record , i post a table with id Pk.
Before an update, i want to check if date range are completely separated.
I have a list of date (EventDate), and 2 parameters minute previus, minute next in other table.
the list became dt1, EventDate, dt2.
dt1 is DATEADD (MI , -@SogliaInf , DataEvento) and Dt2 is DATEADD (MI , @SogliaSup , DataEvento)
now, before update 2 parameters (@SogliaInf, @SogliaSup) i want to check if i made a mistake.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply