SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Max result, remove duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Novice2Sql
Starting Member

33 Posts

Posted - 10/03/2012 :  04:47:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  07:04:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/03/2012 :  09:34:23  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/03/2012 :  10:27:04  Show Profile  Reply with Quote
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 - 10/05/2012 :  02:17:29  Show Profile  Reply with Quote
THANKS EVERYONE FOR YOUR SUGGESTIONS..I WILL TRY EACH OF THEM.
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 10/08/2012 :  03:27:03  Show Profile  Reply with Quote
They worked!!!! Thanks everyone for your help..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/08/2012 :  19:50:57  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000