group by datetime

[bic]

Hi,

I am baffled by this query and can use a little help pls!

query count for each Monday of the week in the last few months between 9 pm
and 1 am. I know I can use datepart() but can't figure out how to query
between 9pm and 1am. Thanks.

--
bic

[Roy Harvey]

Selecting that time period, exclusive of 1:00 AM on the dot, would
look like this:

WHERE (DATENAME(weekday,crdate) = 'Monday'
AND DATEPART(hour,crdate) >= 1)
OR (DATENAME(weekday,crdate) = 'Tuesday'
AND DATEPART(hour,crdate) = 0)

However to GROUP the data and have both sides of midnight appear in
the same group you would need to group by something like:

dateadd(hour, datediff(hour,'20000101',crdate) -2, '20000101')

Roy Harvey
Beacon Falls, CT

[--CELKO--]

>> I am baffled by this query and can use a little help pls! <<

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. 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. Why are you so rude?

>> query count for each Monday of the week in the last few month between 9 pm [sic: 11:00:00 Hrs] and 1 am [sic: 01:00:00 the next day??]. I know I can use DATEPART() but can't figure out how to query between 9 pm [sic] and 1 am [sic]. <<

You really have no idea how time works!! What the hell is that AM and
PM crap? You never heard of ISO-8601 Standards and UTC???

What you do is set up a table of temporal ranges with upper and lower
limits with TIMESTAMP limits and join to it. Hey, you spit on us by
not posting DDL, why should we post DDL and data for you?

Your unit of measurement is wrong and you are getting screwed up. But
your invisible DDL tells us nothing!!

[--CELKO--]

Consider portability and the performance of JOIN versus computation.
I prefer the range table approach.

[bic]

Thanks Roy and it really helps. However I tried and play with your suggested
group by clause but feel that I missed something there. Here is what I have
but still can't get the count by day. Thanks.

select count(*) from ad where Creation_Date between '2007-4-30' and getdate()
AND((DATENAME(weekday,Creation_Date) = 'Monday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Tuesday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Tuesday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Wednesday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Wednesday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Thursday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Thursday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Friday' AND DATEPART(hour,Creation_Date) <= 1))

--
bic

[bic]

Here is what I have but it is picking up 2:00 not just 1:00 and the group by
is not grouping by the day. Thanks.

select Creation_Date,count(*) from ad where Creation_Date between

'2007-4-30' and getdate()
AND((DATENAME(weekday,Creation_Date) = 'Monday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Tuesday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Tuesday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Wednesday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Wednesday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Thursday' AND DATEPART(hour,Creation_Date) <= 1)
OR(DATENAME(weekday,Creation_Date) = 'Thursday' AND
DATEPART(hour,Creation_Date) >= 21) OR (DATENAME(weekday,Creation_Date) =
'Friday' AND DATEPART(hour,Creation_Date) <= 1))

group by Creation_Date,datepart(day,(dateadd(hour,
datediff(hour,'20000101',Creation_Date) -1, '20000101')))

--
bic

[Aaron Bertrand [SQL Server MVP]]

Do you want rows that were created at exactly 1:00 AM but not at
1:00:00.005? Or do you want rows that were created up to 12:59:59.997, but
NOT at 1:00:00 AM?

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"bic" <b...@discussions.microsoft.com> wrote in message
news:402B1212-6976-4887...@microsoft.com...

[rpresser]

On Jun 15, 5:32 pm, bic <b...@discussions.microsoft.com> wrote:
> Here is what I have but it is picking up 2:00 not just 1:00 and the group by
> is not grouping by the day. Thanks.
>

SELECT Key_Date, count(*) FROM (
SELECT Creation_Date,convert(datetime,
floor(convert(float,
dateadd(hour, -2, Creation_Date)))) AS Key_Date
FROM AD
WHERE datepart(hour,Creation_date) in (21,22,23,0,1)
) AS X GROUP BY Key_Date

Specifically, this will classify everything between 2007-06-14
21:00:00.000 and 2007-06-15 01:59:59.997
under the date 2007-06-14.

Notice the "convert(datetime,floor(convert(float..." trick for
extracting the date only and dropping the time. This might be non-
portable (i.e. it might not necessarily work on systems other than MS
SQL SERVER) but works very well. Notice also that to classify the
first two hours of tomorrow under today, we consider the actual
datetime minus two hours. If you want to see which times are being
selected and how they are being classified, comment out the first and
last lines.

[bic]

I replaced DATEPART(hour,Creation_Date) <= 1 with
DATEPART(hour,Creation_Date) <= 0 to exclude the rows between 1:00:00 and
1:59:59. But Aaron, we are not dealing with that active a site esp. during
that kind of hour so I can careless. The main issue now is that it is not
grouping at all. Can someone take a shot at my group by clause? Thanks.
--
bic

[Aaron Bertrand [SQL Server MVP]]

>I replaced DATEPART(hour,Creation_Date) <= 1 with
> DATEPART(hour,Creation_Date) <= 0 to exclude the rows between 1:00:00 and
> 1:59:59. But Aaron, we are not dealing with that active a site esp.
> during
> that kind of hour so I can careless.

Well, you should care. Do you want your data to be accurate, or not? It's
a very simple choice: you either use BETWEEN (and face inaccuracies and
ambiguity) or you don't (and have clean data, regardless of whether you get
10 or 10 billion hits a day). It also helps those who are trying to help
you in making sure that our queries answer the right question. Initially
you had DATEPART(HOUR, ...) <=1 which means up to 1:59:59.997 AM. Another
important distinction!

> The main issue now is that it is not
> grouping at all. Can someone take a shot at my group by clause?

Can you explain what "not grouping at all" means? Which version of the
query are you running? Can you show some sample data and desired results?
See http://www.aspfaq.com/5006

[bic]

Wala, you good man. Thanks whole bunch.
--
bic

[Tony Rogerson]

> Consider portability and the performance of JOIN versus computation.
> I prefer the range table approach.

YEs, the join approach would require a seek operation for every row on the
starting table, that would cost significantly more CPU and IO that using
DATEDIFF etc...

As ever, you theorise and your lack of real industry experience shows you up
as an idiot who knows jack sh1t about coding database systems.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1181938893....@q69g2000hsb.googlegroups.com...

[bic]

Hey i appreciate it that you really cleaned up my query. However a little
problem remains as to how I can group it such that 21:00:00 groups with 22,
23 and the 0 hour from the next day instead of from the same day. Also how to
unsurpress the 0 count entries. Thanks.
--
bic

[bic]

Aaron, I appreciate your stressing on accuracy, however, can you help me by
fixing my group by such it groups 21,22,23 and 0 hour of the next day instead
of the 0 hour of the same day? Thanks.
--
bic

"Aaron Bertrand [SQL Server MVP]" wrote:

[Roy Harvey]

On Fri, 15 Jun 2007 13:54:01 -0700, bic
<b...@discussions.microsoft.com> wrote:

> However I tried and play with your suggested
>group by clause but feel that I missed something there.

Sorry about that. Try grouping on this instead.

dateadd(day,0,datediff(day,0,dateadd(hour,-2,crdate)))

As for the rest, if you wanted to take EVERY day of the week the code
is MUCH simpler:

WHERE DATEPART(hour,Creation_Date) IN (1, 21, 22, 23, 24)

And the test for day of the week can be simplified a bit too:

WHERE DATEPART(hour,Creation_Date) IN (1, 21, 22, 23, 24)
AND DATENAME(weekday,dateadd(hour,-2,Creation_Date)) IN
('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')

[Aaron Bertrand [SQL Server MVP]]

> Aaron, I appreciate your stressing on accuracy, however, can you help me
> by
> fixing my group by such it groups 21,22,23 and 0 hour of the next day
> instead
> of the 0 hour of the same day? Thanks.

Can you show sample data and desired results as I requested, instead of word
problems?

--

Aaron Bertrand
SQL Server MVP

http://www.sqlblog.com/
http://www.aspfaq.com/5006

[rpresser]

Oh, so you want the late nighttime hours to be considered part of the
NEXT day, not the wee morning hours to be considered part of the
PREVIOUS day? Sorry for the misunderstanding.

Change
dateadd(hour, -2, Creation_Date)
into
dateadd(hour, 3, Creation_Date)

What this is doing is associating each time with the time three hours
later (in the previous version, it was the time two hours before), and
then taking the day portion of that.

"Unsurpress 0 count entries" is not quite what you need to do. There
are no entries there to be surpressed. If you need rows for times that
do not occur in your data, then you need to join in a table where
those times DO exist. This is what Mr. Celko always urges (quite
rightly). And if you are going to use this, it makes sense to
transfer the key_date calculation into it. It makes things a great
deal simpler, actually.

CREATE TABLE TimePeriods
(PeriodStart datetime, PeriodEnd datetime, Key_Date datetime)
INSERT TimePeriods VALUES ('2007-06-01 21:00:00', '2007-06-01
21:59:59.999', '2007-06-02')
INSERT TimePeriods VALUES ('2007-06-01 22:00:00', '2007-06-01
22:59:59.999', '2007-06-02')
INSERT TimePeriods VALUES ('2007-06-01 23:00:00', '2007-06-01
23:59:59.999', '2007-06-02')
...etc...

Select TimePeriods.Key_Date, datepart(hour,TimePeriods.PeriodStart),
COUNT(AD.*)
FROM TimePeriods
LEFT JOIN AD
ON AD.Creation_Date between TimePeriods.PeriodStart and
TimePeriods.PeriodEnd

> > last lines.- Hide quoted text -
>
> - Show quoted text -

[bic]

Aaron, sorry I was caught up in something else for the last two days. Here
is the DDL and I want to group by in such a way that the 0 hour is from the
next day rather than from the same day. Thanks.

SELECT Key_Date, count(*) FROM (
SELECT Creation_Date,convert(datetime,
floor(convert(float,
dateadd(hour, -2, Creation_Date)))) AS Key_Date
FROM AD

WHERE datepart(dw,Creation_date) in (2,3,4,5) and
datepart(hour,Creation_date) in (21,22,23,0)

) AS X GROUP BY Key_Date

--
bic

[Aaron Bertrand [SQL Server MVP]]

Look, I'm done guessing, and I'm sure everyone else is too. Looking at your
query again is not going to help anyone solve your problem. And I still
don't know how to translate word problems like "the 0 hour is from the next

day rather than from the same day."

(1) DDL is a CREATE TABLE statement.
(2) Sample data is an INSERT INTO (table created in (1)) statement.
(3) Desired result means a sample of rows you want back from the query based
on the sample data you provided in (2).

Can you please go read http://www.aspfaq.com/5006 before asking again?

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"bic" <b...@discussions.microsoft.com> wrote in message
news:4FC8BA24-747B-44C3...@microsoft.com...