locked
why query result is not showing same result RRS feed

  • Question

  • hey I have this query

    SELECT a.SITE,

     CAST (a.PART_NO AS VARCHAR) AS PART_NO,

     CASE

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 0 AND 365 THEN '0-1 YR'

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 366 AND 730 THEN '1-2 YR'

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 731 AND 1095 THEN '2-3 YR'

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 1096 AND 1460 THEN '3-4 YR'

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) >= 1461 THEN '4 + YR'

    END AS AGEING, a.TRANSACTION_ID, a.QUANTITY, a.UOM, a.UNIT_COST,

     --Modified Date : 18/10/2014 THEN   (SUM(COST_AMOUNT) * ( CURRENCY_RATE)) ELSE SUM((COST_AMOUNT)* (1.0/CURRENCY_RATE))  END

     CASE WHEN a.SITE IN ('SLV') THEN  ROUND((a.UNIT_COST * a.CURRENCY_RATE), 2)  ELSE ROUND((a.UNIT_COST * (1.0 / a.CURRENCY_RATE)), 2)

    END AS UNITCOST_$,

     CASE WHEN (ROW_NUMBER() OVER (PARTITION BY a.PART_NO, DATEDIFF(D, year(a.CREATED), year(GETDATE())) ORDER BY a.site, DATEDIFF(D, a.CREATED, GETDATE()))) = 1 THEN AVG_UNIT_COST ELSE 0

    END AS AVG_UNIT_COST,

     --Modified Date : 18/10/2014 THEN   (SUM(COST_AMOUNT) * ( CURRENCY_RATE)) ELSE SUM((COST_AMOUNT)* (1.0/CURRENCY_RATE))  END

     CASE WHEN (ROW_NUMBER() OVER (PARTITION BY a.PART_NO, DATEDIFF(D, year(a.CREATED), year(GETDATE())) ORDER BY a.site, DATEDIFF(D, a.CREATED, GETDATE()))) = 1 THEN CASE

    WHEN a.SITE IN ('SLV') THEN ROUND((a.AVG_UNIT_COST * a.CURRENCY_RATE), 2) ELSE  ROUND((a.AVG_UNIT_COST * (1.0 / a.CURRENCY_RATE)), 2)

    END ELSE 0

    END AS AVG_UNIT_COST_$,

     CAST (a.CREATED AS DATETIME) AS CREATED, DATEDIFF(D, a.CREATED, GETDATE()) AS INVENTORY_DAYS, a.ACCOUNTING_GROUP,

     a.ACCOUNTING_GROUP_DESC, a.PRODUCT_FAMILY, a.PRODUCT_FAMILY_DESC, a.PRODUCT_CODE, a.PRODUCT_CODE_DESC,

     a.COMM_GROUP_1, a.COMM_GROUP_1_DESC, a.COMM_GROUP_2, a.COMM_GROUP_2_DESC, a.ABC_CLASS, a.TYPE_DESIGNATION,

    a.Site AS PAR_SITE, a.PART_DESCRIPTION,

    CASE

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 0 AND 365 THEN (a.QUANTITY * a.UNIT_COST)

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 366 AND 730 THEN (a.QUANTITY * a.UNIT_COST) * 0.5

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 731 AND 1095 THEN (a.QUANTITY * a.UNIT_COST) * 0.3333

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) BETWEEN 1096 AND 1460 THEN (a.QUANTITY * a.UNIT_COST) * 0.1

    WHEN DATEDIFF(D, a.CREATED, GETDATE()) >= 1461 THEN 0

    END AS WRITE_DOWN_PROVISION

    from

    (select cf.part_no ,cf.TRANSACTION_ID, cf.QUANTITY, ip.UOM, cf.UNIT_COST,

     IP.ACCOUNTING_GROUP_DESC, IP.PRODUCT_FAMILY, IP.PRODUCT_FAMILY_DESC, IP.PRODUCT_CODE, IP.PRODUCT_CODE_DESC,

     IP.COMM_GROUP_1, IP.COMM_GROUP_1_DESC, IP.COMM_GROUP_2, IP.COMM_GROUP_2_DESC, IP.ABC_CLASS, IP.TYPE_DESIGNATION,

     s.Site AS PAR_SITE, IP.PART_DESCRIPTION,s.site ,IP.ACCOUNTING_GROUP,

     AVG_UNIT_COST , VALUE ,cf.CURRENCY_RATE ,

    --case when it.TRANSACTION_ID < 100928 then  convert--(datetime,convert( nvarchar(23),source) ,103)  Else

    cf.CREATED  -- End as CREATED

     from inventory_part_cost_fifo cf left join

      INVENTORY_TRANSACTION_HIST2 it on cf.TRANSACTION_ID =it.TRANSACTION_ID

       INNER JOIN

    inventory_part AS ip  ON cf.PART_NO = ip.PART_NO AND cf.Site = ip.Site  INNER JOIN

     Sites AS s  ON s.Site = cf.Site INNER JOIN  (SELECT   PART_NO,

         SUM(CAST (QUANTITY AS FLOAT) * CAST (UNIT_COST AS FLOAT)) AS VALUE,

         SUM(CAST (QUANTITY AS FLOAT) * CAST (UNIT_COST AS FLOAT)) / SUM(CAST (QUANTITY AS FLOAT)) AS AVG_UNIT_COST,     SITE

     FROM     dbo.inventory_part_cost_fifo

      WHERE    QUANTITY <> 0

      GROUP BY PART_NO, SITE) AS ipf

     ON cf.PART_NO = ipf.PART_NO   AND cf.SITE = ipf.SITE

    ) a

    When I removed red marked line and run the query, it is showing exact no of rows but result is not same

    How

    Consider above as a1 dataset and without redmark lines in query, that is a2 dataset

    Then select * from a1 except select * from a2 then it showing some rows(it should show 0 rows)

    Why

    Suppose there are two tables table1 and table2

    Select * from table1

    Or

    Select * from table1 left join select * from table2 on table1.column1 and table2.column2

    Both give same result or not. Because left join means matching and non matching rows from left side table means all rows am I correct or not


    tsrkreddy

    Thursday, June 4, 2015 10:31 AM

Answers