Group bys

[#1]

Hi everyone.

I need to count the number of service hours (SH) per Service Date (SD). I have tried a host of combinations but just pasted below my latest code and result.

For some reason, Service Dates with common Service Hours are not grouping together but displaying on separate lines.

I know in Oracle I would usually say something like count (SD||SH) and so I did similar as shown in my code. I even had them separated in the code but to no avail.

Please help.

Thanks.

"Claims Summary Temp":
Load distinct monthstart([Service Date]) as SD,
         date([Service Date], 'hh') as SH
Resident "Claims";

"Claims Summary":
Load SD,
         SH,
         count(SD & SH) as Totals
Resident "Claims Summary Temp"
Group By SD,
                SH;
Drop table "Claims Summary Temp";

Result:

(asterisks denotes the problem ones)

SD               SH  Totals

01-09-2013  11   1

01-09-2013  12   1

01-08-2013   5    1

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   6    1  *

01-08-2013   7    1  *

01-08-2013   7    1  *

01-08-2013   7    1  *

01-08-2013   7    1  *

01-08-2013   7    1  *

[jonathandienst]

Ashley

I don't know if I've understood your requirement, but does this not work for you?

Load SD,

         sum(SH) As SumTotal,

         count(SH) as CountTotal

Resident "Claims Summary Temp"

Group By SD;

Regards

Jonathan

[#3]

Hi Jonathan.

Thank you for the feedback.

Each Service Date has a timestamp. I need to count the number of occurrences of each hour of the timestamp for the same Service Date.

Example, 01-08-2013 has 9 claims that occurred during the 6^th hour of the day.

So I want every Service Date to be grouped to the first day of the month so that we can assess the counts per Month. (Therefore I have the monthstart part in my code).

So the output should be something like this for example:

Service Date     SH   Count

01-08-2013       05   1

01-08-2013       06   9

01-09-2013       04   10

01-09-2013       07   2

Thanks.

[jonathandienst]

Then you need something like this:

Load date(floor(SD)) As ServiceDate,

     class(Frac(SD), 1/24) As Hour,

     sum(SH) As SumTotal,

     count(SH) As CountTotal

Resident [Claims Summary Temp]

Group By floor(SD), class(Frac(SD), 1/24)

(check commas and brackets)

You could split the SD field into date and hours when you load Claims Summary Temp. Floor and Frac separate the date and time portions of SD, class groups the time in 1 hour intervals (1/24).

Is this closer to what you need?

Jonathan

[#5]

Hi Jonathan.

Apologies for the late response.

I'm getting something like this below thus far. I haven't tried out your latter suggestion as yet but the reason is because the class function doesn't seem to band in periods of one hour. So I'm trying to work on a few things using your suggestions.

[sbaldwin]

hi i think your problem is :

"Claims Summary Temp":

Load distinct monthstart([Service Date]) as SD,

         date([Service Date], 'hh') as SH

Resident "Claims";

the date function unlike in oracle, just applies a mask, try:

"Claims Summary Temp":

Load distinct monthstart([Service Date]) as SD,

         hour([Service Date]) as SH

Resident "Claims";

Thanks

Steve

[#7]

Hi Steve.

Thank you for your help!

I gathered as much that it is merely masking which is why I was experiencing some issues.

I managed to come right now though. I used Jonathan's code and tweaked it a bit and all seems fine now.

Thanks!

[#8]

Hi Jonathan.

With some slight modifications to your code, I managed to come right.

Many thanks!

[jonathandienst]

The class is showing in date units. 0.041666 of  day is 1 hour. Its not pretty though, so it would look better if you do it like this:

Load date(floor(SD)) As ServiceDate,

     interval(class(Frac(SD), 1/24)) As Hour,

     sum(SH) As SumTotal,

     count(SH) As CountTotal

Resident [Claims Summary Temp]

Group By floor(SD), class(Frac(SD), 1/24)