We help IT Professionals succeed at work.

# SQL Query

on
Need help with a SQL query where I am able to:
Select an account number, ProdID, and TrDate when ProdID 5612 sold twice within 24 hours after ProdID 2808 is sold.

Acct#            ProdID                  TrDate
123            2808                  2017-10-09 11:42:00.000
123            5612                  2017-10-09 11:50:00.000
123            5612                  2017-10-10 11:42:00.000
123            2808                  2017-10-11 11:42:00.000
123            5612                  2017-10-11 13:42:00.000
123            5612                  2017-10-11 14:42:00.000
123            5612                  2017-10-15 11:42:00.000
Comment
Watch Question

## View Solution Only

Database Expert
Awarded 2016
Top Expert 2016

Commented:
What will the output from the input you have given,

Commented:
print the AcctNumber, ProdID, and Trdate only when the product 5612 sold twice within 24 Hours.
The goal is to find out that how many of the 2808 sold when 5612 sold within 24 hours:

123            2808                  2017-10-11 11:42:00.000
123            5612                  2017-10-11 13:42:00.000
123            5612                  2017-10-11 14:42:00.000
Database Expert
Awarded 2016
Top Expert 2016
Commented:

``````SELECT * FROM
(
Select [Acct#],[ProdID],[TrDate]
FROM sold
WHERE [ProdID] = 2808
)r
CROSS APPLY
(
Select [Acct#],[ProdID],[TrDate]
FROM sold
WHERE [ProdID] = 5612 AND [Acct#] = r.[Acct#]
AND [TrDate] BETWEEN r.TrDate AND DATEADD(d,1,r.TrDate)
)u
``````

OUTPUT

``````/*------------------------
SELECT * FROM
(
Select [Acct#],[ProdID],[TrDate]
FROM sold
WHERE [ProdID] = 2808
)r
CROSS APPLY
(
Select [Acct#],[ProdID],[TrDate]
FROM sold
WHERE [ProdID] = 5612 AND [Acct#] = r.[Acct#]
AND [TrDate] BETWEEN r.TrDate AND DATEADD(d,1,r.TrDate)
)u
------------------------*/
Acct#       ProdID      TrDate                  Acct#       ProdID      TrDate
----------- ----------- ----------------------- ----------- ----------- -----------------------
123         2808        2017-10-09 11:42:00.000 123         5612        2017-10-09 11:50:00.000
123         2808        2017-10-09 11:42:00.000 123         5612        2017-10-10 11:42:00.000
123         2808        2017-10-11 11:42:00.000 123         5612        2017-10-11 13:42:00.000
123         2808        2017-10-11 11:42:00.000 123         5612        2017-10-11 14:42:00.000

(4 row(s) affected)
``````

Commented:
Thanks so much Pawan!
Database Expert
Awarded 2016
Top Expert 2016

Commented:
welcome. Happy to help.

Commented:
Thanks Pawan!