Query for below data

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  December 9, 2021 at 1:58 pm

  #3962009

  

  Hi

  I have below data in attached file

   

  Thanks

  • This topic was modified 2 years, 11 months ago by  jagjitsingh.
  • This topic was modified 2 years, 11 months ago by  jagjitsingh.
  • This topic was modified 2 years, 11 months ago by  jagjitsingh.

  Attachments:

  You must be logged in to view attached files.

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  December 9, 2021 at 2:13 pm

  #3962037

  jagjitsingh wrote:

  Hi

  I have below data in attached file

  Thanks

  This seems a bit terse. What are you trying to achieve?

  Note that many people (including me) choose not to open non-text attachments – so I have not looked at your spreadsheet.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  December 9, 2021 at 3:50 pm

  #3962086

  ;WITH CTE AS
  (
      SELECT t1.Code,
             t1.Name,
             t2.ItemCode ItemCode1,
             t2.[Pur Amount],
             t3.ItemCode ItemCode2,
             t3.SaleAmount
        FROM Table1 t1
        LEFT JOIN Table2 t2
               ON t1.Code = t2.Code
        LEFT JOIN Table3 t3
               ON t3.Code = t1.Code
              AND t3.ItemCode = t2.ItemCode
      UNION ALL
      SELECT t1.Code,
             t1.Name,
             NULL ItemCode,
             NULL [Pur Amount],
             t3.ItemCode,
             t3.SaleAmount
        FROM Table1 t1
        INNER JOIN Table3 t3
                ON t3.Code = t1.Code
               AND NOT EXISTS(SELECT *
                                FROM Table2 t2x 
                               WHERE t2x.ItemCode = t3.ItemCode
                                 AND t2x.Code = t3.Code)
  )
  SELECT Code,
         Name,
         ISNULL(ItemCode1,'')    ItemCode,
         ISNULL(CONVERT(varchar,[Pur Amount]),'') [Pur Amount],
         ISNULL(ItemCode2,'')    ItemCode,
         ISNULL(CONVERT(varchar,SaleAmount),'')   SaleAmount
    FROM CTE
  ORDER BY 1, 2, ISNULL(3,'ZZZ');