Need help to built 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 to built T-SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-15 : 02:47:07

I've tables, and rows as follow,

declare @timerH table
(idx smallint,troutehidx smallint,seq tinyint,dprtweekday char(7));
/*idx is a primary key*/
/*Combination of troutehidx, and seq is a unique*/

insert into @timerH values(2,1,1,'1011101');
insert into @timerH values(7,2,1,'1111101');
insert into @timerH values(8,2,2,'0000011');

declare @timerD table
(idx smallint,ttimerHidx smallint,dprttime smalldatetime);
/*idx is a primary key*/
/*ttimerHidx is a FK, and refer to @timerH(idx)*/

insert into @timerD values(2,2,'19000101 10:30');
insert into @timerD values(9,7,'19000101 09:00');
insert into @timerD values(10,7,'19000101 14:30');
insert into @timerD values(11,8,'19000101 11:00');
insert into @timerD values(12,8,'19000101 16:30');

/*dprtweekday is a weekday*/
/*if 1011101, means Sunday, Tuesday, Wednesday, Thursday, and Saturday*/
/*if 1111100, means Sunday, Monday, Tuesday, Wednesday, Thursday, and Friday*/
/*
1. Im looking for help to built T-SQL to generate trip scheduling row into #tripH, and #tripD
between 18 Mar 2010 to 21 April 2010
2. My tables as follow
*/
CREATE TABLE #tripH
(idx smallint identity(1,1),troutehidx smallint,
seq tinyint,dprtdte smalldatetime)

CREATE TABLE #tripD
(idx smallint identity(1,1),ttripHidx smallint,dprttime smalldatetime)
/*ttripHidx is a FK and refer to #tripH(idx)*/

declare @sDte smalldatetime
set @sDte='3/18/2010'
declare @eDte smalldatetime
set @eDte='4/21/2010'
/*sDte is a Start Date. eDte is a End Date*/
/*sDte and eDte will become as paramater, and always change*/
/*How T-SQL look's like?*/

drop table #tripH,#tripD

/*The final results as follow. You can see, dprtdate generated based on dprtweekday in @timerH

#tripH
idx   | troutehidx    | seq    | dprtdte
---------------------------------------------
1       1               1        3/18/2010
2       1               1        3/20/2010
3       1               1        3/21/2010
....
....
44      2               1        3/18/2010
45      2               1        3/20/2010
46      2               1        3/21/2010
....
58      2               2        3/19/2010
59      2               2        3/20/2010
....
....

#tripD
idx   | ttripHidx    | dprttime
---------------------------------------------
1       1              1900-01-01 10:30:00
2       2              1900-01-01 10:30:00  
3       3              1900-01-01 10:30:00
....
....    44             1900-01-01 09:00:00
....    44             1900-01-01 14:30:00
....    45             1900-01-01 09:00:00
....    45             1900-01-01 14:30:00
....    46             1900-01-01 09:00:00
....    46             1900-01-01 14:30:00
....
....    58             1900-01-01 11:00:00
....    58             1900-01-01 16:30:00
....    59             1900-01-01 11:00:00
....    59             1900-01-01 16:30:00
*/

I hope somebody can help me to built the T-SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-15 : 09:55:21

[code]
select idx = row_number() over (order by troutehidx, seq, dprtdte),
troutehidx, seq, dprtdte
from
(
select troutehidx, seq, dprtweekday,
dprtdte = dateadd(day, n, @sDte)
from @timerH h
cross join
(
select n = 0 union all select n = 1 union all select n = 2 union all
select n = 3 union all select n = 4 union all select n = 5 union all
select n = 6
) n
where dateadd(day, n, @sDte) <= @eDte
) h
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1'
order by troutehidx, seq, dprtdte
[/code]

i don't understand what do you want with tripD. Can you please explain how do get to those result ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:27:11

can you explain how idx started with 44 for troutehidx =2 group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-15 : 11:04:24

Based on your code, me execute as follow,

select	idx	= row_number() over (order by troutehidx, seq, dprtdte),
	troutehidx, seq, dprtdte
from	
(
	select	troutehidx, seq, dprtweekday, 
		dprtdte	= dateadd(day, n, @sDte)
	from	@timerH h
		cross join
		(
			select	n = 0	union all	select	n = 1	union all	select	n = 2	union all
			select	n = 3	union all	select	n = 4	union all	select	n = 5	union all
			select	n = 6
		) n
	where	dateadd(day, n, @sDte)	<= @eDte
) h
where	substring(dprtweekday, datepart(weekday, dprtdte), 1)	= '1'
order by troutehidx, seq, dprtdte

The results as follow,

idx | troutehidx | seq  | dprtdte
-----------------------------------------------
1	1	   1	2010-03-18 00:00:00
2	1	   1	2010-03-20 00:00:00
3	1	   1	2010-03-21 00:00:00
4	1	   1	2010-03-23 00:00:00
5	1	   1	2010-03-24 00:00:00
6	2	   1	2010-03-18 00:00:00
7	2	   1	2010-03-20 00:00:00
8	2	   1	2010-03-21 00:00:00
9	2	   1	2010-03-22 00:00:00
10	2	   1	2010-03-23 00:00:00
11	2	   1	2010-03-24 00:00:00
12	2	   2	2010-03-19 00:00:00
13	2	   2	2010-03-20 00:00:00

The result above with no time. Time for each row in @timerH is in @timerD. The relationship between @timerH and @timerD is a 1 to many.

If me execute as follow,

select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx

The result shown as follow,

idx | troutehidx | seq | dprtweekday | dprttime
----------------------------------------------------------
2	1	   1	 1011101	1900-01-01 10:30:00
7	2	   1	 1111101	1900-01-01 09:00:00
7	2	   1	 1111101	1900-01-01 14:30:00
8	2	   2	 0000011	1900-01-01 11:00:00
8	2	   2	 0000011	1900-01-01 16:30:00

The conclusion is, i need the results as follow,

idx | troutehidx | seq | dprtdte              | dprttime
--------------------------------------------------------------------
1	1	   1	 2010-03-18 00:00:00    1900-01-01 10:30:00
2	1	   1	 2010-03-20 00:00:00    1900-01-01 10:30:00
3	1	   1	 2010-03-21 00:00:00    1900-01-01 10:30:00
4	1	   1	 2010-03-23 00:00:00    1900-01-01 10:30:00
5	1	   1	 2010-03-24 00:00:00    1900-01-01 10:30:00
6	2	   1	 2010-03-18 00:00:00    1900-01-01 09:00:00
7	2	   1	 2010-03-18 00:00:00    1900-01-01 14:30:00
8	2	   1	 2010-03-20 00:00:00    1900-01-01 09:00:00
9	2	   1	 2010-03-20 00:00:00    1900-01-01 14:30:00
10	2	   1	 2010-03-21 00:00:00    1900-01-01 09:00:00
11	2	   1	 2010-03-21 00:00:00    1900-01-01 14:30:00
12	2	   1	 2010-03-22 00:00:00    ...
...
13	2	   1	 2010-03-23 00:00:00    ...
...
14	2	   1	 2010-03-24 00:00:00    ...
...
15	2	   2	 2010-03-19 00:00:00    1900-01-01 11:00:00
16	2	   2	 2010-03-19 00:00:00    1900-01-01 16:30:00
17	2	   2	 2010-03-20 00:00:00    1900-01-01 11:00:00
18	2	   2	 2010-03-20 00:00:00    1900-01-01 16:30:00

Finally, the above result will be inserted into #tripH, and #tripD

CREATE TABLE #tripH
(idx smallint identity(1,1),troutehidx smallint,
seq tinyint,dprtdte smalldatetime)

CREATE TABLE #tripD
(idx smallint identity(1,1),ttripHidx smallint,dprttime smalldatetime)
/*ttripHidx is a FK and refer to #tripH(idx)*/

as follow,

/*The final results as follow. You can see, dprtdate generated based on dprtweekday in @timerH

#tripH /*idx is a identity(1,1)*/
idx   | troutehidx    | seq    | dprtdte
---------------------------------------------
1       1               1        3/18/2010
2       1               1        3/20/2010
3       1               1        3/21/2010
....
....
44      2               1        3/18/2010
45      2               1        3/20/2010
46      2               1        3/21/2010
....
58      2               2        3/19/2010
59      2               2        3/20/2010
....
....

#tripD /*ttripHidx is a FK, and refer to #tripH(idx)*/
idx   | ttripHidx    | dprttime
---------------------------------------------
1       1              1900-01-01 10:30:00
2       2              1900-01-01 10:30:00  
3       3              1900-01-01 10:30:00
....
....    44             1900-01-01 09:00:00
....    44             1900-01-01 14:30:00
....    45             1900-01-01 09:00:00
....    45             1900-01-01 14:30:00
....    46             1900-01-01 09:00:00
....    46             1900-01-01 14:30:00
....
....    58             1900-01-01 11:00:00
....    58             1900-01-01 16:30:00
....    59             1900-01-01 11:00:00
....    59             1900-01-01 16:30:00
*/

May be, each insert into #tripH need to stored into OUTPUT to capture the idx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:19:29

did you see my question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-15 : 12:28:45

quote:
Originally posted by visakh16

can you explain how idx started with 44 for troutehidx =2 group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi sir,

me just assumed that value. Actually, that value is generated by identity(1,1). We can ignore that value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-15 : 18:46:15

[code]
insert into #tripH(troutehidx, seq, dprtdte)
select troutehidx, seq, dprtdte
from
(
select troutehidx, seq, dprtweekday,
dprtdte = dateadd(day, n, @sDte)
from @timerH h
cross join
(
select n = 0 union all select n = 1 union all select n = 2 union all
select n = 3 union all select n = 4 union all select n = 5 union all
select n = 6
) n
where dateadd(day, n, @sDte) <= @eDte
) h
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1'
order by troutehidx, seq, dprtdte

insert into #tripD (ttripHidx, dprttime)
select ttripHidx = h.idx, t2.dprttime
from #tripH h
inner join @timerH t1 on h.troutehidx = t1.troutehidx
and h.seq = t1.troutehidx
inner join @timerD t2 on t1.idx = t2.ttimerHidx
order by h.idx, t2.dprttime
[/code]

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-16 : 13:28:57

I'm execute as follow,

select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx

The result shown as follow,

idx | troutehidx | seq | dprtweekday | dprttime
----------------------------------------------------------
2	1	   1	 1011101	1900-01-01 10:30:00
7	2	   1	 1111101	1900-01-01 09:00:00
7	2	   1	 1111101	1900-01-01 14:30:00
8	2	   2	 0000011	1900-01-01 11:00:00
8	2	   2	 0000011	1900-01-01 16:30:00

After execute as follow,

insert into #tripH(troutehidx, seq, dprtdte)
select	troutehidx, seq, dprtdte
from	
(
	select	troutehidx, seq, dprtweekday, 
		dprtdte	= dateadd(day, n, @sDte)
	from	@timerH h
		cross join
		(
			select	n = 0	union all	select	n = 1	union all	select	n = 2	union all
			select	n = 3	union all	select	n = 4	union all	select	n = 5	union all
			select	n = 6
		) n
	where	dateadd(day, n, @sDte)	<= @eDte
) h
where	substring(dprtweekday, datepart(weekday, dprtdte), 1)	= '1'
order by troutehidx, seq, dprtdte

The result in #tripH as follow,

idx |   troutehidx | seq | dprtdte
---------------------------------------------------
1	1	     1	2010-03-18 00:00:00
2	1	     1	2010-03-20 00:00:00
3	1	     1	2010-03-21 00:00:00
4	1	     1	2010-03-23 00:00:00
5	1	     1	2010-03-24 00:00:00
6	2	     1	2010-03-18 00:00:00
7	2	     1	2010-03-20 00:00:00
8	2	     1	2010-03-21 00:00:00
9	2	     1	2010-03-22 00:00:00
10	2	     1	2010-03-23 00:00:00
11	2	     1	2010-03-24 00:00:00
12	2	     2	2010-03-19 00:00:00
13	2	     2	2010-03-20 00:00:00

After execute as follow,

insert into #tripD (ttripHidx, dprttime)
select 	ttripHidx = h.idx, t2.dprttime
from 	#tripH h
	inner join @timerH t1	on 	h.troutehidx	= t1.troutehidx
				and	h.seq		= t1.troutehidx
	inner join @timerD t2	on 	t1.idx 		= t2.ttimerHidx
order by h.idx, t2.dprttime

The result in #tripD as follow,

idx | ttripHidx | dprttime
------------------------------------------
1	1	1900-01-01 10:30:00
2	2	1900-01-01 10:30:00
3	3	1900-01-01 10:30:00
4	4	1900-01-01 10:30:00
5	5	1900-01-01 10:30:00
6	12	1900-01-01 09:00:00
7	12	1900-01-01 11:00:00
8	12	1900-01-01 14:30:00
9	12	1900-01-01 16:30:00
10	13	1900-01-01 09:00:00
11	13	1900-01-01 11:00:00
12	13	1900-01-01 14:30:00
13	13	1900-01-01 16:30:00

There's some missing row in #tripD.

Where's is the #tripD for #tripH as follow,

6	2	     1	2010-03-18 00:00:00
7	2	     1	2010-03-20 00:00:00
8	2	     1	2010-03-21 00:00:00
9	2	     1	2010-03-22 00:00:00
10	2	     1	2010-03-23 00:00:00
11	2	     1	2010-03-24 00:00:00
....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-16 : 13:47:32

[code]
insert into #tripD (ttripHidx, dprttime)
select ttripHidx = h.idx, t2.dprttime
from #tripH h
inner join @timerH t1 on h.troutehidx = t1.troutehidx
and h.seq = t1.seq
inner join @timerD t2 on t1.idx = t2.ttimerHidx
order by h.idx, t2.dprttime
[/code]

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-16 : 22:47:43

Hello Khtan,
Below T-SQL look's fine

insert into #tripD (ttripHidx, dprttime)
select 	ttripHidx = h.idx, t2.dprttime
from 	#tripH h
	inner join @timerH t1	on 	h.troutehidx	= t1.troutehidx
				and	h.seq		= t1.seq
	inner join @timerD t2	on 	t1.idx 		= t2.ttimerHidx
order by h.idx, t2.dprttime

After doing verification, I need to generate trip schedule From: 18 Mar 2010 To: 21 Apr 2010

Below T-SQL

insert into #tripH(troutehidx, seq, dprtdte)
select	troutehidx, seq, dprtdte
from	
(
	select	troutehidx, seq, dprtweekday, 
		dprtdte	= dateadd(day, n, @sDte)
	from	@timerH h
		cross join
		(
			select	n = 0	union all	select	n = 1	union all	select	n = 2	union all
			select	n = 3	union all	select	n = 4	union all	select	n = 5	union all
			select	n = 6
		) n
	where	dateadd(day, n, @sDte)	<= @eDte
) h
where	substring(dprtweekday, datepart(weekday, dprtdte), 1)	= '1'
order by troutehidx, seq, dprtdte

The problem is, the result is not complete.

Currently, below is the result,


#tripH
idx | troutehidx | seq | dprtdte
----------------------------------------------------
1	1	1	2010-03-18 00:00:00
2	1	1	2010-03-20 00:00:00
3	1	1	2010-03-21 00:00:00
4	1	1	2010-03-23 00:00:00
5	1	1	2010-03-24 00:00:00
6	2	1	2010-03-18 00:00:00
7	2	1	2010-03-20 00:00:00
8	2	1	2010-03-21 00:00:00
9	2	1	2010-03-22 00:00:00
10	2	1	2010-03-23 00:00:00
11	2	1	2010-03-24 00:00:00
12	2	2	2010-03-19 00:00:00
13	2	2	2010-03-20 00:00:00

#tripD
idx | ttripHidx | dprttime
---------------------------------------------
1	1	1900-01-01 10:30:00
2	2	1900-01-01 10:30:00
3	3	1900-01-01 10:30:00
4	4	1900-01-01 10:30:00
5	5	1900-01-01 10:30:00
6	6	1900-01-01 09:00:00
7	6	1900-01-01 14:30:00
8	7	1900-01-01 09:00:00
9	7	1900-01-01 14:30:00
10	8	1900-01-01 09:00:00
11	8	1900-01-01 14:30:00
12	9	1900-01-01 09:00:00
13	9	1900-01-01 14:30:00
14	10	1900-01-01 09:00:00
15	10	1900-01-01 14:30:00
16	11	1900-01-01 09:00:00
17	11	1900-01-01 14:30:00
18	12	1900-01-01 11:00:00
19	12	1900-01-01 16:30:00
20	13	1900-01-01 11:00:00
21	13	1900-01-01 16:30:00

Based on below SQL, and result,

select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx;

idx | troutehidx | seq | dprtweekday | dprttime
----------------------------------------------------------
2	1	   1	 1011101	1900-01-01 10:30:00
7	2	   1	 1111101	1900-01-01 09:00:00
7	2	   1	 1111101	1900-01-01 14:30:00
8	2	   2	 0000011	1900-01-01 11:00:00
8	2	   2	 0000011	1900-01-01 16:30:00

For idx=2, there's no 25 Mar 2010, 27 Mar 2010, and so on till @eDte
For idx=7, there's no 25 Mar 2010, 27 Mar 2010, and so on till @eDte
For idx=8, there's no 26 Mar 2010, 27 Mar 2010, and so on till @eDte

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 12:39:28

[code]
declare @days int
select @days = datediff(day, @sDte, @eDte)

insert into #tripH(troutehidx, seq, dprtdte)
select troutehidx, seq, dprtdte
from
(
select troutehidx, seq, dprtweekday,
dprtdte = dateadd(day, NUMBER, @sDte)
from @timerH h
cross join dbo.F_TABLE_NUMBER_RANGE(0, @days)
) h
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1'
order by troutehidx, seq, dprtdte
[/code]

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-17 : 20:38:09

Sir, it's work :). Your guidance is my inspiration

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources