1

I have a table that shows the entry and exit of items into the warehouse. The Camera 1 and Camera 2 document the entry time and exit time respectively of that item. The cameras then classify the item as it enters and leaves the checkpoint with the help of lasers. Eg: Big box: Class 5, Medium Box: Class 3, Small Box: Class 2.

Sometimes, the cameras classification doesn't match each other. Eg: Classification at entry can be Medium box and on exit can be Small box.

I need to find the number of transactions where the class didn't match for the same TransactionDetail and then a percentage of those class mismatches against all the transaction for a certain time range.

My table looks somewhat like this:

---------------------------------------------------------------------------
| AVDetailID | TransDetailID | AVClassID | CamID | CreatedDate            |
---------------------------------------------------------------------------
| 20101522   | 54125478      | 5         | 1     | 2017-05-08 10:15:01:560|
| 20101523   | 54125478      | 5         | 2     | 2017-05-08 10:15:01:620|
| 20101524   | 54125479      | 3         | 1     | 2017-05-08 10:15:03:120|
| 20101525   | 54125479      | 2         | 2     | 2017-05-08 10:15:03:860|
| 20101526   | 54125480      | 4         | 1     | 2017-05-08 10:15:06:330|
| 20101527   | 54125480      | 4         | 2     | 2017-05-08 10:15:06:850|
---------------------------------------------------------------------------

So, in the above case the class changes from 3 to 2 in record 3 and 4. That is one transaction where the class changed. I need to get a percentage of all transactions that where the class changed between each cameras.

The code I tried so far, unsuccessfully is:

SELECT  
    COUNT(TransDetailId)

FROM 
    [AVTransDetail]
WHERE 
    ((SELECT AVCClassId WHERE CamId = 1) <> (SELECT AVCClassId WHERE DetectionZoneId = 2))
    AND CreatedDate >= '2017-04-01'
    AND CreatedDAte <= '2017-04-07'
GROUP BY 
    TransDetailId
halfer
  • 19,824
  • 17
  • 99
  • 186
user1777929
  • 777
  • 1
  • 10
  • 28

1 Answers1

1

You can try to join the table on itself like this:

SELECT  tdBefore.TransDetailId
FROM    AVTransDetail AS tdBefore
INNER JOIN AVTransDetail AS tdAfter
    ON tdBefore.TransDetailID = tdAfter.TransDetailID 
    AND tdBefore.CamID = 1
    AND tdAfter.CamID = 2
WHERE   tdBefore.AVClassID <> tdAfter.AVClassID 
AND tdBefore.CreatedDate >= '2017-04-01' 
AND tdAfter.CreatedDate <= '2017-04-07'

Then to get the percentage:

DECLARE @MinDate DATE = '20170401',
        @MaxDate DATE = '20170407';

SELECT  tdBefore.TransDetailId,
        COUNT(tdAfter.TransDetailID) OVER() AS NumDifferent,
        ((CONVERT(DECIMAL(3, 2), COUNT(tdAfter.TransDetailID) OVER())) / allRecords.Count) * 100 AS DiffPercent,
FROM    AVTransDetail AS tdBefore
INNER JOIN AVTransDetail AS tdAfter
    ON tdBefore.TransDetailID = tdAfter.TransDetailID 
    AND tdBefore.CamID = 1
    AND tdBefore.CamID = 2
CROSS APPLY
(
    SELECT COUNT(*) AS [Count]
    FROM AVTransDetail
    WHERE tdBefore.CreatedDate >= @MinDate
        AND tdAfter.CreatedDate <= @MaxDate
) AS allRecords
WHERE   tdBefore.AVClassID <> tdAfter.AVClassID 
        AND tdBefore.CreatedDate >= @MinDate
        AND tdAfter.CreatedDate <= @MaxDate
Alex
  • 276
  • 2
  • 7
  • Got a Ambiguous column name 'CreatedDate' error message. – user1777929 May 08 '17 at 20:30
  • Forgot to alias the columns... fixed it – Alex May 08 '17 at 20:54
  • Thank you Alex. But I'm not sure why, when I run that code (with the aliases) I get no results. Zero rows. No results at all. Only the headers. No values below them. – user1777929 May 08 '17 at 21:14
  • Even the earlier code where you only joined the table, I ran the code too and got no records. _____ I removed the extra comma before FROM. – user1777929 May 08 '17 at 21:23
  • The dates I used were the two from your example, which are in April. The data you have is all from 8th May, is that all the data you have? – Alex May 08 '17 at 21:52
  • No. I have thousands of records for that date range. So, no, it can't be the date range. I even ran a simple SELECT Statement to pull up those details and got Top 1000 records in a few seconds. – user1777929 May 08 '17 at 21:58
  • I see my mistake...I was checking that `tdBefore.CamID = 1` then `tdBefore.CamID = 2` - That was a typo and should have been `tdAfter.CamID = 2` – Alex May 08 '17 at 22:15
  • Yes. The brought results. However, I get two columns 1)TransDetailID (which shows a list of TransDetailIDs and 2) DiffPercent which only has 0s__________ What I need is a total count of the TransDetailIDs that are mismatched and then the total percentage of mismatched IDs compared to the Total TransDetailIDs for that date range.____ Basically, what percentage of TransDetailIDs that were mismatched. – user1777929 May 08 '17 at 22:51
  • So, I've replaced the first line with SELECT COUNT(tdBefore.TransDetailId) TD -- That gave me the total count of the TransDetailIDs that were mismatched. I also need a column for the percentage of those mismatched IDs vs the total number of TransDetailIDs for that date range. How do I get that column? I've tried.... ,SUM((COUNT(*) OVER() / allRecords.Count) * 100) AS DiffPercent – user1777929 May 08 '17 at 23:23
  • You need to `CONVERT` the result to a decimal for it to work. I've added this to my answer – Alex May 09 '17 at 06:44