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-14 : 07:05:28

My tables and data as follow,

declare @SysRunno table
(Pref varchar(10), ryear char(4), rmoth char(2), runn int);
/*ryear and rmonth is unique*/
insert into @SysRunno values('TR','2010','05',1);
insert into @SysRunno values('TR','2010','06',11);

declare @tInfo table
(DDate datetime, RID varchar(30), Posi tinyint, TripN varchar(10),
BusN varchar(10), Stat varchar(1), Cflg varchar(1), Iflg varchar(1));
/*@tInfo(DDate,RID,Posi) is unique*/
insert into @tInfo values('2010-06-16 00:00:00.000','_R100400000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-06-15 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-06-16 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-06-15 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');
insert into @tInfo values('2010-06-16 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');

1. I want every row in @tInfo have TID column
2. This TID value generated based on DDate and @SysRunno(runn)
3. After TID has been generated, @SysRunno(runn) will runn+1
4. This TID must be unique on every row

So, below is my T-SQL


--Insert tRunNo for new records
insert into @SysRunno (Pref, ryear, rmoth, runn)
select	'TR', year(DDate), right('0' + convert(varchar(2), month(DDate)), 2), 0
from	@tInfo
where	not exists
	(
		select	*	
		from	@SysRunno x
		where	x.Pref='TR' and x.ryear		= year(DDate)
		and	x.rmoth	= month(DDate)
	)

select	t.DDate,
		tid	= 'TR' + right(r.ryear, 2) + r.rmoth + right('00000000' + convert(varchar(10), r.runn + t.row_no), 8),
		rid,posi,TripN,BusN,Stat, Cflg, Iflg
from	(
		select	rid,posi,TripN,BusN,DDate,Stat, Cflg, Iflg,
			row_no = row_number() over (partition by dateadd(month, datediff(month, 0, DDate), 0)
							order by DDate)
		from	@tInfo 
	) t
	inner join @SysRunno r	on	year(t.DDate)	= r.ryear
				and	month(t.DDate)	= r.rmoth

-- Update tRunNo
update	r
set	runn	= r.runn + cnt
from	@SysRunno r
	inner join
	(
		select	ryear = year(DDate), rmoth = month(DDate), cnt = count(*)
		from	@tInfo
		group by year(DDate), month(DDate)
	) t	on	r.ryear		= t.ryear
		and	r.rmoth	= t.rmoth;

/*
my result as follow,
DDate | tid | rid | posi | TripN | Busn | Stat | Cflg | iflg
------------------------------------------------------------------
2010-06-15 00:00:00.000	TR100600000012	_R100600000001	1	1	WKM1925	1	1	1
2010-06-15 00:00:00.000	TR100600000013	_R100600000002	1	19	WNR4474	1	1	1
2010-06-16 00:00:00.000	TR100600000014	_R100600000002	1	19	WNR4474	1	1	1
2010-06-16 00:00:00.000	TR100600000015	_R100600000001	1	1	WKM1925	1	1	1
2010-06-16 00:00:00.000	TR100600000016	_R100400000001	1	1	WKM1925	1	1	1

my current @sysrunno
Pref | ryear | rmoth | runn
---------------------------------
TR	2010	05	1
TR	2010	06	16
till here, my T-SQL running perfectly
*/

My problem is, if my @tInfo data as follow,


declare @tInfo table
(DDate datetime, RID varchar(30), Posi tinyint, TripN varchar(10),
BusN varchar(10), Stat varchar(1), Cflg varchar(1), Iflg varchar(1));
/*@tInfo(DDate,RID,Posi) is unique*/
insert into @tInfo values('2010-06-30 00:00:00.000','_R100400000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-01 00:00:00.000','_R100400000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-02 00:00:00.000','_R100400000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-03 00:00:00.000','_R100400000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-06-30 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-01 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-02 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-07-03 00:00:00.000','_R100600000001','1','1','WKM1925','1','1','1');
insert into @tInfo values('2010-06-30 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');
insert into @tInfo values('2010-07-01 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');
insert into @tInfo values('2010-07-02 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');
insert into @tInfo values('2010-07-03 00:00:00.000','_R100600000002','1','19','WNR4474','1','1','1');

If I'm running my T-SQL, my tid is not unique anymore and my @sysrunno also have a redundant value

How to adjust my T-SQL?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-15 : 07:38:21

Its not unique simply because you are getting duplicates. Add DISTINCT to your query:


select	distinct t.DDate,
		tid	= 'TR' + right(r.ryear, 2) + r.rmoth + right('00000000' + convert(varchar(10), r.runn + t.row_no), 8),
		rid,posi,TripN,BusN,Stat, Cflg, Iflg
from	(
		select	rid,posi,TripN,BusN,DDate,Stat, Cflg, Iflg,
			row_no = row_number() over (partition by dateadd(month, datediff(month, 0, DDate), 0)
							order by DDate)
		from	@tInfo 
	) t
	inner join @SysRunno r	on	year(t.DDate)	= r.ryear
				and	month(t.DDate)	= r.rmoth

If that doesn't give you what you want, then you will need to partition by something that allows the record to be unique.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources