Solved

CF - Display multiple null fields

Posted on 2011-02-14
3
309 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
How to check data in sql table 11 45
Where clause in stored procedure 8 50
Query to Add Late Tolerance 10 60
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now