SQL Query help

Mel · January 6, 2019, 11:03pm

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:

What I am trying to do is:

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.

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.

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

Anyone can help me with this sql trick?

Thank you all

yosiasz · January 7, 2019, 12:26am

Please provide your data as dml ddl

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

Insert into #sampledata

Mel · January 7, 2019, 1:25am

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)

yosiasz · January 7, 2019, 1:58am

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

Mel · January 7, 2019, 2:49am

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

harishgg1 · January 7, 2019, 7:40am

hi

i tried it

please take a look at it

hope it helps

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

harishgg1 · January 7, 2019, 9:05am

hi

i tried again
with the min and max

is this OKAY ??

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

mike01 · January 7, 2019, 2:51pm

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

Mel · January 7, 2019, 5:34pm

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

Mel · January 7, 2019, 5:35pm

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.

bitsmed · January 7, 2019, 5:53pm

Lead and lag was introduced in Microsoft SQL Server 2012.

harishgg1 · January 8, 2019, 6:15am

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

harishgg1 · January 8, 2019, 11:25am

mannesravya · January 8, 2019, 11:49pm

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.

Mel · May 6, 2019, 9:39pm

Thank you harishgg!