Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-02 : 19:56:33
|
Good day!I have this running query that sums up the quantity of an items base on the date criteria. However Ive notice that the result has been displayed twice. If I change the red color below to this: SUM(BeginInventory) AS BeginInventory, SUM(Purchases) as Purchases, SUM(TransfersIn) AS TransfersIn, SUM(TransfersOut) AS TransfersOut, SUM(SoldItem) AS SoldItem If I put Sum on the aggregate variables it doubles the quantity. Which means that result is supposed to be 46 but it displays 92. At the same time it appears twice too which is not correct.The result of the above changed is this:productcode BeginInventory itemname Purchases TransfersIn TransfersOut SoldItem 4567342200 0 cream milk 92 0 0 364567342200 0 cream milk 92 0 0 36 The result of summing the quantity of an item must only be appear once per item. Ive marked red those that I think is the problem or maybe its the LEFT JOIN? Here is my code:USE [POS_DB]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @fromdate as DATETIME,@todate as DATETIMEAsBegin SELECT p.productcode, itemname, description, packtype, itemtype, c.categoryname, s.suppliername, supplierprice, srp, discount, itemlocation, expdate, vatable, service_other_charge_amount_percent, COALESCE(BeginInventory,0) AS BeginInventory, COALESCE(Purchases,0) as Purchases, COALESCE(TransfersIn,0) AS TransfersIn, COALESCE(TransfersOut,0) AS TransfersOut, COALESCE(SoldItem,0) AS SoldItem COALESCE(Sold,0) AS RunningOnhand FROM tbl_product_list p LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate >= @fromdate AND podate < @todate + 1 GROUP BY prodcode,memo)i ON i.prodcode = p.productcode LEFT JOIN (SELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY prodcode,TransferInOutMemo,qty)pts ON pts.prodcode= p.productcode LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem FROM tbl_pos_sales_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY productcode)psd ON psd.productcode= p.productcode LEFT JOIN tbl_supplier s ON s.suppliername=(SELECT s.suppliername WHERE s.suppliercode=p.supplierid) LEFT JOIN tbl_prod_category c ON c.categoryname=(SELECT c.categoryname WHERE c.catgoryid=p.category) GROUP BY p.productcode,itemname,description,packtype,itemtype,c.categoryname, s.suppliername,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,BeginInventory,Purchases,SoldItem, TransfersIn,TransfersOut ORDER BY p.itemname ASCEnd Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-02 : 22:35:53
|
Looks like in your two derived tables you need to change the GROUP BY to just prodcode (and productcode for the other one). Both those derived tables are potentially returning multiple produce code rows. Also I doubt you need the GROUP BY in the main query.Be One with the OptimizerTG |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-03 : 02:06:24
|
thank you TG!Ive changed the derived query to this but still the same result.LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate >= @fromdate AND podate < @todate + 1 GROUP BY prodcode)i ON i.prodcode = p.productcode LEFT JOIN (SELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 BY prodcode)pts ON pts.prodcode= p.productcode LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem FROM tbl_pos_sales_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY productcode)psd ON psd.productcode= p.productcode Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-03 : 06:37:14
|
One way to debug this would be to take one table at a time and see how many rows are returned for the product code of interest (4567342200). So start with tbl_product_listSELECT * FRM tbl_product_list WHERE productcode = 4567342200; This should return only one row. Do that with the second table - and you should still get only one row. Now do that with the third tableSELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 AND prodcode = 4567342200 GROUP BY prodcode,TransferInOutMemo,qty You may get more than one row. If that is the case, that means you have more than combination of TransferInOutMemo/qty. If that is the case, you have to figure out how you want to handle that. Perhaps you should group only by prodcode ? |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-03 : 10:49:12
|
Thank you James K!Upon checking, this 3 tables returns twice or more PO_detail, tbl_product_transfer_detail, tbl_pos_sales_detail. This is I think normal because PO_detail will hold all the detail of items purchased. bl_product_transfer_detail will hold all the detail of items being transfered. And tbl_pos_sales_detail will hold all the detail of items being tsold. I think I missed the point here. Maybe my left joins in the query?Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-03 : 10:57:51
|
can you post how sample data is for a particular po? Is transfer recorded as a single or multiple records?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-03 : 18:40:22
|
What I was suggesting was that inferring from your SELECT statements in the third join, it seems like you would not need to group by TransferInOutMemo,qty. See change below. Give that a try and see if that gets you close to what you are looking for: LEFT JOIN (SELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY prodcode ,TransferInOutMemo,qty )pts ON pts.prodcode= p.productcode |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-03 : 23:02:53
|
But as per OPS post 02/03/2013 : 02:06:24 it seems even after doing the changes he's getting duplicates------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-06 : 06:12:38
|
thank you visakh16, James K, TG!I still have this problem. Sorry for late reply. Anyway here's my table structure. Hope you guys can help me to solve this:PO_detailporefno podate supplierid prodcode itemname qty memo000001 2/2/2013 11 4567342200 cream milk 23 purchases000001 2/2/2013 11 4567342201 dairy cream 16 purchases000002 2/6/2013 11 4567342200 cream milk 12 purchases000003 2/6/2013 11 4567342201 dairy cream 4 beginning.....and so on... Thank you for helping!tbl_product_transfer_detailtransref ptransdate supplierid prodcode itemname TransferInOutMemo qty 1 2/2/2013 11 4567342200 cream milk IN 6 1 2/2/2013 11 4567342201 dairy cream OUT 5 2 2/6/2013 11 4567342200 cream milk IN 3 2 2/6/2013 11 4567342201 dairy cream OUT 4.....and so on... tbl_pos_sales_detailsalesid transdate prodcode productdesc qty salestype00000001 2/2/2013 4567342200 cream milk 4 cash00000001 2/2/2013 4567342201 dairy cream 7 cash 00000002 2/6/2013 4567342200 cream milk 3 credit00000002 2/6/2013 4567342201 dairy cream 2 credit.....and so on... Thank you for helping!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 08:24:51
|
To continue with the thought process that I was describing in my previous post, now you have verified that the first 2 joins should give you only one row. It seems to me like the third join also should do the same - i.e., still give you only one row. That leaves the fourth and fifth joins. See if you have more than one row for a given suppliername in tbl_supplier or more than one row for a given categoryname in tbl_prod_category.Also, the last two joins seem somewhat unusual/unnecessary. Try changing those to:LEFT JOIN tbl_supplier s ON s.suppliername=p.supplierid LEFT JOIN tbl_prod_category c ON c.categoryname=p.category |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-08 : 20:36:55
|
Thank you James K, visakh16!Ive modified the query and removed the last two LEFT JOINS but I still have this duplicate results. Here is the modified one:USE [POS_DB]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @fromdate as DATETIME,@todate as DATETIMEAsBegin SELECT p.productcode, itemname, description, packtype, itemtype, c.categoryname, s.suppliername, supplierprice, srp, discount, itemlocation, expdate, vatable, service_other_charge_amount_percent, COALESCE(BeginInventory,0) AS BeginInventory, COALESCE(Purchases,0) as Purchases, COALESCE(TransfersIn,0) AS TransfersIn, COALESCE(TransfersOut,0) AS TransfersOut, COALESCE(SoldItem,0) AS SoldItem --COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand FROM tbl_product_list p LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate >= @fromdate AND podate < @todate + 1 GROUP BY prodcode,memo)i ON i.prodcode = p.productcode LEFT JOIN (SELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY prodcode,TransferInOutMemo,qty)pts ON pts.prodcode= p.productcode LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem FROM tbl_pos_sales_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY productcode)psd ON psd.productcode= p.productcode GROUP BY p.productcode,itemname,description,packtype,itemtype ,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,BeginInventory,Purchases,SoldItem, TransfersIn,TransfersOut ORDER BY p.itemname ASCEnd Now I got this resultproductcode BeginInventory itemname Purchases TransfersIn TransfersOut SoldItem 4567342200 0 cream milk 1400 0 0 4934567342200 0 cream milk 1400 0 0 599 The first result is correct as Ive check in in its corresponding table. However the second row(color red) should no be displayed because its just the same item. And I also wonder that in the second row the qty is not the same in the first row?Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 23:15:32
|
The last posted statement will definitely bring duplicate results as you're grouping on other fields like TransferInOutMemo which will ahve unique values within a group causing duplicates------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-08 : 23:26:32
|
thank you visakh16!Noted and changed but still I have duplicated results?LEFT JOIN (SELECT prodcode, SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn, SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut FROM tbl_product_transfer_detail WHERE transdate >= @fromdate AND transdate < @todate + 1 GROUP BY prodcode)pts Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-08 : 23:37:37
|
I think this might affect duplicate results?My table structure for tbl_pos_sales_detail will have the same salesid but with different salestype='cash' or 'credit'. Just like below. salesid transdate prodcode productdesc qty salestype00000001 2/2/2013 4567342200 cream milk 4 cash00000001 2/2/2013 4567342201 dairy cream 7 cash 00000002 2/6/2013 4567342200 cream milk 3 credit00000002 2/6/2013 4567342201 dairy cream 2 credit00000001 2/2/2013 4567342200 cream milk 3 credit00000001 2/2/2013 4567342201 dairy cream 1 credit Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 23:45:04
|
Nope still if you're grouping on prodcode you should get only one row per prodcode------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-09 : 01:17:15
|
Thank you visakh16!It seems that the query is correct. But I still cant figure out which part is missing on my table structure!Which part should I execute checking?Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-09 : 01:22:29
|
i think issue is with tbl_product_list table containing duplicates------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-09 : 01:31:33
|
thank you visakh16!I'll check it out..-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-11 : 03:02:20
|
Thank you visakh16!I have already done checking duplicate records on tbl_product_list but there is no duplicate records as Ive checked using this query:use POS_DBgoselect productcode, count(productcode) as NumOccurencesfrom tbl_product_listgroup by productcodehaving (COUNT(productcode) > 1) However Ive noticed that even when I cut the query with just below:USE [POS_DB]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @todate as DATETIMEAsBegin SELECT p.productcode, itemname, COALESCE(BeginInventory,0) AS BeginInventory, COALESCE(Purchases,0) as Purchases FROM tbl_product_list p LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate < @todate + 1 GROUP BY prodcode)i ON i.prodcode = p.productcodeGROUP BY p.productcode,itemname,BeginInventory,Purchases I still have duplicate results like this:productcode itemname BeginInventory Purchases 4567342200 cream milk 0 186 4567342200 cream milk 0 792 The first row should not be displayed! Here is again my PO_detail table structure:porefno podate supplierid prodcode itemname qty memo000001 2/2/2013 11 4567342200 cream milk 23 purchases000001 2/2/2013 11 4567342201 dairy cream 16 purchases000002 2/6/2013 11 4567342200 cream milk 12 purchases000003 2/6/2013 11 4567342201 dairy cream 4 beginning000004 2/8/2013 11 4567342200 dairy cream 2 void Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 03:24:39
|
the issue is your group by. try thisUSE [POS_DB]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @todate as DATETIMEAsBegin SELECT p.productcode, itemname, COALESCE(SUM(BeginInventory),0) AS BeginInventory, COALESCE(SUM(Purchases),0) as Purchases FROM tbl_product_list p LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate < @todate + 1 GROUP BY prodcode)i ON i.prodcode = p.productcodeGROUP BY p.productcode,itemname,BeginInventory,Purchases ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
adbasanta
Posting Yak Master
120 Posts |
Posted - 2013-02-11 : 03:41:48
|
thank you visakh16!Ive changed the query to this but Ive noticed that the quantity is not correct. USE [POS_DB]GO/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Show_all_items_DateView] -- Add the parameters for the stored procedure here @todate as DATETIMEAsBegin SELECT p.productcode, itemname, COALESCE(SUM(BeginInventory),0) AS BeginInventory, COALESCE(SUM(Purchases),0) as Purchases FROM tbl_product_list p LEFT JOIN (SELECT prodcode, SUM(CASE WHEN memo='beginning' THEN qty ELSE 0 END) AS BeginInventory, SUM(CASE WHEN memo='purchases' THEN qty ELSE 0 END) AS Purchases FROM PO_detail WHERE podate < @todate + 1 GROUP BY prodcode)i ON i.prodcode = p.productcodeGROUP BY p.productcode,itemname Now it shows only one result. However the quantity is not correct. Base on this duplicate result:productcode itemname BeginInventory Purchases 4567342200 cream milk 0 186 4567342200 cream milk 0 792 It now becomes this:productcode itemname BeginInventory Purchases 4567342200 cream milk 0 978 It adds the qty of first and second row on the first duplicate results. Which is not supposed to be.Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008 |
|
|
Next Page
|
|
|
|
|