November 12, 2013 at 7:21 am
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.0002014-05-12 00:00:00.000
2014-08-04 00:00:00.0002014-08-22 00:00:00.000
2013-12-04 00:00:00.0002013-12-04 00:00:00.000
2013-12-16 00:00:00.0002013-12-17 00:00:00.000
2013-12-06 00:00:00.0002013-12-06 00:00:00.000
2013-12-19 00:00:00.0002013-12-20 00:00:00.000
2014-01-02 00:00:00.0002014-01-08 00:00:00.000
2013-11-03 00:00:00.0002013-11-10 00:00:00.000
2014-01-01 00:00:00.0002014-01-31 00:00:00.000
2014-01-01 00:00:00.0002014-01-10 00:00:00.000
2014-01-01 00:00:00.0002014-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
November 12, 2013 at 8:10 am
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
November 12, 2013 at 6:19 pm
Exotic JOIN? I think BOL must have forgotten to mention that one. 😛
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 6:29 pm
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[/url]
Using Exotic Joins in SQL - Part 2[/url]
Exotic Joins in T-SQL and How They Translate in SSIS[/url]
Lowell
November 12, 2013 at 6:41 pm
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[/url]
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2013 at 1:37 am
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
November 13, 2013 at 2:53 am
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
November 13, 2013 at 3:38 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply