Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: Subtracting records from the same column

    Hello,

    Following is the table and data inserted

    CREATE TABLE [dbo].[test](
    [vndid] [int] NULL,
    [vndname] [varchar](50) NULL,
    [invdate] [datetime] NULL,
    [invamt] [numeric](10, 2) NULL
    ) ON [PRIMARY]

    GO
    insert into test(vndid,vndname,invdate,invamt)
    values ('1','walmart','08-29-2008','525.0'),
    ('2','walmart','08-29-2008','540.0'),
    ('3','cubs','09-15-2009','600'),
    ('4','cubs','09-15-2009','700'),
    ('5', 'target','09-25-2010','800'),
    ('6','walgreens','05-24-2011','755.0'),
    ('7','macy','04-06-2006','625.0'),
    ('8','macy','04-06-2006','630.0')

    I need to check for amounts on same day to same vendor with a difference of few dollars only between 5 and 30

    this is what I have been trying

    select vndname,invdate,COUNT(*) from test where exists ( select (a.invamt-b.invamt)as value ,a.vndid,a.vndname
    from test a join test b on a.vndname=b.vndname where
    (a.invamt-b.invamt) between 5 and 30 )
    group by vndname,invdate having COUNT(*)>1


    I tried with an exists condition; but it also shows records which occurs on the same day and same vendor but the difference is more then 30$(where as I want amounts with the diff between 5 and 30)

    Please let me know where am I going wrong.

    Thank you

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You were nearly there.

    You had to compare different records (different vndid) but with the same vndname AND (date part of the) invdate.
    By not using ABS (a.invamt - b.invamt), but (a.invamt - b.invamt) between 5 and 30, you filter out doubles: record 1 - record 2 = 5, record 2 - record 1 = -5,
    Code:
    select vndname, invdate, COUNT(*) 
    from test 
    where exists	(select a.vndname
    			,a.invdate
    			,(a.invamt - b.invamt) as valueDiff
    		from test a 
    			join test b on 
    				a.vndname = b.vndname and
    				DATEDIFF(D, 0, a.invdate) = DATEDIFF(D, 0, b.invdate) and
    				a.vndid <> b.vndid
    		where (a.invamt - b.invamt) between 5 and 30 
    		)
    group by vndname, invdate 
    having COUNT(*) > 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Strictly speaking, a.vndid <> b.vndid is not needed. In this case (a.invamt - b.invamt) will be 0 and the WHERE clause (a.invamt - b.invamt) between 5 and 30 will filter them out.

    But it is much clearer to the reader that you are comparing different records from the same vendor with the same date.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by Wim View Post
    Strictly speaking, a.vndid <> b.vndid is not needed. In this case (a.invamt - b.invamt) will be 0 and the WHERE clause (a.invamt - b.invamt) between 5 and 30 will filter them out.

    But it is much clearer to the reader that you are comparing different records from the same vendor with the same date.
    thats nice explanation Wim Great
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  5. #5
    Join Date
    Jul 2010
    Posts
    10
    Thank you Wim!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •