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.
| Author |
Topic |
|
rmrper99
Starting Member
29 Posts |
 Posted - 2009-03-17 : 15:19:02
|
| I have written a view using the following statement. For each project/customer combination, there can be more than one DWE or DE. So, if there are two DWEs for example, I want the data to come from the one with the largest number. So, if there is one D000004 and D000006 then I want the data to come from D000006. In the statement below, it pulls the correct D000006. However, if user5 is larger on D000004 then it pulls that amount. I understand why it works that way but I am not sure how to make it do what I want it to do. SELECT a.PROJECTID, a.CUSTID, MAX(CASE a.SOTYPEID WHEN 'DWE' THEN a.ORDNBR ELSE NULL END) AS DWE,MAX(CASE a.SOTYPEID WHEN 'DWE' THEN a.USER5 ELSE NULL END) AS DWE_EST_MILES,MAX(CASE a.SOTYPEID WHEN 'DWE' THEN a.USER6 ELSE NULL END) AS DWE_EST_WEIGHT,MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.ORDNBR ELSE NULL END) AS DE,MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.ORDDATE ELSE NULL END) AS DE_ORDER_PLACED_DATE,MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.USER9 ELSE NULL END) AS DE_SURVEY_RECEIVED_DATE,MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.USER5 ELSE NULL END) AS DE_EST_MILES,MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.USER6 ELSE NULL END) AS DE_EST_WEIGHT,FROM TABLE |
|
|
|
|
|
|
|
View Help using Max and Case