0

I have the following task: I want to execute fixed SELECT statement against SQL Server table with some fixed condition and I need to do it after specified period of time multiple times... Let's say N times with 1 minute interval between each execution with final goal to display only records which were returned all N times... Final goal is to check if some specific records persist in select results over time or maybe even order them by time they were returned - i.e. one record was returned in 3 first SELECT executions and interval was set to 1 minute hence I can say it was persistent for 3 minutes, another record was returned 5 times - so I can conclude it persisted / met my condition for 5 minutes.

Nature of my condition/data dictates that records not meet this condition for more than one minute and I want to catch abnormal records which meet my condition for prolonged period of time.

From explanation above it seems that it all involves some cycle, temp table and condition to put results in yet another temp table. But I'm posting it here to check if I maybe missing some simpler approach/solution which will allow to achieve the same result?

  • 1
    Can you describe what is happening to the table that's removing the records from the table? I think you'd be better off logging the initial insert and then logging the deletion or update – Arthur D May 22 '17 at 19:43
  • do you mean that you need to find those record which appear in each Select along with duration like how long they have been appearing.Then you can compare with CreateDate and modifyDate.we need to see table structure ,script desire output. – KumarHarsh May 23 '17 at 6:35
  • Important clarification: records stay in the table all the time but they change their Status column value - so I want to check if some records keep specific status for a long time. I want to select all records of status 1, let's say (normally those records come and go quickly) and do repeated SELECTS to see if some of the records persist in Status=1... – Mikhail May 23 '17 at 8:13
1

No, from what you describe, I think that you'll wind up having to track these using some secondary tables.

It sounds like you want to know how long record X stayed in a given status, whether that's the latest status or not. From your description, I'm not sure if records can wind up in a given status multiple times, or if hitting any status is a one-time event. That would impact how this needs to be set up.

It also sounds like there are statuses that matter, and others that do not matter. Hopefully, if there are a large number of records, most records should wind up in statuses that you're not interested in.

So, you'd want to do something like the following:

  • Create a table to hold current status info (let's call that StatusDuration_Active), and a parallel table to keep historical info (StatusDuration_History). Let's assume it will have at least the following fields:
    • a unique ID for each of the above tables (and I'd make this an IDENTITY column). The _History ID will be unconnected to the _Active ID (that is to say, when a record moves to _History, it will get a new ID). Unique IDs are useful in case you need to delete records, among other things - and you will want to delete records from _History periodically.
    • record_id - this will hold the primary key value from the table you're tracking. It can be made a foreign key, but might be useful without being one. Presumably, you'd want to know about a long duration in a given status, even if the record was deleted afterwards.
    • status - the status value from the table you're tracking.
    • first_seen - datetime (or datetime2) column holding the time when the records was seen in this status, when it hadn't been in this status at the previous check.
    • last_seen - same datatype as previous; the last check when the record was still in this status.
    • times_seen - integer value showing the number of consecutive times your process saw this record at this status. Useful for a cross-check against the first and last times, in case your process that checks these goes offline at some point (accidentally or on purpose); you could add code to remove or warn if times_seen < (DATEDIFF(minute, first_seen, last_seen) - 10), for instance.
  • Create a procedure that:

    • Create a temp table as follows:

      CREATE TABLE #StatusDuration
           ( tmp_id INT IDENTITY(1,1) NOT NULL
            ,record_id <datatype>
            ,status <datatype>
            ,first_seen datetime
            ,times_seen INT DEFAULT 0
           );
      

      where record_id will hold the primary key of the table you're tracking, and status will hold the current status from that table.

    • Selects the current time into a variable (@CurTime);
    • Selects all records currently in a status you're interested in into #StatusDuration; set first_seen to @CurTime;
    • Copies all records from StatusDuration_Active where the record ID and status are not in the temp table into StatusDuration_History; these rows should be copied exactly as is;
    • Updates all records in #StatusDuration that are in StatusDuration_Active with the same record_id and status, updating the temp table's first_seen and times_seen with the values from StatusDuration_Active.
    • TRUNCATE TABLE StatusDuration_Active
    • Insert all records in #StatusDuration into StatusDuration_Active; set last_seen to @CurTime, and times_seen to times_seen + 1
  • Set up a job to run the procedure every minute.

Depending on the number of records returned, you could delete records from StatusDuration_Active that got moved to _History, update records that are already present, and insert new records, instead of the TRUNCATE TABLE method. However, if there are a large number of records to be deleted, this might be the fastest method to use.

You'll need to confirm that all this runs within a minute (or schedule it to run less frequently). I'd expect it to be OK, but I'm not sure how many records you'll have.

Note that the TRUNCATE TABLE on StatusDuration_Active will reset the IDENTITY value back to 1 (or whatever you started with) - one of the reasons the IDs shouldn't track over). An ID column on #StatusDuration may actually be unnecessary; I include them wherever possible, because sometimes they are necessary.

As noted, you'll want to clear the oldest rows (optionally keeping the most interesting ones, cases where the statuses remained at a particular duration for longer than normal) from StatusDuration_History, or it will eat up a lot of space, and will take longer and longer to query (and, depending on how you index it, possibly longer and longer to insert into, which may be more of a concern).

When reporting, you may want to use the WITH (NOLOCK) table hint, as both tables will be updated once a minute, and you don't want to interfere with that. If your queries run in a few seconds, and your procedure does as well, then no major worries. If you do use the table hint, note that it'll be possible to hit a row that's in _Active, and to hit the same row because it's been copied to _History. I'd keep the possibility in mind; you could use UNION instead of UNION ALL to combine the data from the two tables to work around this, but I'd not worry about it unless you see it happening and notably affecting your results.

0

There is not a lot of specific information in your request but from what I see you should be able to do joins on sub queries like -

Select a1.CUSTNO, a1.INVNO, a2.Payments_Pending
FROM (Select CUSTNO, count(INVNO) as INVNO 
      from tablename 
      group by CUSTNO) a1 
join  (select CUSTNO, count(Payments_Pending) as Payments_Pending 
       from tablename 
       where Payments_Pending='Y' 
       group by CUSTNO) a2
on    a1.CUSTNO=a2.CUSTNO  
where a1.INVNO = a2.Payments_Pending 
group by a1.CUSTNO, a1.INVNO, a2.Payments_Pending;

I think that's the idea you are looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.