SQL Query help

[Mike Vytal]

I have a table with staff id #s and names and a table with 5 dates. I have a dropdown which gets populated with the dates based on the staff id. For example, employee (EE) 157 lists 5 dates, because he hasn't used any dates yet. But, EE 160 lists 4, because she has used one. I hope this makes sense. Essentially, it's for a timesheet app. When the users login and click new timesheet, the date dropdown should get populated with unused dates based on the staff id, and if they choose existing timesheet, the date dropdown gets populated with the used dates. Please let me know if this example isn't clear enough. Thanks.

 

[ByteMyzer]

So, what is your question?

 

[Mike Vytal]

Sorry Byte, So in the timesheet table, there's a weekending field. I have 100 users. When users login and select new timesheet, based on the staffid, how do I get the date dropdown to populate with unused dates, and if they choose existing timesheet, the date dropdown should get populated with the used dates?

Another example: tblDATES is populated with 5 Saturdays btwn Oct 2 - Oct 30. EE#1 filled out timesheets for 10/2 & 10/9 so 10/16, 10/23, and 10/30 should pop up if new timesheet is selected and 10/2 & 10/9 if existing timesheets is selected. Conversely, EE#2 has filled out timesheets for 10/2 only, so when he selects new timesheet, the dropdown should show 10/9, 10/16, 10/23, and 10/30, and 10/2 if existing is selected.

 

[Mike Vytal]

Solved the problem using a the following select statement in a stored procedure. See code below.

SELECT CONVERT (nvarchar, DateSATURDAY, 101) AS DateSATURDAY FROM tblSATURDAYS WHERE (DateSATURDAY NOT IN (SELECT WEEKENDING FROM tblTIMESHEET WHERE (STAFFID = @uID))) GROUP BY DateSATURDAY HAVING (DateSATURDAY > GETDATE() - 31) ORDER BY DateSATURDAY