Solved

CF - Display multiple null fields

Posted on 2011-02-14
3
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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