SQL Query help - is there any way to do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Default User - 30 Dec 2008 05:02 GMT

Hi,

My working query:

select
min(datetimecreated),min(datetimeexpires),min(datetimeredeemed),sum(redemptionminimum),
sum(redemptionmaximum),min(localvaluetext),sum(discountpergallon),min(maximumpurchaseamount)
from fueldiscounts
where localvaluetext='44000028361'
group by datetimecreated

Here is a picture of the result:

http://home.earthlink.net/~alank2/sql.gif

Note that the first row and second row's are one second apart. Same thing
with rows 6/7 and 8/9. I would like to also group these rows together, but
I can't because the first field is of course one second off. I can't count
on the final field because at two different times per day the same amount
may be present and I don't want them grouped unless they are within a second
of each other.

I had someone recommend the round function, but how do you know if the one
second apart will round differently?

Thanks for any help!!!

Alan

Reply to this Message

--CELKO-- - 30 Dec 2008 05:40 GMT

>> I would like to also group these rows together, but I can't because the first field[sic: columns are not fields] is of course one second off. I can't count on the final field [sic] because at two different times per day the same amount may be present and I don't want them grouped unless they are within a second of each other. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect. People also do not like to click odd websites --
viruses are a problem.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

What do you want to do when you have a longer run of events that are
one or less seconds apart? Do they become one grouping or several?

Reply to this Message

Plamen Ratchev - 30 Dec 2008 06:11 GMT

Here is one method that may give you what you need. It is based on
grouping rows that are second apart by subtracting the second difference
from a fixed date and a sequential ranking number by the date/time
column. This requires SQL Server 2005/2008. You can remove the grouping
by 'day_grp' if it is not required to group by day.

CREATE TABLE FuelDiscounts (
keycol INT PRIMARY KEY,
datetimecreated DATETIME,
datetimeexpires DATETIME);

INSERT INTO FuelDiscounts VALUES (1, '20081104 10:30:11', '20081106');
INSERT INTO FuelDiscounts VALUES (2, '20081104 10:30:12', '20081107');
INSERT INTO FuelDiscounts VALUES (3, '20081104 23:59:59', '20081108');
INSERT INTO FuelDiscounts VALUES (4, '20081105 00:00:00', '20081109');
INSERT INTO FuelDiscounts VALUES (5, '20081105 00:00:01', '20081110');
INSERT INTO FuelDiscounts VALUES (6, '20081105 10:30:00', '20081111');

SELECT MIN(datetimecreated) AS datetimecreated,
MIN(datetimeexpires) AS datetimeexpires,
COUNT(*) AS count_in_grp
FROM (SELECT DATEDIFF(DAY, '20010101', datetimecreated) AS day_grp,
DATEDIFF(SECOND, '20010101', datetimecreated) -
ROW_NUMBER() OVER(ORDER BY datetimecreated) AS sec_grp,
datetimecreated,
datetimeexpires
FROM FuelDiscounts) AS F
GROUP BY day_grp, sec_grp;

/*

Results:

datetimecreated datetimeexpires count_in_grp
----------------------- ----------------------- ------------
2008-11-04 10:30:11.000 2008-11-06 00:00:00.000 2
2008-11-04 23:59:59.000 2008-11-08 00:00:00.000 1
2008-11-05 00:00:00.000 2008-11-09 00:00:00.000 2
2008-11-05 10:30:00.000 2008-11-11 00:00:00.000 1

*/

Signature

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

--CELKO-- - 30 Dec 2008 14:46 GMT

CREATE TABLE FuelDiscounts
(some_key INTEGER NOT NULL PRIMARY KEY,
creation_time DATETIME NOT NULL,
etc);

What about something like this? We create a list of events 1 second
ahead and subtract it:? Thus we get {1,2,3,.} - {2,3,4,..} = {1} I
am making a lot of assumptions here.

SELECT X.*
FROM ((SELECT some_key, creation_time
FROM FuelDiscounts) AS F1
EXCEPT
SELECT some_key, DATEADD (SECOND, 1, creation_time)
FROM FuelDiscounts) AS F2)
AS X (some_key, creation_time);

This is why I asked about a sequence of events instead of just pairs.

Reply to this Message

Default User - 30 Dec 2008 14:54 GMT

Hi,

You are right that they aren't always pairs. I will give your example a try
as well as a learning experience; I haven't used "except" yet...

Thanks,

Alan

> CREATE TABLE FuelDiscounts
> (some_key INTEGER NOT NULL PRIMARY KEY,
[quoted text clipped - 14 lines]
>
> This is why I asked about a sequence of events instead of just pairs.

Reply to this Message

--CELKO-- - 30 Dec 2008 17:55 GMT

I was assuming that all the rows in the "one second time frames" are
the same event, reported a little out of synch, so it makes sense to
drop all but the early or later rows, retaining one representative
row.

Now, if they are different, then we'd need rules for reducing them to
one representative row.

EXCEPT is new to SQL Server. In Oracle, it was called MINUS when they
implemented it years ago. It is the set difference, and in the same
family as UNION and and INTERSECT.

Reply to this Message

Plamen Ratchev - 31 Dec 2008 04:36 GMT

This technique is interesting, but the goal stated here was to summarize
certain columns for each group and using EXCEPT will eliminate them from
the result set.

Signature

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Default User - 30 Dec 2008 14:52 GMT

Hi Plamen,

Thank you !!!!!! Your technique did exactly what I was looking for!!!!!

I appreciate it,

Alan

Reply to this Message