| Author |
 Topic  |
|
adbasanta
Posting Yak Master
108 Posts |
 Posted - 02/02/2013 : 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 36
4567342200 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here
@fromdate as DATETIME,
@todate as DATETIME
As
Begin
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 ASC
End
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 02/02/2013 20:00:45
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 02/02/2013 : 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 Optimizer
TG |
 |
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/03/2013 : 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
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 02/03/2013 : 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,qtyYou 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 ? |
Edited by - James K on 02/03/2013 08:06:48 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/03/2013 : 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
India
47173 Posts |
Posted - 02/03/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 02/03/2013 : 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
India
47173 Posts |
Posted - 02/03/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/06/2013 : 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_detail
porefno podate supplierid prodcode itemname qty memo
000001 2/2/2013 11 4567342200 cream milk 23 purchases
000001 2/2/2013 11 4567342201 dairy cream 16 purchases
000002 2/6/2013 11 4567342200 cream milk 12 purchases
000003 2/6/2013 11 4567342201 dairy cream 4 beginning
.....and so on...
Thank you for helping!
tbl_product_transfer_detail
transref 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_detail
salesid transdate prodcode productdesc qty salestype
00000001 2/2/2013 4567342200 cream milk 4 cash
00000001 2/2/2013 4567342201 dairy cream 7 cash
00000002 2/6/2013 4567342200 cream milk 3 credit
00000002 2/6/2013 4567342201 dairy cream 2 credit
.....and so on...
Thank you for helping!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 02/06/2013 06:24:32 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 02/06/2013 : 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
108 Posts |
Posted - 02/08/2013 : 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here
@fromdate as DATETIME,
@todate as DATETIME
As
Begin
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 ASC
End
Now I got this result
productcode BeginInventory itemname Purchases TransfersIn TransfersOut SoldItem
4567342200 0 cream milk 1400 0 0 493
4567342200 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 |
Edited by - adbasanta on 02/08/2013 20:42:52 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/08/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/08/2013 : 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 |
Edited by - adbasanta on 02/08/2013 23:27:19 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/08/2013 : 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 salestype
00000001 2/2/2013 4567342200 cream milk 4 cash
00000001 2/2/2013 4567342201 dairy cream 7 cash
00000002 2/6/2013 4567342200 cream milk 3 credit
00000002 2/6/2013 4567342201 dairy cream 2 credit
00000001 2/2/2013 4567342200 cream milk 3 credit
00000001 2/2/2013 4567342201 dairy cream 1 credit
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 02/08/2013 23:39:12 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/08/2013 : 23:45:04
|
Nope still if you're grouping on prodcode you should get only one row per prodcode
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/09/2013 : 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
India
47173 Posts |
Posted - 02/09/2013 : 01:22:29
|
i think issue is with tbl_product_list table containing duplicates
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/09/2013 : 01:31:33
|
thank you visakh16!
I'll check it out..
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 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_DB
go
select productcode, count(productcode) as NumOccurences
from tbl_product_list
group by productcode
having (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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here
@todate as DATETIME
As
Begin
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.productcode
GROUP 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 memo
000001 2/2/2013 11 4567342200 cream milk 23 purchases
000001 2/2/2013 11 4567342201 dairy cream 16 purchases
000002 2/6/2013 11 4567342200 cream milk 12 purchases
000003 2/6/2013 11 4567342201 dairy cream 4 beginning
000004 2/8/2013 11 4567342200 dairy cream 2 void
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008 |
Edited by - adbasanta on 02/11/2013 03:25:46 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 03:24:39
|
the issue is your group by. try this
USE [POS_DB]
GO
/****** Object: StoredProcedure [dbo].[Show_all_items_DateView] Script Date: 01/24/2013 23:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here
@todate as DATETIME
As
Begin
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.productcode
GROUP BY p.productcode,itemname,BeginInventory,Purchases
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Show_all_items_DateView]
-- Add the parameters for the stored procedure here
@todate as DATETIME
As
Begin
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.productcode
GROUP 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 |
Edited by - adbasanta on 02/11/2013 03:57:04 |
|
|
|
Topic |
|
|
|
Sum Functions returns Twice?..
