CODEWITHSUNDEEP
sqlinner-joinsql-server-2014
Bjoern Roethig
Bjoern Roethig

Reputation: 31

SQL Server 2014 Lookup value in Table2 between columns

I have two tables. In Table A I have a "StartDate" besides other values. I also have a column "TimeZone". In Table B I have TimeZone, StartDST, EndDST (DST=DaylightSavingTime) for several years.

I want to check - if StartDate is in Table B between B.StartDST and B.EndDST. If NOT... it should give me 0 else 1.

TimeZone exists multiple Times in B.

FROM DutyList A
WHERE NOT EXISTS (SELECT 1 
                  FROM TimeZoneDST B 
                  WHERE A.StartDuty between B.DSTstart and B.DSTend))

This does not give me all records. I need ALL records from DutyList A. I need an extra column "DSTexists" 0 or 1 - if StartDuty is in Table B. I am using SQL Server 2014.

Upvotes: 1

Views: 76

Answers (1)

pwnyexpress
pwnyexpress

Reputation: 1016

SELECT A.*,
      CASE WHEN EXISTS (SELECT 1 FROM TimeZoneDST B 
                        WHERE A.StartDuty between B.DSTstart and B.DSTend)
           THEN 1
           ELSE 0
      END as DSTexists
FROM DutyList A;

Upvotes: 1

Related Questions