|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
I am trying to pull two different information for each month of the year. However, I am having trouble with my SQL statement...I was wondering if someone can point me to the right direction.
SQL statement: SELECT DatePart(mm, Reports.IncidentDate) as MM, Count(Distinct Reports.ReportId) AS intNumIncidents, CASE WHEN RD.reportField_ID <> 563 THEN 0 WHEN RD.reportField_ID = 563 THEN avg(cast(RD.DataText as money)) ELSE 0 END AS AvgValue FROM Reports left outer join reportData as RD on reports.reportID = RD.report_ID WHERE (StoreNum = 33) AND (Reports.intEnabled = 1) AND ((Reports.ReportType_Id = 1 OR Reports.ReportType_Id = 36) OR RD.reportField_ID = 563) AND (DatePart(yyyy, Reports.IncidentDate) = 2004) AND (RD.DataText <> '' AND RD.DataText is not null) AND (Reports.intComplete = 1) group by DatePart(mm, Reports.IncidentDate), RD.reportField_ID - The intNumIncidents counts the number of incidents -The AVGVALUE average it's monetary value in which the value is store in RD.reportField_ID = 563 the error I am getting is : Cannot convert a char value to money. The char value has incorrect syntax. Basically, with the OR statement on ...OR RD.reportField_ID = 563, it will return all row containing a reportType_ID = 1 or 36 with different a reportField_ID <> 36. Thus, it wont be able to convert those variables. I can't figure out how to exclude those case when calculating my average. If I replace the OR to an AND by the RD.reportField_ID = 563 ( ((Reports.ReportType_Id = 1 OR Reports.ReportType_Id = 36) AND RD.reportField_ID = 563) AND ) SQL will return this ------------------- MM | intNumIncidents | AvgValue 7 | 4 | 540.8550 8 | 4 | 148.2425 9 | 2 | 104.4800 10 | 4 | 136.3825 11 | 1 | 254.0000 12 | 2 | 198.3450 As you can notice, the data for Month 1-6 is missing. If I run the intNumIncidents and AvgValue individually, it works fine...I want to combine these two queries into one to speed up my query. Please assist...I greatly appreciate anyone advise. Thanks. |
|
#2
|
||||
|
||||
|
Quote:
Code:
CAST(CharValue) As Money |
|
#3
|
|||
|
|||
|
didnt work
wrong syntax
|
![]() |
| Viewing: ASP Free Forums > Database > SQL Development > T-SQL help (combining queries) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|