Query Results and my recent hair loss

[Guest]

I'm pulling my hair out trying to solve this problem.

I have a single table of data, tblVisitLog;

dos - datetime

location - nvarchar

ticket

pat_num

pat_name

insurance

proc_code

proc_name

mod_code

diag_cod

diag_name

charge - money

groupid - int

First I build a 2-year look back with this query for a chart, and generate a 1 month look back if they haven't selected a DateRange.

I'm finding that there is something wrong with my query that is produced by the user. The number of visits (count of distinct ticket) and the related charges are wrong. It seems to be off by only a few visits, and my first thought was that it had something to do with the time portion of the datetime data, but all my times are 12:00 AM in the DB.

I'm a bit new at CFM, and rusty at best on SQL, so if anyone can provide some guidance, I'd surely appreciate it.

<!--- Get 2 past years data --->

<cfquery name="ChargesByDosQry"  datasource="myDatabase" username="non" password="non">

DECLARE @FirstDate datetime

DECLARE @LastDate datetime

SET @FirstDate = DATEADD(mm, -1, DATEADD(yyyy, -2, CONVERT(datetime, CONVERT(char(7), getdate(), 120) + '-01')))

SET @LastDate = CONVERT(datetime, CONVERT(char(7), getdate(), 120) + '-01')

SELECT month(dos) as MonthNo, year(dos) as YearNo, COUNT(DISTINCT Ticket) AS Visits, SUM(charge) AS Charges FROM tblVisitLog

WHERE dos >= @FirstDate AND dos < @LastDate AND (groupid = '#GroupID#')

GROUP BY year(dos),month(dos) ORDER BY YearNo, MonthNo

</cfquery>

<cfif IsDefined("DateRange")>

     <!--- Get date range data if the user selects a date range --->

          <cfset sep="#Find("to", Form.DateRange)#">

          <cfset StartDate="#Left(Form.DateRange, sep-1)#">

          <cfset EndDate="#Right(Form.DateRange, sep-2)#">

          <cfquery name="MonthQuery" datasource="myDatabase" username="non" password="non">

             SELECT *

             FROM tblVisitLog

             WHERE dos >= '#StartDate#' AND dos < DATEADD(Day, 1, '#EndDate#') AND (groupid = '#GroupID#')

          </cfquery>

<cfelse> <!--- Get last months data if no date range is selected --->

          <cfquery name="MonthQuery" datasource="myDatabase" username="non" password="non">

           DECLARE @FirstDayOfMonth datetime

           DECLARE @FirstDayOfLastMonth datetime

           SET @FirstDayOfMonth = CONVERT(datetime, CONVERT(char(7), getdate(), 120) + '-01')

           SET @FirstDayOfLastMonth = dateadd(mm, - 1, CONVERT(datetime, CONVERT(char(7), getdate(), 120) + '-01'))    SELECT *

           FROM tblVisitLog

           WHERE dos >= @FirstDayOfLastMonth AND dos < @FirstDayOfMonth AND (groupid = '#GroupID#')

           </cfquery>

</cfif>

        <!--- Get census for months data   --->

         <cfquery name="CensusQry" dbtype="Query">

              SELECT COUNT(DISTINCT ticket) AS CensusTotal

              FROM MonthQuery

         </cfquery>

        <!--- Get total Charges for months data    --->

        <cfquery name="ChargesQry" dbtype="Query">

          SELECT SUM(charge) AS TotalCharges

          FROM MonthQuery

        </cfquery>

        <!--- Get unique insurance info --->

        <cfquery name="InsuranceQry" dbtype="Query">

          SELECT insurance, COUNT(insurance) AS InsCount, SUM(charge) AS Charges

          FROM MonthQuery

          GROUP BY insurance

        </cfquery>

        <!--- Get distinct location names and count --->

        <cfquery name="SiteQry" dbtype="Query">

        SELECT location, COUNT(location) AS LocCount, SUM(charge) AS Charges

        FROM MonthQuery

        GROUP BY location

        </cfquery>

[Guest]

I found my error. It was my own "user" error.

My date picker didn't grab the entire months range, so of course the data would be off by a day or two, resulting in a small variation in visits / charges!

AAAAHhhhhhhhhh!

[Dan_Bracuk]

Seems like some overengineering taking place in your first query.  Instead of all those declarations and converts, why not a simple.

TwoYearsAgo = DateAdd("yyyy", -2, now();

where dos >= <cfqueryparam cfsqltype="cf_sql_date" value = "#TwoYearsAgo#">

and dos < getdate()

cfsqltype="cf_sql_date" strips out the time portion for you

When processing user input, you are using functions that produce strings and then use those strings as dates in your query.  That might be messing you up.

Why do you think you are off by a couple of visits?  Do you run the same query outside cf and get different answers?