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


Select between dates in another table.


Select between dates in another table.

Author
Message
Edvard Korsbæk
Edvard Korsbæk
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 367
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28449 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7385 Visits: 6431
Exotic JOIN? I think BOL must have forgotten to mention that one. :-P


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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28449 Visits: 39963
dwain.c (11/12/2013)
Exotic JOIN? I think BOL must have forgotten to mention that one. :-P


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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7385 Visits: 6431
Lowell (11/12/2013)
dwain.c (11/12/2013)
Exotic JOIN? I think BOL must have forgotten to mention that one. :-P


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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Edvard Korsbæk
Edvard Korsbæk
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 367
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
Edvard Korsbæk
Edvard Korsbæk
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 367
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
Edvard Korsbæk
Edvard Korsbæk
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 367
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
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