SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 help with flag based on prev. date by status		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mani6961
Starting Member

Canada
5 Posts
Posted - 07/04/2012 :  11:28:05  Show Profile  Reply with Quote
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

India
47139 Posts
Posted - 07/04/2012 :  19:06:47  Show Profile  Reply with Quote
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/
Go to Top of Page

mani6961
Starting Member

Canada
5 Posts
Posted - 07/05/2012 :  16:00:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47139 Posts
Posted - 07/05/2012 :  16:29:54  Show Profile  Reply with Quote
can you show a sample data to illustrate your scenario clearly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mani6961
Starting Member

Canada
5 Posts
Posted - 07/05/2012 :  16:51:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47139 Posts
Posted - 07/05/2012 :  17:13:45  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mani6961
Starting Member

Canada
5 Posts
Posted - 07/05/2012 :  18:11:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47139 Posts
Posted - 07/05/2012 :  18:16:03  Show Profile  Reply with Quote
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/
Go to Top of Page

mani6961
Starting Member

Canada
5 Posts
Posted - 07/06/2012 :  11:29:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47139 Posts
Posted - 07/06/2012 :  11:36:12  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000