February 14, 2010 at 7:34 am
/*1st, me create as follow*/
create type Date from dateTime
create type Time from dateTime
/*2nd, me create as follow*/
create rule DateOnlyRule as
dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime
go
create rule TimeOnlyRule as
datediff(dd,0,@DateTime) = 0
go
/*3rd, me create as follow*/
EXEC sp_bindrule 'DateOnlyRule', 'Date'
EXEC sp_bindrule 'TimeOnlyRule', 'Time'
/*my table as follow*/
create table tripschedule
(
trnxid int identity primary key,
route varchar(30) not null,
tid char(12) not null,
tripnme varchar(100) not null,
busno varchar(10) not null,
departdate Date not null
)
ALTER TABLE [dbo].[tripschedule] ADD CONSTRAINT [tripschedule_tid] UNIQUE NONCLUSTERED
(
[tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
create table triptimer
(
trnxid int identity primary key,
tid char(12) not null,
cout varchar(10) not null,
departtime Time not null
)
ALTER TABLE [dbo].[triptimer] WITH CHECK ADD CONSTRAINT [FK_triptimer_tripschedule] FOREIGN KEY([tid])
REFERENCES [dbo].[tripschedule] ([tid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[triptimer] CHECK CONSTRAINT [FK_triptimer_tripschedule]
/*Relationship between tripschedule and triptimer is 1 to many*/
/*my parameter in XML as follow*/
<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
My question is,
1. let's say I'm schedule trip between 14 Feb 2010 (from) to 20 Feb 2010 (to).
2. if between 14 feb 2010 to 20 feb 2010 hit the departweekday, value will insert into tripschedule and triptimer
3. How my T-SQL looks like to insert value in XML as above?
Then, my result as follow
tripschedule
route | tid | tripnme | busno | departdate
-------------------------------------------------------------------
IPH-HPT t0000000001 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14
IPH-HPT t0000000002 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20
IPH-HPT t0000000003 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16
IPH-HPT t0000000004 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17
/*tid is a running no with prefix t000000000x*/
triptimer
tid | cout | departtime
------------------------------------
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM
Need help. I'm stuck 🙁
February 14, 2010 at 8:54 am
Where exactly did you get stuck?
Do you have difficulties to shred the xml data?
If so, the following might help you.
If you want us to do the complete work for you you'd need to add a lot more information:
Where and how are your xml data stored?
How do you provide start and end date for the trip?
What have you tried so far?
You also might think about what you asked for: Once we're done answering all your questions by providing the code for it, what's left for you to do other than copy and paste? How close does it come to consulting?
SELECT
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno
FROM @xml.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
SELECT
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @XML.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime')X(Y)
) Z
February 14, 2010 at 9:56 am
lmu92 (2/14/2010)
Where exactly did you get stuck?Do you have difficulties to shred the xml data?
If so, the following might help you.
If you want us to do the complete work for you you'd need to add a lot more information:
Where and how are your xml data stored?
How do you provide start and end date for the trip?
What have you tried so far?
You also might think about what you asked for: Once we're done answering all your questions by providing the code for it, what's left for you to do other than copy and paste? How close does it come to consulting?
SELECTc.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno
FROM @xml.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
SELECT
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @XML.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime')X(Y)
) Z
Using your SQL as follow,
declare @trips xml
set @trips='<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
'
I've as follow,
DECLARE @TripStart datetime
SET @TripStart='2010-02-14'--your trip start date
SELECT
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v)
SELECT
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @trips.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime') X(Y)
) Z
tripschedule
------------
IPH-HPTIPOH-HENTIAN PUTRA1TX 32692010-02-14 00:00:00.000
IPH-HPTIPOH-HENTIAN PUTRA1TX 32692010-02-20 00:00:00.000
IPH-HPTIPOH-HENTIAN PUTRA2TY 19252010-02-16 00:00:00.000
IPH-HPTIPOH-HENTIAN PUTRA2TY 19252010-02-17 00:00:00.000
triptimer
------------
TX 3269IPH9:00AM
TX 3269HPT2:50PM
TY 1925IPH10:30AM
TY 1925HPT5:00PM
How to generate tid? tid is a running no with prefix t000000000x
So, my result as follow,
tripschedule
route | tid | tripnme | busno | departdate
-------------------------------------------------------------------
IPH-HPT t0000000001 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14
IPH-HPT t0000000002 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20
IPH-HPT t0000000003 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16
IPH-HPT t0000000004 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17
/*tid is a running no with prefix t000000000x*/
triptimer
tid | cout | departtime
------------------------------------
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM
February 14, 2010 at 11:20 am
How to generate tid? tid is a running no with prefix t000000000x
It depends. You could use ROW_NUMBER and a temp table or, if you need to get the ID based on some identity values you could use the OUTPUT clause from the first insert.
February 14, 2010 at 11:57 am
lmu92 (2/14/2010)
How to generate tid? tid is a running no with prefix t000000000x
It depends. You could use ROW_NUMBER and a temp table or, if you need to get the ID based on some identity values you could use the OUTPUT clause from the first insert.
declare @trips xml
set @trips='<trips>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA1</tripnme>
<busno>TX 3269</busno>
<departweekdays>
<departweekday>1</departweekday>
<departweekday>7</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">9:00AM</departtime>
<departtime cout="HPT">2:50PM</departtime>
</departtimes>
</trip>
<trip>
<route>IPH-HPT</route>
<tripnme>IPOH-HENTIAN PUTRA2</tripnme>
<busno>TY 1925</busno>
<departweekdays>
<departweekday>3</departweekday>
<departweekday>4</departweekday>
</departweekdays>
<departtimes>
<departtime cout="IPH">10:30AM</departtime>
<departtime cout="HPT">5:00PM</departtime>
</departtimes>
</trip>
</trips>
'
DECLARE @TripStart datetime
SET @TripStart='2010-02-14'--your trip start date
select 't'+RIGHT('0000000000' + CONVERT(varchar(8), row_number() over (order by departdate desc)), 8) as TID,
[route],tripnme,busno,departdate
from
(SELECT /*row_number() over (order by busno,departdate desc) as TID,*/
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v))t1
SELECT
busno,
Node.value('(/departtime/@cout)[1]','varchar(30)') AS cout,
Node.value('(/departtime)[1]','varchar(30)') AS departtime
FROM
(SELECT X.Y.query('.') AS Node,
b.value('busno[1]','varchar(30)') AS busno
FROM @trips.nodes('trips/trip') a(b)
CROSS APPLY
b.nodes('departtimes/departtime') X(Y)
) Z
How to using TID in
select 't'+RIGHT('0000000000' + CONVERT(varchar(8), row_number() over (order by departdate desc)), 8) as TID,
[route],tripnme,busno,departdate
from
(SELECT /*row_number() over (order by busno,departdate desc) as TID,*/
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v))t1
and insert child record in triptimer? The final result as
tripschedule
route | tid | tripnme | busno | departdate
-------------------------------------------------------------------
IPH-HPT t0000000001 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-14
IPH-HPT t0000000002 IPOH-HENTIAN PUTRA1 TX 3269 2010-02-20
IPH-HPT t0000000003 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-16
IPH-HPT t0000000004 IPOH-HENTIAN PUTRA2 TY 1925 2010-02-17
/*tid is a running no with prefix t000000000x*/
triptimer
tid | cout | departtime
------------------------------------
t0000000001 IPH 9:00AM
t0000000001 HPT 2:50PM
t0000000002 IPH 9:00AM
t0000000002 HPT 2:50PM
t0000000003 IPH 10:30AM
t0000000003 HPT 5:00PM
t0000000004 IPH 10:30AM
t0000000004 HPT 5:00PM
February 14, 2010 at 1:09 pm
I'd recommend storing the data in an intermediate table and select from there.
Side note: I don't think it's necessary to quote the complete script for every reply...
February 14, 2010 at 1:26 pm
Me cannot imagine how this 2 sql statment can related.
Really looking for help
February 14, 2010 at 1:44 pm
Store the results of the following query into a temp and use that table for your inserts.
The [data] column holds the data for your second table in xml format. So you'd need to shred that before doing the insert into your second table.
SELECT
't'+RIGHT('0000000000' + CONVERT(varchar(8), row_number() over (order by DATEADD(dd,v.value('.','int') -1,@TripStart) desc)), 8) as TID,
c.value('route[1]','varchar(30)') [route],
c.value('tripnme[1]','varchar(30)') tripnme,
c.value('busno[1]','varchar(30)') busno,
c.query('departtimes/*') AS data,DATEADD(dd,v.value('.','int') -1,@TripStart) departdate
FROM @trips.nodes('trips') a(b)
CROSS APPLY
b.nodes('trip') t(c)
CROSS APPLY c.nodes('departweekdays/departweekday') u(v)
February 15, 2010 at 1:05 am
This query really new to me.
Is that ok, if me dont want to store this departtimes into triptimer table? It's look enough to store the departure date and departure time in a trip scheduling case.
Your guide, is my inspiration.
February 15, 2010 at 1:08 pm
Well, it's up to you whether you want to store the data 😉
My recommendation was to take the select I provided in my last post to fill a temp table and to insert into your two target table based on those results.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply