Column,Date,Update

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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: 1003875 More actions · Answer 2

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 · Answer 3

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 · Answer 4

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