| Author |
 Topic  |
|
|
Novice2Sql
Starting Member
33 Posts |
 Posted - 10/03/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/03/2012 : 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;
|
 |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 10/03/2012 : 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/
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/05/2012 : 02:17:29
|
| THANKS EVERYONE FOR YOUR SUGGESTIONS..I WILL TRY EACH OF THEM. |
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/08/2012 : 03:27:03
|
| They worked!!!! Thanks everyone for your help.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 10/08/2012 : 19:50:57
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Need Max result, remove duplicates
