asked
SQL Select
Hello, I have Two Tables as under SalesDetails PurchaseDetails Date SaleOrderId ProductId Qty Date PurcsOrderId ProductId Qty 2/2/12 S_1 P_1 4 1/2/12 PO_1 P_1 50 3/2/12 S_2 P_1 5 4/2/12 PO_2 P_1 50 3/2/12 S_2 P_2 7 6/2/12 S_3 P_1 3 9/2/12 S_4 1 5 from these two tables i want to show a report like this Product Inquiry Report ProductId: P_1 Date TrId Debit Credit 1/2/12 PO_1 50 2/2/12 S_1 4 3/2/12 S_2 5 4/2/12 PO_2 50 6/2/12 S_3 3 Here Qty column of purchasedetails becomes 'Debit' in the report and Qty in SalesDetails becomes 'Credit' column in
Report.In report, the transaction should be shown in order to Date as shown Is there a way to perform this in a single query, if yes, then how? if no, then is there any other solution? Thanks in advance
This web site works by voting. For all helpful answers below, indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
if you do a join on the two tables, then you cant do an order by 'Date' .. so it would be tough to do it in 1 query. -solution: do a union on both tables and rename the 'Qty' column, then dump into temp table. from there you can do it by using an order by on date column and just selecting what you need, WHERE productid = P_1 ; in crystal reports, create a parameter for this variable to have the header....
You could try this! Please keep in mind that this is totally polished to fit your example without taking into the consideration amount of data, indexes, etc. DECLARE @SalesDetails TABLE ( [Date] DATE , SaleOrderId VARCHAR(4) , ProductId VARCHAR(3) , Qty TINYINT ); DECLARE @PurchaseDetails TABLE ( [Date] DATE , PurcsOrderId CHAR(4) , ProductId VARCHAR(3) , Qty TINYINT ); INSERT INTO @SalesDetails SELECT '20120202' , 'S_1' , 'P_1' , 4 UNION ALL SELECT '20120302' , 'S_2' , 'P_1' , 5 UNION ALL SELECT '20120302' , 'S_2' , 'P_2' , 7 UNION ALL SELECT '20120602' , 'S_3' , 'P_1' , 3 UNION ALL SELECT '20120902' , 'S_4' , '1' , 5; INSERT INTO @PurchaseDetails SELECT '20120102' , 'PO_1' , 'P_1' , 50 UNION ALL SELECT '20120402' , 'PO_2' , 'P_1' , 50; WITH cteRootTable AS ( SELECT SD.[Date] , SD.SaleOrderId AS TrId , SD.Qty FROM @SalesDetails SD WHERE SD.ProductId LIKE 'P_1' UNION ALL SELECT PD.[Date] , PD.PurcsOrderId AS TrId , PD.Qty FROM @PurchaseDetails PD WHERE PD.ProductId LIKE 'P_1' ) SELECT RT.[Date] , RT.TrId , CASE WHEN RT.TrId LIKE 'PO_%' THEN CAST(RT.Qty AS VARCHAR) ELSE '' END AS Debit , CASE WHEN RT.TrId LIKE 'S_%' THEN CAST(RT.Qty AS VARCHAR) ELSE '' END AS Credit FROM cteRootTable RT ORDER BY RT.[Date];
@ikramonly - please share your solution with the forum so that others can benefit from you experience
17 People are following this question.
