SQL Query

Abdul Khan
Abdul Khan used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

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

Commented:
welcome. Happy to help.

Author

Commented:
Thanks Pawan!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial