Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select between dates in another table. Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Hi!

I have these two queries:

select count(*) from dbo.dutyrostershift where shifttype = 1
Select CONVERT(datetime,A.start-36163,105) AS startdate,Convert(datetime,A.fld_end-36163,105) as enddate from dbo.vacation A where Wish_Vacation = 1

The first gives me a number of records given Shifttype, and the second gives me 11 dateranges
startdate enddato
2014-05-02 00:00:00.000 2014-05-12 00:00:00.000
2014-08-04 00:00:00.000 2014-08-22 00:00:00.000
2013-12-04 00:00:00.000 2013-12-04 00:00:00.000
2013-12-16 00:00:00.000 2013-12-17 00:00:00.000
2013-12-06 00:00:00.000 2013-12-06 00:00:00.000
2013-12-19 00:00:00.000 2013-12-20 00:00:00.000
2014-01-02 00:00:00.000 2014-01-08 00:00:00.000
2013-11-03 00:00:00.000 2013-11-10 00:00:00.000
2014-01-01 00:00:00.000 2014-01-31 00:00:00.000
2014-01-01 00:00:00.000 2014-01-10 00:00:00.000
2014-01-01 00:00:00.000 2014-01-07 00:00:00.000

The table dutyrostershift has a field DATO (datetime), and i want to select the records, where DATO is between startdate and enddate in any of the records from query II.

Hints appriciated - I am still learning.

Best regards

Edvard Korsbæk
Post #1513452
Posted Tuesday, November 12, 2013 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
the trick here is what some people call an "exotic" join, where it's not joining on discrete values, but rather on the criteria (between dates) you are looking for.

Something like this should give you the idea, and might even run without changes, based on your table/column names you posted here:
SELECT * FROM DutyRosterShift
INNER JOIN Vacation
ON DutyRosterShift.DATO BETWEEN Vacation.StartDate AND Vacation.EndDato
WHERE DutyRosterShift.ShiftType = 1



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513476
Posted Tuesday, November 12, 2013 6:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 3,648, Visits: 5,326
Exotic JOIN? I think BOL must have forgotten to mention that one.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1513678
Posted Tuesday, November 12, 2013 6:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
dwain.c (11/12/2013)
Exotic JOIN? I think BOL must have forgotten to mention that one.


yeah the whole "exotic' naming convention for non traditional joins started back in 2007 with a series of articles here on SSC:


i read it, so i kinda remember it.

Using Exotic Joins in SQL Part 1
Using Exotic Joins in SQL - Part 2
Exotic Joins in T-SQL and How They Translate in SSIS


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513680
Posted Tuesday, November 12, 2013 6:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 3,648, Visits: 5,326
Lowell (11/12/2013)
dwain.c (11/12/2013)
Exotic JOIN? I think BOL must have forgotten to mention that one.


yeah the whole "exotic' naming convention for non traditional joins started back in 2007 with a series of articles here on SSC:


i read it, so i kinda remember it.

Using Exotic Joins in SQL Part 1
Using Exotic Joins in SQL - Part 2
Exotic Joins in T-SQL and How They Translate in SSIS


Equi-joins, exotic joins, compound joins, triangular joins... we need a glossary to keep 'em all straight!

Interesting articles Lowell. Hadn't heard of that name for them before.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1513682
Posted Wednesday, November 13, 2013 1:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Thanks!

It's very helpfull when you too get som documentation, the articles was interesting.

At the time beeing, I have ended up with:

select RTRIM(C.per_fornavn)+' '+RTRIM(C.per_efternavn) As Employee, A.dato from dbo.dutyrostershift A
INNER Join dbo.vacation B ON A.dato BETWEEN CONVERT(datetime,B.start-36163,105) AND Convert(datetime,B.fld_end-36163,105)
JOIN dbo.vacation D ON D.personalid = A.employeeid
Join DBO.PERSONAL C ON A.employeeid = C.per_personal_id
where A.shifttype = 1 AND D.Wish_Vacation = 1
Order by C.per_personal_id, A.dato

The reason for the two joins to dbo.vacation is to make the where clause work. First time i have used a double join!

I only have one problem left - It shows multible answers for each record.

Say, that emploeee Edvard Korsbæk has wished vacation on 2013.11.13 - that gives one record in dutyrostershift.

But i get from 4 to 12 correct answers on this.

It has something to do with the joins - I Think I know that - But 'How to' only show once?

If i change the first line to:

select RTRIM(C.per_fornavn)+' '+RTRIM(C.per_efternavn) As Employee, A.dato, A.ID from dbo.dutyrostershift A

I only want to show one line for each A.ID

How to?

Best regards

Edvard Korsbæk
Post #1513725
Posted Wednesday, November 13, 2013 2:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Checking is always a good thing.

My Where clause does not work.

It should take records, where dato is between startdate and enddate in vacations (Work) and where wish_vacation is true (=1) , which do not work.

Any suggestions out there?

Best regards

Edvard Korsbæk

Post #1513757
Posted Wednesday, November 13, 2013 3:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Hi all!

This did the trick:

Delete from dbo.dutyrostershift
From
dbo.dutyrostershift A
inner Join dbo.vacation B ON A.dato BETWEEN CONVERT(datetime,B.start-36163,105) AND Convert(datetime,B.fld_end-36163,105) AND B.personalid = A.employeeid
Join DBO.PERSONAL C ON A.employeeid = C.per_personal_id
where B.Wish_Vacation = 1 and A.shifttype = 1 and (C.vacation_as_duty = 0 OR C.vacation_as_duty = 2)

Thanks for the help!

Best regards

Edvard Korsbæk
Post #1513773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse