Crystal Reports Query - not getting correct results

Question

I am in the process of creating a Crystal Report, but am stuck. I have one table (Numbers) with 20 items in it, 3 columns (item#, Price Per Order(PPO), Cases Per Order(CPO). The report is supposed to look at all the sales orders for each of our stores and print out, by item, how much revenue was lost or gained, if they ordered under or over the CPO. The issue I'm having is that there are some stores that haven't ordered some of the 20 items, but I still need these to show up on the report as revenue lost. I know this is mainly accomplished by a left join from the Numbers table. The query I have now is:


SELECT x.*, z.*
FROM NUMBERS x 
LEFT OUTER JOIN (
SELECT  
        BPCUSTOMER.BPCPYR_0 AS PayingBp,
        BPCUSTOMER.BPCNUM_0 AS BpNumber,
        ITMMASTER.ITMREF_0 AS ITMREF,
        SUM(SINVOICED.NETPRI_0) AS NETPRI,
        SUM(SINVOICED.QTY_0) AS Quantity,
        MAX(SINVOICED.ITMDES1_0) AS DES,
        MAX(BPADDRESS.CTY_0) AS BpCity,
        MAX(BPADDRESS.SAT_0) AS BpState

      FROM SINVOICED SINVOICED
        INNER JOIN BPCUSTOMER ON SINVOICED.BPCINV_0 = BPCUSTOMER.BPCNUM_0
        INNER JOIN ITMMASTER ON SINVOICED.ITMREF_0 = ITMMASTER.ITMREF_0
        INNER JOIN BPADDRESS ON SINVOICED.BPCINV_0 = BPADDRESS.BPANUM_0
        INNER JOIN BPCUSTOMER_PAY ON BPCUSTOMER.BPCINV_0 = BPCUSTOMER_PAY.BPCNUM_0

      WHERE (ITMMASTER.TSICOD_2 = N'YE1') 
        AND (BPCUSTOMER.BPCPYR_0 > N'10110')
      GROUP BY BPCUSTOMER.BPCPYR_0, BPCUSTOMER.BPCNUM_0, ITMMASTER.ITMREF_0

) z ON x.[Item #] = z.ITMREF

With this query, I do not get any of the NULL information. If I add in a particular store into the WHERE section, it gets me the correct results, but only for that one particular store. We have over 300 stores, so it wouldn't make sense for me to run it one store at a time. I'm guessing the issue is that when running it for all the stores, every item has been ordered at least once, so it doesn't show the null fields.

Hopefully this isn't too confusing. Any suggestions?

They want the report to show each item grouped by paying bp and then by the store number. this is a SQL database, but I got the query out of Crystal Reports and am trying to manipulate it in order to get me the correct results.

But I've had a request change. They now only want to see the items that were not purchased at all. I am still trying to do this with a left outer join fro the NUMBERS table and am still having the same problem. The query I have now is:

 SELECT "ZP_ITMMASTER"."ITMREF_0", "ZP_ITMMASTER"."ITMDES1_0", "ZP_NUMBERS"."Avg # Cases per order", "ZP_NUMBERS"."Price per order", "ZP_SDELIVERY"."BPCPYR_0", "ZP_SDELIVERY"."BPCORD_0", "ZP_NUMBERS"."Item #", "ZP_ITMMASTER"."TSICOD_2"
 FROM   (("master"."dbo"."ZP_NUMBERS" "ZP_NUMBERS" LEFT OUTER JOIN "master"."dbo"."ZP_SDELIVERYD" "ZP_SDELIVERYD" ON "ZP_NUMBERS"."Item #"="ZP_SDELIVERYD"."ITMREF_0") INNER JOIN "master"."dbo"."ZP_SDELIVERY" "ZP_SDELIVERY" ON (("ZP_SDELIVERYD"."SDHNUM_0"="ZP_SDELIVERY"."SDHNUM_0") AND ("ZP_SDELIVERYD"."SOHNUM_0"="ZP_SDELIVERY"."SOHNUM_0")) AND ("ZP_SDELIVERYD"."STOFCY_0"="ZP_SDELIVERY"."STOFCY_0")) INNER JOIN "master"."dbo"."ZP_ITMMASTER" "ZP_ITMMASTER" ON "ZP_SDELIVERYD"."ITMREF_0"="ZP_ITMMASTER"."ITMREF_0"
 WHERE  "ZP_ITMMASTER"."TSICOD_2"=N'YE1'
 ORDER BY "ZP_SDELIVERY"."BPCPYR_0", "ZP_SDELIVERY"."BPCORD_0", "ZP_ITMMASTER"."ITMREF_0"

Thanks!!

Is there a particular reason why you are grouping on BPCUSTOMER.BPCPYR_0 and BPCUSTOMER.BPCNUM_0? It seems like you only want ITMMASTER.ITMREF_0 since it is the only join field and the last in the list. — craig
What's your database? Not sure if this is a Crystal question. — paulmelnikow

Tim Lentine Tim Lentine · Accepted Answer · 2012-05-23T02:20:18

I agree with @noa, I don't think this is a Crystal issue, more likely a SQL query issue. That said, I suspect you may be onto something with your statement regarding running it for all stores at once if every product has been purchased at least once by one of the 300 stores.

What if you use a cross join between the numbers table and a "stores" table to create a cartesian product between numbers and stores. That is, a record for each item number for each of the 300 stores. Normally I wouldn't recommend this approach, but since we're talking relatively small numbers of entities this might do the trick.

Take the results of the cross join and perform your join against the subquery you included in your question, only this time join not only on item # but also the store id. By joining on the store id you can isolate the products sold by each store.

Crystal Reports Query - not getting correct results

1 Answers