Group by and SUM function

Hi,

Grouping my data by calendar month and display the sum of all the days in a month works perfectly fine.

But we have now realized that for most of our customers it would make sense to display data of their billing month which not nessessary means 1st till 30th/31st.

Is it possible to use the sum function and group by month but tell the graph that start and end day are different to 1st and 30th/31st?

Thanks very much for your help.

What you could do in that case is subtract the necessary number of days from the date so that when you group by month it ends up being offset by their billing date.

In the part query that looks like this:

WADWCStats_cmd.CommandText = "SELECT LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)) AS XLABEL, SUM(Minutes) AS YVALUE FROM Stats GROUP BY LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)), DATEPART('yyyy', Stats_Date), DATEPART('m', Stats_Date) ORDER BY DATEPART('yyyy', Stats_Date) ASC, DATEPART('m', Stats_Date) ASC "

where could I substract the days if we say the first day of the billing period would be the 5th of a month with 30 days?

Sorry but I can't really identify which part of that code stand for the start date.

Thanks very much

You would adjust the date field in the group by area... try:

SELECT LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)) AS XLABEL, SUM(Minutes) AS YVALUE FROM Stats GROUP BY LEFT(Format(Date_Add(Stats_Date, interval -5 day),'mmmm'), 3)+' '+STR(DATEPART('yyyy', Date_Add(Stats_Date, interval -5 day))), DATEPART('yyyy', Date_Add(Stats_Date, interval -5 day)), DATEPART('m', Date_Add(Stats_Date, interval -5 day)) ORDER BY DATEPART('yyyy', Stats_Date) ASC, DATEPART('m', Stats_Date) ASC

Hi,
I have tried it with the code you send, but for some reasons it doesn't work. It does return the error "DynamicWebCharts/Data/diagramm_dataparser.asp?".

I don't know really where to start finding the error, as I don't know exactly what the different parts of the query mean.
Which part returns the month, year, which parts add up the days?

Do you have any idea why it doesn't work?

Thanks very much for teh help!
Jana

It is hard to say without debugging the page directly.

Start by taking that sql statement and running it in your database directly. That will be the easiest way to make adjustments and make sure you are getting the data you need.

I didn't know what database you were using, so my syntax could be wrong for your database type... once you have your SQL statement worked out, if you still have problems you should try browsing the page referenced "DynamicWebCharts/Data/diagramm_dataparser.asp?" directly. When it shows the page name like that it means that page has an error. By viewing the page you can find out more about the precise error.

If you want Webassist to help directly you would have to sign up for a paid phone support incident. That would allow us to connect to your computer and debug the problem with you directly.

Yes I understand that.
It might be that we have a SUM(Minutes) in combination with WHERE in our code. It always results an error.
However to solve this, we have now used the static template and wrote a serie of queries that add all the minutes up. Afterwards they are passed to the x and y value of the chart.
It works! Maybe this helps somebody who is confronted with the same problem!

Thanks