SQLTeam.com | Weblogs | Forums

SQL Query help


#1

Hello SQL Expert,

I have a simple query as stated below:

Select Driver_ID, Registered_Begined, Registered_Ended
From DriverTable

and the output from the above query is:

01

What I am trying to do is:

  1. To find the Registration gap day when the driver begins registered, ended and begin again

Here is the result set I want when I run the updated query from question # 1.

02

So, on the # 2 question, I want to combine the same "Registration Gap" and take the Min Date as the Register_Beginned Date and Max Date as the Register_Ended date, in this case is NULL.

  1. How do I update the final query so get below result set:

03

Anyone can help me with this sql trick?

Thank you all


#2

Please provide your data as dml ddl

Create table #sampledata(driverid int,reg_beg_date datetime, end date datetime)

Insert into #sampledata


#3

Here are the CREATE and INSERT statement, thanks yosiasz:

CREATE TABLE DriverTable
(
Driver_ID VARCHAR(7),
Register_Beginned DATETIME,
Register_Ended DATETIME
)

INSERT INTO DriverTable (Driver_ID,Register_Beginned,Register_Ended)
VALUES ('AM253UT','04/05/2001','11/01/2001'),
('AM253UT','02/05/2002','03/11/2003'),
('AM253UT','05/06/2006','11/04/2006'),
('AM253UT','11/06/2006','06/07/2008'),
('AM253UT','06/09/2008','10/11/2014'),
('AM253UT','10/13/2014',NULL)


#4

how do you get 96 in first row, what is that?


#5

96 is the Datediff between:

SELECT DATEDIFF(DAY,'11/1/2001','2/5/2002') - 96

SELECT DATEDIFF(DAY,'3/11/2003','5/6/2006') - 1152

SELECT DATEDIFF(DAY,'11/4/2006','11/6/2006') - 2

and so on

Basically, if there is a gap between registration begin and end. Drivers may begin register and ended then register again and so on, so we want to know the gap between those dates.

Thanks yosiasz


#6

hi

i tried it

please take a look at it

hope it helps
:slight_smile:
:slight_smile:

thanks

drop create data
use tempdb 
go 

drop table DriverTable
go 
 
CREATE TABLE DriverTable
(
Driver_ID VARCHAR(7),
Register_Beginned DATETIME,
Register_Ended DATETIME
)

INSERT INTO DriverTable (Driver_ID,Register_Beginned,Register_Ended)
VALUES ('AM253UT','04/05/2001','11/01/2001'),
('AM253UT','02/05/2002','03/11/2003'),
('AM253UT','05/06/2006','11/04/2006'),
('AM253UT','11/06/2006','06/07/2008'),
('AM253UT','06/09/2008','10/11/2014'),
('AM253UT','10/13/2014',NULL)
GO 

select * from DriverTable
go
SQL
;WITH rn_cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY (SELECT NULL) ) AS rn, 
                driver_id, 
                register_beginned, 
                register_ended 
         FROM   drivertable) 
SELECT a.*, 
       Datediff(dd, a.register_ended, b.register_beginned) 
FROM   rn_cte a 
       JOIN rn_cte b 
         ON a.rn + 1 = b.rn 

go
Results


#7

hi

i tried again
with the min and max

is this OKAY ??
:slight_smile:
:slight_smile:

SQL ...
;WITH rn_cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY (SELECT NULL) ) AS rn, 
                driver_id, 
                register_beginned, 
                register_ended 
         FROM   drivertable), 
     cte 
     AS (SELECT a.driver_id, 
                Min(a.register_beginned)                            AS 
                Register_Beginned 
                   , 
                Max(a.register_ended)                               AS 
                   Register_Ended, 
                Datediff(dd, a.register_ended, b.register_beginned) AS dd 
         FROM   rn_cte a 
                RIGHT JOIN rn_cte b 
                        ON a.rn + 1 = b.rn 
         GROUP  BY a.driver_id, 
                   Datediff(dd, a.register_ended, b.register_beginned)) 
SELECT * 
FROM   cte 
WHERE  driver_id IS NOT NULL 

go
Results


#8

You can use the lead function to get the results.

drop table if exists #DriverTable

CREATE TABLE #DriverTable
(
Driver_ID VARCHAR(7),
Register_Beginned DATETIME,
Register_Ended DATETIME
)

INSERT INTO #DriverTable (Driver_ID,Register_Beginned,Register_Ended)
VALUES ('AM253UT','04/05/2001','11/01/2001'),
('AM253UT','02/05/2002','03/11/2003'),
('AM253UT','05/06/2006','11/04/2006'),
('AM253UT','11/06/2006','06/07/2008'),
('AM253UT','06/09/2008','10/11/2014'),
('test','11/06/2006','06/07/2008'),
('test','06/09/2008',null),
('AM253UT','10/13/2014',NULL)
GO 

select Driver_ID, Register_Beginned, Register_Ended,NextRegisterBegin,
		DateDiff(day, NextRegisterBegin, Register_Ended)  RegistrationGap
  from (
Select top 100  * ,
	lead(Register_Beginned,1,0) over (order by Driver_ID, Register_Beginned) NextRegisterBegin
  from #drivertable
order by Driver_ID, Register_Beginned) v

#9

Thank you harishgg1, it is working. How long you have been doing this SQL and what is your suggestion for me to be able to work with trick like in SQL?

Thank you again


#10

Thank you Mike01 for additional help but I am not able run it because the lead function is not recognize. I am using SQL 2014.


#11

Lead and lag was introduced in Microsoft SQL Server 2012.


#12

hi Mel

i have a lot of experience in writing SQL
almost 5 years ..
practicing it seriously only last 3 months

SQL is a language...

First see how the data is !!

then see how to get data you want

There is lots of stuff...

Learning how to learn Concept .. may help you

I can hold your hand and show you
please let me know where we can both
.............. "interact and share screen "
SQL Server TSQL

thanks

:slight_smile:
:slight_smile:


#13

#14

How come 4 times '2' in Registration Gap. I have used datediff crosswise for register_Ended and Register_Beginned and i am getting 96,1152,2,2,2,null.