SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sum Functions returns Twice?..
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  03:56:03  Show Profile  Reply with Quote
then what should be your quantity value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/11/2013 :  04:00:18  Show Profile  Reply with Quote
the quantity value should only be 792. As the result of the second duplicate row below(in red color):

productcode        itemname      BeginInventory    Purchases 
4567342200           cream milk          0             186 
4567342200           cream milk          0             792

At this point, it has 978(the qty of first row plus the qty of second row), which is NOT correct.

productcode        itemname      BeginInventory    Purchases 
4567342200           cream milk          0             978

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/11/2013 04:05:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  04:15:20  Show Profile  Reply with Quote
so whats the field which indicates which rows value to be retrieved? is it always value on latest record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/11/2013 :  04:25:15  Show Profile  Reply with Quote
Thank you visakh16!

It doesn't matter about the ordering of the data as long the sum of each product code quantity is correct. Base on this result,

productcode        itemname      BeginInventory    Purchases 
4567342200           cream milk          0             186 
4567342200           cream milk          0             792

Ive notice that the result if you add the quantity of the first row and second row is quantity of the last query result(as can be seen below). But it should only be 792 as Ive check it on the PO_detail table by running this query.

select sum(qty) as totalqty from PO_detail where productcode='4567342200'

And this is the result:

totalqty
792

But as we execute on the last query it gives me 978 which is not correct. As can be seen below.

productcode        itemname      BeginInventory    Purchases
4567342200           cream milk          0             978

IT SHOULD BE:

productcode        itemname      BeginInventory    Purchases
4567342200           cream milk          0             792


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/11/2013 04:28:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  04:41:29  Show Profile  Reply with Quote
try this too


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 (SELECT DISTINCT productcode,itemname 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




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/11/2013 :  06:46:44  Show Profile  Reply with Quote
Thank you visakh16!

Ive made changes as you have said. But I still get duplicate results. Can we convert this to CTE maybe to have a closer result? or maybe Im missing something in the query?

productcode        itemname      BeginInventory    Purchases 
4567342200         cream milk          0             186 
4567342200         cream milk          0             792


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  07:19:59  Show Profile  Reply with Quote
i'm sure you're not telling us the full scenario.
The above code wont give you duplicates for productcode unless multiple itemname values exists for same productcode value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/11/2013 :  07:35:15  Show Profile  Reply with Quote
thank you visakh16!

I'm not sure which table you are referring for duplicate item name of the same product code. But as to the extent of solving this problem, I've checked all the tables with the query below and Ive also listed below tables that have duplicates in product code with each item name:

use POS_DB
go
select productcode, count(productcode) as NumOccurences
from tbl_product_list
group by productcode
having (COUNT(productcode) > 1) 


The following tables have duplicates in product code with each item name and I believe this should be normal because all transactions are saved in this tables. The only difference is their transaction ID and transaction type.Red colors below have duplicates:

tbl_product_list
PO_detail
tbl_product_transfer_detail
tbl_pos_sales_detail


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/11/2013 07:36:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  10:29:13  Show Profile  Reply with Quote
not true as per this definition you will not get any duplicates for product code if you use earlier posted query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/11/2013 :  18:16:15  Show Profile  Reply with Quote
Thank you visakh16!

I do not know what is really the problem in my table. Any data you want just tell me and I will provide it just to solve this problem. Here is my table diagram:


Additional fields of tbl_product_list:

IncomeAccntID
AssetAccntID
itemtype
service_other_charge_AccntID
costprice
service_other_charge_amount_percent

This fields of tbl_product_list have no used at all(just dont mind this):

begininvent
purchased
transferin
transferout
sold
onhand

And here what I am referring as duplicate results:


Thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/11/2013 18:37:53
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  22:56:07  Show Profile  Reply with Quote
can you run this and post back the result?


SELECT productcode
FROM tbl_product_list 
GROUP BY productcode
HAVING COUNT(DISTINCT itemname) >1 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/12/2013 :  08:49:01  Show Profile  Reply with Quote
thank you visakh16!

Ive run the script you've given and I got zero result. screen shot below:


thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/12/2013 08:50:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/12/2013 :  10:08:35  Show Profile  Reply with Quote
then there's no chance that my suggestion at 02/11/2013 : 04:41:29 will give you duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/12/2013 :  17:44:46  Show Profile  Reply with Quote
thank you visakh16!

I believed the query @ 2/11/2012:04:41:29 is correct. But as you can see the result @ 2/11/2013 10:29:13, it still gives me duplicates. what else should i check too?

thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/12/2013 :  18:36:27  Show Profile  Reply with Quote
I suspect this left join is giving me duplicate results. Because when I run this query in PO_detail, it gives me exactly the correct sum of quantity:

SELECT SUM(qty) AS totalqty FROM PO_detail
WHERE prodcode='748485200026'


Maybe a query to not use left join or joins can solve this?

Ive also converted this to CTE, but still I have duplicate results:

;with Inventory_Checking as
(
	SELECT purchases
	FROM 	
	(
		SELECT purchases=0
		from	tbl_product_list p

		union all
		
		select	SUM(qty)
		from	PO_detail pd
		GROUP BY prodcode
	)t
)
select	*
from	Inventory_Checking
ORDER BY purchases ASC 
END


thank you!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/12/2013 18:39:58
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/12/2013 :  19:07:29  Show Profile  Reply with Quote
Maybe the problem also is in the tbl_product_list because when I join this table to PO_detail, it gives duplicate. But when I supplied productcode(query below) without joining tbl_product_list, it gives me the correct quantity:

SELECT SUM(qty) AS totalqty FROM PO_detail
WHERE prodcode='748485200026'


But what is really the problem in my tbl_product_list?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/13/2013 :  04:55:27  Show Profile  Reply with Quote
I cant guess more. As per your answers so far there's nothing which makes me feel like there are duplicates in tbl_product_list
So either you're not using query in given way or there's some other part which you've missed specifying us!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/13/2013 :  19:34:39  Show Profile  Reply with Quote
thank you visakh16!

here is the final data of tbl_product_list and PO_detail. I will also post later its table definitions.

http://www.sendspace.com/file/0o8be9. Files in winrar format.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/14/2013 :  19:37:17  Show Profile  Reply with Quote
And here is the table definitions:

tbl_product_list

USE [POS_DB]
GO

/****** Object:  Table [dbo].[tbl_product_list]    Script Date: 01/29/2013 08:12:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_product_list](
	[productid] [bigint] IDENTITY(1,1) NOT NULL,
	[productcode] [bigint] NOT NULL,
	[itemname] [varchar](50) NULL,
	[description] [varchar](max) NULL,
	[category] [varchar](50) NULL,
	[packtype] [varchar](50) NULL,
	[supplierprice] [money] NULL,
	[srp] [money] NULL,
	[begininvent] [bigint] NULL,
	[purchased] [bigint] NULL,
	[transferin] [bigint] NULL,
	[transferout] [bigint] NULL,
	[sold] [bigint] NULL,
	[onhand] [bigint] NULL,
	[datetimeentered] [datetime] NULL,
	[datetimemodified] [datetime] NULL,
	[enteredby] [varchar](50) NULL,
	[modifiedby] [varchar](50) NULL,
	[status] [bit] NULL,
	[supplierid] [varchar](50) NULL,
	[vatable] [bit] NULL,
	[discount] [bigint] NULL,
	[reorderpoint] [bigint] NULL,
	[editcount] [bigint] NULL,
	[unitid] [varchar](50) NULL,
	[itemlocation] [varchar](50) NULL,
	[adjustment] [bigint] NULL,
	[adjustment_memo] [varchar](50) NULL,
	[expdate] [varchar](20) NULL,
	[discount_memo] [varchar](50) NULL,
	[COGSAccntID] [varchar](50) NULL,
	[IncomeAccntID] [varchar](50) NULL,
	[AssetAccntID] [varchar](50) NULL,
	[itemtype] [varchar](50) NULL,
	[service_other_charge_amount_percent] [varchar](50) NULL,
	[service_other_charge_AccntID] [varchar](50) NULL,
	[costprice] [money] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_category]  DEFAULT ((0)) FOR [category]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_packtype]  DEFAULT ('Not Set') FOR [packtype]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_supplierprice]  DEFAULT ((0)) FOR [supplierprice]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_srp]  DEFAULT ((0)) FOR [srp]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_begininvent]  DEFAULT ((0)) FOR [begininvent]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_purchased]  DEFAULT ((0)) FOR [purchased]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_transferin]  DEFAULT ((0)) FOR [transferin]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_transferout]  DEFAULT ((0)) FOR [transferout]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_sold]  DEFAULT ((0)) FOR [sold]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_onhand]  DEFAULT ((0)) FOR [onhand]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_datetimeentered]  DEFAULT ((0)) FOR [datetimeentered]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_enteredby]  DEFAULT ('Not Set') FOR [enteredby]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_modifiedby]  DEFAULT ('Not Set') FOR [modifiedby]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_status]  DEFAULT ((1)) FOR [status]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_supplierid]  DEFAULT ((0)) FOR [supplierid]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_vatable]  DEFAULT ((0)) FOR [vatable]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_discount]  DEFAULT ((0)) FOR [discount]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_reorderpoint]  DEFAULT ((0)) FOR [reorderpoint]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_editcount]  DEFAULT ((0)) FOR [editcount]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_unitid]  DEFAULT ((0)) FOR [unitid]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_itemlocation]  DEFAULT ('Not Set') FOR [itemlocation]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_adjustment]  DEFAULT ((0)) FOR [adjustment]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_adjustment_memo]  DEFAULT ('Not Set') FOR [adjustment_memo]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_expdate]  DEFAULT ('Not Set') FOR [expdate]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_discount_memo]  DEFAULT ('Not Set') FOR [discount_memo]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_COGSAccntID]  DEFAULT ((0)) FOR [COGSAccntID]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_IncomeAccntID]  DEFAULT ((0)) FOR [IncomeAccntID]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_AssetAccntID]  DEFAULT ((0)) FOR [AssetAccntID]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_itemtype]  DEFAULT ('Not Set') FOR [itemtype]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_service_other_charge_amount_percent]  DEFAULT ((0)) FOR [service_other_charge_amount_percent]
GO

ALTER TABLE [dbo].[tbl_product_list] ADD  CONSTRAINT [DF_tbl_product_list_service_other_charge_AccntID]  DEFAULT ((0)) FOR [service_other_charge_AccntID]
GO

PO_detail

USE [POS_DB]
GO

/****** Object:  Table [dbo].[PO_detail]    Script Date: 01/29/2013 08:13:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PO_detail](
	[porefno] [bigint] NULL,
	[podate] [datetime] NULL,
	[supplierid] [bigint] NULL,
	[supplier] [varchar](50) NULL,
	[prodcode] [varchar](50) NOT NULL,
	[itemname] [varchar](50) NULL,
	[proddesc] [varchar](max) NULL,
	[qty] [float] NULL,
	[unit] [bigint] NULL,
	[packtype] [varchar](50) NULL,
	[price] [money] NULL,
	[srp] [money] NULL,
	[total] [money] NULL,
	[addedby] [varchar](50) NULL,
	[addedas] [varchar](50) NOT NULL,
	[categoryid] [varchar](50) NULL,
	[editmode] [int] NULL,
	[discount] [money] NULL,
	[vendorprice] [money] NULL,
	[vatprice] [money] NULL,
	[discprice] [money] NULL,
	[memo] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_qty]  DEFAULT ((0)) FOR [qty]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_price]  DEFAULT ((0)) FOR [price]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_srp]  DEFAULT ((0)) FOR [srp]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_total]  DEFAULT ((0)) FOR [total]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_categoryid]  DEFAULT ((0)) FOR [categoryid]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_editmode]  DEFAULT ((0)) FOR [editmode]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_discount]  DEFAULT ((0)) FOR [discount]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_vendorprice]  DEFAULT ((0)) FOR [vendorprice]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_vatprice]  DEFAULT ((0)) FOR [vatprice]
GO

ALTER TABLE [dbo].[PO_detail] ADD  CONSTRAINT [DF_PO_detail_discprice]  DEFAULT ((0)) FOR [discprice]
GO


thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/14/2013 21:21:15
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 02/14/2013 :  21:19:47  Show Profile  Reply with Quote
Now I know what the problem is. It seems that the datatype for tbl_product_list is bigint and the datatype for PO_detail prodcode is set to varchar. This gives insufficient result on the quantity. What should be the correct datatype for this values? How can I convert the data in each table without lossing a single part of it?

if this is the productcode='748485200026' in tbl_product_list as datatype bigint then,

In PO_detail, some items prodcode becomes prodcode='0748485200026' as datatype varchar. Notice that it add 0 on the start of the product code.

As Ive look on the Barcode of the item, it has no zero at the start.

Do I need to remove all items in PO_detail that have zeros at the start?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 02/14/2013 22:10:50
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000