November 18, 2008 at 11:47 am
Hello. I'm trying to find the location of a particular account based on an end-user's date range.
Based on the table data below (hope this is an acceptable way to provide you with some working data), Account '1' was assigned to WEST on 11/2/08 and remained there until 11/11 when it was assigned to NORTH. It stayed at NORTH until 11/17 when it was assigned to EAST.
If the end-user submits a query asking for the location of Account 1 between the dates 11/12 and 11/15, I want it to return the NORTH location.
with cte (ACCOUNT, DATE,LOCATION) as
(SELECT '1', GETDATE()-1,'EAST'
UNION
SELECT '1', GETDATE()-16,'WEST'
UNION
SELECT '1', GETDATE()-7, 'NORTH'
UNION
SELECT '2',GETDATE()-7,'EAST')
SELECT * FROM CTE
Thanks in advance for your help/feedback.
Adam.
November 18, 2008 at 2:35 pm
DECLARE @account int, @from datetime, @to datetime
select @account = 1 , @from ='20081112', @to ='20081115'
;with cte (ACCOUNT, DATE,LOCATION) as
(SELECT '1', cast('2008-11-17 00:00:00.000' AS datetime),'EAST'
UNION ALL
SELECT '1', '2008-11-02 00:00:00.000','WEST'
UNION ALL
SELECT '1', '2008-11-11 00:00:00.000', 'NORTH'
UNION ALL
SELECT '2','2008-11-11 00:00:00.000','EAST')
SELECT data.*
FROM
(SELECT c1.account, c1.date as begin_date,
c1.location,
ISNULL((SELECT min(c2.date)
FROM CTE c2
WHERE c2.account = c1.account
and c2.date > c1.date),GETDATE()) as to_date
FROM CTE c1
) data
WHERE data.account = @account
and data.begin_date <= @from
and data.to_date >= @to
* Noel
November 21, 2008 at 3:56 pm
Great thankyou, works perfectly. Always so simple once you see the solution.
noeld (11/18/2008)
DECLARE @account int, @from datetime, @to datetime
select @account = 1 , @from ='20081112', @to ='20081115'
;with cte (ACCOUNT, DATE,LOCATION) as
(SELECT '1', cast('2008-11-17 00:00:00.000' AS datetime),'EAST'
UNION ALL
SELECT '1', '2008-11-02 00:00:00.000','WEST'
UNION ALL
SELECT '1', '2008-11-11 00:00:00.000', 'NORTH'
UNION ALL
SELECT '2','2008-11-11 00:00:00.000','EAST')
SELECT data.*
FROM
(SELECT c1.account, c1.date as begin_date,
c1.location,
ISNULL((SELECT min(c2.date)
FROM CTE c2
WHERE c2.account = c1.account
and c2.date > c1.date),GETDATE()) as to_date
FROM CTE c1
) data
WHERE data.account = @account
and data.begin_date <= @from
and data.to_date >= @to
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply