Unique - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Unique

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 12:38:02

I have a field that I happen to have to use a case statement in where I'm using a MAX Clause... Therefore it's making me group on Work Order Type.

I know on IBM Sequel I can do an Order By # of fields and -1 for the amount on Unique Keys but it doesnt seem as easy on SQL Server.

Here is the Case Statement

(CASE WHEN vwWORDMPF_desc.[W/O Type] IN('DI','NP','SR') AND vwWORDMPF_desc.[W/O Status] = 'CP'
AND vwHOSTPF_desc.[Replace Drop Date]>=vwWORDMPF_desc.[Check In Date] THEN MAX(vwWORDMPF_desc.[Work Order Number]) ELSE NULL END) AS [Work Order Number]

The problem is that they query is making me Group By: vwWORDMPF_desc.[W/O Type]

I cant think of a way right now to get the MAX Order number without using the case statement which leaves me in the next bind. I did try a Max on the whole thing but it didnt help.

Any Help would be greatly appriciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 12:40:18

can you explain with some sample data what output you're trying to achieve?

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 12:40:28

Also to Add. I was reading and I found this

ALTER TABLE vwWOMHIPF_desc ADD CONSTRAINT
uniquekeys UNIQUE NONCLUSTERED
([Account Number],[Work Order Number]) ON [PRIMARY]

The problem is, is that it's not a Table it's saved view. Should I turn it into a table to see if this will then work ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 12:41:33

max(CASE WHEN vwWORDMPF_desc.[W/O Type] IN ('DI','NP','SR') AND vwWORDMPF_desc.[W/O Status] = 'CP'
AND vwHOSTPF_desc.[Replace Drop Date] >= vwWORDMPF_desc.[Check In Date] THEN vwWORDMPF_desc.[Work Order Number] ELSE NULL END) AS [Work Order Number]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 12:43:50

quote:
Originally posted by Peso

max(CASE WHEN vwWORDMPF_desc.[W/O Type] IN ('DI','NP','SR') AND vwWORDMPF_desc.[W/O Status] = 'CP'
AND vwHOSTPF_desc.[Replace Drop Date] >= vwWORDMPF_desc.[Check In Date] THEN vwWORDMPF_desc.[Work Order Number] ELSE NULL END) AS [Work Order Number]

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

i thought of giving this but OP has told in last sentence he tried this but didnt work

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 12:45:10

Sure.

Customer Work Order Type Work Order Status Replace Drop Date Check In Date Work Order Nuumber

1234 DI CP 6/16/2009 6/1/2009 123456
1234 NP CP 6/16/2009 5/1/2009 123546

I can make everything show the same after the max statements except it will show the work order types. Since the case statement is saying that it could be in any of the 3, it could potentially show all 3 for the same customer when in reality I only want to show the last order involved regardless of which one it was. It just happens to force me to put it in the group by.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 12:57:55

Use ROW_NUMBER() instead.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 12:58:59

See http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 12:59:11

last order based on which field?Replace Drop Date or Check In Date ?

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 13:02:48

Check In date :) Going to go look at the blog now.

I either can use the last work order # or last check in date which should lead me to the last work order #

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 13:06:20

select *
from (your old query here
with one column addition
, row_number() over (partition by vwWORDMPF_desc.[W/O Type] order by vwWORDMPF_desc.[Check In Date] desc) as recid
) as d
where recid = 1

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

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 13:17:23

Peso,

I sent you an email. I'm having an issue doing as you stated but I really didnt want to post the entire query on the boards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 13:47:42

email sent back.

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

ZMike
Posting Yak Master

110 Posts

Posted - 2009-06-16 : 14:00:43

Peso,

That seems to work great. Could you explain what that does in simple terms ? I was googling around trying to find the answer.

Thank you so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 14:02:25

Try Microsoft Books Online for references about ROW_NUMBER query.
http://msdn.microsoft.com/en-us/library/ms186734.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources