Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Looping through 2 temporary tables in SQL

Avatar of Louise
Louise asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
14 Comments1 Solution487 ViewsLast Modified:
Im trying to loop through 2 temp tables to get results
temp table 1 returns product ids:
select iProductid, iRelationProdid, vdescription
into #tmpProduct
from tbProduct
where vcodealternative like 'xx'

eg returns
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
into #tmpCustomers
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

eg returns
1234
5678

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
                                          on x.ipurchaseid=xi.ipurchaseid
                                    Where x.icustomerid=pu.icustomerid
                                         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!
ASKER CERTIFIED SOLUTION
Avatar of Leo Torres
Leo TorresFlag of United States of America imageSQL Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answers