Syntax Help!

[allyne]

I have a stored procedure that currently looks like this:

SELECT DISTINCT
BID = InfoROAM2.dbo.Cycle_Summary.MEMBER_BID,
YTDMinutesActual = MAX
(CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200101'
THEN InfoROAM2.dbo.Cycle_Summary.AIR_TIME
ELSE 0
END+
CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200101'
THEN InfoROAM2.dbo.Cycle_Summary.LOCAL_TOLL_TIME +
InfoROAM2.dbo.Cycle_Summary.INTRASTATE_TOLL_TIME+
InfoROAM2.dbo.Cycle_Summary.INTERSTATE_TOLL_TIME
ELSE 0
END)+
MAX
(CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200102'
THEN InfoROAM2.dbo.Cycle_Summary.AIR_TIME
ELSE 0
END+
CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200102'
THEN InfoROAM2.dbo.Cycle_Summary.LOCAL_TOLL_TIME +
InfoROAM2.dbo.Cycle_Summary.INTRASTATE_TOLL_TIME+
InfoROAM2.dbo.Cycle_Summary.INTERSTATE_TOLL_TIME
ELSE 0
END)+
MAX
(CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200103'
THEN InfoROAM2.dbo.Cycle_Summary.AIR_TIME
ELSE 0
END+
CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200103'
THEN InfoROAM2.dbo.Cycle_Summary.LOCAL_TOLL_TIME +
InfoROAM2.dbo.Cycle_Summary.INTRASTATE_TOLL_TIME+
InfoROAM2.dbo.Cycle_Summary.INTERSTATE_TOLL_TIME
ELSE 0
END)+
MAX
(CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200104'
THEN InfoROAM2.dbo.Cycle_Summary.AIR_TIME
ELSE 0
END+
CASE
WHEN
InfoROAM2.dbo.Cycle_Summary.IN_OUT_COLLECT = 'I'
and InfoROAM2.dbo.Cycle_Summary.CALENDAR_MONTH = '200104'
THEN InfoROAM2.dbo.Cycle_Summary.LOCAL_TOLL_TIME +
InfoROAM2.dbo.Cycle_Summary.INTRASTATE_TOLL_TIME+
InfoROAM2.dbo.Cycle_Summary.INTERSTATE_TOLL_TIME
ELSE 0
END)
INTO tblActualYtdMinutes
FROM InfoROAM2.dbo.Cycle_Summary
GROUP BY InfoROAM2.dbo.Cycle_Summary.MEMBER_BID

Each month I need to update the YTDMinutesActual field to add the current month. There has to be a easier or better way to do this but I sure can't figure this one out. Any help would be much appreciated!

Thanks
Cathy

 

[tlbroadbent]

Cathy,

I'm trying to understand what this SP does. Can you post some sample data from InfoROAM2.dbo.Cycle_Summary along with the output from the SP? I beleive this can be greatly simplified but I want to be sure I'm not missing something. Terry

http://members.home.net/tlbroadbent/index.htm

 

[allyne]

Just figured it out. I created a table with the dates and an id number and then linked that table with the table used above this gives me the information I'm looking for. This way I don't need to update the SP. Thanks for all your help! I was making this alot harder than needed.

Cathy