By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,249 Members | 1,614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,249 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT     TOP 100 PERCENT *, DATEDIFF(Minute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(Minute, StartTime, EndTime) 
  2.                       / 60.0 - CreditHours AS TotalLeft
  3. FROM         TSheet ts1
  4. WHERE     (Approved = 1) AND (WorkDate >=
  5.                           (SELECT     TOP 1 WorkDate
  6.                             FROM          TSheet ts2
  7.                             WHERE      ((ts2.WorkType = 3) AND (ts1.udfID = ts2.udfID))
  8.                             ORDER BY ts2.WorkDate DESC))
  9. 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...
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT     TOP 100 PERCENT *
  3. FROM         (SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
  4.                                               / 60.0 - CreditHours AS TotalLeft
  5.                        FROM          TSheet ts1
  6.                        WHERE      (Approved = 1) AND (WorkDate >=
  7.                                                   (SELECT     TOP 1 WorkDate
  8.                                                     FROM          TSheet ts2
  9.                                                     WHERE      WorkType <= 4 AND WorkType != 3 AND ts1.udfID = ts2.udfID
  10.                                                     ORDER BY WorkDate ASC))
  11.                        UNION
  12.                        SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
  13.                                              / 60.0 - CreditHours AS TotalLeft
  14.                        FROM         TSheet ts1
  15.                        WHERE     (Approved = 1) AND (WorkDate >=
  16.                                                  (SELECT     TOP 1 WorkDate
  17.                                                    FROM          TSheet ts2
  18.                                                    WHERE      WorkType = 3 AND ts1.udfID = ts2.udfID
  19.                                                    ORDER BY WorkDate DESC))) tUnion
  20. ORDER BY WorkDate
  21.  
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.
Feb 8 '08 #1
Share this Question
Share on Google+
3 Replies


Jim Doherty
Expert 100+
P: 897
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 :)
Feb 9 '08 #2

P: 2
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.
Feb 11 '08 #3

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT     TOP 100 PERCENT *
  2. FROM         (SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
  3.                                               / 60.0 - CreditHours AS TotalLeft
  4.                        FROM          TSheet ts1
  5.                        WHERE      (Approved = 1) AND (WorkDate >=
  6.                                                   (SELECT     TOP 1 WorkDate
  7.                                                     FROM          TSheet ts2
  8.                                                     WHERE      WorkType <= 4 AND WorkType != 3 AND ts1.udfID = ts2.udfID
  9.                                                     ORDER BY WorkDate DESC))
  10.                        UNION
  11.                        SELECT     TOP 100 PERCENT *, DATEDIFF(MINute, StartTime, EndTime) / 60.0 AS Hours, DATEDIFF(MINute, StartTime, EndTime) 
  12.                                              / 60.0 - CreditHours AS TotalLeft
  13.                        FROM         TSheet ts1
  14.                        WHERE     (Approved = 1) AND (WorkDate >=
  15.                                                  (SELECT     TOP 1 WorkDate
  16.                                                    FROM          TSheet ts2
  17.                                                    WHERE      WorkType = 3 AND ts1.udfID = ts2.udfID
  18.                                                    ORDER BY WorkDate DESC))) tUnion
  19. ORDER BY WorkDate
  20.  
  21.  
Feb 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.