question

farefin avatar image
farefin asked

SQL Query returning in correct result

Hi All,


Please help me here.


I run this sql query and below is the result:

*******************************************************************************************************8

select distinct productiondate,CostCenterKey, adr.AEMainCategoryKey,

Sum(adr.DurationDayFrac*adr.FixedCashCostAE) over(partition by AEMainCategoryKey) / Sum(adr.DurationDayFrac*adr.FixedCashCostAE) over() AELossMagnitude

FROM [OperationsDB].[Analysis].[AEDailyReportings] adr

WHERE adr.DurationDayFrac != 0

AND adr.ProductionDate <= getdate()

AND adr.ProductionDate >= '2017-01-01'

AND adr.SiteName in ('Ludwigshafen','Schwarzheide','Antwerpen')

AND AreaIsCurrent = 'true'

and productiondate = '2022-01-01'

and CostCenterKey = '100030'

group by productiondate,AEMainCategoryKey,CostCenterKey,DurationDayFrac,FixedCashCostAE

******************************************************************************************************

Result of the highlighted query:

1666963750739.png


But i have to remove the 2 filters productiondate = '2022-01-01' and CostCenterKey = '100030' to make a generic query.


So now when i execute the below query the results are not matching.

*****************************************************************************

select productiondate,CostCenterKey,AEMainCategoryKey,AELossMagnitude

from

(

select distinct productiondate,CostCenterKey, adr.AEMainCategoryKey,

Sum(adr.DurationDayFrac*adr.FixedCashCostAE) over(partition by AEMainCategoryKey)*100 / Sum(adr.DurationDayFrac*adr.FixedCashCostAE) over() AELossMagnitude

FROM [OperationsDB].[Analysis].[AEDailyReportings] adr

WHERE adr.DurationDayFrac != 0

AND adr.ProductionDate <= getdate()

AND adr.ProductionDate >= '2017-01-01'

AND adr.SiteName in ('Ludwigshafen','Schwarzheide','Antwerpen')

AND AreaIsCurrent = 'true'

group by productiondate,AEMainCategoryKey,CostCenterKey,DurationDayFrac,FixedCashCostAE

) a

where a.productiondate = '2022-01-01'

and a.CostCenterKey = '100030'

*****************************************************************************


Result of the above query"

1666962793080.png


Please see below.


Please suggest me where am i going wrong.


Thanks

Faizan

sqlserver2012sqlquerysqlerrorsqlteam
1666962793080.png (52.8 KiB)
1666963750739.png (59.6 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

Ummm... according to the two copies of the code you provided, you didn't actually remove the following code...

where a.productiondate = '2022-01-01'
and a.CostCenterKey = '100030'

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.