Tricky group by date problem

**louisyoung187@hotmail.com** · Nov 2 '06, 11:05 AM

Re: Tricky group by date problem

The reason you are having trouble is that your data is not normalized.
Also your data is based on change over time and not on groupings, so to
accomplish this in T-SQL you'll probably have to use CURSORS which is
going to be just as complicated (if not more complicated) than doing it
in VB, so my suggestion is to normalise the database. If this is not
possible then rather stick to doing it in VB

**Razvan Socol** · Nov 2 '06, 11:25 AM

Re: Tricky group by date problem

It's a pretty simple query (much easier than doing it in VB):

SELECT MIN(Date) as From, MAX(Date) as To,
Price1, Price2, Price3
FROM TheTable
GROUP BY Price1, Price2, Price3

Razvan

edouard.spooner @gmail.com wrote:

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

**edouard.spooner@gmail.com** · Nov 2 '06, 11:25 AM

Re: Tricky group by date problem

Hi Razvan,

Thanks, but this wont work.

One of the responses will be

01 Jan 2006 | 09 Jan 2006 | 100 | 100 | 100

Ed

Razvan Socol wrote:

It's a pretty simple query (much easier than doing it in VB):
>
SELECT MIN(Date) as From, MAX(Date) as To,
Price1, Price2, Price3
FROM TheTable
GROUP BY Price1, Price2, Price3
>
Razvan
>
edouard.spooner @gmail.com wrote:

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

**edouard.spooner@gmail.com** · Nov 2 '06, 11:45 AM

Re: Tricky group by date problem

louisyoung187@h otmail.com wrote:

The reason you are having trouble is that your data is not normalized.

I'm intrigued. How would you normalise the data further - the date is a
unqiue field and their would be no benefit in storing the prices in a
separate table

**louisyoung187@hotmail.com** · Nov 2 '06, 12:25 PM

Re: Tricky group by date problem

I'm intrigued. How would you normalise the data further - the date is a
unqiue field and their would be no benefit in storing the prices in a
separate table

Well, remember that all non-key fields needs to be dependant on the
primary key and only on the primary key. 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. The fact that you have a
lot of repeating groups, e.g. 100|100|100 shows that the data isn't
properly normalised. This is not something I can teach you in one
e-mail, so please have a look at the following link:

Home | Enterprise Technology

http://www.utexas.edu/its/windows/database/datamodeling/index.html

Enterprise Technology at The University of Texas in Austin is the University's Information Technology organization delivering tools and services that help the Longhorn community learn, discover, and succeed.

**markc600@hotmail.com** · Nov 2 '06, 12:35 PM

Re: Tricky group by date problem

Try this

create table #mytable(dt datetime,Price1 int, Price2 int, Price3 int)
insert into #mytable(dt,Pri ce1,Price2,Pric e3)
select '01 Jan 2006',100,100,1 00 union all
select '02 Jan 2006',100,100,1 00 union all
select '03 Jan 2006',100,100,1 00 union all
select '04 Jan 2006',115,100,1 00 union all
select '05 Jan 2006',115,100,1 00 union all
select '06 Jan 2006',115,115,1 15 union all
select '07 Jan 2006',115,100,1 00 union all
select '08 Jan 2006',100,100,1 00 union all
select '09 Jan 2006',100,100,1 00

select min(dt) as [From],
max(dt) as [To],
Price1,
Price2,
Price3
from (
select a.dt,
a.Price1,
a.Price2,
a.Price3,
a.dt-(select count(*)
from #mytable b
where b.Price1=a.Pric e1
and b.Price2=a.Pric e2
and b.Price3=a.Pric e3
and b.dt <= a.dt) as Num
from #mytable a
) X
group by Price1,Price2,P rice3,Num
order by 1

**Dan Guzman** · Nov 2 '06, 12:45 PM

Re: Tricky group by date problem

One method:

CREATE TABLE dbo.TestTable
(
MyDate datetime
CONSTRAINT PK_TestTable PRIMARY KEY,
price1 int NOT NULL,
price2 int NOT NULL,
price3 int NOT NULL
)

INSERT INTO dbo.TestTable
SELECT '20060101', 100, 100, 100
UNION ALL SELECT '20060102', 100, 100, 100
UNION ALL SELECT '20060103', 100, 100, 100
UNION ALL SELECT '20060104', 115, 100, 100
UNION ALL SELECT '20060105', 115, 100, 100
UNION ALL SELECT '20060106', 115, 115, 115
UNION ALL SELECT '20060107', 115, 100, 100
UNION ALL SELECT '20060108', 100, 100, 100
UNION ALL SELECT '20060109', 100, 100, 100
GO

SELECT
FromDates.MyDat e AS FromDate,
COALESCE(ToDate s.MyDate, FromDates.MyDat e) AS ToDate,
FromDates.Price 1,
FromDates.Price 2,
FromDates.Price 3
FROM dbo.TestTable AS FromDates
LEFT JOIN dbo.TestTable AS ToDates ON
FromDates.MyDat e = (
SELECT MIN(MyDate)
FROM dbo.TestTable AS b
WHERE
b.MyDate FromDates.MyDat e AND
b.Price1 = FromDates.Price 1 AND
b.Price2 = FromDates.Price 2 AND
b.Price3 = FromDates.Price 3
)
WHERE
NOT EXISTS(
SELECT *
FROM dbo.TestTable AS b
WHERE
b.MyDate = FromDates.MyDat e - 1 AND
b.Price1 = FromDates.Price 1 AND
b.Price2 = FromDates.Price 2 AND
b.Price3 = FromDates.Price 3
)
ORDER BY
FromDates.MyDat e

--
Hope this helps.

Dan Guzman
SQL Server MVP

<edouard.spoone r@gmail.comwrot e in message
news:1162462283 .727604.11730@h 48g2000cwc.goog legroups.com...

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
>

**edouard.spooner@gmail.com** · Nov 2 '06, 12:55 PM

Re: Tricky group by date problem

Mark,

Neat query - to quote Wayne's World, "I am not worthy!"

Thanks for taking the time.

Ed :)

**edouard.spooner@gmail.com** · Nov 2 '06, 12:55 PM

Re: Tricky group by date problem

Mark,

Neat query - to quote Wayne's World, "I am not worthy!"

Thanks for taking the time.

Ed :)

**Razvan Socol** · Nov 2 '06, 12:55 PM

Re: Tricky group by date problem

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:

Microsoft Learn: Build skills that open doors in your career

http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

Gain technical skills through documentation and training, earn certifications and connect with the community

Razvan

**Anith Sen** · Nov 2 '06, 04:55 PM

Re: Tricky group by date problem

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:

- DATABASE DEBUNKINGS

http://www.dbdebunk.com/page/page/629796.htm

The right way to learn normalization is to use good books. As for a
recommendation consider:

Amazon.com

http://www.amazon.com/gp/product/0321197844/

Amazon.com

http://www.amazon.com/dp/0596100124/

--
Anith

**boetsid** · Nov 3 '06, 07:15 AM

day addition

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@gmail.com** · Nov 3 '06, 09:05 AM

Re: day addition

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** · Nov 3 '06, 02:45 PM

Re: day addition

See if this helps:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/5bf7f0f210e5ec52/

--
Anith

Tricky group by date problem

Tricky group by date problem

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment