sqlserver!!! sybase!!!

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 28, 2009 at 10:36 pm

  #220677

  I have written the below query recently.

  create table #YTD

  (

  sID varchar(20),

  Stage varchar(30),

  year_start datetime,

  year_end datetime,

  Jan decimal(16,8) null,

  Feb decimal(16,8) null,

  Mar decimal(16,8) null,

  Apr decimal(16,8) null,

  May decimal(16,8) null,

  Jun decimal(16,8) null,

  Jul decimal(16,8) null,

  Aug decimal(16,8) null,

  Sep decimal(16,8) null,

  Oct decimal(16,8) null,

  Nov decimal(16,8) null,

  Dec decimal(16,8) null,

  MTD_Date datetime,

  MTD_return decimal(16,8) null,

  YTD_return decimal(16,8) null

  )

  GO

  Insert into #YTD (sID,Stage,year_start,year_end,MTD_Date)

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

  go

  create table #Perf

  (

  sID varchar(20),

  Stage char(1),

  PerfDate datetime,

  GrossValue decimal(16,8)

  )

  go

  insert into #Perf

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

  union all

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

  union all

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

  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

  I think there is some bug in the code. But apart from that, i have identified one new thing...Yes! I have executed the same code in sql2000 and sybase12.5.4.

  Please refer the attached document.

  I am very eager to know,

  How Sqlserver handle this Update statement?

  and

  How Sybase handle this Update statement?

  Inputs are highly appreciable!

  karthik

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 29, 2009 at 12:41 am

  #985666

  Edit: Nevermind....

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• PDreyer

  SSC Journeyman

  Points: 86

  More actions

  April 29, 2009 at 6:57 am

  #985861

  For those interested it was also posted at www.dbforums.com/sybase

  From the other threads

  CASE & UPDATE

  and

  Column,Date,Update

  It seems all you really need is a crostab query?