Matching query

SQLScott · December 4th, 2007, 05:00 PM

Hi Everyone,

I have been trying to figure out a way to write a query that I am havign a hard time tackling...

OK, given the following two tables:

TABLE1
StartDate EndDate
----------------------- -----------------------
2007-12-03 18:00:00.000 2007-12-03 21:00:00.000
2007-12-04 18:00:00.000 2007-12-04 21:00:00.000
2007-12-05 18:00:00.000 2007-12-05 21:00:00.000
2007-12-06 18:00:00.000 2007-12-06 21:00:00.000
2007-12-07 18:00:00.000 2007-12-07 21:00:00.000

TABLE2
avdt_CaregiverID avdt_starttime avdt_endtime
-------------------- ----------------------- -----------------------
72 2007-12-03 17:00:00.000 2007-12-03 22:00:00.000
11028 2007-12-03 17:00:00.000 2007-12-03 22:00:00.000
72 2007-12-04 17:00:00.000 2007-12-04 22:00:00.000
72 2007-12-05 17:00:00.000 2007-12-05 22:00:00.000
11028 2007-12-05 17:00:00.000 2007-12-05 22:00:00.000
72 2007-12-06 17:00:00.000 2007-12-06 22:00:00.000
72 2007-12-07 17:00:00.000 2007-12-07 22:00:00.000
11028 2007-12-07 17:00:00.000 2007-12-07 22:00:00.000

Notice that the second table has two CaregiverIDâs. Number 72 has 5 entries for all 5 days. Number 11028 has 3 entries, for Monday/Wednesday/Friday.

How do I write a query that gives me the records from the second table that exactly match the FIRST table.

In other words, I want the CaregiverID from the second table that MATCHES the EXACT dates in the first table. In the example above, the first table list Monday through Friday. CaregiverID 72 matches those dates so I only want that id returned. CaregiverID 11028 does NOT match those dates exactly so I donât want that ID returned.

The times can be ignored.

Makes sense? Any insight is greatly appreciated...

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

planoie · December 4th, 2007, 08:56 PM

I think you are looking for a regular JOIN query:

SELECT avdt_CaregiverID
FROM TABLE2 t2
JOIN TABLE1 t1
ON t2.avdt_starttime=t1.StartDate AND t2.avdt_endtime=t1.EndDate

-Peter

planoie · December 4th, 2007, 09:09 PM

Ok, sorry, I missed the part about ignoring the times.

You'll need to throw on a date function onto each field referenced. Unfortunately there is no data type or a single function that will get you just the date portion of the datetime so I guess you'll need to get the individual date parts for each field in the join. A bit kludgey but it may work. The good thing is that we can test only the year and day of year to avoid needing to test the individual month and day.

SELECT avdt_CaregiverID
FROM TABLE2 t2
JOIN TABLE1 t1
    ON DATEPART(yyyy, t2.avdt_starttime)=DATEPART(yyyy, t1.StartDate)
    AND DATEPART(dy, t2.avdt_starttime)=DATEPART(dy, t1.StartDate)
    AND DATEPART(yyyy, t2.avdt_endtime)=DATEPART(yyyy, t1.EndDate)
    AND DATEPART(dy, t2.avdt_endtime)=DATEPART(dy, t1.EndDate)

-Peter

Peso · December 5th, 2007, 08:32 AM

I think that will error out when dealing with dates later than Feb 28 when on a leap year.

SELECT avdt_CaregiverID
FROM TABLE2 As t2
INNER JOIN TABLE1 AS t1 ON DATEDIFF(DAY, '19000101', t2.avdt_starttime) = DATEDIFF(DAY, '19000101', t1.StartDate)
AND DATEDIFF(DAY, '19000101', t2.avdt_endtime) = DATEDIFF(DAY, '19000101', t1.EndDate)

SQLScott · December 5th, 2007, 03:47 PM

Thanks for your feedback gents, but I still get id 11028 back. I'll keep working on it...

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

rupen · December 6th, 2007, 04:02 AM

Ok I assume that we are matching *exactly* with table1 date and time both.

SELECT avdt_CaregiverID
from TABLE2
where avdt_CaregiverID not in (
   select avdt_CaregiverID from TABLE2 where avdt_starttime not in (
          select StartDate from TABLE1) AND avdt_endtime not in (
                 Select EndDate from TABLE1))
-------------
We can certainly modify this if we want to match only date part.

Rupen Anjaria.:)
------------------
It is not difficult to be on TOP...It is difficult when you carry Truth,Commitment and Transparency with you.

Peso · December 6th, 2007, 08:27 AM

Code:

DECLARE    @Table1 TABLE (StartDate DATETIME, EndDate DATETIME)

INSERT    @Table1
SELECT    '2007-12-03 18:00:00', '2007-12-03 21:00:00' UNION ALL
SELECT    '2007-12-04 18:00:00', '2007-12-04 21:00:00' UNION ALL
SELECT    '2007-12-05 18:00:00', '2007-12-05 21:00:00' UNION ALL
SELECT    '2007-12-06 18:00:00', '2007-12-06 21:00:00' UNION ALL
SELECT    '2007-12-07 18:00:00', '2007-12-07 21:00:00'

DECLARE    @Table2 TABLE (CareGiverID INT, StartTime DATETIME, EndTime DATETIME)

INSERT    @Table2
SELECT    72, '2007-12-03 17:00:00', '2007-12-03 22:00:00' UNION ALL
SELECT    11028, '2007-12-03 17:00:00', '2007-12-03 22:00:00' UNION ALL
SELECT    72, '2007-12-04 17:00:00', '2007-12-04 22:00:00' UNION ALL
SELECT    72, '2007-12-05 17:00:00', '2007-12-05 22:00:00' UNION ALL
SELECT    11028, '2007-12-05 17:00:00', '2007-12-05 22:00:00' UNION ALL
SELECT    72, '2007-12-06 17:00:00', '2007-12-06 22:00:00' UNION ALL
SELECT    72, '2007-12-07 17:00:00', '2007-12-07 22:00:00' UNION ALL
SELECT    11028, '2007-12-07 17:00:00', '2007-12-07 22:00:00'


SELECT        t2.CaregiverID
FROM        @Table1 AS t1
INNER JOIN    @Table2 AS t2 ON DATEDIFF(DAY, '19000101', t2.StartTime) = DATEDIFF(DAY, '19000101', t1.StartDate)
WHERE        DATEDIFF(DAY, '19000101', t2.EndTime) = DATEDIFF(DAY, '19000101', t1.EndDate)
GROUP BY    t2.CaregiverID
HAVING        COUNT(DISTINCT t2.StartTime) = (SELECT COUNT(DISTINCT StartDate) FROM @Table1)

Peso · December 6th, 2007, 08:43 AM

A SQL Server 2005 solution

Code:

-- Prepare sample data
DECLARE    @Table1 TABLE (StartDate DATETIME, EndDate DATETIME)

INSERT    @Table1
SELECT    '2007-12-03 18:00:00', '2007-12-03 21:00:00' UNION ALL
SELECT    '2007-12-04 18:00:00', '2007-12-04 21:00:00' UNION ALL
SELECT    '2007-12-05 18:00:00', '2007-12-05 21:00:00' UNION ALL
SELECT    '2007-12-06 18:00:00', '2007-12-06 21:00:00' UNION ALL
SELECT    '2007-12-07 18:00:00', '2007-12-07 21:00:00'

DECLARE    @Table2 TABLE (CareGiverID INT, StartTime DATETIME, EndTime DATETIME)

INSERT    @Table2
SELECT    72, '2007-12-03 17:00:00', '2007-12-03 22:00:00' UNION ALL
SELECT    11028, '2007-12-03 17:00:00', '2007-12-03 22:00:00' UNION ALL
SELECT    72, '2007-12-04 17:00:00', '2007-12-04 22:00:00' UNION ALL
SELECT    72, '2007-12-05 17:00:00', '2007-12-05 22:00:00' UNION ALL
SELECT    11028, '2007-12-05 17:00:00', '2007-12-05 22:00:00' UNION ALL
SELECT    72, '2007-12-06 17:00:00', '2007-12-06 22:00:00' UNION ALL
SELECT    72, '2007-12-07 17:00:00', '2007-12-07 22:00:00' UNION ALL
SELECT    11028, '2007-12-07 17:00:00', '2007-12-07 22:00:00'

;WITH Yak (CaregiverID, StartTime, EndTime, Matches)
AS (
    SELECT        t2.CaregiverID,    
            t2.StartTime,
            t2.EndTime,
            COUNT(*) OVER (PARTITION BY t2.CaregiverID)
    FROM        @Table2 AS t2
    INNER JOIN    @Table1 AS t1 ON DATEDIFF(DAY, '19000101', t1.StartDate) = DATEDIFF(DAY, '19000101', t2.StartTime)
    WHERE        DATEDIFF(DAY, '19000101', t1.EndDate) = DATEDIFF(DAY, '19000101', t2.EndTime)
)

SELECT    CaregiverID,
    StartTime,
    EndTime
FROM    Yak
WHERE    Matches = (SELECT COUNT(*) FROM @Table1)

SQLScott · December 6th, 2007, 11:36 AM

Yep, that's it! Thanks Peso! I keep forgetting about the OVER clause....

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Peso · December 6th, 2007, 12:01 PM

No worries.

I am still waiting for that mail from your publisher...