|
Home > Archive > ASE Database forum > April 2005 > incrementing variable during Select statement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
incrementing variable during Select statement
|
|
|
| I'm creating a temp. table with my data but I need to
increment the value of the last field [labor_day].
Basically, if I'm processing
'3/1/2005' - '3/4/2005', then, in #temp_table.labor_day,
'3/1' would equal 1 (1st labor day of month), '3/2' and
'3/3' would equal 0 (working_day='N' - weekend), and '3/2'
would equal 2 (2nd workday of month). What would I need to
change here for it work?
select
trans_date, sum(importe) / @cobrado_mtd_mes_act
as
"percentage",
case (select working_day from calendar where cal_date =
trans_date)
when 'Y' then @labor_day
when 'Y' then @labor_day = @labor_day +1 //doesn't
compile
when 'N' then 0
end as 'labor_day'
into #temp_table
from transfacturas t where ...
group by trans_date
| |
| Dave Speight 2005-04-07, 7:03 am |
| Make the last column with a datatype of Identity. If you
don't want to preserve this as an identity and just want it
as a numeric. Create the identity in another temp table and
then do a select into into your target table.
Hope this helps.
Dave Speight
> I'm creating a temp. table with my data but I need to
> increment the value of the last field [labor_day].
> Basically, if I'm processing
> '3/1/2005' - '3/4/2005', then, in #temp_table.labor_day,
> '3/1' would equal 1 (1st labor day of month), '3/2' and
> '3/3' would equal 0 (working_day='N' - weekend), and '3/2'
> would equal 2 (2nd workday of month). What would I need to
> change here for it work?
> select
> trans_date, sum(importe) / @cobrado_mtd_mes_act
as
> "percentage",
> case (select working_day from calendar where cal_date =
> trans_date)
> when 'Y' then @labor_day
> when 'Y' then @labor_day = @labor_day +1 //doesn't
> compile
> when 'N' then 0
> end as 'labor_day'
> into #temp_table
> from transfacturas t where ...
> group by trans_date
| |
|
| If I interpreted you request correctly, the following is an
example to calculate the workday of month for any particular
date. Holidays are not recognized.
create table #mydatetable ( CalDate datetime, DayOfMonth
int, WorkDayOfMonth int)
declare @mydate datetime
select @mydate = '03/01/2005'
declare @count int
select @count = 0
while @count < 31
begin
insert #mydatetable
select @mydate as "Date"
, datepart(day, @mydate) as "DayOfMonth"
,case
when datepart(weekday, @mydate) in (1, 7) then 0
else datepart(day, @mydate)
- (datepart(week, @mydate) - datepart(week, dateadd(day,(
(datepart(day, @mydate) -1) * -1) , @mydate)) ) * 2
end as "WorkDayOfMonth"
select @mydate = dateadd(day, 1, @mydate)
select @count = @count + 1
end
select * from #mydatetable
drop table #mydatetable
> I'm creating a temp. table with my data but I need to
> increment the value of the last field [labor_day].
> Basically, if I'm processing
> '3/1/2005' - '3/4/2005', then, in #temp_table.labor_day,
> '3/1' would equal 1 (1st labor day of month), '3/2' and
> '3/3' would equal 0 (working_day='N' - weekend), and '3/2'
> would equal 2 (2nd workday of month). What would I need to
> change here for it work?
> select
> trans_date, sum(importe) / @cobrado_mtd_mes_act
as
> "percentage",
> case (select working_day from calendar where cal_date =
> trans_date)
> when 'Y' then @labor_day
> when 'Y' then @labor_day = @labor_day +1 //doesn't
> compile
> when 'N' then 0
> end as 'labor_day'
> into #temp_table
> from transfacturas t where ...
> group by trans_date
|
|
|
|
|