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

JohnMac328Asked:
Who is Participating?
 
_agx_Connect With a Mentor 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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JohnMac328Author Commented:
The message portion was just example code - forgot to delete it.
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.