1
\$\begingroup\$

I am very new to MS SQL Server and am trying to learn how to combine window functions within my SQL query. I have two tables that I have to tie together using assoc_id and associate so that I can set the names in alphabetical order for my reports. Can someone tell me if this is the most efficient way to run this query? This database is huge and I need it to run as fast as possible. Please tell me if there are any changes I can make to make this query run as fast and smoothly as possible.

<cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
<cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

<!--- WHITELIST OF VALID LOCATIONS --->
    <cfset validLocs = "OPERATIONS, CCC, QA, DS, PS, LWR, NR, SDL, FSC">
    <cfset locList = "">
    <cfif structKeyExists(FORM,"location") AND len(FORM.location) GT 0>
        <cfloop list="#FORM.location#" index="vLoc"> 
            <cfif listContainsNoCase(validLocs, vLoc) GT 0> 
            <cfset locList = listAppend(locList,vloc)>
            </cfif> 
        </cfloop> 
    <cfelse>
    </cfif>

<cfquery datasource="#application.dsn#" name="GetEmployeeInfo"> 
    SELECT s4.associate /* Associate's ID */ 
        , s4.assoc_name /* Associate's Name */ 
        , s4.trans_location /* Associate's Location */ 
        , s4.checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */ 
        , s4.assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */ 
        , s4.totalChecklistsByAssoc /** Gives you a count of Total Checklists by Specific Associate in All Locations. */ 
        , s4.totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */ 
        , CASE WHEN ( coalesce(s4.assocChecklistsByLoc,0) > 0 ) THEN (CAST(s4.checklistsByAssocLoc AS decimal(8,2))/s4.assocChecklistsByLoc) * 100 ELSE 0 END AS totalChecklistsByAssocLocPct /* This gives you a percent of associate location checklists over count of checklists by Associate in a Location. */ 
        , CASE WHEN ( coalesce(s4.totalAssocChecklistsByAllFilteredLoc,0) > 0 ) THEN (CAST(s4.totalChecklistsByAssoc AS decimal(8,2))/s4.totalAssocChecklistsByAllFilteredLoc) * 100 ELSE 0 END AS totalChecklistsByLocPct /* This gives you a percent of Total Associate Checklists in All Locations over count of Checklists by All Associate in All Locations. */ 
        , s4.rnA /* Placeholder for a record to display the Associate Name. */ 
        , s4.rnL /* Placeholder for a record to display the Location. */ 
        , s4.rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */ 
    FROM ( 
    SELECT s3.* 
        , SUM(s3.assocChecklistsByLoc) OVER (PARTITION BY s3.associate) AS totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */ 
    FROM ( 

    SELECT s2.* 
        FROM ( 
            SELECT a.assoc_name 
                , s1.associate 
                , s1.trans_location 
                , s1.checklistsByAssocLoc 
                , s1.assocChecklistsByLoc 
                , s1.totalChecklistsByAssoc 
                , ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.associate, s1.trans_location) AS rnA /* Placeholder for a record to display the Associate Name */ 
                , ROW_NUMBER() OVER (PARTITION BY s1.associate, s1.trans_location ORDER BY s1.associate, s1.trans_location) AS rnL /* Placeholder for a record to display the Location */ 
                , ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.trans_location DESC) AS rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */ 
    FROM ( 
    SELECT c.associate 
        , c.trans_location 
        , COUNT(*) OVER (PARTITION BY c.associate, c.trans_location) AS checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */ 
        , COUNT(*) OVER (PARTITION BY c.associate) AS totalChecklistsByAssoc /* Gives you a count of Total Checklists by Associate in All Locations. */ 
        , COUNT(*) OVER (PARTITION BY c.trans_location) AS assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */ 
    FROM cl_checklists c 
    LEFT OUTER JOIN tco_associates a ON c.associate = a.assoc_id 
        AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED ASSOCIATE IDs */ 
            WHERE c.[DATE] >= <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" /> /* SELECTED DATES */ 
                AND c.[DATE] <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" /> 
                AND c.trans_location IN ( <cfqueryparam value="#locList#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED LOCATIONS */ 
    ) s1 
    INNER JOIN tco_associates a ON s1.associate = a.assoc_id 
        AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED ASSOCIATE IDs */ 

    ) s2 
    WHERE s2.rnA = 1 OR s2.rnL = 1 /* There will be a final Location (rnL=1 and rnTotAssoc=1). This is the final row. */ 
    ) s3 
    ) s4 
    ORDER BY s4.assoc_name, s4.trans_location
</cfquery> 

<table> 
<thead> 
<th><strong>Associate Name</strong></th> 
<th><strong>Location</strong></th> 
<th><strong>Checklists Generated by Associate</strong></th> 
<th><strong>Checklists Generated by Selected Location(s)</strong></th> 
<th><strong>Associate Percentage of Location Total</strong></th> 
<th></th> 
</thead> 
<cfoutput query="GetEmployeeInfo"> 
<tr> 
<td><cfif rnA EQ 1><strong>#assoc_name#</strong></cfif></td> 
<td><cfif rnL EQ 1>#trans_location#</cfif></td> 
<td>#checklistsByAssocLoc#</td> 
<td>#assocChecklistsByLoc#</td> 
<td>#DecimalFormat(totalChecklistsByAssocLocPct)# %</td> 
<!---<td> rnA: #rnA# | rnL: #rnL# | rnTotAssoc: #rnTotAssoc# </td> --->
</tr> 
<cfif rnTotAssoc EQ 1> 
<tr> 
<td>Associate Total</td> 
<td></td> 
<td>#totalChecklistsByAssoc#</td> 
<td>#totalAssocChecklistsByAllFilteredLoc#</td> 
<td>#DecimalFormat(totalChecklistsByLocPct)# %</td> 
<td></td> 
</tr>
</cfif> 
</cfoutput> 
</table>
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.