how to resolve duplicate data problem

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to resolve duplicate data problem

Author

Topic

simi28
Starting Member

27 Posts

Posted - 2007-04-10 : 04:54:01

Hi All

i want the output like this
date poid sales ref unit cost ordered received sold shrinkage sale type postage delivery payment type
23/3/2007 12345 test - - 1 - tel 20 shipping credit card

for that i have written two sql queries
qry1 =
///

"SELECT im_products_stock_logs.orderid,im_products_stock_logs.log_type,
im_products_stock_logs.log_date, im_products_stock_logs.poid,
products.lead_time,products.cost_price,orders.sales_type,
isnull(orders.totalamt,0) as totalamt, isnull(orders.shippingamt,0)
as shippingamt, orders.delivery_method, orders.payment_method
FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN Products on
im_products_stock_logs.productid= products.productid WHERE
(im_products_stock_logs.productid = 790) and poid=14 order by log_date desc "
///
qry2=
///
SELECT im_products_stock_logs.log_type, SUM(im_products_stock_logs.qty)
AS qty, im_products_stock_logs.poid, DAY(im_products_stock_logs.log_date)
AS Expr2, YEAR(im_products_stock_logs.log_date) AS Expr3,
MONTH(im_products_stock_logs.log_date) AS Expr4,
{ fn MINUTE(im_products_stock_logs.log_date) } AS Expr5,
{ fn HOUR(im_products_stock_logs.log_date) }
AS Expr6 FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN
products ON im_products_stock_logs.productid = products.productid WHERE
( im_products_stock_logs.productid = 790 and im_products_stock_logs.colorid = 2 )
GROUP BY im_products_stock_logs.log_type, im_products_stock_logs.poid,
DAY(im_products_stock_logs.log_date), YEAR(im_products_stock_logs.log_date),
MONTH(im_products_stock_logs.log_date), { fn HOUR(im_products_stock_logs.log_date)
}, { fn MINUTE(im_products_stock_logs.log_date) }
ORDER BY YEAR(im_products_stock_logs.log_date) DESC,
MONTH(im_products_stock_logs.log_date) DESC,
DAY(im_products_stock_logs.log_date) DESC,
{ fn HOUR(im_products_stock_logs.log_date) }
DESC, { fn MINUTE(im_products_stock_logs.log_date) } DESC
///

the table use in are
im_products_stock_logs-orderid,log_type,log_date,poid,
products-lead_time,cost_price
orders-sales_type,delivery_method,payment_method

the sample data is

orders
orderid sales_type delivery_method payment_method
1025 tel v shipping 1

products
productid lead_time cost_price
13 4 45.00

im_products_stock_logs
logid productid orderid log_type log_date poid
40 13 1025 sold 23/3/2007 8

I have written the query for the same is

in my query i m getting the duplicate values .
How can I solve it please help me.
thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 08:10:56

While posting your code:
1. Use proper aliases to make code more readable
2. Use basic indentation principles
3. Use [Code] tags to retain all the formatting you done on the code

Also, is this T-SQL code? I don't think curly braces are allowed in T-SQL.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 08:15:58

Yes, you can Harsh. You force the compiler to use the Data Library for connection instead of T-SQL.

Try this in SSMS or QA.

select { fn MINUTE(current_timestamp) }

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 08:18:06

Simi28, you should not mix different libraries like this.
Use T-SQL function DATEPART instead of { fn } style.

{ fn MINUTE(im_products_stock_logs.log_date) } AS Expr5,
DATEPART(MINUTE, im_products_stock_logs.log_date) AS Expr5,

and

{ fn HOUR(im_products_stock_logs.log_date) }
DATEPART(HOUR, im_products_stock_logs.log_date)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 08:19:19

Aha! I was not aware of that.

Thanks for that information, Peter.

This is what I like about tech-forums...everyday we learn something new (which we think almost impossible earlier).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 08:40:37

http://support.microsoft.com/kb/147731

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 08:45:59

Wonderful piece of information. A million new cells added to my brain.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 08:50:35

I am looking for a complete ODBC function reference, but no luck this far.
Try this http://msdn.microsoft.com/library/en-us/odbc/htm/odbcscalar_functions.asp?frame=true

SELECT { fn CURTIME() },
{ fn CURDATE() }

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 09:01:39

Seems like easier migration path for those who come from foxpro background. Kinda similar to using VB functions inside Access. But of course this has performance penalty added to it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

simi28
Starting Member

27 Posts

Posted - 2007-04-10 : 09:36:54

can you please tell me what is the problem in query?
if it is wrong then please suggest me the right query for the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 09:41:54

You are joining multiple tables.
In one of these JOINs, you have a 1-to-many relationship, thus creating "duplicates".

Peter Larsson
Helsingborg, Sweden

simi28
Starting Member

27 Posts

Posted - 2007-04-10 : 09:46:18

can you suggest me the right query acually i m quite week in queries

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 09:49:49

The problems seems not to be the query (with the exception of the ODBC functions).
It seems to be the data. And since I have no information about your businees rules, all I can give is guesses.

And you still haven't posted what the duplicates are.

Peter Larsson
Helsingborg, Sweden

simi28
Starting Member

27 Posts

Posted - 2007-04-10 : 09:54:42

All data are displaying duplicates values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 10:19:51

Even across the complete record?
Put in a DISTINCT after the SELECT keyword, but before the first column name to get data for.

Peter Larsson
Helsingborg, Sweden

simi28
Starting Member

27 Posts

Posted - 2007-04-11 : 04:03:24

my problem has not solved even by using distinct keyword
please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 04:06:26

Please post your current output and tell us why you think it is wrong.
Also post some sample data and what you would like the output to look like.

Peter Larsson
Helsingborg, Sweden

simi28
Starting Member

27 Posts

Posted - 2007-04-11 : 04:11:58

can I attach the screenshot of then output?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 04:19:25

Almost. You can upload the picture to a site from where you can link to picture here.

Peter Larsson
Helsingborg, Sweden

simi28
Starting Member

27 Posts

Posted - 2007-04-11 : 04:35:17

there is some problem in uploading the file below is the out put what i m getting right now.
DATE PO ID/OID product Unit Cost Ordered Received Sold Shrinkage Sales Type Sale Price Postage Delivery Payment Type
7-Apr-2007 13142 Testing Product 3 Tel £ 2,100.00 £ 0.00 LV Shipping Cash
7-Apr-2007 56 $ 250.00 6 6 £ 0.00 £ 0.00 cash
7-Apr-2007 13142 Testing Product 1 Tel £ 2,100.00 £ 0.00 LV Shipping Cash
7-Apr-2007 13142 Testing Product 1 Tel £ 2,100.00 £ 0.00 LV Shipping Cash
7-Apr-2007 13142 Testing Product 1 Tel £ 2,100.00 £ 0.00 LV Shipping Cash
7-Apr-2007 56 $ 250.00 6 6 £ 0.00 £ 0.00 cash
i have not copied the complete data but u can see the the above output is display duplicate data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 04:39:20

If you do this, you WILL NOT get duplicate values. Unless you are holding some informatin back from us.

qry1 =
///
"SELECT DISTINCT im_products_stock_logs.orderid,im_products_stock_logs.log_type,
im_products_stock_logs.log_date, im_products_stock_logs.poid,
products.lead_time,products.cost_price,orders.sales_type,
isnull(orders.totalamt,0) as totalamt, isnull(orders.shippingamt,0)
as shippingamt, orders.delivery_method, orders.payment_method
FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN Products on
im_products_stock_logs.productid= products.productid WHERE
(im_products_stock_logs.productid = 790) and poid=14 order by log_date desc "
///

Do you have NULLs? Do you have empty space?
Also in query 2, you are grouping and selecting down to the minute, but I can't see them in the output you posted.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources