CODEWITHSUNDEEP
sqlsql-server
elimariaaa
elimariaaa

Reputation: 866

Get all data containing 2 status but not just one of them: SQL Query

I'm working on a query where I want it to show me all the data with 2 specific status only: ORGNSCN and TRANSITRCV - and that TRANSITRCV was there first before the ORGNSCN.

See the sample data here:

enter image description here

So I should get InvoiceNumber FCI150142.

I created a query:

SELECT [InvoiceNumber], [StageID] 
FROM [CARGODB].[dbo].[BoxesUpdateLog] 
WHERE [StageID] = 'ORGNSCN' AND 
      [StageID] = 'TRANSITRCV') 
GROUP BY [InvoiceNumber], [StageID];

My thinking with above is, it will show me the all the data containing those 2 status at least (no dates first) but it displays nothing.

What am I missing?

UPDATE:

I created a query which gives me all the data with both of the status but I can't figure out how to get all the data where TRANSITRCV was entered first before ORGNSCN:

SELECT * FROM (
SELECT [InvoiceNumber], 
       MAX(Case When [StageID] = 'ORGNSCN' Then 1 else 0 end) as [ORGNSCN], 
       MAX(Case When [StageID] = 'TRANSITRCV' Then 1 else 0 end) as [TRANSITRCV]
FROM [CARGODB].[dbo].[BoxesUpdateLog] WHERE [StageID] in ( 'ORGNSCN','TRANSITRCV')
GROUP BY [InvoiceNumber]
) x
WHERE [ORGNSCN] > 0 and 
      [TRANSITRCV] > 0

UPDATE: (sample data)

RecordID    InvoiceNumber   DatePosted                  TransDate               StageID     Status
31478801    FCI150142       2018-04-18  10:30:35.000    2018-04-18 10:30:35.000 TRANSITRCV  RECEIVED
31478851    FCI150142       2018-04-18  11:33:53.330    2018-04-18 11:33:53.330 LOADCNTNR   LOADED
31488281    FCI150142       2018-04-19  09:28:41.000    2018-04-19 09:28:41.000 ORGNSCN     ORIGIN SCAN
31602071    FCI150142       2018-05-01  15:05:20.000    2018-04-19 15:05:20.000 S000000001  RECEIVED
31602883    FCI150142       2018-05-01  15:06:14.000    2018-04-25 15:06:14.000 S000000005  LOADED
31603695    FCI150142       2018-05-01  15:06:37.000    2018-04-28 15:06:37.000 S000000006  DEPARTED

Your help is highly appreciated.

Upvotes: 0

Views: 377

Answers (3)

Paras
Paras

Reputation: 1

You are using 'And' in your query after where clause , which won't be ever true. Use 'Or', or 'IN'

OR

SELECT [InvoiceNumber], [StageID] FROM [CARGODB].[dbo].[BoxesUpdateLog] WHERE [StageID] = 'ORGNSCN' OR [StageID] = 'TRANSITRCV' GROUP BY [InvoiceNumber], [StageID];

IN

SELECT [InvoiceNumber], [StageID] FROM [CARGODB].[dbo].[BoxesUpdateLog] WHERE [StageID] IN ( 'ORGNSCN' , 'TRANSITRCV') GROUP BY [InvoiceNumber], [StageID];

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

Try this Answer:

Table Schema:

DECLARE @BoxesUpdateLog TABLE(RecordId BIGINT, [InvoiceNumber] VARCHAR(15), DatePosted DATETIME, TransDate DATETIME, StageID VARCHAR(25), Status VARCHAR(25))

INSERT INTO @BoxesUpdateLog VALUES
(31478801,'FCI150142','2018-04-18  10:30:35.000','2018-04-18 10:30:35.000','TRANSITRCV','RECEIVED   ')
,(31478851,'FCI150142','2018-04-18  11:33:53.330','2018-04-18 11:33:53.330','LOADCNTNR ','LOADED        ')
,(31488281,'FCI150142','2018-04-19  09:28:41.000','2018-04-19 09:28:41.000','ORGNSCN   ','ORIGIN SCAN')
,(31602071,'FCI150142','2018-05-01  15:05:20.000','2018-04-19 15:05:20.000','S000000001','RECEIVED  ')
,(31602883,'FCI150142','2018-05-01  15:06:14.000','2018-04-25 15:06:14.000','S000000005','LOADED        ')
,(31603695,'FCI150142','2018-05-01  15:06:37.000','2018-04-28 15:06:37.000','S000000006','DEPARTED  ')

Query:

SELECT [InvoiceNumber]
FROM [CARGODB].[dbo].[BoxesUpdateLog] 
WHERE [StageID] in ( 'ORGNSCN','TRANSITRCV')
GROUP BY [InvoiceNumber]
HAVING COUNT(DISTINCT [StageID])=2
    AND MAX(CASE WHEN [StageID] = 'ORGNSCN' THEN DatePosted END) > MAX(CASE WHEN [StageID] = 'TRANSITRCV' THEN DatePosted END)

OutPut:

InvoiceNumber
FCI150142

Check the result in the #SQL Fiddle

Upvotes: 1

Paras
Paras

Reputation: 1

You can use 'DatePosted'column in ORDER BY Clause, that would lead to a sorted result set ordered by the dates these records were posted.

Upvotes: 0

Related Questions