Tricky group by date problem

[edouard.spooner]

Hi,

I have a tricky SQL query problem that I'm having probs with.

I have a table which resembles something like this

Date | Price1 | Price2 | Price3
01 Jan 2006 | 100 | 100 | 100
02 Jan 2006 | 100 | 100 | 100
03 Jan 2006 | 100 | 100 | 100
04 Jan 2006 | 115 | 100 | 100
05 Jan 2006 | 115 | 100 | 100
06 Jan 2006 | 115 | 115 | 115
07 Jan 2006 | 115 | 100 | 100
08 Jan 2006 | 100 | 100 | 100
09 Jan 2006 | 100 | 100 | 100

and I want to write a query/view that will return this

>From | To | Price1 | Price2 | Price3

01 Jan 2006 | 03 Jan 2006 | 100 | 100 | 100
04 Jan 2006 | 05 Jan 2006 | 115 | 100 | 100
06 Jan 2006 | 06 Jan 2006 | 115 | 115 | 115
07 Jan 2006 | 07 Jan 2006 | 115 | 100 | 100
08 Jan 2006 | 09 Jan 2006 | 100 | 100 | 100

Any ideas?

I know how to write a routine that would do the same in VB but I am
looking to do a lot of the same calculation/query so I need it to be
fast (which VB wouldnt be)

TIA

Eddie

[Razvan Socol]

Hello, Ed

Sorry for not looking close enough at the sample data and expected
result. Here is my first attempt at the real problem:

SELECT (
SELECT MIN(Date) FROM TheTable d
WHERE d.Date<=a.Date
AND a.Price1=d.Pric e1 AND a.Price2=d.Pric e2 AND a.Price3=d.Pric e3
AND NOT EXISTS (
SELECT * FROM TheTable e
WHERE e.Date BETWEEN d.Date AND a.Date
AND (e.Price1<>d.Pr ice1 OR e.Price2<>d.Pri ce2 OR e.Price3<>d.Pri ce3)
)
) as StartDate, Date as EndDate,
Price1, Price2, Price3
FROM TheTable a
WHERE NOT EXISTS (
SELECT * FROM TheTable b
WHERE a.Date<b.Date
AND a.Price1=b.Pric e1 AND a.Price2=b.Pric e2 AND a.Price3=b.Pric e3
AND NOT EXISTS (
SELECT * FROM TheTable c
WHERE c.Date BETWEEN a.Date AND b.Date
AND (c.Price1<>b.Pr ice1 OR c.Price2<>b.Pri ce2 OR c.Price3<>b.Pri ce3)
)
)

The above query was inspired by reading (a few years ago) the following
article:
http://msdn.microsoft.com/library/en...eintervals.asp

Razvan

[Anith Sen]

Louis,

>Date seems to be an attribute of an Order (correct me if I'm wrong) and
Price seems to be an attribute of a product, so price would need to be in
a products table and Date would need to be in an orders table.

Since Eddie did not post any information regarding the underlying business
model, how can we conclude what the actual entities and relationships are?
Perhaps it could be a table that represents pricing variations of some
product or service on a given date at three different locations or on three
distinct times or simply three samplings for statistical purposes.

We cannot know if a table is sufficiently normalized or not, unless we fully
comprehend the underlying business model and rules.

>The fact that you have a lot of repeating groups, e.g. 100|100|100 shows
that the data isn't properly normalised.

How do we know if there is a repeating group however? Would you consider a
table like the following not to be properly normalized, given that you have
key column and a few datetime columns?
EmployeeHistory ( emp_id, b_date, a_date, h_date, t_date )

-- with the abbreviated names b_date, a_date, h_date and t_date to stand for
birth_date, applied_date, hire_date and terminated_date respectively.

A repeating group is a rampantly abused term that, for historical reasons,
means a group of values in a column. It does not suggest a repeating set of
similarly named columns in a table, though quite a few online sources
mistakenly state so. In the original post, there is nothing that suggests
the existence of a "repeating group". You have a simple table with four
columns -- essentially four distinctly named, typed attributes with scalar
values, where each non-key attributes are functionally dependant on the key
attribute.

Nothing in his post so far has suggested there are any normalization issues
either. And the problem he posed does not suggest the lack of normalization
either.

Very few online references are useful for learning fundamentals. One of
them, esp for 1NF is:
http://www.dbdebunk.com/page/page/629796.htm

The right way to learn normalization is to use good books. As for a
recommendation consider:
http://www.amazon.com/gp/product/0321197844/
http://www.amazon.com/dp/0596100124/

--
Anith

[boetsid]

I saw in one of the solutions
select dt + 1 from #anytable --like ( here dt is a
datetime field )
which one is more efficient or robust between
dateadd(day, 1, dt) -- or only using
dt + 1

[edouard.spooner]

Hi Boetsid,

I always use dt + 1 because then you're not having to invoke the
dateadd function (which I'm guessing adds overhead).

As dates are stored as numbers anyway within SQL, I dont think you can
go wrong with dt+1, or infact dt-1 or dt+.5 :-)

Ed

boetsid wrote:

I saw in one of the solutions
select dt + 1 from #anytable --like ( here dt is a
datetime field )
which one is more efficient or robust between
dateadd(day, 1, dt) -- or only using
dt + 1

[Anith Sen]

See if this helps:
http://groups.google.com/group/micro...7f0f210e5ec52/

--
Anith

[Hugo Kornelis]

On 2 Nov 2006 23:31:55 -0800, boetsid wrote:

>I saw in one of the solutions
select dt + 1 from #anytable --like ( here dt is a
datetime field )
which one is more efficient or robust between
dateadd(day, 1, dt) -- or only using
dt + 1

Hi boetsid,

Both are equally robust. The behaviour of + with a datetime argument is
documented in Books Online, so it won't change withour prior warning.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4ba8baac-5f07-432c-87c5-d23e7011da55.ht m,
example B.

If you want to know which is more efficient, then set up a loop to
repeat this calculation a few million times. Run it twice, using the
different techniques, and compare results. I don't expect that you'll
find any measurable difference, though.

--
Hugo Kornelis, SQL Server MVP