Solved

CF - Display multiple null fields

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

11 Experts available now in Live!

Get 1:1 Help Now