We help IT Professionals succeed at work.

SQL Query

Abdul Khan
Abdul Khan asked
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

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

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

Author

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:
Please check this -

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

Open in new window


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)

Open in new window

Author

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

Commented:
welcome. Happy to help.

Author

Commented:
Thanks Pawan!