Dates between tables

Question

Dates between tables

Sturev

SSC-Addicted

Points: 436
More actions
April 2, 2011 at 6:41 pm

#237928

I have a need to pull a date column from one table based on the date in another.
Example:
I have a service table that contains details about a service appointment
Then I have another table that contains the weekending dates for the fiscal calendar
I need to report the service details along with the fiscal week it occurred
The issue I'm running into trying to develop the query is when the service date happens at the end of the year (12/30/2009) and the fiscal week is not greater than or even in that year... In this case the fiscal week is 1/2/2010 for 12/30/2009.
I've tried all sorts of queries with no luck (between, in, > & <=, etc), any ideas?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Sturev (4/2/2011)
I have a need to pull a date column from one table based on the date in another.
Example:
I have a service table that contains details about a service appointment
Then I have another table that contains the weekending dates for the fiscal calendar
I need to report the service details along with the fiscal week it occurred
The issue I'm running into trying to develop the query is when the service date happens at the end of the year (12/30/2009) and the fiscal week is not greater than or even in that year... In this case the fiscal week is 1/2/2010 for 12/30/2009.
I've tried all sorts of queries with no luck (between, in, > & <=, etc), any ideas?

1/2/2010 is greater than 12/30/2009, so why doesn't > work ?

SunitaBeck SSC Veteran Points: 277 More actions · Answer 2

Are the data types datetime or smalldatetime? If not - for example if the columns are varchar, this can happen. If so, cast the column to datetime or smalldatetime in the query.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Sturev (4/2/2011)
I have a need to pull a date column from one table based on the date in another.
Example:
I have a service table that contains details about a service appointment
Then I have another table that contains the weekending dates for the fiscal calendar
I need to report the service details along with the fiscal week it occurred
The issue I'm running into trying to develop the query is when the service date happens at the end of the year (12/30/2009) and the fiscal week is not greater than or even in that year... In this case the fiscal week is 1/2/2010 for 12/30/2009.
I've tried all sorts of queries with no luck (between, in, > & <=, etc), any ideas?

How you are doing for November month?

i.e say service date happens at the end of the month 11/30/2009 (Monday),then it sholud be 12/05/2009.If is is possible for you to get the solution then there will be surely a way to find for next month also(Dec '09 -Jan '10 ).What method you have tried and you couldn't get the solution?If you have code post here so that some of us may correct you

Thanks
Parthi

Sturev SSC-Addicted Points: 436 More actions · Answer 4

homebrew01 (4/2/2011)

Sturev (4/2/2011)
I have a need to pull a date column from one table based on the date in another.
1/2/2010 is greater than 12/30/2009, so why doesn't > work ?

There is more than one fiscal week that's greater than, so that alone won't work. The fiscal week ending table has dates from 2000 all the way to 2020. The issue is that I can't figure out how to join the two tables which pulls the correct row in the week ending table.

Sturev SSC-Addicted Points: 436 More actions · Answer 5

parthi-1705 (4/5/2011)

Sturev (4/2/2011)
I have a need to pull a date column from one table based on the date in another.
How you are doing for November month?
i.e say service date happens at the end of the month 11/30/2009 (Monday),then it sholud be 12/05/2009.If is is possible for you to get the solution then there will be surely a way to find for next month also(Dec '09 -Jan '10 ).What method you have tried and you couldn't get the solution?If you have code post here so that some of us may correct you

I can't figure out how to join the two tables for any month... 🙁 Here's what I have currently (returns no records, which is not accurate)

FROM Service INNER JOIN

Fiscal ON CONVERT(CHAR(10), SDATE, 101) > CONVERT(CHAR(10), WEEKEND, 101) AND CONVERT(CHAR(10), SDATE, 101) <= CONVERT(CHAR(10), WEEKEND, 101)

Sturev SSC-Addicted Points: 436 More actions · Answer 6

sunitabeck (4/2/2011)
Are the data types datetime or smalldatetime? If not - for example if the columns are varchar, this can happen. If so, cast the column to datetime or smalldatetime in the query.

The columns are datetime

Sturev SSC-Addicted Points: 436 More actions · Answer 7

Thanks for everyone's input... I got it:

Service INNER JOIN Fiscal ON SDATE BETWEEN DATEADD(d, -6, WEEKEND) AND WEEKEND

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

Sturev (4/6/2011)
Thanks for everyone's input... I got it:
Service INNER JOIN Fiscal ON SDATE BETWEEN DATEADD(d, -6, WEEKEND) AND WEEKEND

Surev,

Your fiscal table should have enough columns defined to determine this sort of thing without any date type calculations. You should have a week start, week end and probably the fiscal year to which it belongs.

Here is an article I wrote on calendar tables that uses a weekly type table and there are some examples of using the BETWEEN operator in the join. (I don't have fiscal year in this table, but it's easy to add it)

http://www.sqlservercentral.com/articles/T-SQL/72345/

The beauty of using only the calendar table and leaving out any date functions - e.g. DATEADD(d, -6, WEEKEND) is that if you change the week ending from a Friday to a Saturday or a Saturday to a Sunday (which really happened to me), only the calendar table needs to be updated and no code needs to be changed.

Todd Fifield