CASE & UPDATE

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 28, 2009 at 10:11 pm

  #73702

  Hi All,

  I have one scenario.

  create table #YTD

  (

  sID varchar(20),

  Stage varchar(30),

  year_start datetime,

  year_end datetime,

  Jan decimal(16,8) ,

  Feb decimal(16,8) ,

  Mar decimal(16,8) ,

  Apr decimal(16,8) ,

  May decimal(16,8) ,

  Jun decimal(16,8) ,

  Jul decimal(16,8) ,

  Aug decimal(16,8) ,

  Sep decimal(16,8) ,

  Oct decimal(16,8) ,

  Nov decimal(16,8) ,

  Dec decimal(16,8) ,

  MTD_Date datetime,

  MTD_return decimal(16,8) ,

  YTD_return decimal(16,8)

  )

  Insert into #YTD (SID,Stage,Year_Start,Year_End,MTD_Date)

  select 'TP001,'P','01/01/2009','24/apr/2009','24/apr/2009'

  I have one more table called Perf

  create table Perf

  (

  sID varchar(20),

  Stage char(1),

  PerfDate datetime,

  GrossValue decimal(16,8)

  )

  insert into Perf

  select 'TP001','P','JAN/31/2009',5

  union all

  select 'TP001','P','FEB/28/2009',4

  union all

  select 'TP001','P','MAR/31/2009',7

  I have to pupulate the above value in #YTD table.

  so i have written one update statement as below

  declare @yy int

  select @yy = datepart(yy,'01/01/2009')

  Update #YTD

  set Jan = case when datepart(mm,PerfDate) = 1

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Feb = case when datepart(mm,PerfDate) = 2

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Mar = case when datepart(mm,PerfDate) = 3

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Apr = case when datepart(mm,PerfDate) = 4

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  May = case when datepart(mm,PerfDate) = 5

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Jun = case when datepart(mm,PerfDate) = 6

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Jul = case when datepart(mm,PerfDate) = 7

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Aug = case when datepart(mm,PerfDate) = 8

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Sep = case when datepart(mm,PerfDate) = 9

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Oct = case when datepart(mm,PerfDate) = 10

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Nov = case when datepart(mm,PerfDate) = 11

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end,

  Dec = case when datepart(mm,PerfDate) = 12

  and datepart(yy,PerfDate) = @yy

  then isnull(GrossValue,0) end

  from Perf

  where #YTD.sID = Perf.sID

  and #YTD.Stage = Perf.Stage

  and PerfDate between '01/01/2009' and '24/apr/2009'

  select * from #YTD

  But the above query is just updating 'MAR' column only. Where is the problem?

  Inputs are highly appreciable!

  karthik

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  April 28, 2009 at 10:53 pm

  #985645

  Why you need #YTD at all?

  I believe Jeff Moden was trying to teach you how to do cross tab queries...

  _____________
  Code for TallyGenerator

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  April 29, 2009 at 6:21 am

  #985829

  Karthik

  I'm just curious, why do you have two different date formats?

  and PerfDate between '01/01/2009' and '24/apr/2009'

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• john.arnott

  SSChampion

  Points: 11882

  More actions

  April 29, 2009 at 10:13 am

  #986094

  Your posted code is incomplete/improperly copied (missing quote mark):

  Insert into #YTD (SID,Stage,Year_Start,Year_End,MTD_Date)

  select 'TP001,'P','01/01/2009','24/apr/2009','24/apr/2009'

  And, as previous poster points out, date formats are incompatible, so the last inserts fail. 'JAN/31/2009' won't go into the datetime field.

  So how did you even get the main select to run if the test data wasn't right?

• Goldie Lesser

  SSCertifiable

  Points: 6155

  More actions

  April 29, 2009 at 9:11 pm

  #986398

  Aside from the fact that your posted code does not work, it seems that your JOIN condition is not correct and that is what's causing your problem.

  Check out this code. You get 3 rows back instead of one.

  select *

  from #YTD

  join #Perf

  on #YTD.sID = #Perf.sID

  and #YTD.Stage = #Perf.Stage

  where PerfDate between '01/01/2009' and '4/24/2009'

  and datepart(yy,PerfDate) = 2009

  Oh, and BTW, any condition that you are checking in EVERY CASE of your CASE statement can be moved to the WHERE clause. In this case datepart(yy,PerfDate) = 2009 does not have to be repeated 12 times.

• Goldie Lesser

  SSCertifiable

  Points: 6155

  More actions

  April 29, 2009 at 9:18 pm

  #986400

  And please do not cross post.

  Please continue replies on this thread: http://www.sqlservercentral.com/Forums/Topic705480-8-1.aspx