asked on

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.

trifecta2k

I'm not sure I understand what you're asking. Can you clarify?

sdom100

ASKER

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

trifecta2k

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.

sdom100

ASKER

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.

sdom100

ASKER

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

sdom100

ASKER

Note: The code I posted above could possibly form part of the solution but not sure how to turn into the full solution
S.

ASKER CERTIFIED SOLUTION

kemanetzis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdom100

ASKER

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