CODEWITHSUNDEEP
mysqlsql
Blackfyre
Blackfyre

Reputation: 17

SQL-Case When Issue

I am trying to sort the transaction dates into an aging policy. When LastDate has been in the location for greater than Aging Days limit policy it should show up as OverAge if not Within referring to the current date. Here is the current table:

+---------+------+----------+-------------+
|LastDate | Part | Location | Aging Days  |
+---------+------+----------+-------------+
12/1/2016   123    VVV          90
8/10/2017   444    RRR          10
8/01/2017   144    PR           21
7/15/2017   12     RRR          10

Here is the query:

select
q.lastdate, 
r.part, r.location,
a.agingpolicy as 'Aging Days'
  from opsintranexcel r (nolock)
left InventoryAging a (nolock) on r.location=a.location
left join (select part,MAX(trandate) as lastdate from opsintran group by 
    part) q on r.part=q.part

Here is the extra column I want added in:

+---------+------+----------+------------+---------+
|LastDate | Part | Location | Aging Days |   Age   |
+---------+------+----------+------------+---------+
 12/1/2016   123     VVV          90       Overage
 8/10/2017   444     RRR          10       Within 
 8/01/2017   144     PR           21       Within
 7/15/2017   12      RRR          10       Overage

I appreciate your help.

Upvotes: 1

Views: 66

Answers (3)

MKR
MKR

Reputation: 20095

You should modify your query as:

select
q.lastdate, 
r.part, r.location,
a.agingpolicy as 'Aging Days',
if(DATEDIFF(NOW(), q.lastdate)) > a.agingpolicy, 'Overage','Within') as 'Age'
from opsintranexcel r (nolock)
left InventoryAging a (nolock) on r.location=a.location
left join (select part,MAX(trandate) as lastdate from opsintran  where 
    trantype='II' and PerPost>='201601' group by part) q on r.part=q.part

Upvotes: -1

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

you can check the difference of the current date and the lastdate value if over or within the aging days

CASE WHEN DATEDIFF(NOW(), q.lastdate) > a.agingpolicy
     THEN 'Overage'
     ELSE 'Within'
 END AS age

Upvotes: 1

Dr. X
Dr. X

Reputation: 2930

I think below code will be work for you

SELECT
  q.lastdate, 
  r.part, 
  r.location,
  a.agingpolicy as 'Aging Days'
  'Age' =
     CASE
       WHEN DATEDIFF( day, q.LastDate, GETDATE() ) > a.agingpolicy THEN 'Overage'
       ELSE THEN 'Within'
     END 
FROM opsintranexcel r (nolock)
LEFT JOIN InventoryAging a (nolock) on r.location=a.location
LEFT JOIN (
    SELECT part,MAX(trandate) as lastdate 
    FROM opsintran 
    WHERE trantype='II' and PerPost>='201601' 
    GROUP BY part) q ON r.part=q.part

Upvotes: 1

Related Questions