0

I have table InventTrans

Columns: 

itemid, datephysical, recordid

I need to find only itemid which have date datephysical older than 01-01-2016 and not exist any newer.

I'm trying this way- but its not work correctly.

select ITEMID 
from INVENTTRANS itra
where itra.DATEPHYSICAL <= CONVERT(datetime, '2016-01-01') 
and ITEMID not in (
    select itemid 
    from INVENTTRANS 
    where itra.DATEPHYSICAL >= CONVERT(datetime, '2016-01-01')
)
group by ITEMID
ColdFire
  • 6,764
  • 6
  • 35
  • 51
ivo jedinecny
  • 3
  • 1
  • "not work correctly", in which way? Add some sample table data and the expected result. (As formatted text, not images.) – jarlh Feb 27 '18 at 10:39
  • The subquery condition looks suspicious. – jarlh Feb 27 '18 at 10:40
  • To paraphrase your query, you want all `ITEMID`s that have ***at least one row*** with a `DATEPHYSICAL <= '2016-01-01'` and ***zero rows*** with a `DATEPHYSICAL >= '2016-01-01'`? If so, what's not working for you? Error messages, unexpected results? Can you create a dummy data-set to demonstrate the issues you're experiencing, so that we can replicate them? Also, telling us the indexes that exist would help us demonstrate a permformant solution. – MatBailie Feb 27 '18 at 10:45
  • You dont need `group by` , use `Distinct` instead. What is datatype of column `itra.DATEPHYSICAL', is it datetime or varchar. Add some sample data – Ven Feb 27 '18 at 10:48
  • I've spotted the issue. The sub-query states `where itra.DATEPHYSICAL >=` which refers to the outer query. It should simply be `where DATEPHYSICAL >=`. Though the answers below a likely better approaches that yours. – MatBailie Feb 27 '18 at 10:51
  • Just read the second comment! – jarlh Feb 27 '18 at 10:52
  • I need to select only records where NOT EXISTS any record after 2016-01-01. Because: I want to delete all itemid which wasn't used last two years. – ivo jedinecny Feb 27 '18 at 10:53
  • @jarlh - Your second comment is vague, you should explicitly point out what particular aspect of the condition is suspicious, why it's suspicious, what the alternative is, etc ;) – MatBailie Feb 27 '18 at 10:53
  • @ MatBailie - exactly. Not working because results is still showing some lines with DATEPHYSICAL >= '2016-01-01'. – ivo jedinecny Feb 27 '18 at 10:57
  • @MatBailie, intentionally vague. When someone doesn't make much effort describing the problem, I don't hurry giving the correct answer. – jarlh Feb 27 '18 at 10:57
  • Thank to all for your effort - I was slow in data preparation. Sorry. – ivo jedinecny Feb 27 '18 at 11:12

3 Answers3

2

Simply use HAVING to verify the max date is before 2016-01-01.

select ITEMID 
from INVENTTRANS itra
group by ITEMID
HAVING MAX(DATEPHYSICAL) < CONVERT(datetime, '2016-01-01')
jarlh
  • 42,561
  • 8
  • 45
  • 63
1

Use a where not exists

select distinct ITEM_ID
from INVENTTRANS t1
where DATEPHYSICAL  < cast('2016-01-01' as date)
and not exists 
  (
   select 1 
   from INVENTTRANS x1 
   where x1.DATEPHYSICAL >= cast('2016-01-01' as date)
   and x1.ITEM_ID = t1.ITEM_ID
  )
JohnHC
  • 10,935
  • 1
  • 24
  • 40
-1

You need the older data from 2016-01-01 then

select ITEMID from INVENTTRANS itra where itra.DATEPHYSICAL < 
CONVERT(datetime, '2016-01-01')
vivek kumar
  • 63
  • 3