date and txn problem

Question

Post reply

date and txn problem

sj999

SSC Veteran

Points: 257
More actions
February 8, 2012 at 5:26 am

#256383

Hi

I want to find all customer IDs that have txn type AB occurring within 24 hours of txn type CD for that customer for the years 2010 and 2011. How do I do this.

The critical fields in the txn table are, txn date, txn type, custid

TABLE STRUCTURE:

TXNTABLE:

CUSTID as varchar 8

TXNTYPE as varchar 2

TXN date as date

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323482 More actions · Answer 2

sj999 (2/8/2012)

Hi

I want to find all customer IDs that have txn type AB occurring within 24 hours of txn type CD for that customer for the years 2010 and 2011. How do I do this.

The critical fields in the txn table are, txn date, txn type, custid

my best guess, since you didn't provide the CREATE TABLE... and INSERT INTO sampel data we really need to give an intelligent answer:



SELECT *

FROM txntable CDRECORDS

LEFT OUTER JOIN txntable ABRECORDS

ON CDRECORDS.custid = ABRECORDS.custid

WHERE CDRECORDS.txntype = 'CD'

  AND ABRECORDS.txntype = 'AB'

  AND ABS(DATEDIFF(hh,CDRECORDS.txndate,ABRECORDS.txndate)) <=24

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!