Case statement returning duplicates

Question

Post reply

Case statement returning duplicates

moeessack123

Right there with Babe

Points: 764
More actions
October 9, 2013 at 4:56 am

#283668

Hi
I have written a query in sql.
When i run it with the case statement it returns duplicate figures
Any reason why it would be doing this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Most probably because the results without the CASE statement will also return duplicate records.

But for us to be able to help you, you should post your query and provide some sample-data.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

moeessack123 Right there with Babe Points: 764 More actions · Answer 2

I have run the query without the case statement and it shows the correct value

here is the sample code

SELECT DISTINCT I.Alternatecode, I.ItemDescr, I.ItemCode, S.Quantity,

S.ActualSellPrice , s1.TradingDate,s.Branchid,c.poscolourcode,s1.docketdate,st.SellPrice1,

case

when

st.markdownprice1 is null

then st.sellprice1

else st.markdownprice1

END AS SP

from saleline S

join Item I on

I.Divisionid = S.Divisionid and

I.DeptGroupid = S.DeptGroupid and

I.DeptSubGroupid = S.DeptSubGroupID and

I.Departmentid = S.Departmentid and

I.ItemId = S.Itemid

join Sale S1 on

S1.Divisionid = S.Divisionid and

S1.Branchid = S.Branchid and

S1.TillID = S.Tillid and

S1.Saleid = S.Saleid

join Stock ST on

st.Divisionid = i.Divisionid and

st.DeptGroupid = i.DeptGroupid and

st.DeptSubGroupid = I.DeptSubGroupid and

st.Departmentid = i.Departmentid and

st.Itemid = i.Itemid

join Colour C on

C.Colourid = s.Colourid

where I.Alternatecode in ('0001') and

s.Quantity >=1 AND

((convert(varchar, s1.TradingDate,111) >= '2013/10/01') and

(CONVERT(varchar, s1.TradingDate,111) <= '2013/10/31'))

ORDER BY SP

Nevyn SSChampion Points: 14210 More actions · Answer 3

First, you could probably be using COALESCE instead of a case statement there.

Second, can you provide some sample data for the tables which demonstrates the problem?

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 4

As mentioned above try using this:

coalesce(st.markdownprice1,st.sellprice1)

That should get you what you want.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

There is absolutely nothing in your code to try to prevent duplicates. A case expression does not do this, it will return whatever values the logic says to return.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

If you mean by "duplicate values" that the same value exists in multiple rows, you need to provide some sample data (CREATE TABLE and INSERT statements) so we can duplicate your problem.

If you mean by "duplicate values" that the same value exists in multiple columns, that is true because you programmed it that way.

In your code (copied below), you select the "st.SellPrice1" as a seperate column, but you also select the same value inside the CASE statement (when st.markdownprice1 has a NULL value). See the bold text in the quoted part below.

SELECT DISTINCT I.Alternatecode, I.ItemDescr, I.ItemCode, S.Quantity,
S.ActualSellPrice , s1.TradingDate,s.Branchid,c.poscolourcode,s1.docketdate,st.SellPrice1
,
case
when
st.markdownprice1 is null
then st.sellprice1
else st.markdownprice1
END AS SP
.....

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

moeessack123 Right there with Babe Points: 764 More actions · Answer 7

My director wants to see a Unit sellingprice for the item '0000'

To do that:

i have to join on primary keys(no foreign keys).

I have to join on the STOCK table and then join on a Stockprice table.

The minute i join on those tables i get duplicate transactions.

Below is the sample code without Joining the Stock and Stockprice Table.

This gives me the correct figures but i can't get a UnitSelling price

SELECT I.Alternatecode, I.ItemDescr, I.ItemCode, S.Quantity,

S.ActualSellPrice , s1.TradingDate,s.Branchid,s1.docketdate

from saleline S

join Item I on

I.Divisionid = S.Divisionid and

I.DeptGroupid = S.DeptGroupid and

I.DeptSubGroupid = S.DeptSubGroupID and

I.Departmentid = S.Departmentid and

I.ItemId = S.Itemid

join Sale S1 on

S1.Divisionid = S.Divisionid and

S1.Branchid = S.Branchid and

S1.TillID = S.Tillid and

S1.Saleid = S.Saleid

where I.Alternatecode in ('0000') and

s.Quantity >=1 AND

((convert(varchar, s1.TradingDate,111) >= '2013/10/01') and

(CONVERT(varchar, s1.TradingDate,111) <= '2013/10/31'))

Nevyn SSChampion Points: 14210 More actions · Answer 8

Hi moeessack123,

The problem with what you have given us is that it leaves us with a lot of detective work to do, and makes it a lot more work to help you. If you scroll up and check the article link in Sean's signature, it will explain the type of information that it is helpful to provide in such a situation.

If you give us definition scripts so we can create temp tables in our own DB's, and sample data which illustrates the problem, and expected vs actual output, solving the problem is way easier. It also lets us give you a tested, completed query back instead of just a description of how to solve the issue.

When just looking at a query, we are forced to jump to conclusions.

In this case, if you are seeing more records when you join to stock and colour than when you don't, the likely answer is that one or both of these tables (more likely stock as colour seems to be linked by primary key) have multiple matching rows to your source query using the information you've given. If the distinct isn't working, it also likely means that either poscolourcode or sellprice1 are different in those "duplicate" records.

To tell you how to fix it, we'd need to know (if that many to one is expected) your rule for how to choose which stock/colour record to link to. If many to one isn't expected, we'd need to know if the join is incorrect, or if there are unexpected data issues.

If you want to analyse the issue by yourself, add st.Divisionid,st.DeptGroupid,st.DeptSubGroupid,I.DeptSubGroupid ,st.Departmentid, and i.Itemid to your query results, find a duplicate record, and then do a separate query of stock looking up the values you got in those results. This will likely get you multiple rows in stock, which will help you decide what to do next.

My GUESS is that you can get multiple stock prices for a stock record (differentiated maybe by either a type code for the type of price, or perhaps by a date range)? And you need rules to decide which of those stock prices to pick, or you need an aggregate function to either take the average, min, or max of those prices.