vendredi 20 février 2015

How to run a subquery based on results of a query SQL

I have 2 queries i'd like to run. The idea here is to run a query on the transaction table by the transaction "type". Based on these results, I want to run another query to see the customers last transaction based on a specific type to see if the service ID was the same. If it's not the same, I want to flag it as "upgraded"


Here is the initial query that Pulls the results from a transactions table based on a transaction type:



Select customerid, serviceid
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save')


The output for this is:



Customerid ServiceID
1 11
2 21
3 21
4 11
5 12
6 11


What i'd like to do next is run this query, matching the customerID to see what the customers last charge was:



Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
Where (transactiontype = 'Cust Purchase')
Group By serviceID


My Final output combining the two queries would be:



Customerid ServiceID Last Purchase Upgraded?
1 11 11 No
2 21 11 Yes
3 21 12 Yes
4 11 10 Yes
5 12 12 No
6 11 11 No


I thought this might work but it doesn't quite give me what I want. It returns too many results, so the query is obviously not correct.:



Select serviceID, MAx(dtcreated) as MostRecent
From Transactions
WHERE Where (transactiontype = 'Cust Purchase') AND EXISTS
(Select customerid, serviceid
from Transactions
where (dtcreated > @startdate and dtcreated < @enddate) and (transactiontype = 'Cust Save'))
GROUP BY serviceid

Aucun commentaire:

Enregistrer un commentaire