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
 Need Max result, remove duplicates

Author  Topic 

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-03 : 04:47:21
I have the following query but I would like only see the last status for the workorder which is based off the statusopened date but it is showing all the different status of the workorder; below is the query

Select
e.el_id,
case
when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('wmlaff','wmla99') ) ) ) then 'ABCT w/Motor'

when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('wmakff','wmak99') ) ) ) then 'IBCT w/Motor'

when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('WMGAAA','WMGKAA','WMGLAA','WMKAAA','WMPAAA','WMPBAA') ) ) ) then 'SRU'

when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('W6RRAA') ) ) ) then 'TSS'

when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('WMNXFF') ) ) ) then 'Fires'

when e.el_id in (
select e.el_id from equipment e join unit_authorization ua on e.authorization_id = ua.authorization_id
where ua.uic in (select uic from unit where battalion_uic in (select battalion_uic from battalion_brigade
where brigade_uic in ('WMLZFF') ) ) ) then 'SBDE'

when e.authorization_id is null and e.plan_id in (530,531,533,508,509) then 'In '||e.plan_id||' Plan No Auth ID?'

end as 'Set',a.Nomenclature,a.model_no,a.lin,e.serial_no,e.plan_id,e.owner_uic,l.location,e.LAST_SCAN_DT, f.aps, w.FMDS_WON, w.apc, w.fss, w.Condition_Cd,(Now() - w.opendate) as 'WOAGE', w.Description, w.Remarks,

MAX(ws.STATUS) as 'WOSTATUS', MAX(ws.StatusOpened) AS 'StatusOpened',(NOW() - ws.StatusOpened) AS 'DAYSINSTATUS',
case
when e.niin in (select niin from RS_NIINs) then 'Y' else 'N' end as 'RollStock',(select mmdf_type from flooreT.MMDF_TYPE where niin = e.niin) as 'Category'
into #temp
from equipment e
join amdf a on e.niin = a.niin
join el_item_location l on e.el_id = l.el_id
join eqlist_view f on e.el_id=f.el_id
join fmds.maintenance_equipment m on m.EL_ID = f.EL_ID
join fmds.WorkOrder w on w.Repair_ID = m.Repair_ID
join fmds.WorkOrder_Status ws on ws.FMDS_WON = w.FMDS_WON
where ( e.plan_id in (530,531,533,508) OR (E.PLAN_ID = 509 AND F.APS = 'ARCENT TSS')) and a.item_category not like '%TRACK%' and w.CloseDate is null
group by e.el_id,'SET',a.Nomenclature,a.model_no,a.lin,e.serial_no,e.plan_id,e.owner_uic,l.location,e.LAST_SCAN_DT, e.authorization_id,f.aps, w.FMDS_WON, w.apc, w.fss, w.Condition_Cd,WOAGE, w.Description,

w.Remarks,DAYSINSTATUS, RollStock, Category;
commit;

select "t"."Set",t.EL_ID,t.APS,t.Nomenclature,t.Model_No,t.LIN,t.Serial_no,s.Date_Service_Due,s.Date_Service_Started,s.Date_Service_Completed,s.Service_type_Cd,sc.Description as 'Svc Description',s.Variance,
t.Plan_ID,t.Owner_UIC,t.Location,t.LAST_SCAN_DT,t.FMDS_WON,t.APC, t.FSS, t.Condition_Cd,t.WOAGE, t.Description, t.Remarks,t.WOSTATUS,t.StatusOpened,t.DAYSINSTATUS,t.RollStock,Category
into #temp2
From Services s
Join SERVICE_TYPE_CODE sc
On s.service_type_cd = sc.service_type_cd
join #temp t
On s.el_id = t.el_id
where s.service_type_cd in ('a','b');
commit;

select *,
case
when a.date_service_due between '2012-01-01' and '2012-01-31' then 'January 2012'
when a.date_service_due between '2012-02-01' and '2012-02-29' then 'February 2012'
when a.date_service_due between '2012-03-01' and '2012-03-31' then 'March 2012'
when a.date_service_due between '2012-04-01' and '2012-04-30' then 'April 2012'
when a.date_service_due between '2012-05-01' and '2012-05-31' then 'May 2012'
when a.date_service_due between '2012-06-01' and '2012-06-30' then 'June 2012'
when a.date_service_due between '2012-07-01' and '2012-07-31' then 'July 2012'
when a.date_service_due between '2012-08-01' and '2012-08-31' then 'August 2012'
when a.date_service_due between '2012-09-01' and '2012-09-30' then 'September 2012'
when a.date_service_due between '2012-10-01' and '2012-10-31' then 'October 2012'
when a.date_service_due between '2012-11-01' and '2012-11-30' then 'Novemeber 2012'
when a.date_service_due between '2012-12-01' and '2012-12-31' then 'December 2012'

when a.date_service_due between '2013-01-01' and '2013-01-31' then 'January 2013'
when a.date_service_due between '2013-02-01' and '2013-02-28' then 'February 2013'
when a.date_service_due between '2013-03-01' and '2013-03-31' then 'March 2013'
when a.date_service_due between '2013-04-01' and '2013-04-30' then 'April 2013'
when a.date_service_due between '2013-05-01' and '2013-05-31' then 'May 2013'
when a.date_service_due between '2013-06-01' and '2013-06-30' then 'June 2013'
when a.date_service_due between '2013-07-01' and '2013-07-31' then 'July 2103'
when a.date_service_due between '2013-08-01' and '2013-08-31' then 'August 2013'
when a.date_service_due between '2013-09-01' and '2013-09-30' then 'September 2013'
when a.date_service_due between '2013-10-01' and '2013-10-31' then 'October 2013'
when a.date_service_due between '2013-11-01' and '2013-11-30' then 'Novemeber 2013'
when a.date_service_due between '2013-12-01' and '2013-12-31' then 'December 2013'

end as 'MonthDue',

(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abk_in') ABK_In,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abkout') ABKOut,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'aty_in') ATY_In,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abkout') ATYOut
from #temp2 a
where a.date_service_due between '2012-01-01' and '2012-12-31'
order by a.date_service_due;

drop table #temp;
drop table #temp2;
commit


Results below from the above query:

FMDS_WON APC FSS Condition_CD WOAGE Description Remarks WOSTATUS StatusOpened
0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE A 9/8/2012 17:15
0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE C 9/11/2012 19:33
0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE K 9/8/2012 17:19
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE A 9/8/2012 13:47
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE C 9/25/2012 10:16
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE C 9/11/2012 19:33
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE K 9/19/2012 14:03
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE K 9/8/2012 14:00


this is what I want:

FMDS_WON APC FSS Condition_CD WOAGE Description Remarks WOSTATUS StatusOpened
0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE C 9/11/2012 19:33
0DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE C 9/25/2012 10:16

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 07:04:57
Wrap your query in a CTE along with a row number, and pick the one with row number = 1 as shown below:
;WITH cte AS( 
SELECT ROW_NUMBER() OVER (PARTITION BY FMDS_WON ORDER BY StatusOpened DESC AS RN),
*,
....
FROM #temp2 a
WHERE a.date_service_due BETWEEN '2012-01-01' AND '2012-12-31'
)
SELECT * FROM cte
WHERE RN=1

ORDER BY
a.date_service_due;
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-03 : 09:34:23
You could replace your giant case statement with this:
DATENAME(month,a.date_service_due) + ' ' + CONVERT(VARCHAR(4),YEAR(a.date_service_due)) 









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 10:27:04
similarly the below subqueries


..
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abk_in') ABK_In,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abkout') ABKOut,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'aty_in') ATY_In,
(select remarks from el_comment where el_id = a.el_id and comment_cd = 'abkout') ATYOut


can be replaced by a join as

..
ABK_In,
ABKOut,
ATY_In,
ATYOut
from #temp2 a
inner join (select el_id,
max(case when comment_cd = 'abk_in' then remarks end) as ABK_In,
max(case when comment_cd = 'abkout' then remarks end) as ABKOut,
max(case when comment_cd = 'aty_in' then remarks end) as ATY_In,
max(case when comment_cd = 'atyout' then remarks end) as ATYOut
from el_comment
group by el_id) e
on e.el_id = a.el_id
where a.date_service_due between '2012-01-01' and '2012-12-31'
order by a.date_service_due;
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-05 : 02:17:29
THANKS EVERYONE FOR YOUR SUGGESTIONS..I WILL TRY EACH OF THEM.
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-08 : 03:27:03
They worked!!!! Thanks everyone for your help..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-08 : 19:50:57
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -