Help specifying a date range based on a start date

Question

Help specifying a date range based on a start date

pe2618

SSC Veteran

Points: 206
More actions
September 11, 2009 at 6:24 am

#72533
Hi,
I don't have masses of experience in T-SQL and am having trouble with a particular problem and are hoping someone maybe able to help.
I have two tables one which contains Project Details (Table A) and another containing Budget/Payment Details (Table B). The tables are joined by ProjectID.
I would like to able to create a SQL View to use for reporting purposes that determines which budget year an 'Invoice Paid Date' falls into. The budget years will have to be calculated from the Start and Finish dates found in 'Table A'. i.e. Where there is a Start Date of 01/10/2006 and Finish Date of 30/09/09 the following budget years would be created.
01/10/2006 - 30/09/2007 = 2006-07
01/10/2007 - 30/09/2008 = 2007-08
01/10/2008 - 30/09/2009 = 2008-09
Then match the Invoice Paid Date from 'Table B' to a budget year using the calculated budget years to eventually get something like:
```
Paid Date      Budget Year
01/12/2006    2006-07
01/11/2007    2007-08
```
etc..
I hope this as made some kind of sense and look forward to any help or advice on how to go about resolving this.
Regards
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

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

vallolet04 Mr or Mrs. 500 Points: 554 More actions · Answer 1

Paul,

I'm a little confused why do you need the Budget Year if you have date columns? maybe i didn't understand correctly, but you can use something really simple like.

Select * from ProjectDetails A

Inner Join PaymentDetails B on a.projectID = b.projectID

where b.PaidDate between '01/10/2006' and '30/09/2009'

If this does not work for you please explain a little bit more :hehe:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Does this help you get where you are trying to go?

declare @PayDate datetime;

set @PayDate = '2006-12-01';

select cast(year(dateadd(mm,3,@PayDate)) as char(4)) + '-' + right(cast(year(dateadd(mm,3,@PayDate)) - 1 as char(4)), 2)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

paul.ette (9/11/2009)
01/10/2006 - 30/09/2007 = 2006-07
01/10/2007 - 30/09/2008 = 2007-08
01/10/2008 - 30/09/2009 = 2008-09

All a view will do in this case is slow things down and obfuscate code. Turn the above into a lookup table and join to it for lookups.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)