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 |
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 querySelect 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 #tempfrom equipment ejoin amdf a on e.niin = a.niinjoin el_item_location l on e.el_id = l.el_idjoin eqlist_view f on e.el_id=f.el_idjoin fmds.maintenance_equipment m on m.EL_ID = f.EL_IDjoin fmds.WorkOrder w on w.Repair_ID = m.Repair_IDjoin fmds.WorkOrder_Status ws on ws.FMDS_WON = w.FMDS_WONwhere ( 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 nullgroup 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,Categoryinto #temp2From Services sJoin SERVICE_TYPE_CODE scOn s.service_type_cd = sc.service_type_cdjoin #temp tOn s.el_id = t.el_idwhere 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') ATYOutfrom #temp2 awhere a.date_service_due between '2012-01-01' and '2012-12-31'order by a.date_service_due;drop table #temp;drop table #temp2;commitResults below from the above query:FMDS_WON APC FSS Condition_CD WOAGE Description Remarks WOSTATUS StatusOpened0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE A 9/8/2012 17:150DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE C 9/11/2012 19:330DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE K 9/8/2012 17:190DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE A 9/8/2012 13:470DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE C 9/25/2012 10:160DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE C 9/11/2012 19:330DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE K 9/19/2012 14:030DA51A221980 A6MW / A 25 BOTH SIDE SHOCK ABSORBER CORROSION UNSCHEDULED MAINTENANCE K 9/8/2012 14:00this is what I want:FMDS_WON APC FSS Condition_CD WOAGE Description Remarks WOSTATUS StatusOpened0DA51A221998 A6MW / A 25 BOTH SIDE SHOCK ABSORBER PISTON CORRSION UNSCHEDULED MAINTEANACE C 9/11/2012 19:330DA51A221980 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 aWHERE a.date_service_due BETWEEN '2012-01-01' AND '2012-12-31')SELECT * FROM cte WHERE RN=1ORDER BY a.date_service_due; |
|
|
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 |
|
|
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,ATYOutfrom #temp2 ainner 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) eon e.el_id = a.el_idwhere a.date_service_due between '2012-01-01' and '2012-12-31'order by a.date_service_due;... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 2012-10-05 : 02:17:29
|
THANKS EVERYONE FOR YOUR SUGGESTIONS..I WILL TRY EACH OF THEM. |
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 2012-10-08 : 03:27:03
|
They worked!!!! Thanks everyone for your help.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-08 : 19:50:57
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|