使一个报告的结果与另一个报告的结果一致. [英] Reconciling results of one report to match to another.

问题描述

水晶报道
SQL服务器
我需要更改Report 2.rpt的帮助.这样的Report2.rpt.应该进行协调并弄清正在报告的内容，并提供与Report1.rpt进行协调的简便方法.

对于Reprot2，它与Report1.rpt稍有不同，因为它排除了如下项:

1)凭单(ITEM.is_voucher_yn = 0)
2)非佣金项目(ITEM.non_commission = 0)

为了清楚起见，Report2应该在总计之后包括另一个部分，称为已排除"(整个部分为零时为空白"):

代金券(零时为空白)
物品详情在这里
非强制性(零时为空白)
物品详情在这里
子总计和报表2的总计总计

最终的总计将与Report1.rpt总计一致.

除报告2中的WHERE子句已满足上述条件外，两个报表的SQL均相同.

我希望创建一个单一的SQL，为我提供这两种情况的单独结果集(最好使用CASE语句)，我可以使用它们从下面的两个查询中分别在我的CR中显示结果

SQL遵循以下规则:

报告1

Crystal reports
SQL SERVER
I need help in changing Report 2.rpt. Such that Report2.rpt. should be reconciling and make it clear what is being reported and provide an easy way of reconciling to Report1.rpt.

In the case of Reprot2 it is slightly different to Report1.rpt in that it excludes items as follows:

1)Vouchers (ITEM.is_voucher_yn = 0)
2)Non Commission Items (ITEM.non_commission = 0)

So that this is clear Report2 should include another section AFTER to grand total called Excluded (The whole section Blank when Zero):

Vouchers (Blank when zero)
item details here
Non Commision (Blank when zero)
item details here
Sub Total and another Grand Total for Report2

The final Grand Total would reconcile to Report1.rpt Grand Total.

The SQL for both reports are same except for the WHERE clause in the REport 2 has got the above conditions.

I am looking to create one single SQL that gives me separate set of results for both conditions(preferably using CASE statements), that I can use to display results separately in my CR from the two queries below

SQL IS AS FOLLOWS:

Report 1

SELECT "sales_tx_lines"."item_qty", 
       "sales_tx_lines"."item_price", 
FROM   "sales_tx_hdr" "SALES_TX_HDR" 
           INNER JOIN "sales_tx_lines"                    
               "sales_tx_hdr"."sale_tx_no" = "sales_tx_lines"."sale_tx_no"
          LEFT OUTER JOIN "branch" 
                       ON "sales_tx_hdr"."branch_no" = "branch"."branch_code" 
         LEFT OUTER JOIN "address" 
                      ON "branch"."address_code" = "address"."address_code" 
        LEFT OUTER JOIN "item" 
                     ON "sales_tx_lines"."item_code" = "item"."item_code"
       LEFT OUTER JOIN "item_category" 
                    ON "item"."item_code" = "item_category"."item_code" 

报告2

Report 2

SELECT "sales_tx_lines"."item_qty", 
       "sales_tx_lines"."item_price", 
FROM   "sales_tx_hdr" "SALES_TX_HDR" 
           INNER JOIN "sales_tx_lines"                    
               "sales_tx_hdr"."sale_tx_no" = "sales_tx_lines"."sale_tx_no"
          LEFT OUTER JOIN "branch" 
                       ON "sales_tx_hdr"."branch_no" = "branch"."branch_code" 
         LEFT OUTER JOIN "address" 
                      ON "branch"."address_code" = "address"."address_code" 
        LEFT OUTER JOIN "item" 
                     ON "sales_tx_lines"."item_code" = "item"."item_code"
       LEFT OUTER JOIN "item_category" 
                    ON "item"."item_code" = "item_category"."item_code" 
WHERE      (item.is_voucher_yn = 0 or item.non_commission = 0)  

感谢您的帮助.

Any help is appreciated.

推荐答案

It is better to use a stored procedure with two parameters.
@ReportType - Report1 or Report2
@Excluded - (This is used only for report2, to show original Report2 data or to show excluded data)

SELECT
sales_tx_lines.item_qty,
sales_tx_lines.item_price,
FROM
sales_tx_hdr SALES_TX_HDR
INNER JOIN sales_tx_lines ON sales_tx_hdr.sale_tx_no = sales_tx_lines.sale_tx_no
LEFT OUTER JOIN branch ON sales_tx_hdr.branch_no = branch.branch_code
LEFT OUTER JOIN address ON branch.address_code = address.address_code
LEFT OUTER JOIN item ON sales_tx_lines.item_code = item.item_code
LEFT OUTER JOIN item_category ON item.item_code = item_category.item_code
WHERE 1 = 1 
AND
(@ReportType = 1) --To check if Report1 or Report2
OR
(@Excluded = 0 AND (item.is_voucher_yn = 0 OR item.non_commission = 0)) --If Report2, Report2 data
OR
(@Excluded = 1 AND (item.is_voucher_yn <> 0 OR item.non_commission <> 0)--If Report2, Excluded data

如果我错了，请纠正我.

问候，
Swami

Please correct me if i am wrong.

Regards,
Swami

您的解决方案:

1)使用存储过程 [参数 [建议的过程 [

Your Solution:

1) Use Stored Procedure[^] instead of inline SQL Query.

2) Pass one parameter[^] to Sotred Procedure[^] which need to differentiate the query i.e. for 1st case or 2nd case.

3)Your stored procedure will look like below

SELECT
    sales_tx_lines.item_qty,
    sales_tx_lines.item_price,
FROM
    sales_tx_hdr SALES_TX_HDR
    INNER JOIN sales_tx_lines ON sales_tx_hdr.sale_tx_no = sales_tx_lines.sale_tx_no
    LEFT OUTER JOIN branch ON sales_tx_hdr.branch_no = branch.branch_code
    LEFT OUTER JOIN address ON branch.address_code = address.address_code
    LEFT OUTER JOIN item ON sales_tx_lines.item_code = item.item_code
    LEFT OUTER JOIN item_category ON item.item_code = item_category.item_code
WHERE 1 = 1 AND (item.is_voucher_yn = CASE WHEN @intType = 1 THEN item.is_voucher_yn ELSE 0 END
                    or item.non_commission = CASE WHEN @intType = 1 THEN item.non_commission ELSE 0 END)

这篇关于使一个报告的结果与另一个报告的结果一致.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！