• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

TSQL pseudo code

Newbie help needed - could you provide me a pseudo code approach to the following problem

I have a table [Processing_FaultDurations] containing
FK_FaultHASCID, FK_AddressID, StartTime, ClosedTime

and a table of time periods that i need to report over T_TimePeriods containing
PK_PeriodID, StartDate,EndDate

Any device can have multiple faults in a given reporting period (PK_PeriodID)

I need to retrieve the following:
For any 10 or more faults occuring on each device in each reporting period ie. when the fault starts between the period start time and end time
I need to get back a table that lists all fault IDs that are the 10th Fault and i need this for every device for every reporting period.

Many thanks
S.
0
sdom100
Asked:
sdom100
  • 5
  • 2
1 Solution
 
trifecta2kCommented:
I'm not sure I understand what you're asking.  Can you clarify?
0
 
sdom100Author Commented:
My data represents fautls on devices. Faults have a start time.
If a device has 10 or more faults in any given time period (start and stop dates for each time period come from the T_TimePeriods table) then i need to log the faultID (FK_FaultHASCID) out to a separate table.

let me know if you need more info
S
0
 
trifecta2kCommented:
select count(faults), faults
from table
group by faults
having count(faults) > 10

Thats the faults that are greater than 10 but how are the 2 tables related.  I don't see a period_id in table 1.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdom100Author Commented:
Thanks for helping out,

The PK_PeriodID is the primary key in a table of consecutive reporting periods.
eg.
row 1 = 01/10/07 - 30/10/07
row 2 = 02/10/07 - 31/10/07
row 3 = 03/10/07 - 01/11/07

I then need to run these periods against the start times that i have for each device to see how many faults for that device started within this period. For any device for any period if there are >= 10 faults then i need to return the ID of only the 10th fault

S.

0
 
sdom100Author Commented:
This SQL will retrieve the FK_AddressID's for any devices that have >10 faults in any given time period

SELECT * FROM
(
SELECT COUNT (*) AS NoInstances,PK_PeriodID,FK_AddressID
FROM Processing_FaultDurations pfd1
JOIN RefData30DayPeriodsOfInterest window
ON pfd1.StartTime > window.StartDate
AND pfd1.StartTime < window.EndDate

GROUP BY PK_PeriodID,FK_AddressID
) groupsSet
WHERE NoInstances >=10
0
 
sdom100Author Commented:
Note: The code I posted above could possibly form part of the solution but not sure how to turn into the full solution
S.
0
 
kemanetzisCommented:
here it is

SELECT DISTINCT TT2.PK_PeriodID, PF2.FK_AddressID, PF2.FK_FaultHASCID
FROM T_TimePeriods TT2 LEFT OUTER JOIN Processing_FaultDurations PF2 ON PF2.StartTime>=TT2.StartDate and PF2.ClosedTime<=TT2.EndDate
WHERE EXISTS
(
select *
from
(
select TT1.PK_PeriodID, PF1.FK_AddressID, count(PF1.FK_AddressID) as NumOfFaults
from T_TimePeriods TT1 LEFT OUTER JOIN Processing_FaultDurations PF1 ON PF1.StartTime>=TT1.StartDate and PF1.ClosedTime<=TT1.EndDate
group by TT1.PK_PeriodID, PF1.FK_AddressID
) BF
where BF.PK_PeriodID = TT2.PK_PeriodID and BF.FK_AddressID = PF2.FK_AddressID and BF.NumOfFaults>=10
)
ORDER BY PK_PeriodID, FK_AddressID
0
 
sdom100Author Commented:
In the end, I solved this and the related problem with a cursor - The cursor was more performant and scaled better.
Thanks for your thoughts though they did influence my thinking
S
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now