Check recordset

Question

Post reply

Check recordset

Alessandro Andreatta

SSC-Addicted

Points: 406
More actions
May 19, 2015 at 3:36 am

#298494

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 ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL.AP Hall of Fame Points: 3244 More actions · Answer 1

Add check constraint to your table.

https://msdn.microsoft.com/en-IN/library/ms190377.aspx

____________________________________________________________

AP

Alessandro Andreatta SSC-Addicted Points: 406 More actions · Answer 2

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

SQL.AP Hall of Fame Points: 3244 More actions · Answer 3

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.

____________________________________________________________

AP

Alessandro Andreatta SSC-Addicted Points: 406 More actions · Answer 4

Yes, i think i must use CTE, and subtract somehow from next/previous record.

But i don't know how

Alessandro Andreatta SSC-Addicted Points: 406 More actions · Answer 5

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

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

Alessandro Andreatta SSC-Addicted Points: 406 More actions · Answer 7

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.