Calculating fields on duplicate values...

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

Calculating fields on duplicate values...

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-11-26 : 10:47:15

Hello,

First, I am very new to SQL so I will try to explain as best as I can.

I have this query:

SELECT DISTINCT SALESLINE.SALESID, SALESLINE.LINENUM, SALESLINE.ITEMID, INVENTDIM.INVENTLOCATIONID, INVENTSUM.AVAILPHYSICAL, SALESLINE.REMAINSALESPHYSICAL,CASE WHEN SALESLINE.REMAINSALESPHYSICAL > INVENTSUM.AVAILPHYSICAL THEN 'CAN BE DEL' ELSE 'CANNOT SHIP'END AS DL, SALESLINE.CREATEDDATE

FROM SALESLINE INNER JOIN INVENTSUM ON SALESLINE.ITEMID = INVENTSUM.ITEMID AND SALESLINE.DATAAREAID = INVENTSUM.DATAAREAID INNER JOIN INVENTDIM ON INVENTSUM.INVENTDIMID = INVENTDIM.INVENTDIMID

WHERE SALESLINE.REMAINSALESPHYSICAL > 0 and SALESLINE.dataareaid = 'xxx' AND INVENTDIM.INVENTLOCATIONID IN ('XXX-ILL', 'XXX-CAL') AND INVENTSUM.AVAILPHYSICAL > 0

ORDER BY DL, SALESLINE.CREATEDDATE, SALESLINE.ITEMID ASC

This produces results like this ( I am only showing important fields):

ITEMID AVAILPHYSICAL REMAINSALESPHYSICAL
------ ------------- --------------------
05071 229 600
05071 229 600
05071 229 600
07784 220 250
07784 220 250
29486 5 21
95141 84 88
95141 4 88
68522 20 25

Here is my question: I need to find a way to look at all DUPLICATE ITEMID's and add up all AVAILPHYSICAL associated with the same ITEMID's, if the sum of those is > then REMAINSALESPHYSICAL then I want to show only ITEMID's that are <= REMAINSALESPHYSICAL.

So with sample data above I want to get this:

ITEMID AVAILPHYSICAL REMAINSALESPHYSICAL
------ ------------- --------------------
05071 229 600
05071 229 600
05071 229 600
07784 220 250
07784 220 250
29486 5 21
95141 84 88
95141 4 88
68522 20 25

The red rows need to be removed because the sum of AVAILPHYSICAL exceeds REMAINSALESPHYSICAL for the same ITEMID's. 05071's AVAILPHYSICAL > REMAINSALESPHYSICAL when you hit the third entry.

How can I accomplish this? I was thinking using this query as a tempview and then writting a UDF to call on it but I am lost as far as how to write the logic for this filter!

Any help is greatly appreciated.

Thank you in advance.

Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 10:53:39

[code]SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL
FROM (
SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL,
ROW_NUMBER() OVER (PARTITION BY ITEMID, AVAILPHYSICAL, REMAINSALESPHYSICAL ORDER BYITEMID) AS recID
FROM Table1
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-11-26 : 11:00:34

Thank you PESO for your quick reply.

I am assuming table1 is my query? How are you adding AVAILPHYSICAL and comparing it to REMAINSALESPHYSICAL?

Also, how are you nesting your query with mine?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 11:11:26

You should use the above query as a source for a derived table in your original query, instead of joining "table" directly.

E 12°55'05.63"
N 56°04'39.26"

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-11-26 : 11:23:59

Peso, not quite sure what you are referring to.

I tried this:

SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL
FROM (
SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL,
ROW_NUMBER() OVER (PARTITION BY ITEMID, AVAILPHYSICAL, REMAINSALESPHYSICAL ORDER BY ITEMID) AS recID
FROM (SELECT DISTINCT SALESLINE.SALESID, SALESLINE.LINENUM, SALESLINE.ITEMID, INVENTDIM.INVENTLOCATIONID,
INVENTSUM.AVAILPHYSICAL, SALESLINE.REMAINSALESPHYSICAL,
CASE WHEN SALESLINE.REMAINSALESPHYSICAL > INVENTSUM.AVAILPHYSICAL THEN 'CAN BE DEL'
ELSE 'CANNOT SHIP'END AS DL, SALESLINE.CREATEDDATE

FROM SALESLINE INNER JOIN
INVENTSUM ON SALESLINE.ITEMID = INVENTSUM.ITEMID
AND SALESLINE.DATAAREAID = INVENTSUM.DATAAREAID INNER JOIN
INVENTDIM ON INVENTSUM.INVENTDIMID = INVENTDIM.INVENTDIMID
WHERE SALESLINE.REMAINSALESPHYSICAL > 0 and SALESLINE.dataareaid = 'xxx'
AND INVENTDIM.INVENTLOCATIONID IN ('xxx-ILL', 'xxx-CAL')
AND INVENTSUM.AVAILPHYSICAL > 0
ORDER BY DL, SALESLINE.CREATEDDATE, SALESLINE.ITEMID ASC
) AS d
WHERE recID = 1

but I got this msg:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So I tried this:

SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL
FROM (
SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL,
ROW_NUMBER() OVER (PARTITION BY ITEMID, AVAILPHYSICAL, REMAINSALESPHYSICAL ORDER BY ITEMID) AS recID
FROM (SELECT DISTINCT SALESLINE.SALESID, SALESLINE.LINENUM, SALESLINE.ITEMID, INVENTDIM.INVENTLOCATIONID,
INVENTSUM.AVAILPHYSICAL, SALESLINE.REMAINSALESPHYSICAL,
CASE WHEN SALESLINE.REMAINSALESPHYSICAL > INVENTSUM.AVAILPHYSICAL THEN 'CAN BE DEL'
ELSE 'CANNOT SHIP'END AS DL, SALESLINE.CREATEDDATE

FROM SALESLINE INNER JOIN
INVENTSUM ON SALESLINE.ITEMID = INVENTSUM.ITEMID
AND SALESLINE.DATAAREAID = INVENTSUM.DATAAREAID INNER JOIN
INVENTDIM ON INVENTSUM.INVENTDIMID = INVENTDIM.INVENTDIMID
WHERE SALESLINE.REMAINSALESPHYSICAL > 0 and SALESLINE.dataareaid = 'xxx'
AND INVENTDIM.INVENTLOCATIONID IN ('xxx-ILL', 'xxx-CAL')
AND INVENTSUM.AVAILPHYSICAL > 0
--ORDER BY DL, SALESLINE.CREATEDDATE, SALESLINE.ITEMID ASC
) AS d
WHERE recID = 1

but get this msg:
Incorrect syntax near '1'.

??? : Again, new to SQL, plz advise! Thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 11:56:46

Try this. seems like you missed a paranthesis

SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL
FROM (
SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL,
ROW_NUMBER() OVER (PARTITION BY ITEMID, AVAILPHYSICAL, REMAINSALESPHYSICAL ORDER BY ITEMID) AS recID
FROM (SELECT DISTINCT SALESLINE.SALESID, SALESLINE.LINENUM, SALESLINE.ITEMID, INVENTDIM.INVENTLOCATIONID,
INVENTSUM.AVAILPHYSICAL, SALESLINE.REMAINSALESPHYSICAL,
CASE WHEN SALESLINE.REMAINSALESPHYSICAL > INVENTSUM.AVAILPHYSICAL THEN 'CAN BE DEL'
ELSE 'CANNOT SHIP'END AS DL, SALESLINE.CREATEDDATE

FROM SALESLINE INNER JOIN
INVENTSUM ON SALESLINE.ITEMID = INVENTSUM.ITEMID
AND SALESLINE.DATAAREAID = INVENTSUM.DATAAREAID INNER JOIN
INVENTDIM ON INVENTSUM.INVENTDIMID = INVENTDIM.INVENTDIMID
WHERE SALESLINE.REMAINSALESPHYSICAL > 0 and SALESLINE.dataareaid = 'xxx'
AND INVENTDIM.INVENTLOCATIONID IN ('xxx-ILL', 'xxx-CAL')
AND INVENTSUM.AVAILPHYSICAL > 0
--ORDER BY DL, SALESLINE.CREATEDDATE, SALESLINE.ITEMID ASC
) AS d
)AS r
WHERE recID = 1

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-11-26 : 12:55:40

Thank you Visakh16,

This ran without error but the results are not what I want.

When I run this:

SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL,
SALESID, DL
FROM (
SELECT ITEMID,
AVAILPHYSICAL,
REMAINSALESPHYSICAL, SALESID, DL,
ROW_NUMBER() OVER (PARTITION BY ITEMID, AVAILPHYSICAL, REMAINSALESPHYSICAL, SALESID, DL ORDER BY ITEMID) AS recID
FROM (SELECT DISTINCT SALESLINE.SALESID, SALESLINE.LINENUM, SALESLINE.ITEMID, INVENTDIM.INVENTLOCATIONID,
INVENTSUM.AVAILPHYSICAL, SALESLINE.REMAINSALESPHYSICAL,
CASE WHEN SALESLINE.REMAINSALESPHYSICAL < INVENTSUM.AVAILPHYSICAL THEN 'CAN BE DEL'
ELSE 'CANNOT SHIP'END AS DL, SALESLINE.CREATEDDATE

FROM SALESLINE INNER JOIN
INVENTSUM ON SALESLINE.ITEMID = INVENTSUM.ITEMID
AND SALESLINE.DATAAREAID = INVENTSUM.DATAAREAID INNER JOIN
INVENTDIM ON INVENTSUM.INVENTDIMID = INVENTDIM.INVENTDIMID
WHERE SALESLINE.REMAINSALESPHYSICAL > 0 and SALESLINE.dataareaid = 'osg'
AND INVENTDIM.INVENTLOCATIONID IN ('OSG-ILL', 'OSG-CAL')
AND INVENTSUM.AVAILPHYSICAL > 0
--ORDER BY DL, SALESLINE.CREATEDDATE, SALESLINE.ITEMID ASC
) AS d
)AS r
WHERE recID = 1 ORDER BY DL, itemid

I get multiple AVAILPHYSICAL for the same ITEMID's. This should not be.

All item's have only one AVAILPHYSICAL integer associated with it.

Here's a short list of results from the query above:

ITEMID AVAILPHYSICAL REMAINSALESPHYSICAL
0135601 114 10
0135601 34 10
0140401 99 30
0140501 52 9
0140501 52 15

item 0140501 available physical is 52 but notice the red AVAILPHYSICAL for item 0135601 it should be 114 in both rows (not 34).

!!! Again, thx for help. Been great thus far!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 04:04:34

So you actually need maximum value of AVAILPHYSICAL to be returned with all?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources