Column,Date,Update

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 27, 2009 at 9:16 pm

  #208880

  Hi All,

  I have one scenario.

  create table mgr

  (

  mgr varchar(20),

  stage varchar(30)

  )

  insert into mgr

  select 'TP001','P'

  union all

  select 'TP002','C'

  union all

  select 'TP003','C'

  I have written one procedure like below

  create procedure p1

  (

  @input_date datetime

  )

  as

  begin

  declare @year_st datetime,@year_end datetime

  select @year_st = '1/1' + datepart(yy,@input_date)

  select @year_end = @input_date

  ...

  ...

  end

  suppose if i give '24/apr/2009' then

  @year_st = '01/01/2009'

  @year_end = 28/apr/2009'

  i have to get the monthend date between @year_st and @year_end - current month (i.e previous month last date)

  i.e

  31/jan/2009

  28/feb/2009

  31/mar/2009

  I have one table called Perf

  create table Perf

  (

  mgrid varchar(20),

  perfdt datetime,

  grossvalue decimal(16,8)

  )

  insert into Perf

  select 'TP001','31/jan/2009',5.00

  union all

  select 'TP001','28/feb/2009',6.00

  union all

  select 'TP001','31/mar/2009',5.50

  union all

  select 'TP002','31/jan/2009',2.00

  union all

  select 'TP002','28/feb/2009',1.00

  union all

  select 'TP002','31/mar/2009',7.00

  Expected result format:

  mgrid,stage,year_st,year_end,31jan2009,28feb2009,31mar2009

  If i give jun/20/2009 as input date, then we need to add apr and may date's in the column.

  Input are welcome!

  I am trying to implement 'Tally' table logic.

  karthik

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 27, 2009 at 9:51 pm

  #984822

  I'm sorry, but I really can't follow what your are asking or what you are trying to accomplish. Nothing in your post really makes any sense to me. Maybe you should provide more details as to what you need to accomplish. Also, if you provide the DDL for the tables, sample data (in a readily consumable format), the expected results of the process you are attempting to complete based on the sample data, and the code you have currently developed, that may help in understanding your problem.

  It will also help if you could verify the version of SQL Server you are using. I have seen you asking questions at different times in both the 2000 and 2005 forums, and we need to know which version you are using to attempt to solve this problem

• Jeff Moden

  SSC Guru

  Points: 1004583

  More actions

  Follow

  April 27, 2009 at 10:48 pm

  #984832

  karthikeyan (4/27/2009)

  ---

  i have to get the monthend date between @year_st and @year_end - current month (i.e previous month last date)

  i.e

  31/jan/2009

  28/feb/2009

  31/mar/2009

  I am trying to implement 'Tally' table logic.

  I'll do the "hard" part...

  [font="Courier New"]DECLARE @Input_Date DATETIME

   SELECT @Input_Date = '24 APR 2009'

  DECLARE @Year_St  DATETIME,

          @Year_End DATETIME,

          @DateList VARCHAR(8000)

   SELECT @Year_St  = DATEADD(yy,DATEDIFF(yy,0,@Input_Date),0),

          @Year_End = @Input_Date

   SELECT @DateList = ISNULL(@DateList+',','')+REPLACE(CONVERT(CHAR(11),DATEADD(mm,t.N,@Year_St)-1,106),' ','')

     FROM dbo.Tally t

    WHERE t.N <= DATEDIFF(mm,@Year_St,@Year_End)

    PRINT @DateList[/font]

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 28, 2009 at 3:01 am

  #984909

  Thanks Jeff.

  Meantime i have developed the below code to get the desired output...

  DECLARE @Input_Date DATETIME

  SELECT @Input_Date = '28/apr/2009'

  DECLARE @Year_St DATETIME,

  @Year_End DATETIME

  SELECT @Year_St = '1/1/' + convert(char(4),datepart(yy,@Input_Date)),

  @Year_End = @Input_Date

  SELECT DATEADD(dd,-1,DATEADD(mm,N,@Year_St))

  FROM dbo.Tally

  WHERE N <= DATEDIFF(mm,@Year_St,@Year_End)

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 28, 2009 at 3:13 am

  #984920

  Lynn,

  I am using sql2000.

  I have identified the month end date for the given input range now.

  i.e input date = 28/apr/2009

  so @year_st = 01/01/2009

  @year_end = 28/apr/2009 -- given date should be considered as year end.

  month end for the given range

  31/jan/2009

  28/feb/2009

  31/mar/2009

  now

  i have to show the Perf value for the corrsponding date.

  Output format is:

  mgrid,stage,year_st, year_end, 31jan2009, 28feb2009, 31mar2009

  TP001 P 01/01/2009 28/apr/2009 value from perf table value from perf table

  value from perf table

  karthik