By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

Tricky group by date problem

P: n/a
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

Nov 2 '06 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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

Nov 2 '06 #2

P: n/a
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

ed*************@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
Nov 2 '06 #3

P: n/a
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

ed*************@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
Nov 2 '06 #4

P: n/a

lo***********@hotmail.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

Nov 2 '06 #5

P: n/a
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:

http://www.utexas.edu/its/windows/da...ing/index.html

Nov 2 '06 #6

P: n/a
Try this

create table #mytable(dt datetime,Price1 int, Price2 int, Price3 int)
insert into #mytable(dt,Price1,Price2,Price3)
select '01 Jan 2006',100,100,100 union all
select '02 Jan 2006',100,100,100 union all
select '03 Jan 2006',100,100,100 union all
select '04 Jan 2006',115,100,100 union all
select '05 Jan 2006',115,100,100 union all
select '06 Jan 2006',115,115,115 union all
select '07 Jan 2006',115,100,100 union all
select '08 Jan 2006',100,100,100 union all
select '09 Jan 2006',100,100,100
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.Price1
and b.Price2=a.Price2
and b.Price3=a.Price3
and b.dt <= a.dt) as Num
from #mytable a
) X
group by Price1,Price2,Price3,Num
order by 1

Nov 2 '06 #7

P: n/a
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.MyDate AS FromDate,
COALESCE(ToDates.MyDate, FromDates.MyDate) AS ToDate,
FromDates.Price1,
FromDates.Price2,
FromDates.Price3
FROM dbo.TestTable AS FromDates
LEFT JOIN dbo.TestTable AS ToDates ON
FromDates.MyDate = (
SELECT MIN(MyDate)
FROM dbo.TestTable AS b
WHERE
b.MyDate FromDates.MyDate AND
b.Price1 = FromDates.Price1 AND
b.Price2 = FromDates.Price2 AND
b.Price3 = FromDates.Price3
)
WHERE
NOT EXISTS(
SELECT *
FROM dbo.TestTable AS b
WHERE
b.MyDate = FromDates.MyDate - 1 AND
b.Price1 = FromDates.Price1 AND
b.Price2 = FromDates.Price2 AND
b.Price3 = FromDates.Price3
)
ORDER BY
FromDates.MyDate
--
Hope this helps.

Dan Guzman
SQL Server MVP

<ed*************@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.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
Nov 2 '06 #8

P: n/a
Mark,

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

Thanks for taking the time.

Ed :)

Nov 2 '06 #9

P: n/a
Mark,

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

Thanks for taking the time.

Ed :)

Nov 2 '06 #10

P: n/a
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.Price1 AND a.Price2=d.Price2 AND a.Price3=d.Price3
AND NOT EXISTS (
SELECT * FROM TheTable e
WHERE e.Date BETWEEN d.Date AND a.Date
AND (e.Price1<>d.Price1 OR e.Price2<>d.Price2 OR e.Price3<>d.Price3)
)
) 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.Price1 AND a.Price2=b.Price2 AND a.Price3=b.Price3
AND NOT EXISTS (
SELECT * FROM TheTable c
WHERE c.Date BETWEEN a.Date AND b.Date
AND (c.Price1<>b.Price1 OR c.Price2<>b.Price2 OR c.Price3<>b.Price3)
)
)

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

Razvan

Nov 2 '06 #11

P: n/a
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
Nov 2 '06 #12

P: n/a
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

Nov 3 '06 #13

P: n/a
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
Nov 3 '06 #14

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

--
Anith
Nov 3 '06 #15

P: n/a
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.htm,
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
Nov 3 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.