SQL Syntax Help on a Query

Hello all, ok I am going to explain this the best I can.  I have a table called TransactionType.  In this table I have the following fields and example records:

TransactionID         TransationType       TransactionDate      CustomerID
1                             File Upload              11/17/2008               1
2                             Check-Out               11/18/2008               1
3                             Check-In                  11/19/2008               1
4                             Check-Out               11/20/2008               1

So what I need to do is based on the MAX TransactionID for each customer, I need to check if the latest record is = Check-Out.  So this means I need to skip past the File Upload record here.  So I need to get a resultset of customerid where there last transactionid was check-out and not check-in ignoring anything like File Upload.  I hope this makes sense.  So in this case I would get 1 in my resultset but if Check-In was id 4 then it would not.
sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EmesCommented:
I Think this is what you need

select TransationType where  TransactionID          =  (select max(TransactionID))


if not please explain more
sbornstein2Author Commented:
I need to check if that max transaction id though is = check-out not looking at File Upload transaction type
EmesCommented:
sorry here is a better sql

select TransationType,CustomerID
from TransactionType
Where TransactionID  =  (select max(TransactionID) from TransactionType )
group by CustomerID,TransationType
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

EmesCommented:
select TransationType,CustomerID
from TransactionType
Where TransactionID  =  (select max(TransactionID)
from TransactionType
where TransationType ='check-out' )
group by CustomerID,TransationType
sbornstein2Author Commented:
and the aggregate is not working in a where clause so I assume I need some type of group by
digital_thoughtsCommented:
Ok, here's a sample I put together based on what I think is the requirement you have:

USE IT
CREATE TABLE #Table
(
      TransactionID INT IDENTITY(1,1),
      TransactionType VARCHAR(20),
      TransactionDate SMALLDATETIME,
      CustomerID INT
)

INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'File Upload', '11/17/2008', 1
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'Check-Out', '11/18/2008', 1
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'File Upload', '11/17/2008', 2
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'Check-In', '11/19/2008', 1
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'Check-Out', '11/18/2008', 2
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'Check-In', '11/19/2008', 2
INSERT INTO #Table (TransactionType, TransactionDate, CustomerID)
SELECT 'Check-Out', '11/20/2008', 1

SELECT
      *
FROM
      #Table A
      INNER JOIN
      (
            SELECT
                  CustomerID,
                  MAX(TransactionID) TransactionID
            FROM
                  #Table
            GROUP BY
                  CustomerID
      ) B ON B.CustomerID = A.CustomerID AND B.TransactionID = A.TransactionID
WHERE
      A.TransactionType = 'Check-Out'

DROP TABLE #Table
sbornstein2Author Commented:
close this is only pulling back the max customerid, I need it grouped so I pull back all the customer ids that have a last transaction type of Check-Out and skipping the File Upload records.  So I dont want anything that is 'Check-In' as the last record again not looking at the File Upload record if say it was the last record of a customerid set
sbornstein2Author Commented:
digital yours is pulling back all the id's  Here is an example set.

1      Check-Out      11/18/2008 11:17:37 PM          6
2      Check-In      11/19/2008 12:05:21 AM                  6
3      Check-Out      11/20/2008 12:00:00 AM          7
4      Check-In      11/21/2008 12:00:00 AM                  7
5      File Upload      11/21/2008 12:00:00 AM          7
6      Check-Out      11/21/2008 12:00:00 AM          7
7      Check-Out      11/21/2008 12:00:00 AM          8
8      File Upload      11/21/2008 12:00:00 AM          8

I should get back the following customerid's only 7 and 8 because 6 has a last one of Check-In.  Number 8 should come back because ignoring the File Upload record it is Check-Out
digital_thoughtsCommented:
Can you post the query you are using? I tested and the query I put together should only retun Customer ID records where the max Transaction ID record is a "Check-Out" record...
Now to allow Customer ID 8 to show up, a slight modification is needed:

SELECT
      *
FROM
      #Table A
      INNER JOIN
      (
            SELECT
                  CustomerID,
                  MAX(TransactionID) TransactionID
            FROM
                  #Table
            WHERE
                  TransactionType <> 'File Upload'
            GROUP BY
                  CustomerID
      ) B ON B.CustomerID = A.CustomerID AND B.TransactionID = A.TransactionID
WHERE
      A.TransactionType = 'Check-Out'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EmesCommented:
this returns a 7 and 8

SELECT
      A.CustomerID
FROM
      TransactionType A
      INNER JOIN
      (
            SELECT
                  CustomerID,
                  MAX(TransactionID) TransactionID
            FROM
                  TransactionType
            GROUP BY
                  CustomerID
      ) B ON B.CustomerID = A.CustomerID AND B.TransactionID = A.TransactionID
--having
      where A.TransationType != 'Check-In'
sbornstein2Author Commented:
this is returning to me

6
7
7
7
8
8
sbornstein2Author Commented:
I have it, this is it

SELECT
      A.CustomerID
FROM
     Customers A
      INNER JOIN
      (
            SELECT
                  CustomerID,
                  MAX(TransactionID) TransactionID
            FROM
                        Customers
                  WHERE TransactionType <> 'File Upload'
            GROUP BY
                  CustomerID
      ) B ON B.TransactionID = A.TransactionID
      where A.TransactionType = 'Check-Out'
Group By A.CustomerID
EmesCommented:
take a look at my snap shot and see where we differ.


sample.bmp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.