June 14, 2010 at 10:51 am
I've 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 @tTimerH table
(rid varchar(30), posi tinyint, busn varchar(10), tripn varchar(1),
wday char(7), stat varchar(1), cflg varchar(1), iflg varchar(1));
insert into @tTimerH values('_R100400000001','1','WKM1925','1','1101111','1','1','1');
insert into @tTimerH values('_R100600000001','1','WKM1925','1','1111111','1','1','1');
So far, i have below SQL to generate trip schedule From:30-Jun-2010 To: 1 July 2010
declare @sDte smalldatetime
set @sDte='6/30/2010'
declare @eDte smalldatetime
set @eDte='7/1/2010'
select DDate,rid,posi, busn, tripn, stat, cflg, iflg
from
(selectDDate,rid,posi, busn, tripn, wday, stat, cflg, iflg
from
(
select rid,posi, busn, tripn, wday, stat, cflg, iflg, Tally.N,
DDate = dateadd(day, Tally.N-1, @sDte)
from @tTimerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
wheresubstring(wday, datepart(weekday, DDate), 1)= '1')t1;
/*my resultset as follow*/
DDate | RID | Posi | Busn | TripN | Stat | cflg | iflg
---------------------------------------------------------------------------------------------------
2010-06-30 00:00:00_R1004000000011WKM19251111
2010-07-01 00:00:00_R1004000000011WKM19251111
2010-06-30 00:00:00_R1006000000011WKM19251111
2010-07-01 00:00:00_R1006000000011WKM19251111
I'm looking for help
1. I want every row in aobove resultset have TID column
2. This TID value generated based on resultset(DDate) and @SysRunno(runn)
3. This TID must be unique on every row
4. TID is 14 digit
5. TID format is TR<yy in DDate><mm in DDate><000000**>
6. If row not exits in @SysRunno, then insert. Runn=1
7. If row exist on @sysRunno, then update. Runn+1
So, my resultset as follow,
TID | DDate | RID | Posi | Busn | TripN | Stat | cflg | iflg
--------------------------------------------------------------------------------------------------
TR100600000012 2010-06-30 00:00:00_R1004000000011WKM19251111
TR100700000001 2010-07-01 00:00:00_R1004000000011WKM19251111
TR100600000013 2010-06-30 00:00:00_R1006000000011WKM19251111
TR100700000002 2010-07-01 00:00:00_R1006000000011WKM19251111
and my new @SysRunno as follow,
Pref | ryear | rmoth | runn
--------------------------------------------
TR 2010 05 1
TR 2010 06 13
TR 2010 07 2
Really need help to adjust my T-SQL above
June 14, 2010 at 1:08 pm
Miss.Delinda,
This does not update/insert the values into @SysRunno, but it does produce the results that you desire. You would need to dump this into a temp table and update/insert the @SysRunno table.
;WITH CTE AS
(
select DDate,rid,posi, busn, tripn, stat, cflg, iflg,
-- need the row number partitioned by year/month, ordered by rid
RN = ROW_NUMBER() OVER (PARTITION BY YEAR(DDate), MONTH(DDate) ORDER BY rid)
from
(select DDate,rid,posi, busn, tripn, wday, stat, cflg, iflg
from
(
select rid,posi, busn, tripn, wday, stat, cflg, iflg, Tally.N,
DDate = dateadd(day, Tally.N-1, @sDte)
from @tTimerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
where substring(wday, datepart(weekday, DDate), 1) = '1')t1
)
SELECT -- now build the TID column
TID = 'TR' +
RIGHT(CONVERT(varchar(4), year(DDate)),2) +
RIGHT('00' + convert(varchar(2), month(DDate)),2) +
REPLICATE('0', 6) +
RIGHT('00' + convert(varchar(2), IsNull(s.runn,0) + CTE.RN),2),
DDate, rid, posi, busn, tripn, stat, cflg, iflg
FROM CTE
LEFT JOIN @SysRunno s
ON s.Pref = 'TR'
AND s.ryear = YEAR(CTE.DDate)
AND s.rmoth = MONTH(CTE.DDate)
ORDER BY rid, DDate
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 15, 2010 at 1:29 am
Based on your guidance, below is my latest statement,
declare @tInfo table
(TID varchar(30), DDate datetime, RID varchar(30), Posi tinyint, BusN varchar(10),
TripN varchar(10), Stat varchar(1), Cflg varchar(1), Iflg varchar(1));
;WITH CTE AS
(
select DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg,
-- need the row number partitioned by year/month, ordered by rid
RN = ROW_NUMBER() OVER (PARTITION BY YEAR(DDate), MONTH(DDate) ORDER BY rid)
from
(select DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg
from
(
select rid,posi, busn, tripn, wday, stat, cflg, iflg, Tally.N,
DDate = dateadd(day, Tally.N-1, @sDte)
from (select t4.RID,t4.Posi,t4.Busn,t4.TripN,t4.WDay,t4.Stat,t4.Cflg,t4.Iflg
from
(select t1.RID from troute t1 inner join
(select a.b.value('idx[1]','smallint') as idx
from @data.nodes('data/ud') a(b))t2 on t1.idx=t2.idx)t3
inner join troutetimerh t4 on t3.RID=t4.RID) h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
where substring(wday, datepart(weekday, DDate), 1) = '1')t1
)
insert into @tInfo
SELECT -- now build the TID column
TID = 'TR' +
RIGHT(CONVERT(varchar(4), year(DDate)),2) +
RIGHT('00' + convert(varchar(2), month(DDate)),2) +
REPLICATE('0', 6) +
RIGHT('00' + convert(varchar(2), IsNull(s.runn,0) + CTE.RN),2),
DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg
FROM CTE
LEFT JOIN @SysRunno s
ON s.Pref = 'TR'
AND s.ryear = YEAR(CTE.DDate)
AND s.rmoth = MONTH(CTE.DDate)
ORDER BY rid, DDate
/*now, i've resultset with TID in temp table*/
/*below statement will do the transaction in @SysRunno*/
update t1
set runn=t1.runn+t2.runn from @SysRunno as t1
inner join
(
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo
group by left(tid,2),year(ddate),month(ddate)
) as t2
on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmonth
insert into @SysRunno(Pref,ryear,rmoth,runn)
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo as t1
where not exists(select * from @SysRunno where pref=left(tid,2) and ryear=year(ddate) and rmoth=month(ddate))
group by left(tid,2),year(ddate),month(ddate)
My question is
1. How can I prevent others transaction changing the particular value in @SysRunno untill my transaction is finish?
2. This prevention is VERY IMPORTANT to make sure, there's no duplication of TID in @tInfo, and to prevent the redundancy of row in @SysRunno like as follow
@SysRunno
Pref | ryear | rmoth | runn
-----------------------------------------
TR2010051
TR20100613
TR20107 6
TR20107 6
June 15, 2010 at 3:49 am
Try a TRANSACTION
June 15, 2010 at 8:01 pm
It's enough as follow,
set transaction isolation level repeatable read
begin tran
declare @tInfo table
(TID varchar(30), DDate datetime, RID varchar(30), Posi tinyint, BusN varchar(10),
TripN varchar(10), Stat varchar(1), Cflg varchar(1), Iflg varchar(1));
;WITH CTE AS
(
select DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg,
-- need the row number partitioned by year/month, ordered by rid
RN = ROW_NUMBER() OVER (PARTITION BY YEAR(DDate), MONTH(DDate) ORDER BY rid)
from
(select DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg
from
(
select rid,posi, busn, tripn, wday, stat, cflg, iflg, Tally.N,
DDate = dateadd(day, Tally.N-1, @sDte)
from (select t4.RID,t4.Posi,t4.Busn,t4.TripN,t4.WDay,t4.Stat,t4.Cflg,t4.Iflg
from
(select t1.RID from troute t1 inner join
(select a.b.value('idx[1]','smallint') as idx
from @data.nodes('data/ud') a(b))t2 on t1.idx=t2.idx)t3
inner join troutetimerh t4 on t3.RID=t4.RID) h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
) h
where substring(wday, datepart(weekday, DDate), 1) = '1')t1
)
insert into @tInfo
SELECT -- now build the TID column
TID = 'TR' +
RIGHT(CONVERT(varchar(4), year(DDate)),2) +
RIGHT('00' + convert(varchar(2), month(DDate)),2) +
REPLICATE('0', 6) +
RIGHT('00' + convert(varchar(2), IsNull(s.runn,0) + CTE.RN),2),
DDate, RID, Posi, BusN, TripN, Stat, Cflg, Iflg
FROM CTE
LEFT JOIN @SysRunno s
ON s.Pref = 'TR'
AND s.ryear = YEAR(CTE.DDate)
AND s.rmoth = MONTH(CTE.DDate)
ORDER BY rid, DDate
/*now, i've resultset with TID in temp table*/
/*below statement will do the transaction in @SysRunno*/
update t1
set runn=t1.runn+t2.runn from @SysRunno as t1
inner join
(
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo
group by left(tid,2),year(ddate),month(ddate)
) as t2
on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmonth
insert into @SysRunno(Pref,ryear,rmoth,runn)
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo as t1
where not exists(select * from @SysRunno where pref=left(tid,2) and ryear=year(ddate) and rmoth=month(ddate))
group by left(tid,2),year(ddate),month(ddate)
commit tran
This is my understanding,
1. There's no others transaction will change value in @SysRunno untill above transaction is finish
Need someone to confirm
June 16, 2010 at 5:44 am
miss.delinda (6/15/2010)
My question is1. How can I prevent others transaction changing the particular value in @SysRunno untill my transaction is finish?
2. This prevention is VERY IMPORTANT to make sure, there's no duplication of TID in @tInfo, and to prevent the redundancy of row in @SysRunno like as follow
Table variables are local to the batch that you're running... no other users will be able to even see it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 16, 2010 at 10:18 pm
tq sir.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply