Daylight savings query problem

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

Transact-SQL (2000)

Daylight savings query problem

Author

Topic

raydenl
Starting Member

16 Posts

Posted - 2006-05-03 : 23:31:00

Our company has a table that contains one row for every period in every day. There are 48 periods per day so there are 48 rows per day in the table. The only exception is daylight savings days. On one day in March there is a day with 50 periods, and on one day in October there is a day with 46 periods. This is reflected by the table (calendar) which has a structure like so:

myDate myPeriod
1-mar-2006 1
1-mar-2006 2
...
1-mar-2006 48
2-mar-2006 1
etc..

Now I have another table (myTable) with a structure like so:

myDate myPeriod myArea myValue
1-mar-2006 1 AAA 45.67
1-mar-2006 1 AAA 23.34
1-mar-2006 1 BBB 67.23
1-mar-2006 1 CCC 12.78
1-mar-2006 2 AAA 18.95
etc...

This table does not recognise daylight savings so has exactly 48 periods per day even if the day is a daylight savings day.

This table can have numerous rows per day, per period and per area.

What I need is a select statement that selects between a start and end date, but if one of the days included is a daylight savings day then I need to adjust that day repectively. i.e. a) if one of the days is only supposed to have 46 periods then dispose of periods 47-48, and vice versa b) if the day is supposed to have 50 periods then return the rows for period 48 duplicated to make rows for periods 49 and 50.

I think I have done part a):


select a.myDate, a.myPeriod, myArea, myValue  from
(
   SELECT * FROM dbo.calendar
   WHERE mydate BETWEEN @StartDate AND @EndDate
) a
left outer join
(
   SELECT * FROM dbo.myTable
   WHERE mydate BETWEEN @StartDate AND @EndDate and buysell = 'Sell'
) b
on a.mydate = b.mydate and a.myperiod = b.myperiod
order by a.mydate, myperiod

but I have no idea how to do part b)

anyone know?

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 03:58:54

Can you just disable the daylight saving time switch on that server, and stick with Winter or Summer time the year round?

We had a problem too with data being transferred to another computer based on "All changes since the last successful update" - that lost all the changes for the hour when the clocks went back ...

So there may be other reasons for considering this approach!

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 10:53:29

How about this?


select	a.myDate
	,a.myPeriod
	,b.myArea
	,b.myValue
from	dbo.calendar a
left join dbo.myTable b
		on b.mydate = a.mydate 
		and b.myperiod = a.myperiod
		and b.buysell = 'sell'
where	a.mydate BETWEEN @StartDate AND @EndDate

union all

select	b.myDate
	,b.myPeriod
	,b.myArea
	,b.myValue
from	dbo.myTable b
left join dbo.calendar a
		on a.mydate = b.mydate 
		and a.myperiod = b.myperiod
where	b.mydate BETWEEN @StartDate AND @EndDate
and	b.buysell = 'sell'
and	a.mydate is null --exclude matching rows from calendar

order by 1,2

Be One with the Optimizer
TG

raydenl
Starting Member

16 Posts

Posted - 2006-05-04 : 20:34:49

Kristen - No daylight savings is very important to our company, need to recognise those periods.

TG - No, that doesnt work.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 23:14:18

>>TG - No, that doesnt work.
care to expand on why didn't it work? Errors? wrong results? wrong order?

Here is some sample code with results. Why don't you post what the output should be based on this data (or add you own data)


use pubs
set nocount on

create table calendar 	(mydate datetime, myPeriod int)
create table myTable (mydate datetime, myPeriod int, myArea varchar(3), myValue numeric(4,2))
go

insert calendar
select '1-mar-2006', 1 union all
select '1-mar-2006', 2 union all
select '1-mar-2006', 3

insert myTable
select '1-mar-2006', 1, 'AAA', 45.67 union all
select '1-mar-2006', 1, 'AAA', 23.34 union all
select '1-mar-2006', 1, 'BBB', 67.23 union all
select '1-mar-2006', 1, 'CCC', 12.78 union all
select '1-mar-2006', 2, 'AAA', 18.95 union all
select '2-mar-2006', 1, 'AAA', 10.11

declare @startDate datetime, @enddate datetime
select @startDate = '2006-3-1'
	,@enddate = '2006-3-2'

select	a.myDate
	,a.myPeriod
	,b.myArea
	,b.myValue
from	dbo.calendar a
left join dbo.myTable b
		on b.mydate = a.mydate 
		and b.myperiod = a.myperiod
		--and b.buysell = 'sell'
where	a.mydate BETWEEN @StartDate AND @EndDate

union all

select	b.myDate
	,b.myPeriod
	,b.myArea
	,b.myValue
from	dbo.myTable b
left join dbo.calendar a
		on a.mydate = b.mydate 
		and a.myperiod = b.myperiod
where	b.mydate BETWEEN @StartDate AND @EndDate
--and	b.buysell = 'sell'
and	a.mydate is null --exclude matching rows from calendar

order by 1,2

go
drop table myTable
drop table calendar

--====================================================================================
output:

myDate                                                 myPeriod    myArea myValue 
------------------------------------------------------ ----------- ------ ------- 
2006-03-01 00:00:00.000                                1           AAA    45.67
2006-03-01 00:00:00.000                                1           AAA    23.34
2006-03-01 00:00:00.000                                1           BBB    67.23
2006-03-01 00:00:00.000                                1           CCC    12.78
2006-03-01 00:00:00.000                                2           AAA    18.95
2006-03-01 00:00:00.000                                3           NULL   NULL  --row from calendar that isn't in myTable
2006-03-02 00:00:00.000                                1           AAA    10.11 --row from myTable that isn't in Calendar

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources