| Author |
Topic |
|
mani6961
Starting Member
10 Posts |
 Posted - 2012-07-04 : 11:28:05
|
Hey all,
I need help with a query. Basically I have data similar to this
Booking TimeStamp Status BookDate BookTime
1 06/16/2012 Moved 07/07/2012 900
1 07/01/2012 Moved 07/07/2012 1000
Basically I need a stored procedure (something) that, if run for a date of 01/07/2012, for a unique booking, the view will flag the record as UNCHANGED_DATE (i.e. note the time change but the book date remained the same.
Can someone help me write a query that will help me flag this record:
i.e. something like:
Booking TimeStamp Status BookDate Unchanged_Date
1 07/01/2012 Moved 07/07/2012 1
I hope this makes sense. I will try to clarify if it does not. I was thinking of ordering by TimeStamp and grabbing the one BEFORE the last date (somehow) but not sure how to do this in TSQL-2000.
Thanks, any help would be appreciated.
-Ty |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-04 : 19:06:47
|
seems like this
DECLARE @Date datetime
SELECT @Date = '20120701'
SELECT TOP 1 Booking,
TimeStamp,
Status,
BookDate,
CASE WHEN Timestamp = @Date THEN 1 ELSE 0 END AS Unchanged_Date
FROM table
WHERE Timestamp < = @date
ORDER BY Timestamp DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
mani6961
Starting Member
10 Posts |
Posted - 2012-07-05 : 16:00:53
|
quote:
Originally posted by visakh16
seems like this
DECLARE @Date datetime
SELECT @Date = '20120701'
SELECT TOP 1 Booking,
TimeStamp,
Status,
BookDate,
CASE WHEN Timestamp = @Date THEN 1 ELSE 0 END AS Unchanged_Date
FROM table
WHERE Timestamp < = @date
ORDER BY Timestamp DESC
Hi, thanks, but not really what I had in mind. Based on a date, it should be able to work on a multitude of records (i.e. not just top 1 but for each set of Bookings in a query).
-Ty |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 16:29:54
|
can you show a sample data to illustrate your scenario clearly?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mani6961
Starting Member
10 Posts |
Posted - 2012-07-05 : 16:51:48
|
quote:
Originally posted by visakh16
can you show a sample data to illustrate your scenario clearly?
Yes.
Booking TimeStamp Status BookDate BookTime
1 06/16/2012 Moved 07/07/2012 900
1 07/01/2012 Moved 07/07/2012 1000
2 06/16/2012 Moved 08/14/2012 900
2 06/29/2012 Moved 08/16/2012 1130
3 04/13/2012 Moved 06/06/2012 700
3 07/01/2012 Moved 06/06/2012 1600
4 06/30/2012 Moved 07/14/2012 800
4 07/01/2012 Moved 07/25/2012 900
Basically, if I run a report on this data for a TimeStamp of 07/01/2012 I would expect to see the following:
Booking TimeStamp Status BookDate Unchanged
1 07/01/2012 Moved 07/07/2012 Yes
3 07/01/2012 Moved 06/06/2012 Yes
4 07/01/2012 Moved 07/25/2012 No
Notice that I will only get bookings for the run date. Furthermore, notice that 1 and 3 were flagged as unchanged because the movement resulted in no change of the actula BookDate (just the time, which I don't care about) whereas with the 4th booking the date did change, so it should be flagged as "No" (meaning that it WAS changed).
Hope this clears things up a bit - it is somewhat complicated. In the report, the end user doesn't care about bookings (run for a particular date \ date span) where only the TIME changes (they only care about when the date of the booking changes)
-Ty |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 17:13:45
|
[code]
DECLARE @yourTimeStamp datetime
SELECT @yourTimeStamp = '20120701'
SELECT *,
CASE WHEN DATEDIFF(dd,0,t1.BookDate) = DATEDIFF(dd,0,t.BookDate) THEN 'Yes' ELSE 'No' END AS Unchanged
FROM Table t
OUTER APPLY (SELECT TOP 1 BookDate
FROM table
WHERE Booking = t.Booking
AND timestamp < t.Timestamp
ORDER BY timestamp DESC) t1
WHERE TimeStamp = @yourTimeStamp
[/code]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mani6961
Starting Member
10 Posts |
Posted - 2012-07-05 : 18:11:48
|
quote:
Originally posted by visakh16
DECLARE @yourTimeStamp datetime
SELECT @yourTimeStamp = '20120701'
SELECT *,
CASE WHEN DATEDIFF(dd,0,t1.BookDate) = DATEDIFF(dd,0,t.BookDate) THEN 'Yes' ELSE 'No' END AS Unchanged
FROM Table t
OUTER APPLY (SELECT TOP 1 BookDate
FROM table
WHERE Booking = t.Booking
AND timestamp < t.Timestamp
ORDER BY timestamp DESC) t1
WHERE TimeStamp = @yourTimeStamp
Unfortunately, OUTER APPLY wasn't introduced until SQL 2005, I'm stuck with using 2000.
-Ty |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 18:16:03
|
ah...didnt notice its 2000 forum
DECLARE @yourTimeStamp datetime
SELECT @yourTimeStamp = '20120701'
SELECT *,
CASE WHEN DATEDIFF(dd,0,PrevBookDate) = DATEDIFF(dd,0,BookDate) THEN 'Yes' ELSE 'No' END AS Unchanged
FROM
(
SELECT *,
(SELECT TOP 1 BookDate
FROM table
WHERE Booking = t.Booking
AND timestamp < t.Timestamp
ORDER BY timestamp DESC) AS PrevBookDate
FROM Table t
WHERE TimeStamp = @yourTimeStamp
)t1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mani6961
Starting Member
10 Posts |
Posted - 2012-07-06 : 11:29:53
|
quote:
Originally posted by visakh16
ah...didnt notice its 2000 forum
DECLARE @yourTimeStamp datetime
SELECT @yourTimeStamp = '20120701'
SELECT *,
CASE WHEN DATEDIFF(dd,0,PrevBookDate) = DATEDIFF(dd,0,BookDate) THEN 'Yes' ELSE 'No' END AS Unchanged
FROM
(
SELECT *,
(SELECT TOP 1 BookDate
FROM table
WHERE Booking = t.Booking
AND timestamp < t.Timestamp
ORDER BY timestamp DESC) AS PrevBookDate
FROM Table t
WHERE TimeStamp = @yourTimeStamp
)t1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Perfect! Works like a charm, thanks!
-Ty |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-06 : 11:36:12
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
|
help with flag based on prev. date by status