Solved

CF - Display multiple null fields

Posted on 2011-02-14
3
310 Views
Last Modified: 2012-05-11
I am trying to show checked out  videos for each employee by listing who has a Checked out date (not null) and a In date that is not null times two.  There are a maximum of 2 videos checked out per person.  The sql shows who has videos out but I want it to show when a person has two null checked in when they have two not null checked out fields.  The end of my SQL statement is pseudo code to show where I am trying to specify two checked out for an employee. Any help is appreciated
SELECT     Employees.First_Name, Employees.Last_Name, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date
FROM         Employees INNER JOIN
                      Detail ON Employees.EmployeeID = Detail.EmployeeID INNER JOIN
                      VideoList ON Detail.VideoID = VideoList.VideoID
WHERE     (Detail.In_Date IS NULL) times two and Detail_out is not null times two

Open in new window

0
Comment
Question by:JohnMac328
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34889225
For 2005, try an OVER clause.  I don't know if you really need the "Out_Date" not null check. But I left it in there just in case.

SELECT    t.*
FROM     (   
         SELECT Employees.EmployeeID, Employees.Last_name, Employees.First_name, VideoList.video_name,
                Detail.out_date, Detail.in_date, 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

Open in new window

0
 

Author Closing Comment

by:JohnMac328
ID: 34889264
Thanks again as always agx!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34889293
Welcome :)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question