• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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.
0
sbornstein2
Asked:
sbornstein2
  • 6
  • 5
  • 2
1 Solution
 
EmesCommented:
I Think this is what you need

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


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

select TransationType,CustomerID
from TransactionType
Where TransactionID  =  (select max(TransactionID) from TransactionType )
group by CustomerID,TransationType
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
EmesCommented:
select TransationType,CustomerID
from TransactionType
Where TransactionID  =  (select max(TransactionID)
from TransactionType
where TransationType ='check-out' )
group by CustomerID,TransationType
0
 
sbornstein2Author Commented:
and the aggregate is not working in a where clause so I assume I need some type of group by
0
 
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
0
 
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
0
 
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
0
 
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'
0
 
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'
0
 
sbornstein2Author Commented:
this is returning to me

6
7
7
7
8
8
0
 
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
0
 
EmesCommented:
take a look at my snap shot and see where we differ.


sample.bmp
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now