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

Checking the SQL statement with CFIF

What would the syntax be to check the SQL statement that is provided?  I am trying to check if an employee is allowed to check out any videos or maybe restrict the list that displays to only employees that are eligible to checkout videos.  Any help is appreciated.

Thanks
SELECT     t .*
FROM         (SELECT     Employees.EmployeeID, Employees.Last_name, Employees.First_name, VideoList.video_name, Detail.out_date, Detail.in_date, Role, 
                                              COUNT(*) OVER (Partition BY Employees.EmployeeId) AS CountNotCheckedIn
                       FROM          Employees INNER JOIN
                                              Detail ON Employees.EmployeeID = Detail.EmployeeID INNER JOIN
                                              VideoList ON Detail.VideoID = VideoList.VideoID
                       WHERE      Detail.In_Date IS NULL AND Detail.Out_Date IS NOT NULL) t
WHERE     t .CountNotCheckedIn = 2 AND Role <> '2'

Open in new window

0
JohnMac328
Asked:
JohnMac328
  • 4
  • 2
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
"employee is allowed to check out"

only you know the rule for an employee that determiines the eligibility...

post an excel file that shows a sample data for each table... and also the result set that you are looking for...

also, add the query that you have and the result that you get from this query...
0
 
JohnMac328Author Commented:
I forgot to submit this portion as a part of the question
<cfquery name="getCheckEligible" datasource="#datasource#">
SELECT     t .*
FROM         (SELECT     Employees.EmployeeID, Employees.Last_name, Employees.First_name, VideoList.video_name, Detail.out_date, Detail.in_date, Role, 
                                              COUNT(*) OVER (Partition BY Employees.EmployeeId) AS CountNotCheckedIn
                       FROM          Employees INNER JOIN
                                              Detail ON Employees.EmployeeID = Detail.EmployeeID INNER JOIN
                                              VideoList ON Detail.VideoID = VideoList.VideoID
                       WHERE      Detail.In_Date IS NULL AND Detail.Out_Date IS NOT NULL) t
WHERE     t .CountNotCheckedIn = 2 AND Role <> '2'
 </cfquery>

<cfif IsDefined('getCheckEligible')>
 <cfscript>
    WriteOutput('
        <script language="JavaScript">
      doyou = confirm("Do you like the ColdFusion? (OK = Yes   Cancel = No)"); 
              if (doyou == true)
                alert("Me Too!"); 
            else if (doyou == false)
                alert("*Sniff* *Sniff* Too Bad!"); 
        </script>
    ');

</cfscript>
</cfif>

Open in new window

0
 
JohnMac328Author Commented:
The message portion was just example code - forgot to delete it.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
post sample data for tables and the result that you are looking for in a excel format or text...
0
 
JohnMac328Author Commented:
I just need the syntax to check a true/false against a query.  Is that possible like this?

<cfif IsDefined('getCheckEligible')>
0
 
_agx_Commented:
restrict the list that displays to only employees that are eligible to checkout videos

That's the opposite of what your query's doing.  If you just want to display a list of employees that have less than 2 videos checked out, do something like this

SELECT  Employees.EmployeeID, Employees.Last_name 
FROM    Employees LEFT JOIN 
      (     SELECT EmployeeID, COUNT(*) AS TotalNotCheckedIn
            FROM   Detail
            WHERE  out_date IS NOT NULL 
            AND    In_Date IS NULL
            GROUP BY EmployeeID
            HAVING COUNT(*) > 1
        ) 
        mx ON Employees.EmployeeID = mx.EmployeeID
WHERE   mx.EmployeeID IS NULL

Open in new window

0
 
JohnMac328Author Commented:
Sorry I have been so confusing, I wanted to have both versions for different parts of the program.  That's perfect - thanks again.
0
 
_agx_Commented:
Gotcha, that makes sense then!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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