Firehose mode: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

Grouping with non-existent data

I need to build a report that groups data by a given time frame increment (15
min, 30 min, 1 hour). But I need the report to show every increment weather
there is data in my result set or not. So if I was to run my query for the
time frame of 12pm - 1pm and got the following data:
12:00, 1, 1
12:10, 1, 1
12:20, 1, 1
12:50 1, 1
And was asked to display my report using the 15 minute increment I should
get this back:
12:00 - 2 records
12:15 - 1 record
12:30 - 0 record
12:45 - 1 records
Now my grouping expression works to group the data (Hours are limited in the
query):
= ((DATEPART("h", Fields!EntryDate.Value))
* (60 / Parameters!Granularity.Value)
+ (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
Parameters!Granularity.Value)))
However if there isn't any data in one of the incremental groups that group
isn't displayed. How can I make the grouping work so that even if there is
no data for my group I still get a row?
Let me know if this doesn't make any sence and I'll try to clarify it.I have to do this and it is not simple. You need to have the data there to
be able to show it. This means you need to have a stored procedure that
fills in the missing rows (or more likely, puts in all the rows and then
joins it with the query so you have all the times shown).
RS can't group on something that isn't there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> I need to build a report that groups data by a given time frame increment
(15
> min, 30 min, 1 hour). But I need the report to show every increment
weather
> there is data in my result set or not. So if I was to run my query for
the
> time frame of 12pm - 1pm and got the following data:
> 12:00, 1, 1
> 12:10, 1, 1
> 12:20, 1, 1
> 12:50 1, 1
> And was asked to display my report using the 15 minute increment I should
> get this back:
> 12:00 - 2 records
> 12:15 - 1 record
> 12:30 - 0 record
> 12:45 - 1 records
> Now my grouping expression works to group the data (Hours are limited in
the
> query):
> = ((DATEPART("h", Fields!EntryDate.Value))
> * (60 / Parameters!Granularity.Value)
> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> Parameters!Granularity.Value)))
> However if there isn't any data in one of the incremental groups that
group
> isn't displayed. How can I make the grouping work so that even if there
is
> no data for my group I still get a row?
> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||I was afraid of that. I guess I'll have to figure out how to build a
query/stored procedure that will return me the proper time sets given a
start/end and increment.
Thanks for your help.
"Bruce L-C [MVP]" wrote:
> I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> > I need to build a report that groups data by a given time frame increment
> (15
> > min, 30 min, 1 hour). But I need the report to show every increment
> weather
> > there is data in my result set or not. So if I was to run my query for
> the
> > time frame of 12pm - 1pm and got the following data:
> >
> > 12:00, 1, 1
> > 12:10, 1, 1
> > 12:20, 1, 1
> > 12:50 1, 1
> >
> > And was asked to display my report using the 15 minute increment I should
> > get this back:
> >
> > 12:00 - 2 records
> > 12:15 - 1 record
> > 12:30 - 0 record
> > 12:45 - 1 records
> >
> > Now my grouping expression works to group the data (Hours are limited in
> the
> > query):
> > = ((DATEPART("h", Fields!EntryDate.Value))
> > * (60 / Parameters!Granularity.Value)
> > + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> > Parameters!Granularity.Value)))
> >
> > However if there isn't any data in one of the incremental groups that
> group
> > isn't displayed. How can I make the grouping work so that even if there
> is
> > no data for my group I still get a row?
> >
> > Let me know if this doesn't make any sence and I'll try to clarify it.
> >
>
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||But you can do it with a query, i have done that sometimes, if you giove me
some tim i´ll try to find out if i have it right away, just email me to give
a reminder.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>

Wednesday, March 28, 2012

Grouping problem

I am working on a report for a small POS. The report should allow the user to choose the time interval to group sales records, e.g. 1 hour, 2 hours or 4 hours. I believe I can setup this by the DiscretizationMethod and DiscretizationBucketCount of the Hour attribute in my DimTime dimension. However, the problem that I am facing is this POS will support multiple branches. Each branch will have their particular opening and closing hour. So, how can I group all the transaction into groups, said "Before Shop Open" and "After Shop Closed"? This sounds strange but will happen quite often as overtime work is always expected in my living place.

If this is infeasible, is there any workaround? I think the business user certainly want to know how many transaction has been created in those extra time.

In the other report, it is required to generate a transaction count by amount. The user should be able to specify the amount interval and upper limit. e.g. if amount interval and upper limit are set to 50 and 150, then the transaction will be grouped into 4.
0<=amount<50
50<=amount<100
100<=amount<150
amount>=150

I have no idea to this. First, I don't know how can I get the amount for each sale order as my fact table is storing sales order item information only. Second, how can I make this customizable grouping just like the report stated above? Thanks!

Hi Alex:

You pose two difficult problems. I'll address the second problem because you provided the most detail and clearly stated the issues. To restate, the issues are:

(1) How can you get the amount for each sale order?

(2) How can you allow customizable grouping?

Addressing issue (1) about the amount for the sales order. If the sales amount for the sales order is not in your fact table then you will not be able to access the sales amount in your cube. You have to go back to the ETL process and bring in the sales amount as part of yur fact table.

Issue (2), customizable grouping, is best approached on the client side of your application. Alternatively you, as an administrator, could create a separate attribute hierarchy for each branch with it's own amount interval and upper limit. I think your choice of a solution (client side, or separate hierarchy per branch) depends upon how many branches you have, and how much management you want to put in as an administrator. Creating transaction count by amount on the client is simple if you have the transaction amount as a measure. Get the transaction count by using a calculated member with the MDX count() function. Within each query you can adjust the amount interval and upper limit for each user. Here's an example:

WITH MEMBER MEASURES.[Less than 50] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] < 50)'

MEMBER MEASURES.[Between 50 and 100] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] > 50 AND Measures.[Sales Amount] < 100)'

SELECT {MEASURES.[Less than 50] , MEASURES.[Between 50 and 100]} ON COLUMNS FROM [my cube]

Hope this helps.

PGoldy

|||Hi PGoldy,

First, thank you for your input to these difficult problems that I am facing right now. Actually, I have come up with sort of solution after the post but it still doesn't work very well.

For issue 1, I found out that even I don't have a total for the sales order stored in the fact table. I can get it by creating a "Named Query". In this query, I will group the fact table records by the transaction ID. In this way, I obtain the sales amount per transaction, not per item. It looks good.

For issue 2, I use the "Named Query" that just created a bit further. In that query, besides the total amount per transaction. I create another field which is a floored amount. I am using this function.

floor(convert(decimal, sum(ItemAmount)) / 50) * 50

By doing this, I am able to make those sales total into the starting value of their groups. e.g. 38 returns 0, 59 returns 50 and 160 returns 150.
It seems really good at first. However, I have another problem to make this perfect or really usable. In SSAS, if there's no data exists for a specific group. It won't get display. e.g. if I got 38, 59 and 160 in my sales order total. I will only get the groups 0~49, 50~99 and 150~149. The problem is the missing 100~149. For business user, I think it's not acceptable to have a gap in the report like this. So, how can I fill in this gap?

Moreover, is there any best practice for my situation? I think this is a very common scenario but I can't find any useful reference.

Regards,
Alex|||

Hi Alex:

Best practice is creation of a hierarchy which has the "bucket" ranges you want. Then link each fact table record to the appropriate bucket with a foreign key. It's a common practice and used in most implementations. Below is a link to a series of articles by Bill Pearson which articulate (very well) the functionality you're looking for and a lot more. Good luck.

PGoldy

|||Dear PGoldy,

Could you please check whether the links has been posted? Thanks!

Regards,
Alex|||

Hi Alex. Sorry about the delay. Below is the link. PaulG

http://www.databasejournal.com/article.php/1459531

sql

Grouping performance monitor time data after pivoting help please

O.K., here is the problem. I am setting up performance monitoring for
various 2000 and 2003 boxes at my enterprise. I have got Performance
Monitor recording the data into a sql2000 database. The problem I've
got is the way that it stores data required me to write TSQL to Pivot
the data because of the columnar way it is represented by Performance
monitor. Performance monitor creates three tables called CounterData,
CounterDetails and DisplayToID. If I do a join between CounterData and
CounterDetails, on the 'CounterID' column, I can extract the
following meaningful data:
CounterDate MachineName CounterValue
2006-06-07 12:26:14.891 <Server1> 99.8566308243728
2006-06-07 12:27:14.860 <Server1> 99.8704663212435
2006-06-07 12:28:14.860 <Server1> 99.8611111111111
2006-06-07 12:29:14.860 <Server1> 99.8506905561777
....
2006-06-08 07:34:21.908 <Server2> 99.7949850181359
2006-06-08 07:35:21.909 <Server2> 99.7628170041963
2006-06-08 07:36:21.909 <Server2> 99.7827540106952
2006-06-08 07:37:21.910 <Server2> 99.7800338409476
....
In order to get this data into a linear graph, I have to Pivot the
MachineName column so that it looks like this: (which I have done).
Note that there are many more servers which explains all the NULLS.
They haven't been included for brevity.
CounterDateTime <Server1> <Server2>
2006-06-08 10:43:22.438 NULL 99.8652291105121
2006-06-08 10:43:22.463 NULL NULL
2006-06-08 10:43:23.330 NULL NULL
2006-06-08 10:43:24.388 99.8756991920447 NULL
2006-06-08 10:43:27.791 NULL NULL
2006-06-08 10:44:17.038 NULL NULL
2006-06-08 10:44:22.438 NULL 99.866577718479
2006-06-08 10:44:22.463 NULL NULL
2006-06-08 10:44:23.330 NULL NULL
2006-06-08 10:44:24.403 99.8441760810284 NULL
2006-06-08 10:44:27.791 NULL NULL
2006-06-08 10:45:17.054 NULL NULL
2006-06-08 10:45:22.469 NULL 99.8621640248105
....
As you can see, the time intervals are all over the place and there is
no guarantee that both servers will take a reading at exactly the same
point in time. What I need to do is divide the times into neat steps
like - say - 5 minute intervals and group all the random times into
the five minute slots in which they belong; taking averages where the
'time entries' are grouped. I have no idea how to do this and am
hoping that there is a guru out there up to the challenge.How to pivot a resultset is a pretty commonly requested thing in T-SQL.
So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
you to Aaron Bertrand's article on the subject on aspfaq.com:
http://www.aspfaq.com/show.asp?id=2462
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>O.K., here is the problem. I am setting up performance monitoring for
>various 2000 and 2003 boxes at my enterprise. I have got Performance
>Monitor recording the data into a sql2000 database. The problem I've
>got is the way that it stores data required me to write TSQL to Pivot
>the data because of the columnar way it is represented by Performance
>monitor. Performance monitor creates three tables called CounterData,
>CounterDetails and DisplayToID. If I do a join between CounterData and
>CounterDetails, on the 'CounterID' column, I can extract the
>following meaningful data:
>CounterDate MachineName CounterValue
>2006-06-07 12:26:14.891 <Server1> 99.8566308243728
>2006-06-07 12:27:14.860 <Server1> 99.8704663212435
>2006-06-07 12:28:14.860 <Server1> 99.8611111111111
>2006-06-07 12:29:14.860 <Server1> 99.8506905561777
>....
>2006-06-08 07:34:21.908 <Server2> 99.7949850181359
>2006-06-08 07:35:21.909 <Server2> 99.7628170041963
>2006-06-08 07:36:21.909 <Server2> 99.7827540106952
>2006-06-08 07:37:21.910 <Server2> 99.7800338409476
>....
>In order to get this data into a linear graph, I have to Pivot the
>MachineName column so that it looks like this: (which I have done).
>Note that there are many more servers which explains all the NULLS.
>They haven't been included for brevity.
>CounterDateTime <Server1> <Server2>
>2006-06-08 10:43:22.438 NULL 99.8652291105121
>2006-06-08 10:43:22.463 NULL NULL
>2006-06-08 10:43:23.330 NULL NULL
>2006-06-08 10:43:24.388 99.8756991920447 NULL
>2006-06-08 10:43:27.791 NULL NULL
>2006-06-08 10:44:17.038 NULL NULL
>2006-06-08 10:44:22.438 NULL 99.866577718479
>2006-06-08 10:44:22.463 NULL NULL
>2006-06-08 10:44:23.330 NULL NULL
>2006-06-08 10:44:24.403 99.8441760810284 NULL
>2006-06-08 10:44:27.791 NULL NULL
>2006-06-08 10:45:17.054 NULL NULL
>2006-06-08 10:45:22.469 NULL 99.8621640248105
>....
>As you can see, the time intervals are all over the place and there is
>no guarantee that both servers will take a reading at exactly the same
>point in time. What I need to do is divide the times into neat steps
>like - say - 5 minute intervals and group all the random times into
>the five minute slots in which they belong; taking averages where the
>'time entries' are grouped. I have no idea how to do this and am
>hoping that there is a guru out there up to the challenge.
>
>|||Mike, I have already pivotted the result set. That is not what I need
to know. In this post I am asking how to group the random time results
to fixed increments. Say every hour over two w

s or ever minute over
a day etc.
Mike Hodgson wrote:
> How to pivot a resultset is a pretty commonly requested thing in T-SQL.
> So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
> you to Aaron Bertrand's article on the subject on aspfaq.com:
> http://www.aspfaq.com/show.asp?id=2462
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> togbabe wrote:
>
> --080200050206060200040400
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3519
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>How to pivot a resultset is a pretty commonly requested thing in
> T-SQL. So I don't have to rehash it again (OK, I'm lazy...sometimes)
I
> refer you to Aaron Bertrand's article on the subject on aspfaq.com: 
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">ttp://www.aspfaq.com/show.asp?id=2462" target="_blank">h
ttp://www.aspfaq.com/show.asp?id=2462</a> 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite="mid1150258807.128411.20110@.h76g2000cwa.googlegroups.com"
> type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performance mon
itoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
> </pre>
> </blockquote>
> </body>
> </html>
> --080200050206060200040400--|||Oops, sorry (that'll teach me not to read the question properly). OK,
then you just need the grouping column to be stripped of the data you're
not interested in (hours, minutes, seconds, etc.). For example, if you
want hourly averages you can say (untested):
SELECT
DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS HourlyInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)
If you wanted the intervals to be every minute just change the "hh" in
the datetime functions to "mi" (untested):
SELECT
DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
If you use too small an interval eventually the DATEDIFF() function will
fail with an overflow (it'll try to return a number too big for an int)
so you might want to pick some arbitrary base date instead of 0, like
(untested):
SELECT
DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
AS SecondInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY
DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>Mike, I have already pivotted the result set. That is not what I need
>to know. In this post I am asking how to group the random time results
>to fixed increments. Say every hour over two w

s or ever minute over
>a day etc.
>Mike Hodgson wrote:
>
>
>|||On Wed, 14 Jun 2006 15:18:20 +1000, Mike Hodgson wrote:
(snip)
>If you wanted the intervals to be every minute just change the "hh" in
>the datetime functions to "mi" (untested):
> SELECT
> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
Hi Mike,
And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:
SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5

>If you use too small an interval eventually the DATEDIFF() function will
>fail with an overflow (it'll try to return a number too big for an int)
>so you might want to pick some arbitrary base date instead of 0, like
>(untested):
> SELECT
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
> AS SecondInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
Recommend format for dates is yyyymmdd, without the dashes. With the
dashes added, it is no longer guaranteed to be unambiguous under all
possible locale settings. (Not that it matters much when both date and
month are 1 anyway <g> ).
Hugo Kornelis, SQL Server MVP|||OK, so I still didn't read the original question
properly...<blush>...well, I'm a busy man. Thanks for the addition Hugo
(and thanks for the comment on my blog, I'll update it as soon as I get
a minute - it was written some time ago and I've learnt a few tricks
since then).
*mike hodgson*
http://sqlnerd.blogspot.com
Hugo Kornelis wrote:

>On Wed, 14 Jun 2006 15:18:20 +1000, Mike Hodgson wrote:
>(snip)
>
>Hi Mike,
>And to get the 5-minute intervals "togbabe" originally requested, throw
>some integer division into the equation:
> SELECT
> DATEADD(mi,
> 5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
> 0) AS FiveMinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5
>
>
>Recommend format for dates is yyyymmdd, without the dashes. With the
>dashes added, it is no longer guaranteed to be unambiguous under all
>possible locale settings. (Not that it matters much when both date and
>month are 1 anyway <g> ).
>
>|||Thanks mate. This is all good. I have implemented this and it is
working as a general mechanism for displaying my Performance Monitor
data. This is much better than using the performance monitor because
you can take readings for every minute over long periods of time and
change the granularity of the data over longer or shorter periods by
simply changing the 'datepart' argument. You can also archive
extracted data for 'long term trend analysis. Now, this is great and
thanks for that. One thing though, the date functions only allow
intervals of seconds, minutes, hours and days etc. Is there any way
anybody can think of of aggregating across time intervals of 10 minutes
or - say - 5 seconds etc. I believe that this can't be done with
the 'date functions.'
Thanks Mike for you solution.
Mike Hodgson wrote:
> Oops, sorry (that'll teach me not to read the question properly). OK,
> then you just need the grouping column to be stripped of the data you're
> not interested in (hours, minutes, seconds, etc.). For example, if you
> want hourly averages you can say (untested):
> SELECT
> DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS HourlyInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)
> If you wanted the intervals to be every minute just change the "hh" in
> the datetime functions to "mi" (untested):
> SELECT
> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
> If you use too small an interval eventually the DATEDIFF() function will
> fail with an overflow (it'll try to return a number too big for an int)
> so you might want to pick some arbitrary base date instead of 0, like
> (untested):
> SELECT
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
> AS SecondInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
>
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> togbabe wrote:
>
> --040707020103090502010403
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 10360
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Oops, sorry (that'll teach me not to read the question properly).
;
> OK, then you just need the grouping column to be stripped of the data
> you're not interested in (hours, minutes, seconds, etc.). For exampl
e,
> if you want hourly averages you can say (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS
> HourlyInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)</tt> 
> </blockquote>
> <tt>If you wanted the intervals to be every minute just change the "hh"
> in the datetime functions to "mi" (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS
> MinuteInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)</tt> 
> </blockquote>
> <tt>If you use too small an interval eventually the DATEDIFF() function
> will fail with an overflow (it'll try to return a number too big for an
> int) so you might want to pick some arbitrary base date instead of 0,
> like (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt>
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01') AS
> SecondInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')</tt> </blockquote>
> <tt> 
> Hope this helps. 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite="mid1150260526.579264.154670@.u72g2000cwu.googlegroups.com"
> type="cite">
> <pre wrap="">Mike, I have already pivotted the result set. That is not
what I need
> to know. In this post I am asking how to group the random time results
> to fixed increments. Say every hour over two w

s or ever minute over
> a day etc.
> Mike Hodgson wrote:
> </pre>
> <blockquote type="cite">
> <pre wrap="">How to pivot a resultset is a pretty commonly requested t
hing in T-SQL.
> So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
> you to Aaron Bertrand's article on the subject on aspfaq.com:
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">ttp://www.aspfaq.com/show.asp?id=2462" target="_blank">h
ttp://www.aspfaq.com/show.asp?id=2462</a>
> --
> *mike hodgson*
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlne
rd.blogspot.com</a>
>
> togbabe wrote:
> </pre>
> <blockquote type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performance
monitoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
>
> </pre>
> </blockquote>
> <pre wrap="">--080200050206060200040400
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3519
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type
">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>How to pivot a resultset is a pretty commonly requested thing in
> T-SQL. So I don't have to rehash it again (OK, I'm lazy...someti
mes) I
> refer you to Aaron Bertrand's article on the subject on aspfaq.com:<br&
gt;
> <a class="moz-txt-link-freetext" href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=ht
tp://www.aspfaq.com/show.asp?id=2462">"http://www.aspfaq.com/show.asp?id=2462"</a>&g
t;<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">htt
p:/
/www.aspfaq.com/show.asp?id=2462</a></a> 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> --<
;br>
> mike
> hodgson <a href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=http://s
qlnerd.blogspot.com">"http://sqlnerd.blogspot.com"</a>><a class="moz-txt-link-freetext" href
="http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a><
;/a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mid1150258807.128411.2
0110@.h76g2000cwa.googlegroups.com">"mid1150258807.128411.20110@.h76g2000cwa.g
ooglegroups.com"</a>
> type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performan
ce monitoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
> </pre>
> </blockquote>
> </body>
> </html>
> --080200050206060200040400--
> </pre>
> </blockquote>
> <pre wrap=""><!-->
> </pre>
> </blockquote>
> </body>
> </html>
> --040707020103090502010403--|||Yep - Hugo answered that in his post in this thread:
<quote>
And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:
SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5
</quote>
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>Thanks mate. This is all good. I have implemented this and it is
>working as a general mechanism for displaying my Performance Monitor
>data. This is much better than using the performance monitor because
>you can take readings for every minute over long periods of time and
>change the granularity of the data over longer or shorter periods by
>simply changing the 'datepart' argument. You can also archive
>extracted data for 'long term trend analysis. Now, this is great and
>thanks for that. One thing though, the date functions only allow
>intervals of seconds, minutes, hours and days etc. Is there any way
>anybody can think of of aggregating across time intervals of 10 minutes
>or - say - 5 seconds etc. I believe that this can't be done with
>the 'date functions.'
>Thanks Mike for you solution.
>Mike Hodgson wrote:
>
/www.aspfaq.com/show.asp?id=2462</a></a> 
;/a>
>
>

Grouping pblm

Hi,

I have a query which returns several data fields and one of the grouping criteria is the time.
The data format for the time field resembles this: "10.11.2005 15:45:37" .. which is 'date.month.year calltime'.
I want the query to group data by the hour, therefore i wrote the query like this: Left([AllCalls.CALLTIME],13) AS HourlyCallTime, the HourlyCallTime field shows the data in this format: 10.11.2005 15, however, the grouping is not done. It only groups properly when I do this: Left([AllCalls.CALLTIME],12) AS HourlyCallTime, but then the problem is that the HourlyCallTime field does not show the proper format, it only displays this much: '10.11.2005 1'

I hope some1 can help me out :)

ThksI speak Oracle SQL; this is, as far as I can tell, not a language I know, but perhaps this piece of advice will help you ...

In Oracle, date format you wrote as an example (10.11.2005 15:45:37) is only one (of many possible) representations of a date column. Dates are stored as a number, and it is up to the developer to choose format he wants to present data to the end user.

Now, in Oracle, you should do this: FIRST format date column to desired format, and THEN write string functions on it.

For example, it would look like this:
- First part of the solution:
SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss') FROM ...

- Second part of the solution:
SELECT SUBSTR(TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss'), 1, 13) FROM ...

I really wouldn't know is this the case in your database, but - if nothing else shows up - you could try with this.|||Hello Littlefoot,

Thks 4 the quick rep, i've been working on it but now seem to be having sum other pblm,
my query

SELECT Zones.Zone, Left([AllCalls.CALLTIME],13) AS HourlyCallTime, SCCount.Connected, ((SCCount.Connected/(UCCount.NotConnected+SCCount.Connected)*100)) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone] And (AllCalls.PREFIX)=[SCCount].[PREFIX] And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone, Left([AllCalls.CALLTIME],13), SCCount.Connected, UCCount.NotConnected, AllCalls.PREFIX;

is not grouping all the calls, it's separating the connected and not connected such that am having twice the same row of data.
When I remove the SCCount.Connected and UCCount.NotConnected, it doesn't run, comes up with "query does not include the specified xpression .."

:S|||Hm, it seems that you, actually, do not want to GROUP data, but BREAK output on the hour. I'd say that use of a GROUP BY is meaningless if there's no aggregate function (such as MAX or AVG or COUNT) in the SELECT statement.

I don't know the tool you use (do you run this query on command prompt or in a reporting tool); if it is some kind of a report builder, you might want to use master-detail blocks of data.

On command prompt, all you can do is use of an ORDER BY clause and, eventually, use of (as Oracle provides) some kind of a BREAK command which will visually break data on the screen. Something like this:SQL> break on hire_year
SQL> select to_char(hiredate, 'yyyy') hire_year, ename, hiredate
2 from emp
3 order by 1, 2;

HIRE ENAME HIREDATE
-- ---- ---
1980 SMITH 17.12.80
1981 ALLEN 20.02.81
BLAKE 01.05.81
CLARK 09.06.81
FORD 03.12.81
JAMES 03.12.81
JONES 02.04.81
KING 17.11.81
MARTIN 28.09.81
TURNER 08.09.81
WARD 22.02.81
1982 MILLER 23.01.82

12 rows selected.

SQL>|||But I do want to group the data by zones and by the hour, but the query wouldn't run unless i include the connected n notconnected as part of the grouping criteria as well which is messing it up|||I assume Connected and NotConnectedand are some times and you calculete percentage. So if you group data by Zones etc. why don't you summarize times?

SELECT
Zones.Zone,
Left([AllCalls.CALLTIME],13) AS HourlyCallTime,
sum(SCCount.Connected),
((sum(SCCount.Connected)/(sum(UCCount.NotConnected+SCCount.Connected))*100) ) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone]
And (AllCalls.PREFIX)=[SCCount].[PREFIX]
And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone,
Left([AllCalls.CALLTIME],13),
AllCalls.PREFIX;

BTW you have too many brackets there. It's MS Access generated code, isn't it? :-) Why don't you select AllCalls.PREFIX if you group by it?|||Connected and notconnected are the count result from another query, and access wont let me run the query unless I have 'SCCount.Connected' and 'UCCount.NotConnected' in the grouping criteria ..

Yes, MS Access keeps adding loads of brackets when i run the query and go bak 2 sql view :Ssql

grouping or formatting issue

hey there

I have a date time field

Fields!CreatedDate.Value which I am grouping on and Counting on an ID Field

result of grouping is this

7/13/2006 12:00:00 AM 1

7/21/2006 12:00:00 AM 11

8/7/2006 12:00:00 AM 3

8/8/2006 12:00:00 AM 2

I know if I put (Month(Fields!CreatedDate.Value)

I get this result

7 1

7 11

8 3

8 2

What I really want is

July 12

August 5

how do I do this please

thanks

Hi there!
I dont know if this will resolve your issue but maybe if you try the following:

Format((Month(Fields!CreatedDate.Value)), "MMM")

If this works like I think it might then it should return:

Jul 12

Aug 5......etc

I cant test right now so I cant be sure...!?|||

Try

Monthname(Datepart("m", Fields!CreatedDate.Value))

This report function will return the "January, Feburary, March and etc...

Ham

|||

ok thanks for the replies

unfortunately not quite complete

Ham what I get then is

July 1

July 11

August 1

August 3

etc

so I need

total for July

total for August

etc

cheers

|||

thought I should put the sql

SELECT TicketNumber, CreatedDate
FROM Ticket
ORDER BY CreatedDate

grouped on created date - associated to parent group created date

count on ticketnumber

cheers

Dianne

|||

jewel,

Try

Placing the expression Monthname(Datepart("m", Fields!CreatedDate.Value)) into your group expression (edit group), this will cause the group to only consider the month when summating the values.

Ham

|||

thanks Ham

I had to put this in the group expression and the field expression to get it to work? I presume this is ok?

But yes it does work now thanks heaps for your help

jewel

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful

|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand

Monday, March 26, 2012

Grouping data by time period

Hi everyone,

I'm now on a TSQL problem and hope somebody here can help me.
I have a log data for a website that logs IP addresses and date/time of the access, and I shall group IP values by time period, beginning from the 1st access, at each 30 minutes.

Does anyone here can help me with this issue? Any tips will be very appreciated.

TIA

MarceloHere is a generic code that you should get you started.
declare @.interval int
set @.interval=30
select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1|||Thanks a lot, worked like a charm. Only thing I need to figure out now is how to count 30 minutes since first access from an IP address, but your code will help me a lot.

Regards,

Grouping by Time

Hello,
I am trying to create a query where I can group by the time of day something
happens.
For example, somebody (we don't care who) does something ( 'ev' below). We
capture the date and time this thing happens.
For analysis, a doctor wants to know what times the day these things are
happening. The grouping would be by the hour, counting the number of times
a specific thing happens.
I am not sure how to represent the hourly range. Maybe by a number ? For
example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
Basically, the output should be
time range ev number of ev's in the time range.
I tried this query, but did not work.
SELECT ev, MIN(dt), COUNT(*)
FROM YourTable
GROUP BY ev, DATEDIFF(HH,'20000101',dt)
Thanks for your time.What about that ?
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:Zasge.6007$Ay3.501@.lakeread06...
> Hello,
> I am trying to create a query where I can group by the time of day
> something happens.
> For example, somebody (we don't care who) does something ( 'ev' below).
> We capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of
> times a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice
> here.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>|||Try,
use northwind
go
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
SELECT
ev,
MIN(dt),
COUNT(*)
FROM
YourTable
GROUP BY
ev,
convert(char(13), dt, 126)
drop table YourTable
AMB
"Jack" wrote:

> Hello,
> I am trying to create a query where I can group by the time of day somethi
ng
> happens.
> For example, somebody (we don't care who) does something ( 'ev' below). W
e
> capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of time
s
> a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here
.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>
>|||That works great. Thank you.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uOyubrlVFHA.628@.TK2MSFTNGP09.phx.gbl...
> What about that ?
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Jack" <jack@.jack.net> schrieb im Newsbeitrag
> news:Zasge.6007$Ay3.501@.lakeread06...
>

Grouping by N minute intervals

I have a query that returns full date/time, hour, and minute, and other
stuff. The report needs to be grouped by various minute intervals, but I am
having difficulty getting it grouped by any interval period.
How do I group my data in 15 Minute Intervals. Is it similar to group by 30
Minute Intervals? I am making the presumption that a variable can be used to
allow the user to change the interval from the values of 15, 20, and 30
Minutes.
I am using SQL Server to fetch the data, and have access to the query code,
so if any suggestions involve something on the Query end instead of the
report end, I can do that too.Rob,
Either you have to use analysis services or create intervals using SQL in
the dataset. I had a smiliar problem with sales reports. In some of the
months we didn't have any sales for a particular product. The reports
instead of showing zero sales, they were not showing up at all. So, I
created zero sales for every product, for a certain period of time - on the
fly and sum group them with actual sales.
Hope this helps.
Regards,
Cem
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:D54A54E9-EE31-48A2-8318-2C891F1B533C@.microsoft.com...
> I have a query that returns full date/time, hour, and minute, and other
> stuff. The report needs to be grouped by various minute intervals, but I
am
> having difficulty getting it grouped by any interval period.
> How do I group my data in 15 Minute Intervals. Is it similar to group by
30
> Minute Intervals? I am making the presumption that a variable can be used
to
> allow the user to change the interval from the values of 15, 20, and 30
> Minutes.
> I am using SQL Server to fetch the data, and have access to the query
code,
> so if any suggestions involve something on the Query end instead of the
> report end, I can do that too.

Friday, March 23, 2012

grouping by a datetime column

i want to group the records in a table by day , using a datetime column.
Therefore I have to get rid of the time of that column before grouping.
What is the proper way to do that?
thnks..prefect wrote:
> i want to group the records in a table by day , using a datetime column
.
> Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>
GROUP BY
DATEPART(month, datevalue),
DATEPART(day, datevalue),
DATEPART(year, datevalue)|||SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>i want to group the records in a table by day , using a datetime column.
>Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>|||> GROUP BY
> DATEPART(month, datevalue),
> DATEPART(day, datevalue),
> DATEPART(year, datevalue)
FYI, on a large table, this can be a significant performance hit...
In fact, my solution is only marginally better. The best solution would
probably combine a static calendar table (see http://www.aspfaq.com/2519 for
some practical usage).|||that is what i look for.
thanks..
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||Aaron Bertrand [SQL Server MVP] wrote:
> FYI, on a large table, this can be a significant performance hit...
> In fact, my solution is only marginally better. The best solution would
> probably combine a static calendar table (see http://www.aspfaq.com/2519 f
or
> some practical usage).
>
Agreed.|||Aaron , I want to send the DateColumnName to a UDF for some processing,
then return something.
But I have a error like "DateColumnName is not in group by clause..."
My usage is as follows:
SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||What exactly are you doing, formatting it for the client? Why don't you let
the presentation/client tier do this? What does dbo.MyUDF do, exactly, that
CONVERT() with a style option couldn't do?
Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that exist
in the SELECT list that are not constants or aggregates must also appear in
GROUP BY clause. But a slightly more efficient way would be to perform the
function against the result instead of during the aggregation:
SELECT
dt,
dbo.MyUDF(dt),
cnt
FROM
(SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
cnt = COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
) x
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1|||I created a computed Column for DateColumnName
as DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
and used this computed column for grouping and parameter for MyUdf. it is
working.
But i would wanna know if there is a better way..
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||
> What exactly are you doing, formatting it for the client? Why don't you
> let the presentation/client tier do this?
yes it should be this way. But for some reason off my hand , i am not able
to do
in the presentation layer.

> What does dbo.MyUDF do, exactly, that CONVERT() with a style option
> couldn't do?
no, unfortunately..

> Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that
> exist in the SELECT list that are not constants or aggregates must also
> appear in GROUP BY clause. But a slightly more efficient way would be to
> perform the function against the result instead of during the aggregation:
>
> SELECT
> dt,
> dbo.MyUDF(dt),
> cnt
> FROM
> (SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> cnt = COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ) x
> ORDER BY 1;
i will try that, can you comment my other post?

> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
>

Wednesday, March 21, 2012

Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan

Monday, March 19, 2012

Group for each half hour

I am trying to make a report that lists phone calls that are grouped by time (using group export). I can do it by hour using the option "section will be printed: for each hour" but i need it to do it for each half hour and there is no option for that. It is possible do to it? How can i do it (In Crystal Reports 10 or 11)?

Thank you.Create a formula @.half_an_hour:

if minute({table.call_date_time})<30 or
(minute({table.call_date_time})=30 and second({table.call_date_time})=0)
then // returns h:00 - h:30
totext(hour({table.call_date_time}),0)&':00 - '&totext(hour({table.call_date_time}),0)&':30'
else // returns h:31 - h:59
totext(hour({table.call_date_time}),0)&':31 - '&totext(hour({table.call_date_time}),0)&':59'

The formula checks to see if the minute<30
or
is equal to 30 and the second=0 then it returns 'h:00 - h:30' otherwise
'h:31 - h:59'

Group your records on {table.cal_date_time} for each hour, suppress the GH and GF of that group;
then group your records on the {@.half_an_hour} formula.

As the 'Grop2 Name' field returns only time info, you will need to insert the {table.cal_date_time} and to format it as 'Date'.|||Thank you for the reply!! I can now group the calls by half hour.

group footer and table footer sums used in % formula

I'm having a hard time understanding how to use my group footer Sum to
be used in a formula with my table footer Sum to get a % (group) of
total (table)? They are both text boxes Sum(Fields!loanamount.Value).
These are not fields that I can grab and put into an expression. How do
I proceed? Thanks for any help.Your expression would be:
=Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
"datasetForTABLE") * 100
Where group1NAME is the name of the grouping in which you are summing - this
is called the SCOPE. The name of the dataset that you bound to the TABLE
itself is what you will scope for "datasetForTABLE" (be sure to include the
quotes).
=-Chris
"nancy" <northtexassupply@.yahoo.com> wrote in message
news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> I'm having a hard time understanding how to use my group footer Sum to
> be used in a formula with my table footer Sum to get a % (group) of
> total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> These are not fields that I can grab and put into an expression. How do
> I proceed? Thanks for any help.
>|||Thanks. That helped alot.
Chris Conner wrote:
> Your expression would be:
> =Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
> "datasetForTABLE") * 100
> Where group1NAME is the name of the grouping in which you are summing - this
> is called the SCOPE. The name of the dataset that you bound to the TABLE
> itself is what you will scope for "datasetForTABLE" (be sure to include the
> quotes).
> =-Chris
> "nancy" <northtexassupply@.yahoo.com> wrote in message
> news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> > I'm having a hard time understanding how to use my group footer Sum to
> > be used in a formula with my table footer Sum to get a % (group) of
> > total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> > These are not fields that I can grab and put into an expression. How do
> > I proceed? Thanks for any help.
> >

Monday, March 12, 2012

group data by weeks

I have data entered into a table using a datetime field. How can I group the data one week at a time and show mulitple weeks at a time?Look at the datepart function.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx|||

What do you mean by showing multiple weeks at a time?

Lets assume you have columns Cost as currency and BoughtOn as date

SELECT COUNT(Cost) as Items, SUM(Cost) As TotalPrice, DATEPART(wk, BoughtOn) As Week FROM yourTable GROUP BY DATEPART(wk, BoughtOn)

Is that what you need?

--
SvenC

|||

I'm trying for something like this:

Name

Week End Date

Product1

Product2

Porduct3

Rep1

9/9/2006

1130

331

Rep1

9/16/2006

130

3021

1452

Rep1

9/23/2006

1351

3513

1542

What you have above is very close SvenC.

|||

Can you show your table definition or definitions from which the above result set should come?

--
SvenC

|||

Here is the sql I am using now:

SELECT dbo.sales_rep.sales_rep_first_name + ' ' + dbo.sales_rep.sales_rep_last_name AS Name, dbo.sale.sale_dts, dbo.sale.sale_type,

SUM(dbo.sale.total_pt_of_sale_amt) AS POS, DATEPART(wk, sale_dts) As Week

FROM dbo.sale INNER JOIN

dbo.sales_rep ON dbo.sale.sales_rep_user_id = dbo.sales_rep.sales_rep_user_id

WHERE (dbo.sale.sale_dts BETWEEN @.start AND @.end) and sale_type in ('qqq', 'ttt', 'fff', '11111)

and dbo.sales_rep.sales_rep_user_id in ('id123','id1234','id2151','id5214')

GROUP BY DATEPART(wk, sale_dts), dbo.sale.sale_dts, dbo.sales_rep.sales_rep_last_name, dbo.sales_rep.sales_rep_first_name, dbo.sale.sale_type, dbo.sale.total_pt_of_sale_amt

The sale table is

sale_id int
sales_rep_user_id varchar(7)
sale_dts datetime
sale_type varchar(10)
total_pt_of_sale_amt

|||And what results do you get with that?

Do you need to concatenate the week datepart with the year datepart to get uniqueness for a given week?|||

I get data like this:

Rep Name

Date

Type

POS

Week

|||So you don't get any data?|||

Sorry, didn't know you wanted to see the data:

Rep Name

Date

Type

POS

Week

RepName1

9/5/2006

Product1

1731

RepName1

9/5/2006

Product1

216

RepName1

9/5/2006

Prodcut2

240

RepName1

9/5/2006

Product1

1960

RepName1

9/5/2006

Prodcut2

15000

RepName2

9/5/2006

Product1

120

RepName2

9/5/2006

Prodcut2

600

RepName2

9/6/2006

Product1

800

RepName2

9/6/2006

Product1

1680

RepName2

9/6/2006

Product1

168

RepName2

9/6/2006

Product1

348

|||That helps.

Now, with your data, where do you want to go? What should the data look like?|||

I'd like to had the date listed as the Satuday of the week. For example: 9/9/2006.

Something like this:

Rep Name	Date	Product1	Product2	Product3
Rep1	9/9/2006	1302	1731	3020
Rep1	10/14/2006	680	311	3151
Rep1	10/21/2006	21001	15000	36
Rep2	10/21/2006	0	3033	11220
Rep3	9/9/2006	1	1002	12125
Rep3	9/16/2006	3320	2111	3601
Rep3	9/23/2006	12102	2210	1101
Rep3	10/14/2006	2151	3264	36

Where there is one record for each rep per week.

|||So you want to pivot your data in a query? That is to say you want dynamic columns? So you'll have as many product columns as the max(type) per sales rep?

Oh boy...|||Pivot the data, yes. However I'm only looking for four (4) sale types and five (5) sales reps.|||Well, try this.

select repname, weekend, sum(Product1Col), sum(Product2Col), sum(Product3Col), sum(Product4Col)
from (
select [repname], 'weekend' = case datepart("dw",[YourDateField])
when 1 then dateadd("dd",6,[YourDateField])
when 2 then dateadd("dd",5,[YourDateField])
when 3 then dateadd("dd",4,[YourDateField])
when 4 then dateadd("dd",3,[YourDateField])
when 5 then dateadd("dd",2,[YourDateField])
when 6 then dateadd("dd",1,[YourDateField])
when 7 then [YourDateField]
end,
'Product1Col' = case [type]
when 'Product1' then [POS]
else 0
end,
'Product2Col' = case [type]
when 'Product2' then [POS]
else 0
end,
'Product3Col' = case [type]
when 'Product3' then [POS]
else 0
end,
'Product4Col' = case [type]
when 'Product4' then [POS]
else 0
end
from table
where [repname] in ('Rep1','Rep2','Rep3','Rep4','Rep5')
) GROUP BY [repname], [weekend]

Friday, March 30, 2012

Grouping with non-existent data

Wednesday, March 28, 2012

Grouping problem

Grouping performance monitor time data after pivoting help please

Grouping pblm

grouping or formatting issue

Grouping on time

grouping on date without time

Monday, March 26, 2012

Grouping data by time period

Grouping by Time

Grouping by N minute intervals

Friday, March 23, 2012

grouping by a datetime column

Wednesday, March 21, 2012

Group with Time Values - Please Help

Monday, March 19, 2012

Group for each half hour

group footer and table footer sums used in % formula

Monday, March 12, 2012

group data by weeks

Firehose mode

Blog Archive

About Me