grab 2 sets of data from single table based on a condition

[Shukaido]

I'm a VERY novice SQL user. I've got an Access ADP project connected to an SQL 2000 Server.

My dilemma is that I have a single table that stores all data for my particular project, with links to other data tables, of course. I need to get a set of data for every client that has an entry in this table. The condition is, if an invoice entry exists, I only want records where the WorkDate is the same, or newer than the newest invoice ( invoice is WorkType of 3); If the client doesn't have an invoice yet, I need to get everything for that particular client that has a billable work type, ie. WorkType <= 4. This query is being used in a subform, and filtered based on a clientID from the parent form.
I've tried several solutions and none of them seems to give me this differentiation . I started with a subquery, I can get the appropriate information, but only for the clients who already have an invoice.

SELECT     TOP 100 PERCENT *, DATEDIFF(Minute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(Minute, StartTime, EndTime) 
                      / 60.0 - CreditHours AS TotalLeft
FROM         TSheet ts1
WHERE     (Approved = 1) AND (WorkDate >=
                          (SELECT     TOP 1 WorkDate
                            FROM          TSheet ts2
                            WHERE      ((ts2.WorkType = 3) AND (ts1.udfID = ts2.udfID))
                            ORDER BY ts2.WorkDate DESC))
ORDER BY WorkDate

I can't seem to get the appropriate information for clients who DON'T have an invoice, yet. I ended up trying a union, but that just ends up returning ALL records... I think the first query in this union needs some work.
This is what I've ended up with...

 
SELECT     TOP 100 PERCENT *
FROM         (SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
                                              / 60.0 - CreditHours AS TotalLeft
                       FROM          TSheet ts1
                       WHERE      (Approved = 1) AND (WorkDate >=
                                                  (SELECT     TOP 1 WorkDate
                                                    FROM          TSheet ts2
                                                    WHERE      WorkType <= 4 AND WorkType != 3 AND ts1.udfID = ts2.udfID
                                                    ORDER BY WorkDate ASC))
                       UNION
                       SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
                                             / 60.0 - CreditHours AS TotalLeft
                       FROM         TSheet ts1
                       WHERE     (Approved = 1) AND (WorkDate >=
                                                 (SELECT     TOP 1 WorkDate
                                                   FROM          TSheet ts2
                                                   WHERE      WorkType = 3 AND ts1.udfID = ts2.udfID
                                                   ORDER BY WorkDate DESC))) tUnion
ORDER BY WorkDate

As I said, this seems to send back all records, not just the ones I want. At this point, I'm just not sure what I even need to be looking for. Any help will be greatly appreciated.

[Jim Doherty]

In order to help can you clarify please ...which logical item of data determines a client as NOT having an invoice. WorkType of 3 determines an invoice as you mention from your post, but what determines no invoice? a null value or something??

Jim :)

[Shukaido]

There's no real "I don't have an invoice" value.
An invoice is just a line in along with the rest of the other data, Worktype <= 4 is billable, 3 is invoice for clamping calculation(s). This is part of my problem, I don't have a real easy way to say "no invoice" because I'm using this one primary table with the other tables as jus linked values for combo boxes, etc.

I do have a friend helping me work on this problem, as well, And it's possible that he's helped me get what I want with a delecate combination of subqueries and unions... I'll test it later today, and let you know if it solves it.

[amitpatel66]

There's no real "I don't have an invoice" value.
An invoice is just a line in along with the rest of the other data, Worktype <= 4 is billable, 3 is invoice for clamping calculation(s). This is part of my problem, I don't have a real easy way to say "no invoice" because I'm using this one primary table with the other tables as jus linked values for combo boxes, etc.

I do have a friend helping me work on this problem, as well, And it's possible that he's helped me get what I want with a delecate combination of subqueries and unions... I'll test it later today, and let you know if it solves it.

The problem that you are facing is due to the ASC keyword that you are using in first query of the two, so TOP 1 will take the first value (lowest) and all other workdate will be greated than the first one. You can use DESC if you want only one record and not all records.

Something like this:

[code=sql]
SELECT TOP 100 PERCENT *
FROM (SELECT TOP 100 PERCENT *, DATEDIFF(MINute , StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute , StartTime, EndTime)
/ 60.0 - CreditHours AS TotalLeft
FROM TSheet ts1
WHERE (Approved = 1) AND (WorkDate >=
(SELECT TOP 1 WorkDate
FROM TSheet ts2
WHERE WorkType <= 4 AND WorkType != 3 AND ts1.udfID = ts2.udfID
ORDER BY WorkDate DESC))
UNION
SELECT TOP 100 PERCENT *, DATEDIFF(MINute , StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute , StartTime, EndTime)
/ 60.0 - CreditHours AS TotalLeft
FROM TSheet ts1
WHERE (Approved = 1) AND (WorkDate >=
(SELECT TOP 1 WorkDate
FROM TSheet ts2
WHERE WorkType = 3 AND ts1.udfID = ts2.udfID
ORDER BY WorkDate DESC))) tUnion
ORDER BY WorkDate

[/code]