How to group by using calculated column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to group by using calculated column

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-09-09 : 06:28:58

in below query ,how to group by RollingDate

select
COST_SAP_PLANT_CHR ,
'RollingDate'= CASE
WHEN KC_COST_METRICS_TIME_DATE_STAM <= getdate()
THEN DatePart(year,"KC_COST_METRICS_TIME_DATE_STAM")*100 + DatePart(month, "KC_COST_METRICS_TIME_DATE_STAM")
ELSE 19981212 END,
SUM(COST_DELAY_COST_NBR) as Delay_Cost ,
SUM(COST_PROPE_LOSS_COS) as Prop_Loss_Costs,
SUM(COST_WORK_HISTO_COS) as Work_Hist_Costs,
SUM(COST_WASTE_COST_NBR) as Waste_Costs
from COST
group by COST_SAP_PLANT_CHR,
'RollingDate'= CASE
WHEN COST_TIME_DATE_STAM <= getdate()
THEN DatePart(year,"COST_TIME_DATE_STAM")*100 + DatePart(month, "COST_TIME_DATE_STAM")

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-09 : 06:33:17

[code]
select
COST_SAP_PLANT_CHR ,
'RollingDate'= CASE
WHEN KC_COST_METRICS_TIME_DATE_STAM <= getdate()
THEN DatePart(year,"KC_COST_METRICS_TIME_DATE_STAM")*100 + DatePart(month, "KC_COST_METRICS_TIME_DATE_STAM")
ELSE 19981212 END,
SUM(COST_DELAY_COST_NBR) as Delay_Cost ,
SUM(COST_PROPE_LOSS_COS) as Prop_Loss_Costs,
SUM(COST_WORK_HISTO_COS) as Work_Hist_Costs,
SUM(COST_WASTE_COST_NBR) as Waste_Costs
from COST
group by COST_SAP_PLANT_CHR,
~~'RollingDate'=~~ CASE
WHEN COST_TIME_DATE_STAM <= getdate()
THEN DatePart(year,"COST_TIME_DATE_STAM")*100 + DatePart(month, "COST_TIME_DATE_STAM")
ELSE 19981212 END[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 13:04:24

or just


select 
COST_SAP_PLANT_CHR ,
RollingDate,
SUM(COST_DELAY_COST_NBR) as Delay_Cost ,
SUM(COST_PROPE_LOSS_COS) as Prop_Loss_Costs, 
SUM(COST_WORK_HISTO_COS) as Work_Hist_Costs,
SUM(COST_WASTE_COST_NBR) as Waste_Costs
from (SELECT *,'RollingDate'= CASE 
WHEN KC_COST_METRICS_TIME_DATE_STAM <= getdate()
THEN DatePart(year,"KC_COST_METRICS_TIME_DATE_STAM")*100 + DatePart(month, "KC_COST_METRICS_TIME_DATE_STAM")
ELSE 19981212 END FROM  COST)t
group by COST_SAP_PLANT_CHR,
RollingDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources