Is it possible to add a where clause in a PARTITION statement

Question

5.00/5 (1 vote)

See more:

Hi below is my current select statement i am using to select my primary based on the Symptom weight.

VB

( CASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages_Backup3.productserial ORDER BY LIstAllStages_Backup3.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primary'

this is how my system works.

the product is received,goes to a stage ,a symptom is selected,the product goes the the next stage,symptom,SymptomWeight is selected etc,There are about 10 stages.

I have created a union all statement to select product,stagecode,symptom,workcompleted and have called this LIstAllStages_Backup3.

The above PARTITION works perfect when the product only goes through the system once as it would select one primary symptom as Y based on the SymptomWeight.

The problem i am having is if the product is received again and the symptomWeight is less than the previous primary symptom on my report it would show the primary symptom to be the highest weighted symptom.

Is it possible for me to add a where clause so that it will select and assign a primary only to
products that are in the range of >DateReceived and <datedispatched>

[EDIT #1]
Structure of the table is as follows

id (int)
Serial	(nvarchar 10)
code    (Nvarchar 4)
passfail (int )
Faultid	int
SubFaultid int
Symptomid int
WorkCompleted int
SymptomWeight	int

Code = Stage code
for all 10 stages

id| serial|code|PassFail|Faultid|SubfaultID|Symptomid|Workcompleted|SymptomWeight
422|40284 |SLLS|  1	|NULL	|NULL	   |	84   |	2012-01-04 |   999

[/END]

[EDIT #2]
The Partion by is ranking the serial based on the Symptom. Which is great if the Serial has only come into the system once. That way i get to select my symptom with the greatest weight

Sample data
Symmptoms

NFF 900
NFD 850
NFC 800
NFS 750
NHF 700
NNF 650

Receiving Table

id|serial | Workcompleted |symptom | SymptomWeight
1|100     | 2013-03-01    |NULL    | 999(Default Value if Symptom is null)
2|100     | 2013-04-11    |NULL    | 999

Dispatch Table

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-12     | NULL   | 999
2| 100    |2013-04-15     | NULL   | 999

Table 1

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-03     | NFF    | 900
3| 100    |2013-04-11     | NFD    | 850

Table 2

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    |2013-03-04     | NFD    | 850
5| 100    |2013-04-12     | NFC    | 800

Union of Table A,B,Receiving and Dispatch VwListallstages

id|serial | Workcompleted |symptom | SymptomWeight
1| 100    | 2013-03-01    |NULL    | 999(Default Value if Symptom is null)
2| 100    | 2013-04-11    |NULL    | 999
1| 100    |2013-03-12     | NULL   | 999
2| 100    |2013-04-15     | NULL   | 999
1| 100    |2013-03-03     | NFF    | 900
3| 100    |2013-04-11     | NFD    | 850
1| 100    |2013-03-04     | NFD    | 850
5| 100    |2013-04-12     | NFC    | 800

Desired Results
This is my current select statement

SQL

select id|serial | Workcompleted |symptom | SymptomWeight
CASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages.serial
ORDER BY LIstAllStages.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primary
from listallstages

id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100    | 2013-03-01    |NULL    | 999          | N
1| 100    |2013-03-12     | NULL   | 999          | N
1| 100    |2013-03-03     | NFF    | 900          | N
1| 100    |2013-03-04     | NFD    | 850          | Y
2|100     | 2013-04-11    |NULL    | 999          | N
2| 100    |2013-04-15     | NULL   | 999          | N
3| 100    |2013-04-11     | NFD    | 850          | N
5| 100    |2013-04-12     | NFC    | 800          | Y

Current Result

id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100    | 2013-03-01    |NULL    | 999          | N
1| 100    |2013-03-12     | NULL   | 999          | N
1| 100    |2013-03-03     | NFF    | 900          | N
1| 100    |2013-03-04     | NFD    | 850          | N
2|100     | 2013-04-11    |NULL    | 999          | N
2| 100    |2013-04-15     | NULL   | 999          | N
3| 100    |2013-04-11     | NFD    | 850          | N
5| 100    |2013-04-12     | NFC    | 800          | Y

[/EDIT]

Posted 16-Apr-13 0:15am

isi19

Updated 16-Apr-13 5:46am

Maciej Los

v3

Add a Solution

Comments

Herman<T>.Instance 16-Apr-13 6:26am

Have you tried putting the Row_Number option in a function?

Maciej Los 16-Apr-13 6:44am

We need more details about database structure, example data, etc.
Sounds like you need CTE[^].

isi19 16-Apr-13 8:52am

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-16T03:20:00

You're economicla with words, with description of your issue... So, i can only guess that you need to replace ROW_NUMBER() with another Ranking Functions (T-SQL)[^], for example DENSE_RANK()[^] function. DENSE_RANK() returns the rank of rows within the partition of a result set, without any gaps in the ranking; the rank of a row is one plus the number of distinct ranks that come before the row in question.

SQL

( CASE WHEN (DENSE_RANK() OVER (PARTITION BY LIstAllStages_Backup3.productserial ORDER BY LIstAllStages_Backup3.SymptomWeight ASC)) = 1 THEN 'Y' ELSE 'N' END 'Primary'

[EDIT #1]
I have done something this:

SQL

DECLARE @Receiving Table (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Receiving (id, serial, Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-01', NULL, 999) --(Default Value if Symptom is null)
INSERT INTO @Receiving (id, serial, Workcompleted, symptom, SymptomWeight)
VALUES(2, 100, '2013-04-11', NULL, 999)

DECLARE @Dispatch TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Dispatch (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-12', NULL, 999)
INSERT INTO @Dispatch(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(2, 100, '2013-04-15' ,NULL, 999)

DECLARE @Table1 TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Table1 (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-03', 'NFF', 900)
INSERT INTO @Table1(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(3, 100, '2013-04-11', 'NFD', 850)

DECLARE @Table2 TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT)
INSERT INTO @Table2 (id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(1, 100, '2013-03-04', 'NFD', 850)
INSERT INTO @Table2(id, serial,  Workcompleted, symptom, SymptomWeight)
VALUES(5, 100, '2013-04-12', 'NFC', 800)

--Union of Table A,B,Receiving and Dispatch VwListallstages

DECLARE @VwListallstages TABLE (id INT, serial INT, Workcompleted DATETIME, symptom VARCHAR(30), SymptomWeight INT, stage INT)

INSERT INTO @VwListallstages (id, serial,  Workcompleted, symptom, SymptomWeight, stage)
SELECT *
FROM (
	SELECT *, 1 AS stage
	FROM @Receiving
	UNION ALL
	SELECT *, 4 AS stage
	FROM @Dispatch
	UNION ALL
	SELECT *, 2 AS stage
	FROM @Table1
	UNION ALL
	SELECT *, 3 AS stage
	FROM @Table2
) AS T

SELECT * 
FROM (
	SELECT t1.*, CASE WHEN t2.[Primary] IS NULL THEN 'N' ELSE 'Y' END AS [Primary]
	FROM @VwListallstages AS t1 LEFT JOIN (
			SELECT serial, MONTH(WorkCompleted) AS MonthOfWC, MAX(WorkCompleted) AS WorkCompleted, MAX(stage) AS [Primary]
			FROM @VwListallstages
			WHERE symptom IS NOT NULL 
			GROUP BY serial, MONTH(WorkCompleted)
			) AS t2 ON t1.serial = t2.serial AND t1.WorkCompleted = t2.WorkCompleted
	) AS T
ORDER BY id, stage

My results:

1   100 2013-03-01 00:00:00.000 NULL    999 1   N
1   100 2013-03-03 00:00:00.000 NFF 900 2   N
1   100 2013-03-04 00:00:00.000 NFD 850 3   Y
1   100 2013-03-12 00:00:00.000 NULL    999 4   N
2   100 2013-04-11 00:00:00.000 NULL    999 1   N
2   100 2013-04-15 00:00:00.000 NULL    999 4   N
3   100 2013-04-11 00:00:00.000 NFD 850 2   N
5   100 2013-04-12 00:00:00.000 NFC 800 3   Y