Comparing max orderid and date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Comparing max orderid and date?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-10-19 : 13:30:14

Apologies for asking something that's probably very easy to solve but I've been going round in circles with this

I have a table in Database1 called orders. PK is orderid and there is also a orderdate field.

I have a table in Database2 called snapshot. This table is populated each night at 23:59.59 with the maximum orderid from the orders table together with the capture date.

So it looks like

capturedate maxorderid
15/10/2012 12345
16/10/2012 12446
17/10/2012 12880

(orderid and maxorderid are the common fields between the two tables)

How do I query the orders table to return any records where orderid > than snapshot.maxorderid AND orderdate <= capturedate?

The idea behind this is to find instances of where staff are retrospectively booking orders (e.g, they've taken an order at 5.29p.m on a Friday night and don't process it until Monday morning because they'd rather go home!)

Any advice greatly appreciated as always.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 13:41:57

I think you already solved your own problem:

quote:
where orderid > than snapshot.maxorderid AND orderdate <= capturedate?

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-10-19 : 14:35:24

I'm wondering whether my problem is the blasted way SQL handles dates

capturedate is in one format, orderdate is in 2012-10-10 00:00:00.0000 format

Do I need to be monkeying around with CONVERT?

Why can't SQL treat dates as dates?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-19 : 14:50:21

Check the data types of these columns


SELECT column_name, data_type from INFORMATION_SCHEMA.[COLUMNS] 
WHERE TABLE_NAME = 'Orders' AND COLUMN_NAME = 'OrderDate';

Similar query for the other table/column as well.

If both are DATETIME types, then you are probably ok. If not, change your WHERE clause to this:

WHERE orderid > snapshot.maxorderid 
	AND CAST(orderdate AS DATETIME) <= DATEADD(dd,DATEDIFF(dd,0,capturedate),1)

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-10-20 : 12:19:13

Many thanks, will give it a try on Monday in the a.m

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 14:44:13

quote:
Originally posted by Rasta Pickles

I'm wondering whether my problem is the blasted way SQL handles dates

capturedate is in one format, orderdate is in 2012-10-10 00:00:00.0000 format

Do I need to be monkeying around with CONVERT?

Why can't SQL treat dates as dates?

It will treat dates as dates so long as you're using correct datatype for your fields (date/datetime/datetime2) and send values in unambiguos format like YYYYMMDD

make sure you read this

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources