May 24, 2015 at 2:05 pm
Hi SQL gurus,
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()]
I'm guessing some sort of recursion is required here to produce this.
I'd appreciate any pointers with this conundrum.
Thank you in advance!
May 24, 2015 at 2:37 pm
Here's one way that could work:
CREATE TABLE AssetPlacements (
ID int,
AssID int,
LocID int,
PlacementDate date)
INSERT INTO AssetPlacements VALUES
(1,1,1,'2015-05-01'),
(2,1,2,'2015-05-06'),
(3,1,3,'2015-05-09'),
(4,2,1,'2015-05-03'),
(5,2,2,'2015-05-07'),
(6,2,3,'2015-05-11');
WITH CTE (ID, AssID, LocID, PlacementDate, RN) AS (
SELECT
ID,
AssID,
LocID,
PlacementDate,
RN=ROW_NUMBER () OVER (PARTITION BY AssID ORDER BY PlacementDate ASC)
FROM AssetPlacements
)
SELECT
FirstLoc.ID,
FirstLoc.AssID,
FirstLoc.LocID,
StartDate=FirstLoc.PlacementDate,
EndDate=ISNULL(NextLoc.PlacementDate,GETDATE())
FROM CTE FirstLoc
LEFT JOIN
CTE NextLoc
ON NextLoc.AssID=FirstLoc.AssID
AND NextLoc.RN=FirstLoc.RN+1
There are likely more elegant ways of doing it, but this was the first I thought of, and should give you enough to figure out alternative methods from here, if needed.
I hope this helps!
May 24, 2015 at 2:44 pm
Something like this:
/*
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
*/
declare @AssestPlacement table(
ID int,
AssestID int,
LocationID int,
PlacementDate date);
insert into @AssestPlacement
values (1,1,1,'2015-05-01'),
(2,1,2,'2015-05-06'),
(3,1,3,'2015-05-09'),
(4,2,1,'2015-05-03'),
(5,2,2,'2015-05-07'),
(6,2,3,'2015-05-11');
with basedata as (
select
ID,
AssestID,
LocationID,
PlacementDate,
rn = row_number() over (partition by AssestID order by PlacementDate)
from
@AssestPlacement
)
select
bd1.ID,
bd1.AssestID,
bd1.LocationID,
bd1.PlacementDate StartDate,
isnull(bd2.PlacementDate,cast(getdate() as date)) EndDate
from
basedata bd1
left outer join basedata bd2
on (bd1.AssestID = bd2.AssestID and bd1.rn = bd2.rn - 1)
order by
bd1.AssestID,
bd1.PlacementDate;
May 24, 2015 at 3:17 pm
Heh, it looks like Lynn and I had identical thoughts 🙂
I originally didn't even mention this since the question was posted in the 2008 forum, but just on the off-chance that you are running 2012, LEAD and LAG were introduced for just such a situation.
With the same sample data as before, the query would look like this:
SELECT
FirstLoc.ID,
FirstLoc.AssID,
FirstLoc.LocID,
StartDate=FirstLoc.PlacementDate,
EndDate=LEAD(PlacementDate,1,GETDATE()) OVER (PARTITION BY AssID ORDER BY PlacementDate ASC)
FROM AssetPlacements FirstLoc
It looks nicer, and sometimes even gives a nice performance gain.
Cheers!
May 24, 2015 at 3:29 pm
Hi Jacob and Lynn,
Thank you so much for your quick replies and the brilliant solutions.
I've placed your scripts into a stored procedure and they work just great.
USE [AssetManager]
GO
/****** Object: StoredProcedure [dbo].[GetAssetDates] Script Date: 05/24/2015 22:27:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetAssetDates]
AS
BEGIN
WITH CTE (ID, AssID, LocID, PlacementDate, RN) AS (
SELECT
ID,
AssID,
LocID,
PlacementDate,
RN=ROW_NUMBER () OVER (PARTITION BY AssID ORDER BY PlacementDate ASC)
FROM AssetPlacements
)
SELECT
FirstLoc.ID,
FirstLoc.AssID,
FirstLoc.LocID,
StartDate=FirstLoc.PlacementDate,
EndDate=ISNULL(NextLoc.PlacementDate,GETDATE())
FROM CTE FirstLoc
LEFT JOIN
CTE NextLoc
ON NextLoc.AssID=FirstLoc.AssID
AND NextLoc.RN=FirstLoc.RN+1
END
I'm not sure if I should start another topic but my next question follows on from my first question...
If I now introduce 2 date parameters to the stored procedure, how could I utilize the start and end date fields to only return records where the date parameters lie between them? Something like this.
(@FrmDt BETWEEN StartDate AND EndDate) OR
(@ToDt BETWEEN StartDate AND EndDate)
I do appreciate your time and help with this!!
May 24, 2015 at 4:14 pm
If I understand that correctly, the logic is actually equivalent to the simpler looking:
WHERE StartDate<=@ToDt AND EndDate>=@FrmDt
However, note that when you add that WHERE clause, you won't be able to use the StartDate and EndDate column aliases.
Those aliases are defined in the SELECT clause, and the WHERE clause is processed before the SELECT, so you'd actually have to use the full expressions in the WHERE clause, like this:
WHERE FirstLoc.PlacementDate<=@ToDt AND ISNULL(NextLoc.PlacementDate,GETDATE())>=@FrmDt
Cheers!
May 24, 2015 at 4:30 pm
Thank you Jacob and Lynn, top people!!.
It's all great, it works and gives the desired results.
Thank you so much for your help it's saved me a big headache!! 🙂
May 24, 2015 at 5:14 pm
I'm glad we could help! The world is a better place with fewer headaches 🙂
Cheers!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply