Im trying to loop through 2 temp tables to get results
temp table 1 returns product ids:
select iProductid, iRelationProdid, vdescription
where vcodealternative like 'xx'
9153 9344 L1
9154 9345 L2
temp table 2 returns customer ids of people who've bought one of these products:
SELECT DISTINCT dbo.tbPurchase.iCustomerId
FROM dbo.tbPurchaseItem INNER JOIN
dbo.tbPurchase ON dbo.tbPurchaseItem.iPurchaseId = dbo.tbPurchase.iPurchaseId INNER JOIN
dbo.tbProduct ON dbo.tbPurchaseItem.iProductId = dbo.tbProduct.iProductId INNER JOIN
dbo.tbCustomerGroup INNER JOIN
dbo.tbCustomer ON dbo.tbCustomerGroup.iCustomerId = dbo.tbCustomer.iCustomerId ON
dbo.tbPurchase.iCustomerId = dbo.tbCustomer.iCustomerId
WHERE (dbo.tbPurchase.dtPurchase > CONVERT(DATETIME, '2010-06-01 00:00:00', 102)) AND (dbo.tbCustomerGroup.iGroupId = 1572) AND
(dbo.tbProduct.vCodeAlternative LIKE 'xx')
GROUP BY dbo.tbPurchase.iCustomerId
Now what I need to do is
for each customer in tmpCustomer, for each row in tmpProduct find the last time they bought either a product matching iProductid or iRelationProdid within the time frame
and return the qty.
I'd help with this part of the query in another post, now Im trying to put it together so it can be called
in sql rather than my external program
-- open a cursor that looks at each customer in tmpCustomer and each row in tmpProduct
-- for each customer
SELECT PuI.iQty as TotalQty, Pu.iCustomerId
FROM dbo.tbPurchaseItem as PuI
INNER JOIN dbo.tbPurchase as Pu
ON PuI.iPurchaseId = Pu.iPurchaseId
WHERE Pui.iProductId in
(select iProductid, iRelationProdid from #tmpProduct) -- do this for each row in #tmpProduct
and pu.icustomerid= #tmpCustomer.iCustomerid -- for each customer in #tmpCustomer
and pu.dtpurchase >= '29 jun 2010'
and pu.iPurchaseId = (select max(x.iPurchaseId)
from dbo.tbpurchase as x
Inner Join dbo.tbpurchaseitem as xi
and (xi.iproductid in (select iProductid, iRelationProdid from #tmpProduct) )
I could do this in loops in my asp code but it dramatically impact the database as there will be approximately 2K customers and 5 product rows per query.
Appreciate any help!