June 9, 2005 at 11:53 am
hi- having some problems working out a value based on a stored date range in a lookup table...
table a has columns called startdate and events
table b has a list of periodstartdates and periodenddates, and the corresponding period that each date range belongs to
im trying to work out when given an event from table a, how to pick the period from b it falls in.
help!!
June 9, 2005 at 12:11 pm
Select * from tableA A inner join dbo.TableB B on A.StartDate between B.PeriodStartDate and B.PeriodEndDate
June 9, 2005 at 12:31 pm
cheers remi.. this doesnt appear to work all too well though but i think that's down to my poor explanation.
its part of a report writing tool.. basically this particular query will get passed a datevalue in the form of a variable such as ||ThisDate||
from this i need to query the datetime tables
so i followed:
Select distinct Financial_Periods.Period from events
inner join Financial_Periods on '||ThisDate||'
between Financial_Periods.StartDate and Financial_Periods.EndDate
(where evetns is the table the original value comes from. unsure what to put here as i dont really want to join using it)
still comes up with 2 values for almost every record .
is that a little clearer? THanks!!
June 9, 2005 at 12:46 pm
A little clearer but not enough.
Can you post the table definitions, some sample data along with the desired output??
Gonna be much simpler that way.
June 10, 2005 at 1:27 am
Sure ok... table financialperiods:
FY Period StartDate EndDate
1 1 01/04/2005 31/04/2005
1 2 01/05/2005 29/05/2005
1 3 30/05/2005 29/06/2006
etc etc
Table Events
Eventnumber Arrangmentnumber StartDate EndDate
1 1 01/04/2005 01/04/2005
2 1 02/04/2005 03/04/2005
3 1 01/04/2005 02/04/2005
4 2 05/04/2005 06/04/2005
where events form part of an arrangement im trying to find which period an arrangement falls into. this is done on the earliest start dates of evetns within that arrangement. so the variable ill be passing will be the min(events.startdate). Either that or i can pass the arrangementnumber and work out the dates from doing a join that way..
Thanks Remi!!
June 10, 2005 at 2:26 am
SELECT F.FY, F.PERIOD, F.STARTDATE, F.ENDDATE
FROM FINANCIALPERIODS F(NOLOCK)
WHERE (SELECT MIN(E.STARTDATE)
FROM EVENT E(NOLOCK)
WHERE E.ARRANGEMENTNUMBER = @PARM)
BETWEEN F.STARTDATE AND F.ENDDATE
NOTE: @PARM is supplied variable
Coach James
June 10, 2005 at 2:26 am
Hi,
Try this
Select distinct Financial_Periods.Period from
(Select Min(StartDate),ArrangementNumber from events
inner join Financial_Periods on '||ThisDate||'
between Financial_Periods.StartDate and Financial_Periods.EndDate
June 10, 2005 at 3:57 am
hi guys thanks so muchfor your replies!!
Coach James im afriad your suggestion returns 2 values for most of the records and W_Squared yours doesnt appear to work as you havent selected financial periods in the subquery you can't select it from the main query and also the join throws up an error if i put in fiancialperiods.period in the subquery!
arrgh!!
June 10, 2005 at 7:53 pm
SELECT top 1 F.FY, F.PERIOD, F.STARTDATE, F.ENDDATE
FROM FINANCIALPERIODS F(NOLOCK)
WHERE (SELECT MIN(E.STARTDATE)
FROM EVENT E(NOLOCK)
WHERE E.ARRANGEMENTNUMBER = @PARM)
BETWEEN F.STARTDATE AND F.ENDDATE
NOTE: @PARM is supplied variable
And if you want the later of the two returns use: ORDER BY F.FY DESC, F.PERIOD DESC
Coach James
June 12, 2005 at 3:27 am
Sorry, for some reason, it didn't paste, hope this is nearer to what you want to achieve
Select E.EventNumber, E.ArrangementNumber, F.FY,F.Period,F.StartDate,F.Enddate from
(Select Eventnumber,Min(StartDate) as MSD,ArrangementNumber from events
Group By EventNumber, Arrangementnumber) E
inner join Financial_Periods F on E.MSD
between F.StartDate and F.EndDate
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply