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 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 a query in 30 seconds

Hi,

How can I make a query and group the registries in a interval of 30 seconds...like

for each line I have a datetime field that have all the day, and I need it to return just like

TIME Contador_type1 Contador_type2 Total

01-01-2006 00:00:30.000 2 5 7

01-01-2006 00:01:00.000 3 7 10

It's just an example...but that's the result that I need and my table is

data_hora -- datetime field

tipo - 1 or 2 -- count

nrtelefone - that's is the number dialed.

Thanks

Hi there and welcome to the groups,

see if that one helps:

As you didn��t specified your logic in your request (if it should be rounded up or down if its perhaps 00:15 (00:00 vs. 00:30)), you possible have to tweak the case branch.

DROP TABLE SomeTable

GO

CREATE TABLE SomeTable

(

SomeColumn datetime,

SomeValue int

)

INSERT INTO SomeTable

VALUES(GETDATE(),1)

INSERT INTO SomeTable

VALUES(DATEADD(mi,30,GETDATE()),1)

SELECT SUm(SomeValue),

DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

FROM

(

SELECT

CONVERT(VARCHAR(10),SOmeColumn,112) AS Date ,

SomeValue,

DATEPART(hh,SomeColumn) Hours,

(CASE WHEN DATEPART(mi,SomeColumn) <30 THEN 30 ELSE 0 END) Minutes

From SomeTable

) SUbQUery

GROUP BY DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

Drop Table SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Use a similar solution to that suggested by sussemeyer above, but use integer division of 30 (rounds result to nearest whole number) with the seconds, as this would be more efficient, and would do away with the CASE statement, which is computationally costly.

HTH For more SQL tips, check out my blog below:

|||

Hi,

I tried that and didn't worked, I need to count how many rows are, in a period of 30 seconds.

Thanks for helping almost there...

|||

As I the code above didn't work can you give me an example ?

Thanks

|||

Hi,

OK you didn��t mention that you just wanted to have the count, then just replace the SUM() with a COUNT(*) and you��ll be fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK,

But in the case of 30 seconds, it don't work ?

I tried to change...but it increase the minutes wrongly..

Thanks

|||Coudl you please post some sample data ?

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Does this help?

Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TimeChunk, count(*)

From myTable

Group By Floor(Convert(Float, data_hora) * 24 * 60 *2);

|||

Here it is..with the columns

COD_CLIENTE,DATA_HORA,NRTELEFONE,RAMAL,TEMPO_SEGUNDOS,TIPO,TEMPO_ATENDIMENTO,JAPROCESSADO VALOR,VALOR_CONC,VALOR_TARIFA,VALOR_TARIFA_CONC,CLASSIFICA,LOCALIDADE,VALOR_TEMPO,NUMERO_E1, BLOQUEADO,TABELA_TELEFONICA
149,2006-03-01 09:48:26.000,0800784403,6935,1.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,.5000,01132816900,1,1
149,2006-03-01 09:44:16.000,01144145754,6922,324.0,2,0,NULL,.6600,.7536,.1100,.1256,CONUR,ATIBAIA - SP,6.0000,01132816900,1,1
149,2006-03-01 09:49:25.000,01137417505,6935,108.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:53:59.000,01159258359,6941,103.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:57:58.000,01332995733,6922,228.0,2,0,NULL,1.0440,1.9800,.2900,.5500,DDD,SANTOS - SP,3.6000,01132816900,1,1
149,2006-03-01 10:01:20.000,01184262728,6923,76.0,2,0,NULL,.5500,.6710,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.1000,01132816900,1,1
149,2006-03-01 09:55:32.000,01121874200,6932,457.0,2,0,NULL,.6800,.9600,.0850,.1200,LOCAL,SAO PAULO - SP,8.0000,01132816900,1,1
149,2006-03-01 10:02:24.000,05534125161,6937,104.0,2,0,NULL,.6720,.8800,.4200,.5500,DDD,SANTA MARIA - RS,1.6000,01132816900,1,1
149,2006-03-01 10:05:05.000,01121617500,6935,67.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:04:41.000,01934627164,6937,136.0,2,0,NULL,.8820,1.1550,.4200,.5500,DDD,AMERICANA - SP,2.1000,01132816900,1,1
149,2006-03-01 10:11:05.000,01934582333,6936,46.0,2,0,NULL,.4200,.5500,.4200,.5500,DDD,SANTA BARBARA D OESTE - SP,1.0000,01132816900,1,1
149,2006-03-01 10:12:14.000,01121617525,6935,9.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:08:37.000,01161035061,6913,255.0,2,0,NULL,.4250,.6000,.0850,.1200,LOCAL,SAO PAULO - SP,5.0000,01132816900,1,1
149,2006-03-01 10:11:35.000,01434546110,6941,263.0,2,0,NULL,1.7640,2.3100,.4200,.5500,DDD,BAURU - SP,4.2000,01132816900,1,1
149,2006-03-01 10:18:37.000,01934063393,6936,87.0,2,0,NULL,.5460,.7150,.4200,.5500,DDD,AMERICANA - SP,1.3000,01132816900,1,1
149,2006-03-01 10:26:12.000,01121617525,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:25:25.000,01132536576,6920,78.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:23:20.000,01162222734,6904,244.0,2,0,NULL,.3400,.4800,.0850,.1200,LOCAL,SAO PAULO - SP,4.0000,01132816900,1,1
149,2006-03-01 10:29:40.000,01991638344,6924,87.0,2,0,NULL,1.4820,1.5600,1.1400,1.2000,VC2,AREA 019 - CELULAR - SP,1.3000,01132816900,1,1
149,2006-03-01 10:33:54.000,011102,6926,70.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:35:22.000,01934692606,6926,110.0,2,0,NULL,.7140,.9350,.4200,.5500,DDD,AMERICANA - SP,1.7000,01132816900,1,1
149,2006-03-01 10:41:11.000,01161280656,6924,28.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:38:32.000,01381340603,6921,257.0,2,0,NULL,4.6740,4.9200,1.1400,1.2000,VC2,AREA 013 - CELULAR - SP,4.1000,01132816900,1,1
149,2006-03-01 10:50:16.000,01121617500,6935,73.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:50:46.000,08007728486,6923,186.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,2.9000,01132816900,1,1
149,2006-03-01 10:54:16.000,01169574019,6923,124.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:57:38.000,01155102193,6915,87.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:58:53.000,01171484387,6947,47.0,2,0,NULL,.3000,.3660,.5000,.6100,VC1,AREA 011 - CELULAR - SP,.6000,01132816900,1,1
149,2006-03-01 11:00:36.000,01155922414,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:59:43.000,01171484387,6925,104.0,2,0,NULL,.8000,.9760,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.6000,01132816900,1,1

And what I need to return is something like(ex not using the above)

DATA_HORA TOTAL

01/03/2006 00:00:30.000 5

01/03/2006 00:01:00.000 2

01/03/2006 00:01:30.000 6

Thanks

|||It's almost that...but I need the role day time from 01/03/2006 00:00:00.000 to 01/03/2006 23:59:59.997 for example|||

How about this then:

Select Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0) As TimeChunk, count(*)

From myTable

Group By Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0);

|||

OK, this works, but it does not group by an interval of 30 seconds, it's just put together all registries that has the same time....

Did you get the idea or not ? Like I did something(based on your idea) like a while the increase the time from 01/01/2006 00:00:00.000 to 02/01/2006 23:59:30.000 just using dateadd(ss,30,@.date) but the problem is....to fill the total field I need to do a select that counts and it takes a long time to complete...

I'm asking if there is a faster way...

|||

I am not sure what you are after if my last SQL statement does not produce the result you are looking for. The statement I gave you will group all records together that fall withing the 30 intervals. If it does not then I did something wrong.

|||

Hum...ok..I'll check again...but do you Know webchart ? I'm using this query to build a smoothlinechart, but it's getting an strange format..

Do you think that I need to pass the whole interval, or you think that the values that is missing the graph will automatically put 0 ?

Thanks anyway, it worked with Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TEMPO, count(*) AS TOTAL From pabx WHERE cod_cliente = 221 AND data_hora BETWEEN '20060201' AND '20060203' Group By Floor(Convert(Float, data_hora) * 24 * 60 *2) ORDER BY Floor(Convert(Float, data_hora) * 24 * 60 *2)

sql

group data by time interval

What SQL will group timed events, so I can see how many records were recorde
d
in each 10 minute interval. There may be no data for some 10 minute
intervals, in which case I must report zero.
for example...with raw data in a table as
2006/06/19 12:00:00 record 1
2006/06/19 12:05:00 record 2
2006/06/19 12:21:00 record 3
2006/06/19 12:22:00 record 4
2006/06/19 12:23:00 record 5
2006/06/19 12:24:00 record 6
result should be
2 records for 12:00-12:10
0 records for 12:10-12:20
4 records for 12:20-12:30Well, should it be inclusive? e.g. what if there are three records with
2006-06-19 12:10:00? Which bucket do they fall in? The first, the second,
both?
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:B406B58A-DCA1-4AC4-828B-C057F91B0FCF@.microsoft.com...
> What SQL will group timed events, so I can see how many records were
> recorded
> in each 10 minute interval. There may be no data for some 10 minute
> intervals, in which case I must report zero.
> for example...with raw data in a table as
> 2006/06/19 12:00:00 record 1
> 2006/06/19 12:05:00 record 2
> 2006/06/19 12:21:00 record 3
> 2006/06/19 12:22:00 record 4
> 2006/06/19 12:23:00 record 5
> 2006/06/19 12:24:00 record 6
>
> result should be
> 2 records for 12:00-12:10
> 0 records for 12:10-12:20
> 4 records for 12:20-12:30
>|||Here's a start, perhaps.
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
CREATE TABLE #blat (ts SMALLDATETIME);
INSERT #blat SELECT '20060619 12:00:00'
UNION ALL SELECT '20060619 12:05:00'
UNION ALL SELECT '20060619 12:21:00'
UNION ALL SELECT '20060619 12:22:00'
UNION ALL SELECT '20060619 12:23:00'
UNION ALL SELECT '20060619 12:24:00';
SELECT TOP 144 id = IDENTITY(INT,1,1)
INTO #foo
FROM sysobjects a
CROSS JOIN sysobjects b;
SELECT [start] = CONVERT(CHAR(5), [start], 108),
[end] = CONVERT(CHAR(5), [end], 108),
[count] = COALESCE(COUNT(b.ts), 0)
FROM
(
SELECT
[start] = DATEADD(MINUTE, (id-1)*10, 0+DATEDIFF(DAY, 0, GETDATE())),
[end] = DATEADD(MINUTE, id*10, 0+DATEDIFF(DAY, 0, GETDATE()))
FROM #foo
) f
LEFT OUTER JOIN
#blat b
ON
b.ts >= f.[start]
AND b.ts < f.[end]
WHERE
f.[end] > '20060619 12:00'
AND f.[end] <= '20060619 12:30'
GROUP BY CONVERT(CHAR(5), [start], 108),
CONVERT(CHAR(5), [end], 108)
ORDER BY 1;
DROP TABLE #blat, #foo;
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:B406B58A-DCA1-4AC4-828B-C057F91B0FCF@.microsoft.com...
> What SQL will group timed events, so I can see how many records were
> recorded
> in each 10 minute interval. There may be no data for some 10 minute
> intervals, in which case I must report zero.
> for example...with raw data in a table as
> 2006/06/19 12:00:00 record 1
> 2006/06/19 12:05:00 record 2
> 2006/06/19 12:21:00 record 3
> 2006/06/19 12:22:00 record 4
> 2006/06/19 12:23:00 record 5
> 2006/06/19 12:24:00 record 6
>
> result should be
> 2 records for 12:00-12:10
> 0 records for 12:10-12:20
> 4 records for 12:20-12:30
>|||>> What SQL will group timed events, so I can see how many records [sic] were rec
orded in each 10 minute interval. <<
Populate a table with the desired time slots, thus:
CREATE TABLE ReportTimeslots
(range_name CHAR(18) NOT NULL PRIMARY KEY,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time));
Then do your query.
SELECT R.range_name, COUNT(*) AS event_cnt
FROM ReportTimeslots AS R
LEFT OUTER JOIN
Events AS E
ON E.event_time BETWEEN R.start_time AND R.end_time
GROUP BY R.range_name;
Another trick is a VIEW that sets itself each day by using the
CURRENT_TIMESTAMP for the range_name and does a little temporal math to
get the proper start and end times.|||Aaron,
Using your script as a start, I came up with this:
select cnt,
dateadd(mi, min10*10, '20060619') mfrom_inclusive,
dateadd(mi, min10*10 + 10, '20060619') mto_exclusive
from(
select count(*) cnt, datediff(mi, '20060619', ts)/10 min10 from
#blat
group by datediff(mi, '20060619', ts)/10
) t|||Yes, clever, and I considered inlining it in a similar way, but the
following row would be missing from the result:
0 records for 12:10-12:20
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1150744942.340640.153600@.h76g2000cwa.googlegroups.com...
> Aaron,
> Using your script as a start, I came up with this:
> select cnt,
> dateadd(mi, min10*10, '20060619') mfrom_inclusive,
> dateadd(mi, min10*10 + 10, '20060619') mto_exclusive
> from(
> select count(*) cnt, datediff(mi, '20060619', ts)/10 min10 from
> #blat
> group by datediff(mi, '20060619', ts)/10
> ) t
>|||You can write a TVF to generate your time-series and then left-outer-join it
with the original data to preserve groups.
Conor
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uyqgdZ9kGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Yes, clever, and I considered inlining it in a similar way, but the
> following row would be missing from the result:
> 0 records for 12:10-12:20
>
> "Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
> news:1150744942.340640.153600@.h76g2000cwa.googlegroups.com...
>|||I agree, thanks for the feedback. This takes care of it:
select coalesce(cnt, 0),
dateadd(mi, (id-1)*10, '20060619') mfrom_inclusive,
dateadd(mi, (id-1)*10 + 10, '20060619') mto_exclusive
from #foo left join (
select count(*) cnt, datediff(mi, '20060619', ts)/10 min10 from
#blat
group by datediff(mi, '20060619', ts)/10
) t
on #foo.id = t.min10+1
I would be interesting to compare the performance of both queries on
different hardware.|||Hi Alexander,
The query written is neat and clean
But would it work for boundary value '
like if I reduce the interval from 10 to 5 minute and it show 1 rows in
count instead of 2.
for 12:05:00
With warm regards
Alexander Kuznetsov wrote:
> Aaron,
> Using your script as a start, I came up with this:
> select cnt,
> dateadd(mi, min10*10, '20060619') mfrom_inclusive,
> dateadd(mi, min10*10 + 10, '20060619') mto_exclusive
> from(
> select count(*) cnt, datediff(mi, '20060619', ts)/10 min10 from
> #blat
> group by datediff(mi, '20060619', ts)/10
> ) t

Group by Time interval.

I have a table(work_order) with time as varchar(5).

The values in table looks like this

work_order_id rtim

1 08:15
2 08:45
3 10:13
4 14:56

and so on...

I want to count how many work orders for every half an hour.

The result should look like this

Hours Count
8 10
8:30 15
9 34
9:30 03

and so on...

really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.

Any Help is Appreciated.

Thankyou.
Jaidev ParuchuriBetter to store your times as a DATETIME column:

CREATE TABLE Work_Order (work_order_id INTEGER PRIMARY KEY, rtim DATETIME
NOT NULL)

INSERT INTO Work_Order VALUES (1, '2003-11-11T08:15:00')
INSERT INTO Work_Order VALUES (2, '2003-11-11T08:45:00')
INSERT INTO Work_Order VALUES (3, '2003-11-11T10:13:00')
INSERT INTO Work_Order VALUES (4, '2003-11-11T14:56:00')

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',rtim)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

If you have to keep the Rtim column as CHAR:

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',
CONVERT(DATETIME,rtim,108)
)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

--
David Portas
----
Please reply only to the newsgroup
--|||"Jaidev Paruchuri" <jaidev@.criticalresourcetech.com> wrote in message
news:f885ab3.0311110746.7084cc95@.posting.google.co m...
> I have a table(work_order) with time as varchar(5).
> The values in table looks like this
> work_order_id rtim
> 1 08:15
> 2 08:45
> 3 10:13
> 4 14:56
> and so on...
> I want to count how many work orders for every half an hour.
> The result should look like this
> Hours Count
> 8 10
> 8:30 15
> 9 34
> 9:30 03
> and so on...
> really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.
> Any Help is Appreciated.
> Thankyou.
> Jaidev Paruchuri

CREATE TABLE Work_Orders
(
work_order_id INT NOT NULL PRIMARY KEY,
rtim CHAR(5) NOT NULL
)

SELECT Hrs.h + Sep.s + Mins.begin_min AS start_time,
COUNT(rtim) AS order_count
FROM (SELECT '00' AS h UNION ALL SELECT '01' AS h UNION ALL
SELECT '02' AS h UNION ALL SELECT '03' AS h UNION ALL
SELECT '04' AS h UNION ALL SELECT '05' AS h UNION ALL
SELECT '06' AS h UNION ALL SELECT '07' AS h UNION ALL
SELECT '08' AS h UNION ALL SELECT '09' AS h UNION ALL
SELECT '10' AS h UNION ALL SELECT '11' AS h UNION ALL
SELECT '12' AS h UNION ALL SELECT '13' AS h UNION ALL
SELECT '14' AS h UNION ALL SELECT '15' AS h UNION ALL
SELECT '16' AS h UNION ALL SELECT '17' AS h UNION ALL
SELECT '18' AS h UNION ALL SELECT '19' AS h UNION ALL
SELECT '20' AS h UNION ALL SELECT '21' AS h UNION ALL
SELECT '22' AS h UNION ALL SELECT '23' AS h) AS Hrs
CROSS JOIN
(SELECT ':' AS s) AS Sep
CROSS JOIN
(SELECT '00' AS begin_min, '29' AS end_min
UNION ALL
SELECT '30' AS begin_min, '59' AS end_min) AS Mins
LEFT OUTER JOIN
Work_Orders AS WO
ON rtim BETWEEN Hrs.h + Sep.s + Mins.begin_min AND
Hrs.h + Sep.s + Mins.end_min
GROUP BY Hrs.h + Sep.s + Mins.begin_min

Regards,
jag|||John

This query is beyond Excellence !!

This is exactly what i need.

Thank you for valuable your time!

regards,
--Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
David,

Your Query worked fine.
I didnt look at it earlier.

Thankyou very much .
Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Group by Datepart 30 sec interval?

Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my English
This example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:

>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English
|||Thanks for answering!! As I told before I��m starting so I��m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:

> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:

Group by Datepart 30 sec interval?

Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:

>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I��m starting so I��m not very good
at
this. Would you mind explainig a bit how it works? Cause I do not see it. An
d
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:

> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very goo
d at
> this. Would you mind explainig a bit how it works? Cause I do not see it.
And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
>|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it shoul
d
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>

Group by Datepart 30 sec interval?

Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I´m starting so I´m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
> >Hello, im starting with sql and i need to group data in the form of:
> > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> >datepart(30*s,date). Is any way of getting it?
> >
> >I know i can get the same in vb passing the interval by parameter, by is it
> >possible to do it this way?
> >
> >Thanks a lot!!!
> >
> >Sorry, for my English
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
> Thanks for answering!! As I told before I´m starting so I´m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
> > This example takes a datetime and truncates it to 30 second intervals.
> > By grouping on the expression you should get what you need.
> >
> > select crdate,
> > dateadd(ms,- (datepart(ms,crdate) +
> > ((datepart(second,crdate) % 30) * 1000)),
> > crdate)
> > from sysobjects
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> > wrote:
> >
> > >Hello, im starting with sql and i need to group data in the form of:
> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> > >datepart(30*s,date). Is any way of getting it?
> > >
> > >I know i can get the same in vb passing the interval by parameter, by is it
> > >possible to do it this way?
> > >
> > >Thanks a lot!!!
> > >
> > >Sorry, for my English
> >|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>> Thanks for answering!! As I told before I´m starting so I´m not very good at
>> this. Would you mind explainig a bit how it works? Cause I do not see it. And
>> i supposse the ms stands for miliseconds, but vba in excel gives me object
>> error.
>> Thanks a lot indeed!!
>> "Roy Harvey" wrote:
>> > This example takes a datetime and truncates it to 30 second intervals.
>> > By grouping on the expression you should get what you need.
>> >
>> > select crdate,
>> > dateadd(ms,- (datepart(ms,crdate) +
>> > ((datepart(second,crdate) % 30) * 1000)),
>> > crdate)
>> > from sysobjects
>> >
>> > Roy Harvey
>> > Beacon Falls, CT
>> >
>> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
>> > wrote:
>> >
>> > >Hello, im starting with sql and i need to group data in the form of:
>> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>> > >datepart(30*s,date). Is any way of getting it?
>> > >
>> > >I know i can get the same in vb passing the interval by parameter, by is it
>> > >possible to do it this way?
>> > >
>> > >Thanks a lot!!!
>> > >
>> > >Sorry, for my English
>> >