Is datediff the answer to my troubles?

[Bebe]

I have a temp table that gives me two time entries we'll say invoice_date1 and invoice_date2

i need to extract the time difference between the two dates and if it is greater than an hour or 60 minutes i need to return it in a count statement.

how would i do this?

i did this and it is not working:
SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id IS NULL
AND ledger_id < 0
AND DATEDIFF ( mi , invoice_date1, invoice_date2) > 60

the only part that does not work in this is my date statment when i query before that statement I get correct results after that statement I do not.

[Mongo]

Hey Bebe!

Assuming #WorkingData field structure is something like:

Field Name .......... Datatype
ledger_id .............. integer (or long, double, single, numeric, whatever)
invoice_date1 ....... datetime
invoice_date2 ....... datetime

You won't have many records where the ledger_id is both NULL and less than zero, so you should change that part of your SARG.

Then, assuming invoice_date1 is earlier than invoice_date2 (since you otherwise get a negative return), the next hurdle is to avoid calculating a DATEDIFF return for null dates or a range greater than an integer value.

See if something like this work.

SELECT COUNT (*)			
	FROM #WorkingData	
	WHERE ledger_id < 0
	AND DATEDIFF ( hh
          , ISNULL( invoice_date1, GETDATE() )
          , ISNULL( invoice_date2
              , ISNULL( invoice_date1, GETDATE() ))) > 1

Hope this helps.

[Bebe]

Sorry i wrote it wrong it should be;

SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id2 IS NULL
AND ledger_id1 < 0
AND DATEDIFF ( mi , invoice_date1, invoice_date2) > 60

does this make a difference?

[Mongo]

If ledger_id1, ledger_id2, invoice_date1, invoice_date2 are all the applicable the fields in your #WorkingData table, then try...

SELECT COUNT (*)			
	FROM #WorkingData	
	WHERE ledger_id2 IS NULL 
        AND ledger_id1 < 0 
	AND DATEDIFF ( hh
          , ISNULL( invoice_date1, GETDATE() )
          , ISNULL( invoice_date2
              , ISNULL( invoice_date1, GETDATE() ))) > 1

Let me know how it works.