Need a SQL Query to find Last/Latest Child for Each Parent item in a table

[zmbd]

nothing is easy; however, it is rarely impossible

OK,
Lets go back to the origninal post:

...The requirement is to get the latest serviced date for each car in the table ...
...largest SrvcID for a car would be the latest SrvcID for the same. Similarly I have to get largest SrvcIDs for each car in the table...

From NarenKer's last post:
We now know that [CarNo] should be unique and PK in another table.
we now know that [SrvcID] should be unique and PK in tbl_service.

Is it possible for a given [CarNo] to have the same [ServiceDT] with different [SrvceID] for each record such as in an invoice for services... say something along the lines of refueling?

_________________________ 
[CarNo] [SrvcID] [ServiceDt]
------------------------- 
CAB123  200901  01/01/2009 
CAB123  201001  01/01/2009
CAB123  201203  01/01/2009 
-------------------------

As NeoPa stated... is there the possiblity that the records are batch entered by many service stations; thus the highest [SrvcID] may not reflect the newest date?

If both are true, that the highest [SrvcID] is not related to the newest date then I suspect that you might not be able to do this reliably without some more information.

-z

[NeoPa]

@Naren
We don't want to mislead anyone. We are here to help, and if we mislead that is worse than simply not helping. It doesn't help anyone who reads it and it also makes us look incompetent. Speaking for myself, I'm not happy to confuse anyone, nor am I happy to look incompetent. It's a no-brainer then, to be helpful in the best way we can :-)

[NarenKeer]

Hi zmbd,

We now know that [CarNo] should be unique and PK in another table.
we now know that [SrvcID] should be unique and PK in tbl_service.

Yes the above is true.

Though the [SrvcID] field is getting updated by many service stations simultaniously; for a particular car, there is no possiblity that the same car is serviced in more than one service station.

However, like you said, there are scenarios where a particular car can have more than one [SrvcID] on the same day. Still, the fact is that unless the existing [SrvcID] is closed, the users cannot create a new one. And that closure is determined by the [ServiceDt], as the [ServiceDt] is when the servicing for that particular car is completed.

Hence, The client is interested in the last/latest [SrvcID]. As they just want to see when was the last time a car is last serviced and what was done in the last service.

Hope I answered all your questions.

Thanks,
Naren

[NeoPa]

NarenKeer:
Hence, The client is interested in the last/latest [SrvcID]. As they just want to see when was the last time a car is last serviced and what was done in the last service.

You're assuming that [SrvcID] is always assigned ascending numbers. As I said in post #14 :

NeoPa:
It is never recommended to rely on anything about an 'AutoNumber' field other than its uniqueness.

[NarenKeer]

Oh ok,

Now I got your point clearly. So I should use the [ServiceDt], instead of [SrvcID]

I will change it now.

[zmbd]

ServiceDT not useable

[Highligh]@NarenKeer[/Highlight]

[SeviceDT] is not reliable. In post #18 you affirm that multiple [SrvcID] can have the same [ServiceDT]

From what NeoPa has stated, [SrvcID] is also unreliable.

So trying to show how Human error can garbage up your data... let's try the following:

CAB123
Goes into SrvcStn=1 on 2002-03-02
SrvcStn_1 starts the record [SrvcID] = 101
The service is compleated the same day.
Maybe the station attendant is forgetfull...
Closes that record out on 2002-03-10

In the meantime,
CAB123
Goes into SrvcStn=2 on 2002-03-02
Now... if understand correctly, [SrvcID] = 101 is still open therefore:
SrvcStn_2 starts the record [SrvcID] = 102
The service is compleated the same day.
Maybe the station attendant is forgetfull...
Closes that record out on 2002-03-03

In the meantime,
CAB123
Goes into SrvcStn=3 on 2002-03-02
Now... if understand correctly, [SrvcID] = 103 is still open therefore:
(where'd 103 come from... see below)
SrvcStn_3 starts the record [SrvcID] = 104
The service is compleated the same day.
This attendent is on the ball and closed out the same day the service is compleated.
Closes that record out on 2002-03-02

Inattentive attendant number 1:
CAB123
Goes into SrvcStn=4 on 2002-03-01
Let's say the attendant was distracted and forgot to create the [SrvcID]
Instead he makes the entry on 2002-03-02 before service SrvcStn_3 starts their serivice
Now because the last [SrvcID] is 102 we now get [SrvcID]=103
The service is compleated the same day - 2002-03-01
ASSUME he did this entry correctly.

Inattentive attendant number 2:
CAB123
Goes into SrvcStn=5 on 2002-03-01
Let's say the attendant was distracted and forgot to create the [SrvcID]
Instead he makes the entry on 2002-03-02 after service SrvcStn_3 starts their serivice
Now... if understand correctly, [SrvcID] = 104 is still open therefore:
SrvcStn_5 starts the record [SrvcID] = 105
The service is compleated the same day - 2002-03-01
ASSUME he did this entry correctly.


To help follow things I'm going to add to fields [ID], [Station], and [StartDate] just so we can follow the above data... understanding that neither field is part of your table as given:

[ID][Station][CarNo][SrvcID][StartDate][ServiceDT]
1   4   CAB123   103   2002-03-01   2002-03-01
2   5   CAB123   105   2002-03-01   2002-03-01
3   3   CAB123   104   2002-03-02   2002-03-02
4   2   CAB123   102   2002-03-02   2002-03-03
5   1   CAB123   101   2002-03-02   2002-03-10

So the last station to service [CarNo]=CAB123 is [Station]=3; therefor, the record we want is [ID]=3 with [SrvcID]=104
We can't rely on the [ServiceDT] as that will pull [ID]=5
We can't rely on [SrvcID] as that will pull [ID]=2

I think you'll need more information to reliably pull the correct [SrvcID] for the last time a given [CarNo] is serviced.

-z

[NeoPa]

zmbd:
If both are true, that the highest [SrvcID] is not related to the newest date then I suspect that you might not be able to do this reliably without some more information.

The solution I proposed in post #6 works on the basis that no vehicle will be serviced at any service station more than once in any single day. It uses the composite of both [CarNo] and [ServiceDT] to identify a record uniquely. It seems to me that this is a viable solution to the problem, and doesn't rely on any assumptions that are not logically safe.

Considering all we really have to go on is the GROUPing, which is mandated anyway, and the aggregated field ([ServiceDT], then this will always find the (or a) record which matches the [CarNo] and the maximum value for [ServiceDT]. If that's not unique then the problem lies with the data or the question - depending on which way you choose to look at it.

[zmbd]

logic impass... we're missing some information.

NeoPa:
the solution i proposed in post #6 works on the basis that no vehicle will be serviced at any service station more than once in any single day.

That basis may prove faulty... say the "service" is nothing more than a refueling of the vehicle; thus, it could visit 100 stations within a single day.

NarenKeer:
though the ... [srvcid] field is getting updated by many service stations simultaneously; for a particular car, ...

So, as in my example data, you may have multiple records open at the same time for the same [CarNo] at multiple stations.

.... However, like you said, there are scenarios where a particular car can have more than one [srvcid] on the same day ....

and here we return the fact that any given [SrvcID] may be a 1:M with [ServiceDT] for any given car; thus, possibly invalidating NeoPa's basis for #6 as in my example data in #21 is possible.

Now we're stuck with the logic.
As NeoPa has stated: "...the problem lies with the data or the question..."

Either NeoPa or my solutions will work for a given dataset, NeoPa's perhaps is more robust; however, in this case... if a set of data as in #21 is possible, then you're request is not possible without more information.

-z

[NeoPa]

zmbd:
and here we return the fact that any given [SrvcID] may be a 1:M with [ServiceDT] for any given car; thus, possibly invalidating NeoPa's basis for #6 as in my example data in #21 is possible.

You make some very good points, however :

NeoPa:
If that's not unique then the problem lies with the data or the question - depending on which way you choose to look at it.

Maybe the value in [ServiceDT] is a Date/Time value rather a date alone. It certainly should be in the situation described. Otherwise the question is at fault rather than the solution, which is as good as you can get in the circumstances available.

[NarenKeer]

Hi zmbd,

First I think I should make my point clear on how the [ServiceDt] is determined
There is no possiblities that the [SrvcId] for a car can be created in two service stations at the same time. As the cars can be taken out of the station only after billing is done. The Service date should be updated when the billing is done.

However, there are possiblities that the same car goes out of Station1 in the morning, and it faces some problem, so on the way the same car goes in to Station2 for checkup. when done, the billing in second station will also be done in the same day. So, the same vehicle will have two SrvcIDs for the same day.

Second thing, I had a chat with the Sybase and java guys again, the sybase guy confirmed that the [SrvcID] keeps growing as a bigger number and never the latest [SrvcID] would be lesser than the old one. However, I asked him that few years down the line, this ID may grow as big as a 10digit number or more, which would be very bad to have such kind of growing number series. I suggested him to generate numbers like below from front end[yymmdd<Six_Digi t_CarNo><two_di git_growing_num ber>]

It will look like below,
20091012TN01230 1
20091012TN01230 2
20091012TN01230 3
20101124KA09770 1
20101124KA09770 2
20101011TN09770 1
20111104KA09770 1

Date - [20091012] | CarNo - [TN0123] | 2 Digit Growing Number [03]


This would make life easier for developers to identify things. They said they will get back to me after a little feasibilty study.

Hope things will work well.

[zmbd]

NarenKeer:

I'm not sure that changing the [SrvcID] format/information will help.
Knowing that the car is "locked" may be helpfull... Does the [ServiceDT] record the time as well as the date?

[NarenKeer]

zmbd,

nope, it has only date in DD/MM/YYYY format

[zmbd]

IMHO: Your data is still at fault.

Capturing the time along with the date in [ServicDT] would be more helpfull than changing the [SrvcID]. With the additional information, I think, you could then use either NeoPa or my queries to get the data ... the car certainly couldn't be in the same or two places at the same time (maybe?).

Also, if I read this correctly, your new [SrvcID] is simply a compounding of "[ServiceDT][CarNo]##"... duplicating the data you already have. This may make things better for the reporting; however, for the query not so much.

-z

[NeoPa]

Making [ServiceDt] a Date and time field might be a simpler change to make, though [SrvcID] being built to allow unique identification of a record as well as identifying both the car and the date (as you suggest) could also prove helpful.