SQL query, correct way of writing

[Arg81]

Hey guys,

Here's a quick table layout;

Parent Table
ID No

Child Table
ID No, Rev No, RequiredDate

Basically the child table will have matching ID No, and then Rev No would be 1, 2, 3, 4 etc etc to infinity (normally 10!!) - this is programmatically assigned (looks at the last Rev No for that ID No, then + 1 to it)

What I need to do, is select all ID Numbers from the parent table that have a RequiredDate < next 7 days for the latest Rev No (the highest).
This is so I can create a popup to the user to tell them what ID Numbers need working on as their RequiredDate is less than a week away.

Not sure how to properly write this out, I'm hoping it can be done in one simple SQL query, so that the results look like;

ID No RequiredDate
27 - 10/01/07
54 - 12/01/07
135 - 14/01/07


The issue I have at the moment when trying to do this, is that all previous Rev No for the ID have a requiredDate, but obviously I need the latest and only the latest Rev No, and all other Rev No for that ID to be ignored....

Any help much appriciated, thanks.
Luke

 

[jhomminga]

You'll need a correlated subquery and derived table

0) Get the max REV NO for each ID
1) Calculate, in correlated subquery, the number of days interval [note the B to A linkage]
2) Wrap in derived and filter for days < 6

SELECT [ID No], THE_MAX, DAYS_DUE
FROM (
--get the max for each ID
SELECT [ID No],
MAX([Rev No]) THE_MAX,
/* correlated subquery to derive the days for this row */
(SELECT DATEDIFF(d,RequiredDate,GetDate()) FROM YOURTABLE B WHERE B.[ID No] = A.[ID No] AND B.[Rev No] = A.[Rev No]) DAYS_DUE
FROM YOURTABLE A
GROUP BY [ID No]
) Z
WHERE DAYS_DUE < 7


HTH

 

[TechGnome]

That seems a little over complicated.... maybe because I approached it differently.

SELECT *
FROM [your table] YT
INNER JOIN (SELECT [ID No], MAX([Rev No]) AS MaxRevNo
                FROM [your table]) MYT
  ON YT.[ID No] = MYT.[ID No]
      AND YT.[Rev No] = MYT.MaxRevNo
WHERE DATEDIFF(d, YT.RequiredDate, GETDATE()) < 7

-tg
GROUP BY [ID No]

 

[jhomminga]

Two good examples

Nice one. Being self taught I do things in little nuggets that build up to the solution.

With my style I can run the inner query, make sure its right, then run the next outer query, etc until I get the result I want.

At any rate, V-B_New-B, hope you get some useful dynamic from the two styles.

Remember to pull out the profiler and IO STATS to check for which technique is fastest, fewest reads, i.e. most performant.

HTH

 

[Arg81]

As you can see Table A doesn't have Revision Number - this is only in the child table (B).

I've tried both versions of the code and I get the error;

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'DWRNumber'.

(DWRNumber is my IDNo)

 

[jhomminga]

Here is a complete working example

I only used your child table to accomplish the result

USE YOUR_DATABASE
GO
IF (OBJECT_ID('DELETE_ME') IS NOT NULL) BEGIN
DROP TABLE DELETE_ME
END
GO
CREATE TABLE DELETE_ME (
[ID NO] INT,
[REV NO] INT,
[REQUIREDDATE] SMALLDATETIME
)
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 1, '1/1/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 2, '1/2/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 3, '1/3/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 4, '1/4/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 5, '1/5/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 6, '1/16/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (1, 7, '1/17/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (2, 1, '1/10/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (2, 2, '1/12/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (2, 3, '1/13/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (2, 4, '1/14/2007')
INSERT INTO DELETE_ME ([ID NO], [REV NO],[REQUIREDDATE]) VALUES (2, 5, '1/15/2007')

--should be properly indented here to make for easy reading.

SELECT Y.[ID NO],
Y.[THE_MAX],
Y.[DAYS_DUE]
FROM (
SELECT [ID NO],
[THE_MAX],
(SELECT DATEDIFF(D,REQUIREDDATE,GETDATE())
FROM DELETE_ME B
WHERE B.[ID NO] = Z.[ID NO] AND B.[REV NO] = Z.[THE_MAX]
) DAYS_DUE

FROM (
SELECT A.[ID NO],
MAX([REV NO]) THE_MAX
FROM DELETE_ME A
GROUP BY A.[ID NO]
) Z
) Y
WHERE Y.DAYS_DUE < 7


--

I hope you like this debugging technique. It took me years to master
-- 1) RUN the A section to see that part works
-- 2) RUN the Z section to see that part works
-- 3) RUN the Y section to see you get the correct results

-- now change the (2, 5, '1/15/2007') insert to be (2, 5, '1/19/2007') and you will get two rows in your result instead of just one.

 

[Arg81]

cool, I think my problem when trying your first post was I still was trying to join parent and child.

I'll give that a go and get back to you

Luke

 

[TechGnome]

Nice one. Being self taught I do things in little nuggets that build up to the solution.

With my style I can run the inner query, make sure its right, then run the next outer query, etc until I get the result I want.

At any rate, V-B_New-B, hope you get some useful dynamic from the two styles.

Remember to pull out the profiler and IO STATS to check for which technique is fastest, fewest reads, i.e. most performant.

HTH

Thanks, the only reason I'd come up with that is because it's just like many of the queries we do here at the office.... otherwise, I'd have probably used the same approach you did.

-tg

 

[Arg81]

Jhomminga,

Got your code working, thanks a million

Regards,
Luke

 

[cjard]

What I need to do, is select all ID Numbers from the parent table that have a RequiredDate < next 7 days for the latest Rev No (the highest).
This is so I can create a popup to the user to tell them what ID Numbers need working on as their RequiredDate is less than a week away.

Can I just ask what relevance the highest RevNo has at all? If you want a list of all ID numbers whose required date is some time in the next 7 days, then it's:

SELECT
  id
FROM
  table
WHERE
  RequiredDate >= Now AND RequiredDate <= DATEADD(DAY,7,Now)

Is it that there might be something with a required date this week, but its revision 2, and that project already has a revision 3 that is next month?

 

[Arg81]

you've actually made a good point, but....

Revision comes in, with work to be done within the next 7 days, so

ID: 10001
Rev: 1
Date Due: 26/01/07

Someone will pick that up, and think "got 2 hours free today, I'll do that now." Does the work and off its goes for feedback. If the Account Manager signs it off and requests another revision, it would look like;

ID: 10001
Rev: 2
Date Due: 30/01/07


So in the case above, the query will come back showing ID10001 has 2 lots of work due within the next 7 days, but I only need it to display the latest, in this case "ID10001 Revision 2 must be complete within the next 7 days"

Does that make sense??

 

[cjard]

Mmh.. I know what you mean kinda. Do revisions all have to be completed? Do they overlap, rollup, or are they separate work units? I'd be tempted to put a CompletedDate in the table too, and make the query:

SELECT
id, rev
FROM
table
WHERE
RequiredDate >= Now AND RequiredDate <= DATEADD(DAY,7,Now)
AND CompletedDate IS NULL

"Project ID [id], revision [rev] is due soon"



But this is because I work with a system of separate work units.. if Rev2 would only ever be added if rev1 is already completed, then yep - a max(rev no) can be used. Might be better to use completed date (and an added date) anyway so you can get stats of e.g. how long stuff takes, how ahead of schedule you perform, or how late you are running etc... Over time this table might grow quite large, and having the DB calculate a group/max is more intensive than a simple filter (complete date is null). THe other point of note is that the completedate route can support either rolling or separate units.. max rev can only support rolling revisions

 

[Arg81]

I've set my program to look at the grid, and if no Feedback has been added to a Response then the user cannot add a new Revision - in that sense yes the revision has to be completed.

The rows I have in that table are;
WorkID (from parent table)
RevID (auto increment for each WorkID, starting at 1)
ResponseHeader
Response
ResponseDate
FeedbackHeader
Feedback
FeedbackDate
nextRevDueDate

Basically the headers are there as these are viewed in the grid, it makes a quick and simple view to look at to see what work has been done, and what's needed on each revision. The grid then can be expanded to view all the details in greater depth.

This isn't an urgent requirement, it's a mod I'd like to add to my new version because I'm always getting users whinge about they aren't sure which of their projects needs working on and I thought this would be the simple solution!

 

[cjard]

So the presence/absence of feedback is evidence of the "Completion" state of the revision?

SELECT ... FROM ... WHERE date >= now and date <= dateadd(day,7,now) AND Feedback IS NULL

?


Find all works due in the next week wehre no feedback has been left.. It *seems* to fit your requirement, seeing as there can not be 2 revisions both with null feedback..

 

[Arg81]

But if the feedback is null, there is no nextRevDueDate - this is set once the account manager gets the feedback and enters it.

Our Account Managers are a bit of a pain with this system. they are suppose to fill in Feedback as soon as they get it, even if it requires not having to start a new revision - in this case it would be Feedback "No more work to be done", no nextRevDueDate set, and then them closing the Project (basically changing a field from 1 to 2 in the parent table)