Query

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  November 12, 2017 at 4:05 am

  #326170

  Hi

     I have 1 Table  with field Document Type  . I want those records of Customer whose Sum(Amount) of Document Type 4 - Sum(Amount) of Docutype6 > 0

  then those records of that Customer whose Document Type in (4,6) should be displayed.

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  November 12, 2017 at 4:12 am

  #1967664

  jagjitsingh - Sunday, November 12, 2017 4:05 AM

  Hi

     I have 1 Table  with field Document Type  . I want those records of Customer whose Sum(Amount) of Document Type 4 - Sum(Amount) of Docutype6 > 0

  then those records of that Customer whose Document Type in (4,6) should be displayed.

  Thanks

  Post the DDL (create table) script, sample data as an insert statement, what you have tried so far and the expected results please!
  😎

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 12, 2017 at 6:39 am

  #1967676

  Without DDL, you're probably looking for using SUM with an OVER clause. Probably inside a CTE. This is all I can guess without DDL:

  WITH CTE AS(

     SELECT *,

        SUM(CASE WHEN YT.DocType = 4 THEN YT.Amount END) OVER (PARTITION BY YT.Customer) -

       SUM(CASE WHEN YT.DocType = 6 THEN YT.Amount END) OVER (PARTITION BY YT.Customer) AS Type4Minus6

     FROM Yourtable YT

  )

  SELECT *

  FROM CTE

  WHERE Type4Minus6 > 0;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  November 12, 2017 at 9:11 pm

  #1967706

  jagjitsingh - Sunday, November 12, 2017 4:05 AM

  Hi

     I have 1 Table  with field Document Type  . I want those records of Customer whose Sum(Amount) of Document Type 4 - Sum(Amount) of Docutype6 > 0

  then those records of that Customer whose Document Type in (4,6) should be displayed.

  Thanks

  You've written out the requirements and the seem pretty simple... at least try something on your own.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)