| Author |
 Topic  |
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
 Posted - 02/11/2013 : 03:56:03
|
then what should be your quantity value?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 04:00:18
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 04:15:20
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 04:25:15
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 04:41:29
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 06:46:44
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 07:19:59
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 07:35:15
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 10:29:13
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/11/2013 : 18:16:15
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 22:56:07
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/12/2013 : 08:49:01
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/12/2013 : 10:08:35
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/12/2013 : 17:44:46
|
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 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/12/2013 : 18:36:27
|
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 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/12/2013 : 19:07:29
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/13/2013 : 04:55:27
|
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/
|
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/13/2013 : 19:34:39
|
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 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/14/2013 : 19:37:17
|
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 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 02/14/2013 : 21:19:47
|
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 |
|
|
|
Topic |
|
Sum Functions returns Twice?..
