SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


check 2 date columns for same date


check 2 date columns for same date

Author
Message
tim.hansen
tim.hansen
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 343
Here is the situation:
I have multiple events with start and end dates.
I've been asked to find the difference between the start and end dates for each event for each clientID.
Simple enough using datediff.
BUT.....
The user wants to treat 2 (or more) records as ONE if the end date of the first record is the same as the start date of the next.


use master
--drop table #temp
create table #temp(
pk_id int,
clientID int,
lastName char(20),
firstName char(20),
startDate datetime,
endDate datetime
)

insert into #temp(pk_id, clientID, lastName, firstName, startDate, endDate)
select 1, 1, 'Jones', 'Mary', '2011-01-01', '2011-01-05' union all
select 2, 1, 'Jones', 'Mary', '2011-01-05', '2011-01-10' union all
select 3, 5, 'Barnes', 'Victor', '2011-01-05', '2011-02-10' union all
select 4, 1, 'Jones', 'Mary', '2011-01-10', '2011-01-15' union all
select 5, 4, 'Green', 'Sue', '2011-01-04', '2011-01-15' union all
select 6, 1, 'Jones', 'Mary', '2011-01-16', '2011-01-31' union all
select 7, 2, 'Smith', 'John', '2011-01-02', '2011-01-31' union all
select 8, 3, 'Brown', 'Fred', '2011-02-02', '2011-02-15' union all
select 9, 4, 'Green', 'Sue', '2011-01-15', '2011-01-31' union all
select 10, 4, 'Green', 'Sue', '2011-02-15', '2011-02-21'


Using the code below we get 10 rows returned from the temp table.

select clientID, lastName, firstName, startDate, endDate, datediff(dd, startDate, enddate) as dtDiff
from #temp
order by clientID, startDate, endDate




What I need to have is 7 rows:

clientID lastName firstName startDate endDate dtDiff
1 Jones Mary 2011-01-01 00:00:00.000 2011-01-15 00:00:00.000 14
1 Jones Mary 2011-01-16 00:00:00.000 2011-01-31 00:00:00.000 15
2 Smith John 2011-01-02 00:00:00.000 2011-01-31 00:00:00.000 29
3 Brown Fred 2011-02-02 00:00:00.000 2011-02-15 00:00:00.000 13
4 Green Sue 2011-01-04 00:00:00.000 2011-01-31 00:00:00.000 27
4 Green Sue 2011-02-15 00:00:00.000 2011-02-21 00:00:00.000 6
5 Barnes Victor 2011-01-05 00:00:00.000 2011-02-10 00:00:00.000 36



As you can see Mary Jones and Sue Green each have 2 events even though there are 4 and 3 records respectively in the source table. There will never be overlapping date ranges for a clientID.

I know there are many cursor based options for this problem, but I'm looking for a simple SQL solution as we may need to transfer the SQL code into BusinessObjects.

Thanks in advance for any assistance.
Tim Hansen


Living in Paradise: N 34°16'07.99" W 119°12'17.58"
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95693 Visits: 38968
Based on what you have provided, here is what I came up with to solve your problem.



--drop table #temp
create table #TimeTemp(
pk_id int,
clientID int,
lastName char(20),
firstName char(20),
startDate datetime,
endDate datetime
)

insert into #TimeTemp(pk_id, clientID, lastName, firstName, startDate, endDate)
select 1, 1, 'Jones', 'Mary', '2011-01-01', '2011-01-05' union all
select 2, 1, 'Jones', 'Mary', '2011-01-05', '2011-01-10' union all
select 3, 5, 'Barnes', 'Victor', '2011-01-05', '2011-02-10' union all
select 4, 1, 'Jones', 'Mary', '2011-01-10', '2011-01-15' union all
select 5, 4, 'Green', 'Sue', '2011-01-04', '2011-01-15' union all
select 6, 1, 'Jones', 'Mary', '2011-01-16', '2011-01-31' union all
select 7, 2, 'Smith', 'John', '2011-01-02', '2011-01-31' union all
select 8, 3, 'Brown', 'Fred', '2011-02-02', '2011-02-15' union all
select 9, 4, 'Green', 'Sue', '2011-01-15', '2011-01-31' union all
select 10, 4, 'Green', 'Sue', '2011-02-15', '2011-02-21';

with TimeTemp (
pk_id,
clientID,
lastName,
firstName,
startDate,
endDate,
rn
) as (
select
pk_id,
clientID,
lastName,
firstName,
startDate,
endDate,
ROW_NUMBER() over (partition by clientID order by startDate)
from
#TimeTemp
)
, TestDate (
pk_id,
clientID,
lastName,
firstName,
startDate,
endDate,
groupid
) as (
select
tt1.pk_id,
tt1.clientID,
tt1.lastName,
tt1.firstName,
tt1.startDate,
tt1.endDate,
tt1.rn - ISNULL(tt2.rn,0)
from
TimeTemp tt1
left outer join TimeTemp tt2
on (tt1.clientID = tt2.clientID
and tt1.startDate = tt2.endDate)
)
select
clientID,
lastName,
firstName,
min(startDate) as startDate,
max(endDate) as endDate,
datediff(dd,min(startDate),max(endDate)) as DtDiff
from
TestDate td
group by
clientID,
lastName,
firstName,
groupid
order by
td.clientID;

drop table #TimeTemp;



Hope this helps, and thank you for providing us with the DDL, sample data, and expected results. It helped a lot.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6493 Visits: 25557
Another way

WITH Starts AS (
SELECT a.clientID, a.lastName, a.firstName, a.startDate
FROM #temp a
WHERE NOT EXISTS (SELECT * FROM #temp b
WHERE b.clientID=a.clientID
AND b.endDate=a.startDate)),
Ends AS (
SELECT a.clientID, a.endDate
FROM #temp a
WHERE NOT EXISTS (SELECT * FROM #temp b
WHERE b.clientID=a.clientID
AND b.startDate=a.endDate))
SELECT s.clientID, s.lastName, s.firstName, s.startDate,
MIN(e.endDate) AS endDate,
DATEDIFF(day,s.startDate,MIN(e.endDate)) AS dtDiff
FROM Starts s
INNER JOIN Ends e ON e.clientID=s.clientID
AND s.startDate<e.endDate
GROUP BY s.clientID, s.lastName, s.firstName, s.startDate
ORDER BY s.clientID, s.startDate;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




tim.hansen
tim.hansen
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 343
Thanks Lynn.
I will give this solution to the SQL developer working on this project and let him see if it will work for him.

Tim Hansen


Living in Paradise: N 34°16'07.99" W 119°12'17.58"
Mike01
Mike01
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2545 Visits: 1533
NOTE: The first code is much more efficient. Just based on the sample data the first had a scan count of 2 and logical reads of 2, while the second had 4 scan counts and 30 logical reads.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95693 Visits: 38968
Hate to burst my own bubble, but there is a bug in my code. The code as posted will only work if there is no gap in the first set of records. If there is a gap between the first and second record, it fails.

Here is some new code. To fully understand what is happening, you need to read the article I reference below regarding Running Totals. You want the article written my Jeff Moden, not mine, as he explains how it works. Then you need to read the discussion as well. If I recall there has been verification code written, and that is discussed in the discuss thread as well.



create table dbo.TimeData( -- Represents source data
pk_id int,
clientID int,
lastName char(20),
firstName char(20),
startDate datetime,
endDate datetime
);

insert into dbo.TimeData(pk_id, clientID, lastName, firstName, startDate, endDate)
select 1, 1, 'Jones', 'Mary', '2011-01-01', '2011-01-05' union all
select 2, 1, 'Jones', 'Mary', '2011-01-06', '2011-01-10' union all
select 3, 5, 'Barnes', 'Victor', '2011-01-05', '2011-02-10' union all
select 4, 1, 'Jones', 'Mary', '2011-01-10', '2011-01-15' union all
select 5, 4, 'Green', 'Sue', '2011-01-04', '2011-01-15' union all
select 6, 1, 'Jones', 'Mary', '2011-01-15', '2011-01-31' union all
select 7, 2, 'Smith', 'John', '2011-01-02', '2011-01-31' union all
select 8, 3, 'Brown', 'Fred', '2011-02-02', '2011-02-15' union all
select 9, 4, 'Green', 'Sue', '2011-01-15', '2011-01-31' union all
select 10, 4, 'Green', 'Sue', '2011-02-15', '2011-02-21';

create table #TimeTemp( -- Work table
pk_id int,
clientID int,
lastName char(20),
firstName char(20),
startDate datetime,
endDate datetime,
groupid int null,
primary key clustered (clientID, startDate)
);

insert into #TimeTemp (
pk_id,
clientID,
lastName,
firstName,
startDate,
endDate
)
select
pk_id,
clientID,
lastName,
firstName,
startDate,
endDate
from
dbo.TimeData;

declare @currentDate datetime,
@groupid int,
@clientID int;
set @currentDate = '19000101'; -- zero date, hopefully not equal to any date in your system
set @groupid = 0;
set @clientID = 0;

update #TimeTemp set
@groupid = groupid = @groupid + case when clientID <> @clientID then 1
when startDate <> @currentDate then 1
else 0 end,
@currentDate = endDate,
@clientID = clientID
from
#TimeTemp with (tablockx)
option
(maxdop 1);

select * from #TimeTemp; -- Show what is in the work table after the update

select
clientID,
lastName,
firstName,
min(startDate) as startDate,
max(endDate) as endDate,
datediff(dd,min(startDate),max(endDate)) as DtDiff
from
#TimeTemp td
group by
clientID,
lastName,
firstName,
groupid
order by
td.clientID;

drop table #TimeTemp;

drop table dbo.TimeData;



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search