• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Query from Access to Coldfusion

I've been developing web-apps with Coldfusion for ages. Unfortunately, I've always done ground-up apps and havent had to transfer from old MS programs, I am now faced with this issue. I have a Query that runs in Access to pull data from an SQL server and uses decision functions.. I am trying to convert it to Coldfusion however I cant seem to get it to list Properly. I need someone to convert this to an Coldfusion cfquery in an SQL friendly manner and still keep the returned query structure. Can anyone help with this??

<!--- QUERY START --->

SELECT DISTINCTROW EventTypes.EventType AS Type, IIf(Month([DateOccurred])<=6,Year([DateOccurred]),Year([DateOccurred])+1) AS [Financial Year], DepartmentList.Department, Sum(Events.TotalCost) AS Cost, Sum(Events.TotalDaysLost) AS DaysLost, Count(Events.EventID) AS [Count]
FROM DepartmentList INNER JOIN (Events INNER JOIN EventTypes ON Events.EventTypeID = EventTypes.EventTypeID) ON DepartmentList.DepartmentID = Events.DepartmentCategoryID
GROUP BY EventTypes.EventType, IIf(Month([DateOccurred])<=6,Year([DateOccurred]),Year([DateOccurred])+1), DepartmentList.Department
HAVING (((EventTypes.EventType)="Injury") AND ((IIf(Month([DateOccurred])<=6,Year([DateOccurred]),Year([DateOccurred])+1))>1989))
ORDER BY EventTypes.EventType, IIf(Month([DateOccurred])<=6,Year([DateOccurred]),Year([DateOccurred])+1);

<!--- QUERY END --->
0
wulfshayde
Asked:
wulfshayde
  • 2
1 Solution
 
Tacobell777Commented:
I think it might be this, no time to test, so send me any errors you get

SELECT DISTINCT EventTypes.EventType AS Type,
      CASE(Month([DateOccurred]))
      WHEN <= 6 THEN Year([DateOccurred])
      ELSE Year([DateOccurred])+1
      END AS [Financial Year],
      DepartmentList.Department,
      Sum(Events.TotalCost) AS Cost,
      Sum(Events.TotalDaysLost) AS DaysLost,
      Count(Events.EventID) AS [Count]
FROM DepartmentList
INNER JOIN (Events INNER JOIN EventTypes ON Events.EventTypeID = EventTypes.EventTypeID)
ON DepartmentList.DepartmentID = Events.DepartmentCategoryID
GROUP BY EventTypes.EventType,
            CASE(Month([DateOccurred]))
      WHEN <= 6 THEN Year([DateOccurred])
      ELSE Year([DateOccurred])+1
      END AS [Financial Year], DepartmentList.Department
HAVING (((EventTypes.EventType)="Injury")
AND             CASE(Month([DateOccurred]))
      WHEN <= 6 THEN Year([DateOccurred])
      ELSE Year([DateOccurred])+1
      END AS [Financial Year]
ORDER BY EventTypes.EventType,
            CASE(Month([DateOccurred]))
      WHEN <= 6 THEN Year([DateOccurred])
      ELSE Year([DateOccurred])+1
      END AS [Financial Year], ;
0
 
wulfshaydeAuthor Commented:
Thanks for the response Tacobell777 thats an amazing result.

Im getting the following error. I've also tried changing it to just < 7 and LTE and no go. This is the error for your example above.

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near '<'.

If I change to to LTE I get this

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near '6'.

Any more help you can give would be greatly appreciated.

0
 
wulfshaydeAuthor Commented:
Okay, I got it, but if it wasn't for you steering me in the CASE direction I probably never would have. The working code is below:

SELECT    EventTypes.EventType AS Type, DepartmentList.Department, SUM(Events.TotalCost) AS Cost, SUM(Events.TotalDaysLost) AS DaysLost, COUNT(Events.EventID) AS NumEvents, CASE WHEN Month(Events.DateOccurred) <= 6 THEN Year(Events.DateOccurred) ELSE Year(Events.DateOccurred)+1 END AS FinancialYear
FROM      dbo.DepartmentList, dbo.Events, dbo.EventTypes
WHERE     dbo.Events.DepartmentCategoryID = dbo.DepartmentList.DepartmentID
  AND     dbo.Events.EventTypeID = dbo.EventTypes.EventTypeID
GROUP BY EventTypes.EventType, DepartmentList.Department, CASE WHEN Month(Events.DateOccurred) <= 6 THEN Year(Events.DateOccurred) ELSE Year(Events.DateOccurred)+1 END
HAVING   ((EventTypes.EventType = 'Injury') AND CASE WHEN Month(Events.DateOccurred) <= 6 THEN Year(Events.DateOccurred) ELSE Year(Events.DateOccurred)+1 END > #UserVar1#)
ORDER BY EventTypes.EventType, CASE WHEN Month(Events.DateOccurred) <= 6 THEN Year(Events.DateOccurred) ELSE Year(Events.DateOccurred)+1 END;

Thanks for your help Tacobell777. ;)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now