Selecting Months data for Fusion Charts

[rhenderson2]

Hi,

I have a table:

itemNum, mmddyy, duration(hours), incidentType(string)

01, 2/1/04, 4, fault
02 3/4/04, 3.5, process

I need an ms sql query to sum duration and # of "types"
for each month, even when the month has no data. This is for a fusioncharts graph. It will display sum of duration (hours) and number of incidentTypes for each month and vertical bars.

eg: Jan 4 hours 1 incident Type=fault

so I need to feed the chart making program the above data for each month.

Thanks for any help!

Rudy.

 

[Golom]

Question

If you had

[tt]
itemNum mmddyy duration(hours) incidentType(string)

01 2/1/04 4 fault
02 2/5/04 4 process
03 2/9/04 3.5 fault
[/tt]
Do you count that as 2 incident types (process & fault) or as 3 because there are 3 records?

 

[dhookom]

Are you asking for the display of a month (say March) even if there is no data for that month? This would not be probable without a March date stored in a table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GHolden]

Hi,

If I understand correctly the following SQL should give you what roughly you want...

Assuming:

Table is called tblIncident
Fields are itemNum, mmddyy, Duration and IncidentType
(You may have to alter a little for your exact needs)

SELECT Count(tblIncident.itemNum) AS CountOfitemNum,
    Format(DatePart("m",[mmddyy]),"mmm") AS Month,
    Sum(tblIncident.Duration) AS SumOfDuration,
    tblIncident.IncidentType
FROM tblIncident
GROUP BY Format(DatePart("m",[mmddyy]),"mmm"),
     tblIncident.IncidentType;

There are two ways to write error-free programs; only the third one works.

 

[rhenderson2]

Golom,

It would be just 2 incident types.

Dhookom,

If there is no data for a particular month, like any other chart I'd just show "0". I'd probably just write some asp code to do this.

I'm still wondering how to put monthly sums into a variable for the month so the chart will show it.

Thanks for your help. I sure appreciate it. But I am still not clear on how to sum the months, place the sums into variables (sum(January), sum (February)...etc
so i can use these sums to setup the graph bar height for the chart.

Rudy.

 

[Golom]

GHolden's code will produce the sums that you want (I believe.)

With regard to dhookom's question and your response.

This code will give you a result for only those months for which you have data. If a month is missing, there will be no record for it (i.e. it will not appear on your chart.) If you want to generate values for missing months then look at Rudy Limeback's discussion about using an integers table at this URL.

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html

 

[JonFer]

For charting the results, I would use a crosstab query with hard-coded column headings of "Month01","Month02", etc.

Column: "Month" & Format(DateDiff("m",[Enter first date to include],[mmddyy])+1,"00")
Value: Sum(Duration)
Row: Type
Where: [mmddyy]>=[Enter first date to include]

You could include a column which indicates the [Enter first date to include] parameter so you know the date range of the columns.

 

[rhenderson2]

Folks,

Thanks for your replies. I am a bit of a newbie and thought this was an easy one. I'll just write 12
"select sum(duration) where month(date)=1"
if month(date)<>"" then Janduration=sum
else Janduration=0
end if

close.rs
open.rs
"select sum(duration) where month(date)=2", etc, etc

I can't seem to follow how your replies would work in this instance. I know it is due to my lack of expertise.

Thanks!

 

[rhenderson2]

Oops, told you I was a newbie. I should have written:

"select sum(duration) where month(date)=1"
if sum<>"" then Janduration=sum
else Janduration=0
end if

close.rs
open.rs
"select sum(duration) where month(date)=2", etc, etc

I know my syntax has errors, but I'll just slog thru it till it works. Thanks again!