SQL statement needed ASAP :(

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
I have to run a query but not sure how to do it...

I have a table called ProcessTable and the table looks like this:

ProcessID......ElementID.....QueueID.....StatusID....Date
----------------------------------------------------------------------
.....1................1001..............10..............1..........1/1/2000
.....2................1001..............10..............2..........1/1/2000
.....3................1001..............10..............3..........1/2/2000

.....4................1002..............10..............1..........3/3/2000
.....5................1002..............10..............2..........3/4/2000
.....6................1002..............20..............1..........3/7/2000

.....7................1003..............30..............1..........5/5/2000
.....8................1003..............30..............3..........6/6/2000

.....9................1004..............50..............1..........7/7/2000
....10...............1004..............10..............1..........8/8/2000
....11...............1004..............10..............3..........9/9/2000


ProcessID: auto-incremented
StatusID: 1-Not Started, 2- In Progress, 3-Closed

In my query I want to see all the ElementIDs that QueueID is 10 and are Closed (StatusID=3) or moved to another queue (ie: line 5 -> the queue changed from 10 to 20).

Also I want to see the beginning date and the end date...
Beginning date is the first action that QueueID=10 and
Ending that is either QueueID=10 and StatusID=3 or QueueID WAS 10 and sent to another queue;

so it will look like this:
ElementID...........StartDate..........EndDate
--------------------------------------------------
1001..................1/1/2000.............1/2/2000
1002..................3/3/2000.............3/7/2000
1004..................8/8/2000.............9/9/2000


1003 is not in the list because QueueID is not 10
1004's StartDate is the first appearance of 10 in QueueID


At this point I am clueless, any help is greatly appreciated
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I'm not sure if the break between queues can be done in a query. I got the rest of it done in two queries, but just can't see any way to do the break. Therefore, if it was me, I would do this within VBA.
I would have two recordsets. first one would be made up of two queries. First query would give a list of all queue 10 transactions. Here is the SQL:
Code:
SELECT tblProcessTable.ElementID, tblProcessTable.QueueID, IIf([statusID]="1",[statusdate],Null) AS StartDate, IIf([statusID]="3",[statusdate],Null) AS EndDate
FROM tblProcessTable
GROUP BY tblProcessTable.ElementID, tblProcessTable.QueueID, IIf([statusID]="1",[statusdate],Null), IIf([statusID]="3",[statusdate],Null)
HAVING (((tblProcessTable.QueueID)="10"));
The second query would produce one record for each ElementID within the "10" queue. Here is the SQL for that query (builds on the first query):
Code:
SELECT Query21.ElementID, Query21.QueueID, Max(Query21.StartDate) AS MaxOfStartDate, Max(Query21.EndDate) AS MaxOfEndDate
FROM Query21
GROUP BY Query21.ElementID, Query21.QueueID;
With this query in hand, the VBA code can walk through it and whenever there is a blank date, check in the original file to see if this ElementID changed queues after the start date in 10, or the end date of 10.
HTH,
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
this works for me...
Code:
SELECT
ElementID
, Min(iif(queueID = 10, [Date], null)) AS bgn_dt
, Max(PT1.Date) AS end_dt
FROM ProcessTable AS PT1
WHERE Exists (select "X" from ProcessTable where elementID = PT1.elementID and queueID = 10)
GROUP BY ElementID;

hth,
Giacomo
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Giacomo
Very nice! And yes, it works for me too, but I sure don't understand it. Yes, I've been over it a few times. Would you explain it please. Thanks!
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647

ADVERTISEMENT

Thank you guys

I appreciate it.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
hey Vic,

well I sat down to type out an explanation for you this morning and I realized that I had made an error. While my SQL works with the dataset provided I realized that if there where another elementID that was QueueID = 10 but StatusID <> 3 I would include it instead of exclude.

So I reworked my SQL to this...
Code:
SELECT 
ElementID 
, Min(iif(queueID = 10, [Date], null)) AS bgn_dt 
, Max(PT1.Date) AS end_dt 
FROM ProcessTable AS PT1 
WHERE Exists (
select "X" 
from ProcessTable as PT2
, ProcessTable as PT3
 where 
PT2.elementID = PT1.elementID 
and PT2.elementID = PT3.elementID 
and PT3.queueID = 10
and ((PT2.queueID = 10 AND PT2.statusID =3) OR PT2.queueID <> 10)
) 
GROUP BY ElementID;

Now a quick explanation… I assume that the EXISTS is what is throwing you off. An EXISTS statement is a sub-query that evaluates to True/False. In this case we only want the ElementID’s that are in QueueID 10 and Closed (statusID = 3) or were ever in QueueID 10.

Code:
select "X" 
from ProcessTable as PT2
, ProcessTable as PT3
 where 
PT2.elementID = PT1.elementID 
and PT2.elementID = PT3.elementID 
and PT3.queueID = 10
and ((PT2.queueID = 10 AND PT2.statusID =3) OR PT2.queueID <> 10)

The part of an EXISTS statement that is most confusing from a SQL perspective is that it does not matter what records are returned, but rather whether or not records are returned. In order to illustrate this I coded it as Select “X” FROM… Sometimes what I do is spell out what I am trying to achieve with the EXISTS in the SQL so I can remember it easily at a later time like this: SELECT “I want all records that were are in QueueID 10 and StatusID = 3 or moved to another QueueID” FROM …
In the SQL above I self-joined the ProcessTable pulling from PT3 only the records that were in QueueID 10, and pulling from PT2 only the records that were in QueueID 10 and Closed or not in QueueID 10 anymore.

So now what this sub-query provides is a True/False list of the records to be evaluated. The Main query then is very simple:

Code:
SELECT 
ElementID 
, Min(iif(queueID = 10, [Date], null)) AS bgn_dt 
, Max(PT1.Date) AS end_dt 
FROM ProcessTable AS PT1 
WHERE Exists (…)
GROUP BY ElementID;

It is a Totals Query where I am pulling in the MIN([DATE]) where the queueID = 10 and the MAX([DATE]) regardless of what queueID it is in.

Hope that explains it. If not you can PM me. My explanations probably aren’t the best…

Giacomo
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

Giacomo,
Thank you! Your explainations are great. I considered doing the PM, but thought that if I did not understand, it might be helpful to some others too. Not that I understand so much, but generally an explaination is good for more than just one.
I have never used the Exists before, and I think I really like it. Of course, I need to use it a few time to help get it into my thinking pattern, so when I really do need something like it, I can remember it.
Thanks again for the explaination!
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Thank you guys for all the responses. I have another question which is related to this subject, in my ProcessTable I added few more records and now it looks like this:

ProcessID......ElementID.....QueueID.....StatusID....Date
----------------------------------------------------------------------
.....1................1001..............10..............1..........1/1/2000
.....2................1001..............10..............2..........1/1/2000
.....3................1001..............10..............3..........1/2/2000

.....4................1002..............10..............1..........3/3/2000
.....5................1002..............10..............2..........3/4/2000
.....6................1002..............20..............1..........3/7/2000

.....7................1003..............30..............1..........5/5/2000
.....8................1003..............30..............3..........6/6/2000

.....9................1004..............50..............1..........7/7/2000
....10...............1004..............10..............1..........8/8/2000
....11...............1004..............10..............3..........9/9/2000
....12...............1004..............10..............1.........10/10/2000
....13...............1004..............10..............2.........11/11/2000
....14...............1004..............40..............1.........12/12/2000

so the query should return this:
ElementID...........StartDate..........EndDate
--------------------------------------------------
1001..................1/1/2000.............1/2/2000
1002..................3/3/2000.............3/7/2000
1004..................8/8/2000.............9/9/2000
1004.................10/10/2000.........12/12/2000

I am having hard time getting the correct dates when I break up 1004 into two pieces (in my actual database I might have more data that I might have to break into more than 2 pieces)

any help is appreciated.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
All of a sudden, ALL the rules have changed. Is this the final set of rules, or are they going to change again? Best to give us all the facts before asking for help.
Let us know when you have provided all the different ways this has to work, then it will be much easier to provide the help you are requesting.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Dear Vic,

Thanks for the quick reply.

I dont know if you understood my question correctly but the rules didnt really change; there is just one addition that's all. If the ElementID has multiple beginning (QueueID=10 AND StatusID=1 just like as before) and ending points (QueueID=10 AND StatusID=3 OR Ended up in a different QueueID just like as before) I would like to see the beginning and the ending dates for that multiple entries.

Thank you
 

Forum statistics

Threads
1,136,990
Messages
5,678,979
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top