Need help on T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on T-SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-13 : 12:43:39

I've tables and data as follow,

declare @tRunNo table
(idx tinyint identity(1,1), ryear char(4), rmonth char(2), runno int);
/*ryear and rmonth is unique*/
insert into @tRunno values('2010','05',1);
insert into @tRunno values('2010','06',11);

declare @t1 table
(idx smallint identity(1,1), ddte datetime, wday char(7));
insert into @t1 values('20100521','1011111');
insert into @t1 values('20100524','1000111');
insert into @t1 values('20100627','1011101');
insert into @t1 values('20100701','1010011');

1. How my SQL look's like to built output as follow,

tid             | ddte         | wday
----------------------------------------------------------
TR100500000002  | 5/21/2010    | 1011111
TR100500000003  | 5/24/2010    | 1000111
TR100600000012  | 6/27/2010    | 1011101
TR100700000001  | 7/1/2010     | 1011101

*tid value is generated from row in @tRunNo based on ddte

2. After output above built, the current @tRunNo as follow

ryear   | rmonth   | runno
---------------------------------------
2010    | 05       | 3
2010    | 06       | 12
2010    | 07       | 1

*you'll see, if row exist, it will update runno=runno+1. if not exist, it will insert starting from 1

Really need help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-13 : 22:50:35

[code]
-- Insert tRunNo for new records
insert into @tRunNo (ryear, rmonth, runno)
select year(ddte), right('0' + convert(varchar(2), month(ddte)), 2), 0
from @t1
where not exists
(
select *
from @tRunNo x
where x.ryear = year(ddte)
and x.rmonth = month(ddte)
)

select tid = 'TR' + right(r.ryear, 2) + r.rmonth + right('00000000' + convert(varchar(10), r.runno + t.row_no), 8),
t.ddte, t.wday
from (
select ddte, wday,
row_no = row_number() over (partition by dateadd(month, datediff(month, 0, ddte), 0)
order by ddte)
from @t1
) t
inner join @tRunNo r on year(t.ddte) = r.ryear
and month(t.ddte) = r.rmonth

-- Update tRunNo
update r
set runno = r.runno + cnt
from @tRunNo r
inner join
(
select ryear = year(ddte), rmonth = month(ddte), cnt = count(*)
from @t1
group by year(ddte), month(ddte)
) t on r.ryear = t.ryear
and r.rmonth = t.rmonth
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-13 : 23:29:26

tq sir. you're my inspiration

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources