Problem With Selecting a History Record 1

[Happy2]

Can anyone help me real quick, please? Thanks in advance.

I have a table that contains all update history of every single check. Within each check, I need to
pull out the TimeStamp of a update batch (regardless of stage) WHEN the ProblemID #1 is not included
in that batch anymore. In the table below, I need to pull out TimeStamp of records
from ID #8 (7/9/2003 11:30:15 AM) and #15 (7/9/2003 11:30:15 AM).

Below is the table with 2 checks here (there are many many more checks will be in the table,
so I cannot say where ID in (8, 15)):


ID CheckNo Stage ProblemID Updated TimeStamp

1 1 1 1 First Time 7/7/2003 10:12:50 AM
2 1 1 2 First Time 7/7/2003 10:12:50 AM
3 1 1 3 First Time 7/7/2003 10:12:50 AM
4 1 1 4 First Time 7/7/2003 10:12:50 AM

5 1 1 1 Second Time 7/8/2003 12:30:25 PM
6 1 1 2 Second Time 7/8/2003 12:30:25 PM
7 1 1 3 Second Time 7/8/2003 12:30:25 PM

8 1 2 3 Third Time 7/9/2003 11:30:15 AM
9 1 2 4 Third Time 7/9/2003 11:30:15 AM


10 2 1 1 First Time 7/7/2003 10:12:50 AM
11 2 1 2 First Time 7/7/2003 10:12:50 AM
12 2 1 3 First Time 7/7/2003 10:12:50 AM
13 2 1 4 First Time 7/7/2003 10:12:50 AM

15 2 1 2 Second Time 7/8/2003 12:30:25 PM
16 2 1 3 Second Time 7/8/2003 12:30:25 PM

 

[clarkin]

ok .. trying to make sense of your query.

You want to get:
for each checkno, the TimeStamp column of the earliest update batch where there is no problemid=1 in the batch

Is that right? (also i'm assuming that TimeStamp column is actually of type datetime?)

First off, if you can - change "updated" column to "UpdateNo" so instead of 'First Time', 'Second Time' it would just have 1, 2 or 3 in it (type int).

Second, lets try and do something simple-ish with the data once we have changed that column.
So, lets try and get the earliest batch time for checkno 1.

SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo
  FROM TableName AS T
  WHERE CheckNo = 1
  AND NOT EXISTS
    (SELECT ProblemID FROM TableNAme
     WHERE UpdateNo = T.UpdateNo
     AND ProblemID = 1)

You may well ask .. what the hell does all that do? I'll break it down:
[tt]SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo[/tt]
select timestamp (min doesn't matter in ur data, but useful), and the updateNo
[tt] FROM TableName AS T[/tt]
from your table with a table alias T for later
[tt] WHERE CheckNo = 1[/tt]
limit it to rows with checkno = 1
[tt] AND NOT EXISTS
(SELECT ProblemID FROM TableNAme
WHERE UpdateNo = T.UpdateNo
AND ProblemID = 1)[/tt]
limit it to rows where something is returned in the sub select
-> the sub select searches through the same table name again, but only considers rows with the same UpdateNo as the current one (WHERE UpdateNo = T.UpdateNo), and selects any rows with a problemid of 1.

See if you can understand that, then we can go one step further with GROUP BY to make it work so you don't have to specify the checkno.

 

[Happy2]

Hi Clarkin,

Yes, you are right! what you are assuming are the things that I need to do as:
"for each checkno, the TimeStamp column of the earliest update batch where there is no problemid=1 in the batch

Is that right? (also i'm assuming that TimeStamp column is actually of type datetime?)"

I understand your select statement, so we can go with the GROUP BY, right?. Thanks a lot.

 

[clarkin]

if you follow all that and you've changed your Updated column so it holds numbers (1,2,3) instead of "first time, second .." then you should be ready for the group by.

SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo, CheckNo
  FROM TableName AS T
  WHERE NOT EXISTS
    (SELECT ProblemID FROM TableNAme
     WHERE UpdateNo = T.UpdateNo
     AND ProblemID = 1)
  GROUP BY CheckNo

simple eh?
All your doing here is adding CheckNo to the list of values to be returned, removing the hardcoded checkno number (WHERE checkno = 1), and telling it to group your results by CheckNo. This is also where using MIN() comes in handy, as its an 'aggregate' function - so with GROUP BY it makes you only get one row back for each checkno.

You should get back :
[tt]
EarliestBatchTime UpdateNo CheckNo
7/9/2003 11:30:15 AM 3 1
7/8/2003 12:30:25 PM 2 2
[/tt]

let me know if it works as expected and if you understand whats happening

 

[Happy2]

Hi Clarkin,

Yes I enjoy it. There is one more step that happens is that if the ProblemID of record ID #1 now is ProblemID #5 instead of ProblemID #1, but I still have to pull out TimeStamp of record ID #8 because I have to pull out an update batch after the batch that has ProblemID #1. Then what should I do? I only need TimeStamp.

Thank you so much.

If
ID CheckNo Stage ProblemID Updated TimeStamp

1 1 1 5 First Time 7/7/2003 10:12:50 AM
2 1 1 2 First Time 7/7/2003 10:12:50 AM
3 1 1 3 First Time 7/7/2003 10:12:50 AM
4 1 1 4 First Time 7/7/2003 10:12:50 AM

5 1 1 1 Second Time 7/8/2003 12:30:25 PM
6 1 1 2 Second Time 7/8/2003 12:30:25 PM
7 1 1 3 Second Time 7/8/2003 12:30:25 PM

8 1 2 3 Third Time 7/9/2003 11:30:15 AM
9 1 2 4 Third Time 7/9/2003 11:30:15 AM

 

[clarkin]

ok so what you are doing here is changing our basic definition of the problem - this is what the old one was:

You want to get:
for each checkno, the TimeStamp column of the earliest update batch where there is no problemid=1 in the batch

But now we want to get:
for each checkno, the TimeStamp column of the earliest update batch after a batch which contains a problemId 1 where there is no problemid=1 in the batch

this new definition will work for your earlier ones too, I think. So, a bit more complicated.. first lets modify the statement without the group by and see if we can get it to work for the new problem definition:
[tt]
SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo
FROM TableName AS T
WHERE CheckNo = 1
AND NOT EXISTS
(SELECT ProblemID FROM TableNAme
WHERE UpdateNo = T.UpdateNo
AND ProblemID = 1)
AND EXISTS
(SELECT ProblemID FROM TableNAme
WHERE UpdateNo = T.UpdateNo - 1
AND ProblemID = 1)
[/tt]
I *think* this will work - a very simple modification. All we're adding is a check that the previous updateNo has got a problemID in it. This is where you will run into problems with your table as shown in your last post - you need to change your data in Updated from varchar type to an int type. (I've called it UpdatedNo instead too) Why? Because we are subtracting 1 from it in the new bit above.

So a row of your table would look like:
ID CheckNo Stage ProblemID UpdatedNo TimeStamp

1 1 1 5 1 7/7/2003 10:12:50 AM

Let me know if that works

 

[Happy2]

Hi Clarkin,



I can't get UpdateNo = T.UpdateNo - 1 since there can be as many more updated batches before the time stamp of the record that I want to pull out. I am still working on it right now and the weekend. Please let me know if you have any suggestions for me. According to the table below, I need to pull out time stamp of record #12 (7/10/2003 1:31:16 PM)

"But now we want to get:
for each checkno, the TimeStamp column of the earliest update batch after a batch which contains a problemId 1 where there is no problemid=1 in the batch" I have to go with this case because I just found out yesterday.

Thanks for your time.

SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo
FROM CheckDetail AS T
WHERE CheckNo = 1
AND NOT EXISTS
(SELECT ProblemID FROM CheckDetail
WHERE UpdateNo = T.UpdateNo
AND ProblemID = 1)
AND EXISTS
(SELECT ProblemID FROM CheckDetail
WHERE UpdateNo = T.UpdateNo - 1
AND ProblemID = 1)

CheckDetail table:

ID CheckNo Stage ProblemID UpdateNo TimeStamp

1 1 1 5 1 7/7/2003 10:12:50 AM
2 1 1 2 1 7/7/2003 10:12:50 AM
3 1 1 3 1 7/7/2003 10:12:50 AM
4 1 1 4 1 7/7/2003 10:12:50 AM

5 1 1 5 2 7/8/2003 12:30:25 PM
6 1 1 2 2 7/8/2003 12:30:25 PM
7 1 1 3 2 7/8/2003 12:30:25 PM
8 1 1 4 2 7/8/2003 12:30:25 PM

9 1 1 1 3 7/9/2003 11:30:15 AM
10 1 1 2 3 7/9/2003 11:30:15 AM
11 1 1 3 3 7/9/2003 11:30:15 AM

12 1 2 3 4 7/10/2003 1:31:16 PM
13 1 2 4 4 7/10/2003 1:31:16 PM

 

[clarkin]

ok to see what was going wrong I created a table (I called mine cTest) similar to yours. Here's all the code:

SET NOCOUNT ON

DROP TABLE cTest
CREATE TABLE cTest(
[ID] int NULL,
Checkno int NULL,
Stage int NULL,
ProblemID int NULL,
UpdateNo int NULL,
[TimeStamp] datetime NULL
)

INSERT INTO cTest
VALUES (1,1,1,5,1,'2003-07-11 18:00:00')
INSERT INTO cTest
VALUES (2,1,1,2,1,'2003-07-11 18:00:00')
INSERT INTO cTest
VALUES (3,1,1,3,1,'2003-07-11 18:00:00')
INSERT INTO cTest
VALUES (4,1,1,4,1,'2003-07-11 18:00:00')

INSERT INTO cTest
VALUES (5,1,1,5,2,'2003-07-12 18:00:00')
INSERT INTO cTest
VALUES (6,1,1,2,2,'2003-07-12 18:00:00')
INSERT INTO cTest
VALUES (7,1,1,3,2,'2003-07-12 18:00:00')
INSERT INTO cTest
VALUES (8,1,1,4,2,'2003-07-12 18:00:00')

INSERT INTO cTest
VALUES (9,1,1,1,3,'2003-07-13 18:00:00')
INSERT INTO cTest
VALUES (10,1,1,2,3,'2003-07-13 18:00:00')
INSERT INTO cTest
VALUES (11,1,1,3,3,'2003-07-13 18:00:00')

INSERT INTO cTest
VALUES (12,1,1,3,4,'2003-07-14 18:00:00')
INSERT INTO cTest
VALUES (13,1,1,4,4,'2003-07-14 18:00:00')

SELECT * FROM cTest

-- for each CheckNo, get earliestBatchTime of batch after first batch with ProblemID 1
SELECT MIN(TimeStamp) AS EarliestBatchTime, UpdateNo, CheckNo
  FROM cTest AS T
  WHERE NOT EXISTS
    (SELECT ProblemID FROM cTest
     WHERE UpdateNo = T.UpdateNo
     AND ProblemID = 1)
  AND EXISTS
    (SELECT ProblemID FROM cTest
     WHERE UpdateNo = T.UpdateNo - 1
     AND ProblemID = 1)
  GROUP BY CheckNo, UpdateNo

-- get all rows of batch after first batch with problemID 1 (specify CheckNo in WHERE)
SELECT *
  FROM cTest AS T
  WHERE CheckNo = 1
  AND NOT EXISTS
    (SELECT ProblemID FROM cTest
     WHERE UpdateNo = T.UpdateNo
     AND ProblemID = 1)
  AND EXISTS
    (SELECT ProblemID FROM cTest
     WHERE UpdateNo = T.UpdateNo - 1
     AND ProblemID = 1)

Seems the only problem was the MIN() when we weren't using GROUP BY.

Here's what the above outputs:

ID          Checkno     Stage       ProblemID   UpdateNo    TimeStamp                   
----------- ----------- ----------- ----------- ----------- --------------------------- 
1           1           1           5           1           2003-07-11 18:00:00.000
2           1           1           2           1           2003-07-11 18:00:00.000
3           1           1           3           1           2003-07-11 18:00:00.000
4           1           1           4           1           2003-07-11 18:00:00.000
5           1           1           5           2           2003-07-12 18:00:00.000
6           1           1           2           2           2003-07-12 18:00:00.000
7           1           1           3           2           2003-07-12 18:00:00.000
8           1           1           4           2           2003-07-12 18:00:00.000
9           1           1           1           3           2003-07-13 18:00:00.000
10          1           1           2           3           2003-07-13 18:00:00.000
11          1           1           3           3           2003-07-13 18:00:00.000
12          1           1           3           4           2003-07-14 18:00:00.000
13          1           1           4           4           2003-07-14 18:00:00.000

EarliestBatchTime           UpdateNo    CheckNo     
--------------------------- ----------- ----------- 
2003-07-14 18:00:00.000     4           1

ID          Checkno     Stage       ProblemID   UpdateNo    TimeStamp                   
----------- ----------- ----------- ----------- ----------- --------------------------- 
12          1           1           3           4           2003-07-14 18:00:00.000
13          1           1           4           4           2003-07-14 18:00:00.000

Does that solve everything?

 

[Happy2]

Hi Clarkin,

It works except for 1 exceptional case that related to human input error, but I can't delete record - I have to consider as a normal case. The problem is for example updateNo 1 has ProblemID 1, updateNo 2 doesn't have ProblemID 1, updateNo 3 has ProblemID 1 again. In this case, the select statement will show 2 records ID # 5 and # 12. I only need TimeStamp for record ID # 12. Thanks for your big help!!!

CheckDetail table:

ID CheckNo Stage ProblemID UpdateNo TimeStamp

1 1 1 1 1 7/7/2003 10:12:50 AM
2 1 1 2 1 7/7/2003 10:12:50 AM
3 1 1 3 1 7/7/2003 10:12:50 AM
4 1 1 4 1 7/7/2003 10:12:50 AM

5 1 1 5 2 7/8/2003 12:30:25 PM
6 1 1 2 2 7/8/2003 12:30:25 PM
7 1 1 3 2 7/8/2003 12:30:25 PM
8 1 1 4 2 7/8/2003 12:30:25 PM

9 1 1 1 3 7/9/2003 11:30:15 AM
10 1 1 2 3 7/9/2003 11:30:15 AM
11 1 1 3 3 7/9/2003 11:30:15 AM

12 1 2 3 4 7/10/2003 1:31:16 PM
13 1 2 4 4 7/10/2003 1:31:16 PM

 

[clarkin]

its quite likely that to catch 'human error' typos like this you will manually have to check which ones shouldn't be reported, and either remove them manually or hardcode them to be ignored in the reporting query.

See if you can modify the code to ignore UpdateNo = 2 for that CheckNo.




Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[Happy2]

Hi Clarkin,

That error is kind of accepted. Let say that they first add a check and record as problemID 1. Next time they go back to edit the record and record there is no ProblemID 1 anymore. However, they later found out that they made a mistake last time and ago and record the ProblemID 1 again. Every time they update, a new updateNo is generated to keep track of all the changes. I am not allowed to do anything about it except pulling out the wanted record. Tough huh? Thanks for all your big help!!!