Solved

CF - Display multiple null fields

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

829 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