Report Design: combine two queries

combine two queries

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19948
Printed Date: 18 May 2024 at 9:59pm

Topic: combine two queries

Posted By: joeld_mn
Subject: combine two queries
Date Posted: 26 Aug 2013 at 6:42am

Working on an inventory report. Created a SQL command statement to list all parts and if there are an quantities on hand. Since parts could be in multiple warehouse, I have grouped by part number, hidden the details and show the on hand summary, part number and other data,(min/max qty) in the group footer. Created a prompt in crystal so the user can select parts by customer.

Sql Command:
Select P.PartNum, P.PartDescription,
     PC.ClassID, PC.Description as 'Class Description',
     PP.MinimumQty, PP.MaximumQty, PP.LeadTime, PP.MinOrderQty,
     C.CustID, C.Name as 'Customer Name',
               PB.WarehouseCode, PB.BinNum, PB.OnHandQty, PB.LotNum
From Part P
     Inner Join PartClass PC on P.Company = PC.Company
          and P.ClassID = PC.ClassID
     Inner Join PartPlant PP on P.Company = PP.Company
          and P.PartNum = PP.PartNum
     Inner Join Customer C on P.Company = C.Company
          and Left(P.ClassID,3) = C.CustID
               Left Outer Join PartBin PB on P.Company = P.Company
          and P.PartNum = PB.PartNum
Where P.TypeCode = 'P'
Order By P.PartNum

So far so good. I get the expected results of all parts and any on hand quantities. The result display quickly at this point.

Now for each part returned in the above query, I need to know if we have purchased and/or used any of those parts in the last year. So I created another command query that looks for the all possible purchase and use types that happened in the last year.

Select PartNum, LotNum, TranQty, TranType, TranDate
From PartTran
Where TranType in ('Pur-Ins', 'Pur-Stk', 'Pur-Mtl', 'Pur-Ukn', 'Stk-Mtl', 'Stk-Cus')
     and TranDate > DATEADD(YEAR,-1,GETDATE())
Order By PartNum

I created a subreport using the above query and linked the PartNum from the footer to the PartNum in the above query. The subreport has two running totals that total the TranTypes that start with Pur and the TranTypes that start with Stk to get my purchased and used quantities. I placed the subreport in the group footer and I get the desired results but it takes minutes to run and when I go to the next page it takes time to generate each page.

The report as is, is poorly designed because it is too slow to be usable. The separate queries run very fast in SQL Management Studio, but my implementation in Crystal need some help.

Any ideas on how to speed up the report?

Thanks.

Replies:

Posted By: DBlank
Date Posted: 26 Aug 2013 at 7:20am

if I understnad you correctly try and imbed your second query as sub queries in your first query. You only wan starting sums values for each partnumber...

Select P.PartNum, P.PartDescription,
     PC.ClassID, PC.Description as 'Class Description',
     PP.MinimumQty, PP.MaximumQty, PP.LeadTime, PP.MinOrderQty,
     C.CustID, C.Name as 'Customer Name',
               PB.WarehouseCode, PB.BinNum, PB.OnHandQty, PB.LotNum, V1.StartQty, V2.StartPurchase
From Part P
     Inner Join PartClass PC on P.Company = PC.Company
          and P.ClassID = PC.ClassID
     Inner Join PartPlant PP on P.Company = PP.Company
          and P.PartNum = PP.PartNum
     Inner Join Customer C on P.Company = C.Company
          and Left(P.ClassID,3) = C.CustID
               Left Outer Join PartBin PB on P.Company = P.Company
          and P.PartNum = PB.PartNum

left outer join

(Select PartNum, sum(TranQty) as StartQty
From PartTran PT1
Where TranType in ('Stk-Mtl', 'Stk-Cus')
and TranDate > DATEADD(YEAR,-1,GETDATE())

group by partnum) V1 on V1.partnum = P.partnum

left outer join

(Select PartNum, sum(TranQty) as StartPurchase
From PartTran PT2
Where TranType in ('Pur-Ins', 'Pur-Stk', 'Pur-Mtl', 'Pur-Ukn')
and TranDate > DATEADD(YEAR,-1,GETDATE())

group By PartNum) as V2 on V2.partnum = P.partnum

Where P.TypeCode = 'P'
Order By P.PartNum

Posted By: joeld_mn
Date Posted: 26 Aug 2013 at 7:55am

Thanks so much! I had tried to make a single query multiple time and found a lot of ways that didn't work. Your query works perfectly.

Thanks for taking the time to help!
Joel