Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Capturing Historical/past data from Current data in tables Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
Below is some sample ddl and data. Each row represents an item that is on rent between a period of time. If I run the query as of now it gives me all the rentals in there current state as of today ,I want to be able to see what rentals were actually active/on rent on a particular day in the past e.g yesterday, 2 days ago, 3 days ago etc. I am finding it difficult to do this as I am looking at the data as of today, the rental could be closed(off-rent) today but active yesterday, How do I capture historical data? There is more columns in the table, I posted the below as I thought date columns would be most relevant, All ideas are welcome and any help is greatly appreciated.


IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable
( NUMBER INT PRIMARY KEY CLUSTERED,
CLOSE_DATE datetime,
Date_out datetime,
Date_in datetime
)
INSERT INTO #mytable (Number, CLOSE_DATE, Date_out, Date_in)

SELECT '1004','15/10/2012','28/09/2012','15/10/2012' UNION ALL
SELECT '1005','15/10/2012','12/09/2012','15/10/2012' UNION ALL
SELECT '1006','14/10/2012','17/06/2012','14/10/2012' UNION ALL
SELECT '1007','14/10/2012','17/08/2012','12/10/2012' UNION ALL
SELECT '1008','14/10/2012','17/05/2012','11/10/2012' UNION ALL
SELECT '1009','14/10/2012','01/06/2012','14/10/2012' UNION ALL
SELECT '1010','13/10/2012','17/03/2012','10/10/2012' UNION ALL
SELECT '1011','14/07/2012','01/07/2012','13/07/2012' UNION ALL
SELECT '1012','21/09/2012','17/09/2012','24/09/2012' UNION ALL
SELECT '1013','03/08/2012','21/06/2012','03/08/2012' UNION ALL
SELECT '1014','04/09/2012','17/08/2012','04/09/2012'

SELECT Number,Close_Date,Date_Out , Date_In
FROM #mytable


Post #1373336
Posted Tuesday, October 16, 2012 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
Thanks for the ddl and sample data.

Something like this?

declare @TargetDate datetime = dateadd(dd, datediff(dd, 0, getdate()) - 2, 0) --this will get a date 2 days ago
select @TargetDate

select * from #mytable
where Date_out <= @TargetDate and Date_in >= @TargetDate



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1373393
Posted Tuesday, October 16, 2012 10:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
Actually that probably would not get items that are not yet returned. You probably need something like this to accommodate a NULL in Date_in.

select * from #mytable
where Date_out <= @TargetDate and (Date_in >= @TargetDate or Date_in is null)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1373394
Posted Thursday, October 18, 2012 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:35 AM
Points: 40, Visits: 154
I was off the radar yesterday, thanks for the help sean , its got most of the way
Post #1374260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse