help with flag based on prev. date by status

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with flag based on prev. date by status

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources