A real Challenge, please help - 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 2000 Forums

SQL Server Development (2000)

A real Challenge, please help

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:07:03

I would like to make the following data in my table (when I specify a date range)

SID fromDate ToDate Price
1 2006/01/01 2006/1/3 100
1 2006/1/4 2006/1/5 150

into

SID Adate Price
1 2006/1/2 100
1 2006/1/3 100
1 2006/1/4 150

Because this is used in my search engine so the second table must be compiled out efficiently and in real-time.

Please help me to write a short SQL code(SQL Only) to help me to achieve this~~!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:12:12

Why would you like to omit the first date in the range of 20060101 to 20060103, and omit the last date in the range for 20060104 to 20060105?

PLEASE POST PROPER SAMPLE DATA AND PROPER EXPECTED OUTPUT BASED ON THE PROVIDED SAMPE DATA.

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:16:19

quote:
Originally posted by Peso

Why would you like to omit the first date in the range of 20060101 to 20060103, and omit the last date in the range for 20060104 to 20060105?

PLEASE POST PROPER SAMPLE DATA AND PROPER EXPECTED OUTPUT BASED ON THE PROVIDED SAMPE DATA.

Peter Larsson
Helsingborg, Sweden

Hi, Thank you for your prompt reply.
The first table is a simplified raw data and when the user specifies a data range, only those requested would be outputed, resulting in the second table. So the first date and the last date are omited to indicate that user might specify any range they desire.
Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:18:12

Shouldn't the specified from date, and the to date, be inclusive?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:28:52

What will happen when there is a gap?

SID fromDate ToDate Price
1 2006/1/11 2006/1/13 100
1 2006/1/18 2006/1/19 150

And the user choose 2006/1/5 to 2006/1/21 ?

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:31:18

quote:
Originally posted by Peso

Shouldn't the specified from date, and the to date, be inclusive?

Peter Larsson
Helsingborg, Sweden

weather the fromdate and todate are inclusive depends on the user's request. The user might only want the price from 2006/1/2-2006/1/4, as shown in this case, then I have to extract the data out from the raw data and display it properly.

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:33:59

quote:
Originally posted by Peso

What will happen when there is a gap?

SID fromDate ToDate Price
1 2006/1/11 2006/1/13 100
1 2006/1/18 2006/1/19 150

And the user choose 2006/1/5 to 2006/1/21 ?

Peter Larsson
Helsingborg, Sweden

Good question
Thanks for asking
If there is a gap then it will just display Null

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:37:23

quote:
Originally posted by wangyc77

quote:
Originally posted by Peso

What will happen when there is a gap?

SID fromDate ToDate Price
1 2006/1/11 2006/1/13 100
1 2006/1/18 2006/1/19 150

And the user choose 2006/1/5 to 2006/1/21 ?

Peter Larsson
Helsingborg, Sweden

Good question
Thanks for asking
If there is a gap then it will just display Null

it will still dislay all the dates but the price field is Null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:43:22

[code]-- prepare sample data
declare @t table (sid int, fromdate datetime, todate datetime, price money)

insert @t
select 1, '2006/1/11', '2006/1/13', 100 union all
select 1, '2006/1/18', '2006/1/19', 150

-- initialize user request
declare @fromdate datetime,
@todate datetime

select @fromdate = '2006/1/5',
@todate = '2006/1/21',
@fromdate = @fromdate + 1,
@todate = @todate - 1

-- something to start with
select t.sid,
x.date as adate,
t.price
from f_table_date(@fromdate, @todate) as x
left join @t as t on t.fromdate <= x.date and t.todate >= x.date[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:44:43

MVJ's function F_TABLE_DATE is found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 02:52:22

quote:

Originally posted by Peso

-- prepare sample data
declare @t table (sid int, fromdate datetime, todate datetime, price money)

insert	@t
select	1, '2006/1/11', '2006/1/13', 100 union all
select	1, '2006/1/18', '2006/1/19', 150

-- initialize user request
declare	@fromdate datetime,
	@todate datetime

select	@fromdate = '2006/1/5',
	@todate = '2006/1/21',
	@fromdate = @fromdate + 1,
	@todate = @todate - 1

-- something to start with
select		t.sid,
		x.date as adate,
		t.price
from		f_table_date(@fromdate, @todate) as x
left join	@t as t on t.fromdate <= x.date and t.todate >= x.date

Peter Larsson
Helsingborg, Sweden

Thank you, I will give it a quick try and see if there is anymore question

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 03:05:53

quote:

Originally posted by wangyc77

quote:

Originally posted by Peso

-- prepare sample data
declare @t table (sid int, fromdate datetime, todate datetime, price money)

insert	@t
select	1, '2006/1/11', '2006/1/13', 100 union all
select	1, '2006/1/18', '2006/1/19', 150

-- initialize user request
declare	@fromdate datetime,
	@todate datetime

select	@fromdate = '2006/1/5',
	@todate = '2006/1/21',
	@fromdate = @fromdate + 1,
	@todate = @todate - 1

-- something to start with
select		t.sid,
		x.date as adate,
		t.price
from		f_table_date(@fromdate, @todate) as x
left join	@t as t on t.fromdate <= x.date and t.todate >= x.date

Peter Larsson
Helsingborg, Sweden

Thank you, I will give it a quick try and see if there is anymore question

does
select @fromdate = '2006/1/5',
@todate = '2006/1/21',
@fromdate = @fromdate + 1,
@todate = @todate - 1

belongs to f_table_date??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 03:24:06

No, if you make this as a stored procedure, @fromdate and @todate are the parameters passed to the stored procedure from your client application.

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-25 : 03:59:11

quote:
Originally posted by Peso

No, if you make this as a stored procedure, @fromdate and @todate are the parameters passed to the stored procedure from your client application.

Peter Larsson
Helsingborg, Sweden

so I just create your
F_TABLE_DATE (found at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519)
into my DB right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 04:23:54

It is not mine, it is Michael's function.
But yes, you copy and paste that function into QA or SSMS and run once. Then the function is stored in your database server.

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-26 : 01:03:41

quote:
Originally posted by Peso

It is not mine, it is Michael's function.
But yes, you copy and paste that function into QA or SSMS and run once. Then the function is stored in your database server.

Peter Larsson
Helsingborg, Sweden

Hi, I am kind of new in this so I am having some problem.
I created the function and it works great and I copied now your code and ran it.
The whole thing runs great but when I use SELECT, shouldn't the data show in the SQL Query Analyzer grid? I know it is a dumb question....
Thank you
Jon

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 01:06:40

quote:
shouldn't the data show in the SQL Query Analyzer grid

press Ctrl-D and run you query. Ctrl-D will switch the result to GRID. Ctrl-T to text

or click on the execute mode to change

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-26 : 01:23:50

quote:
Originally posted by khtan

quote:
shouldn't the data show in the SQL Query Analyzer grid

press Ctrl-D and run you query. Ctrl-D will switch the result to GRID. Ctrl-T to text

or click on the execute mode to change

KH

Hi,
Sorry I didnt phrase my question well. I did run in result in Grid Mode, but the problem is that nothing is shown in the grids
Thank you

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-26 : 01:30:16

When I ran the function, it says

Server: Msg 8152, Level 16, State 2, Procedure F_TABLE_DATE, Line 477
Character or binary data will be truncated

Is this harmless?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 01:33:09

How do you run it ? can you post it here ?

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-01-26 : 01:37:22

quote:
Originally posted by khtan

How do you run it ? can you post it here ?

KH

The results in Grid option is turned on.
For the function, I just placed the compltet code and ran it by pressing the arrow button in QA and the function is created with the errors shown above. But it seems ok.

For the join table, I just pasted the exact code and ran it.
I didnt alter anything.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources