Select Query - 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)

Select Query

Author

Topic

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-08-16 : 05:46:11

Hi all
I have a table which stores number of sales for a given time period.
Now i have to fetch data from this table for a given time span.

table is as follows

create table rates
(
from_date smalldatetime,
to_date smalldatetime,
quantity int
)

Insert into rates values ('7/1/2010','7/5/2010',4)
Insert into rates values ('7/8/2010','7/10/2010',2)
Insert into rates values ('7/12/2010','7/14/2010',5)
Insert into rates values ('7/17/2010','7/20/2010',8)
Insert into rates values ('7/23/2010','7/25/2010',7)

If i pass '7/1/2010' and '7/30/2010' as start and end times i need the result as follows.

2010-07-01 00:00:00 2010-07-05 00:00:00 4
2010-07-06 00:00:00 2010-07-07 00:00:00 0
2010-07-08 00:00:00 2010-07-10 00:00:00 2
2010-07-10 00:00:00 2010-07-11 00:00:00 0
2010-07-12 00:00:00 2010-07-14 00:00:00 5
2010-07-15 00:00:00 2010-07-16 00:00:00 0
2010-07-17 00:00:00 2010-07-20 00:00:00 8
2010-07-21 00:00:00 2010-07-22 00:00:00 0
2010-07-23 00:00:00 2010-07-25 00:00:00 7
2010-07-26 00:00:00 2010-07-30 00:00:00 0

ie for each period where there not exists a record i need zero or null

How to achieve this?
Thanks in advance..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-16 : 08:09:03

You'll need to create a table that has all the time spans in it and then left join it to your rates table.

Jim

Everyday I learn something that somebody else already knew

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-08-16 : 08:40:20

i did it last.

create table rates
(
from_date smalldatetime,
to_date smalldatetime,
quantity int
)

Insert into rates values ('7/1/2010','7/5/2010',4)
Insert into rates values ('7/8/2010','7/10/2010',2)
Insert into rates values ('7/12/2010','7/14/2010',5)
Insert into rates values ('7/17/2010','7/20/2010',8)
Insert into rates values ('7/23/2010','7/25/2010',7)

create table temprates
(
from_date smalldatetime,
to_date smalldatetime,
quantity int
)

Declare @dtpFDate smalldatetime
Declare @dtpTDate smalldatetime
Declare @dtpTempFDate smalldatetime
Declare @dtpTempTDate smalldatetime
Declare @intTempQuantity int
Declare @count int
Set @dtpFDate = '7/1/2010'
Set @dtpTDate = '7/30/2010'
Set @count = 1
While @dtpFDate <= @dtpTDate
Begin
Set @dtpTempFDate = NULL
Select @dtpTempFDate = from_date,
@dtpTempTDate = to_date,
@intTempQuantity = quantity from
(
Select *,
row_number() over (order by from_date) as [ID]
from rates
) as t
where ID = @count

If not @dtpTempFDate is null
Begin
Print @dtpTempFDate

If @dtpFDate < @dtpTempFDate
Insert into temprates values (@dtpFDate,dateadd(Day,-1,@dtpTempFDate),0)
Else
Print @intTempQuantity
Insert into temprates values (@dtpTempFDate,@dtpTempTDate,@intTempQuantity)

Set @count = @count+1
Set @dtpFDate = dateadd(day, 1, @dtpTempTDate)
End
Else
Begin
Insert into temprates values (@dtpFDate,@dtpTDate,0)
Set @dtpFDate = dateadd(Day,1,@dtpTDate)
Set @count = @count+1

End
End

Select * from temprates

Drop table temprates
Drop table rates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 11:57:31

[code]
SELECT from_date,to_date,quantity
FROM
(
SELECT *
FROM rates
WHERE from_date >=@fromdate AND to_date <=@ToDate
UNION ALL
SELECT DATEADD(dd,1,r1.to_date) AS StartDate,
DATEADD(dd,-1,r2.from_date) AS EndDate,
0
FROM rates r1
CROSS APPLY (SELECT TOP 1 from_date
FROM rates
WHERE from_date > r1.to_date
ORDER BY from_date
)r2
WHERE r2.from_date > DATEADD(dd,1,r1.to_date)
)t
ORDER BY from_date
[/code]

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

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-08-18 : 05:03:19

Hi jimf
thanks for your help.
i was away from office for last three days.
it really helped me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources