Solved

Finding Items with multiple PM Schedules

  • 20 December 2022
  • 9 replies
  • 78 views
bskallerud
Rank
Userlevel 6
Badge +11

We have a situation where we have some Installed Items that for some reason have multiple PM Schedules. I am trying to track these down, but have no luck making a SQL query to find these:

How do I link Item against pm_sched?

icon

Best answer by Phil Seifert 20 December 2022, 10:30

View original

9 replies

Phil Seifert
Rank
Userlevel 7
Badge +21

Hi Bjørn,

I have used the below script in the past with other customers to find duplicate PM dates which actually is checking if there are multiple PM dates in the same month:

select * from (

select cl.cconth_id, it.serial_no, cl.pm_sched_id,
LEFT(CONVERT(varchar, pm.last_sched_pm_date,112),6) YYYYMM,
count(it.serial_no) qty 

from pm_sched_dates pm with(nolock)
inner join ccontl cl with(nolock) on cl.ccontl_id = pm.ccontl_id
inner join item it with(nolock) on it.item_id  = pm.item_id
where (1=1)

and pm.order_id is null
and pm.last_sched_pm_date > '30 April 2020'
and pm.last_sched_pm_date < '1-June-2020'

group by cl.cconth_id, it.serial_no, cl.pm_sched_id, LEFT(CONVERT(varchar, pm.last_sched_pm_date,112),6)
having count(it.serial_no) > 1

) list

order by yyyymm, cconth_id, serial_no

 

You can see the joins from the ccontl to the pm_sched_date is via the ccontl_id.

There is also a cconth.pm_sched_id and as shown above, the ccontl.pm_sched_id. Perhaps this will help?

 

Phil Seifert
Rank
Userlevel 7
Badge +21

Hi Bjørn,

Please also check the item.pm_sched_id.

From your screen shot, one appears to be on the item itself and the other on the mentioned contract.

Therefore your query will need to first identify from the item related PM schedule from item.pm_sched_id and then another query to identify the ccontl.pm_sched_id.

 

 

Chris_Perkins
Rank
Userlevel 5
Badge +12

Hi Phil,

Sorry to jump in Bjorn. This is very useful and we have a lot of these too, usually with dates one day apart.  Do you know what causes these additional lines to be created and more important, how to get rid of them?  I can remove them one by one by cloning the PM Sched. and then undoing again but that takes time to do one by one.  If there is way to fix these en masse, that would be really useful.

Thanks

Chris

Phil Seifert
Rank
Userlevel 7
Badge +21

HI Chris,

I am not sure about the cause but I think I have a script to remove the extra PM Date scheduled in a month which I will send you to separately.

 

bskallerud
Rank
Userlevel 6
Badge +11

Thanks, Phil. It did not do quite what I had in mind - it does not sadly not help me hunt down the items with 2 schedules (or I did not tweak this correctly)

bskallerud
Rank
Userlevel 6
Badge +11

Hi Phil,

Sorry to jump in Bjorn. This is very useful and we have a lot of these too, usually with dates one day apart.  Do you know what causes these additional lines to be created and more important, how to get rid of them?  I can remove them one by one by cloning the PM Sched. and then undoing again but that takes time to do one by one.  If there is way to fix these en masse, that would be really useful.

Thanks

Chris

I think these came from an import - the initial import had a montly PM Schedule, but after the contracts were activated the technical team wanted to switch to a 4-week cycle in a certain area. This was updated by import - and created havoc

Chris_Perkins
Rank
Userlevel 5
Badge +12

Thanks Bjørn, probably the same for us.  Not sure if it will help you to get started but this bit of SQL can show where a serial number has more than one PM schedule set BUT for me it shows where we have more than one Installed item with the same serial, rather than one with more that one PM Schdule… but I thought I would share it anyway…

   select serial_no, count(pm_sched_id)qty 
   from item with(NOLOCK)
   group by serial_no, pm_sched_id
   having count(pm_sched_id)>1

Good Luck (and Merry Christmas)

Chris

bskallerud
Rank
Userlevel 6
Badge +11

@Chris_Perkins - I have been playing around a bit, and here is a query that will list all future PMs without an order (in this case all PMs for contracts that start with a ‘K’)

 

’’SELECT        TOP (100) PERCENT dbo.pm_sched_dates.cconth_id, dbo.pm_sched_dates.item_id, dbo.item.bpart_id, dbo.item.serial_no, CONVERT(VARCHAR(10), dbo.pm_sched_dates.last_sched_pm_date, 104) AS [Next PM Date], 
                         dbo.ccont_type.descr AS [Cont Type], dbo.pm_sched_dates.pm_sched_id
FROM            dbo.pm_sched_dates INNER JOIN
                         dbo.cconth ON dbo.pm_sched_dates.cconth_id = dbo.cconth.cconth_id INNER JOIN
                         dbo.item ON dbo.pm_sched_dates.item_id = dbo.item.item_id INNER JOIN
                         dbo.ccont_type ON dbo.cconth.ccont_type_id = dbo.ccont_type.ccont_type_id
WHERE        (dbo.pm_sched_dates.order_id IS NULL)
GROUP BY dbo.pm_sched_dates.cconth_id, dbo.pm_sched_dates.ccontl_id, dbo.pm_sched_dates.item_id, dbo.cconth.cconth_stat_id, dbo.item.serial_no, dbo.ccont_type.descr, dbo.item.bpart_id, CONVERT(VARCHAR(10), 
                         dbo.pm_sched_dates.last_sched_pm_date, 104), dbo.pm_sched_dates.pm_sched_id, dbo.pm_sched_dates.pm_sched_line, dbo.pm_sched_dates.pm_sched_line_id
HAVING        (dbo.pm_sched_dates.cconth_id LIKE N'K%') AND (dbo.cconth.cconth_stat_id BETWEEN 51 AND 599)
ORDER BY dbo.pm_sched_dates.cconth_id

I copy the data over to Excel, and make certain to sort by contract_id, serial_no, PM Date. I then compare the PM dates for each line with the one above (if serian_no is the same), and if i get any weird results (like 0 days) I know where to look:

Here you even see my very nifty IF-formula (replace HVIS with IF)

Chris_Perkins
Rank
Userlevel 5
Badge +12

Hi Bjørn,

This is very useful, thank you.

Reply


Terms & ConditionsCookie settings