Adding conditional columns to a SQL server SELECT query

Hello, we have a mixed environment of SQL server 2008 R2 and 2012.  All using express versions on our various servers.  I pull monthly reports in SQL Server Management Studio for users that show project data based on dates and whether those particular projects are overdue or nearing a deadline.

My query is a moderately simple Select query with inner joins and a Where clause.  Among the fields selected is a due date field.  What I have been doing thus far is export the results to excel and then add 3 columns to use excel formulas:

determine if due date is less than today's date (True or False)
indicate if project is overdue (Yes or No)
and also to count the number of days between the due date and today's date which can be positive or negative value.  So if the due date was 10 days before today's date, then it would show -10, but if it was 10 days after today's date, it would show 10.

And then lastly, I would sort the list based on if it was overdue and how many days it was past due.

So my question is this, is it possible to do all of this in a Select Query without having to make these modifications in Excel?  Whereas the report would already have the 3 columns added with the conditions above?
LVL 17
bigeven2002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
post your existing simple query and list the changes needs to be done to it.

Mike
0
bigeven2002Author Commented:
I dont have the exact query with me right now, but I can mimic it.
SELECT dbo.prjdata.prjid, dbo.prjdata.prjname, dbo.prjdata.prjtype, dbo.prjdata.prjusrid, dbo.prjdata.prjduedate, dbo.prjdata.prjstatus, dbo.prjusers.usrfirst, dbo.prjusers.usrlast
FROM dbo.prjdata
INNER JOIN dbo.prjusers
ON dbo.prjdata.prjuserid = dbo.prjusers.usrid
WHERE
dbo.prjdata.prjtype = "consultation"
AND
dbo.prjdata.prjstatus = 0

Open in new window


Status of 0 means the project is not finished.

What I would like to add to the query is the 3 bullet points mentioned in the question.  I don't know how to modify the query above to add the columns to the result to indicate if dbo.prjduedate is less than today's date and to count number of days between dbo.prjduedate and today's date.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Meanwhile. if you could create a few rows of data and how you want it look like when it is done, then I can put it in a temp table and test the solution before giving the working solution to you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alfred A.Commented:
Ok.  Based on Due Date and 3 computed columns, you could have something like this below.   Also, I assumed that you are comparing the date component only.   I hope this helps.

SELECT DueDate, 
    (CASE WHEN DATEDIFF(DAY, DueDate, DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))) > 0 THEN 'True'
      ELSE 'False'
	  END) AS [DueDateLessThanTodaysDate],
    (CASE WHEN DATEDIFF(DAY,DueDate,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))) > 0 THEN 'Yes'
	   ELSE 'No'
	  END) AS [ProjectOverdue],
     DATEDIFF(DAY,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())),DueDate) AS [NoOfDays]
FROM

(YOUR TABLES)

ORDER BY DATEDIFF(DAY,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())),DueDate)

Open in new window

0
bigeven2002Author Commented:
Ok as requested I have attached an Excel sheet of a few example rows.  The first tab is the current query results.  The second tab (proposed) shows what I add to the report and how it is sorted.  I cannot recall formulas so I just put static values.

I need to make a correction on the first bullet point in the question.  The TRUE and FALSE condition would indicate if the due date is within 10 days of today's date as indicated in the 10daysout column.
Book1.xlsx
0
bigeven2002Author Commented:
Thanks Alfred.  I will check to see if the CASE works for this.  I return to the office tomorrow morning.

On the first part, I made an error in my question where instead of TRUE or FALSE being if due date is less than today's date, rather it needs to be TRUE if due date is within 10 days of today's date, or FALSE if it is not within 10 days.

So the TRUE condition would be met if the due date was between 6/2/15 and 6/12/15.
0
Alfred A.Commented:
Ok.   I adjusted the true or false one by just adding ABS() function and if it is less than or equal to 10.   I hope this helps.

SELECT DueDate, 
    (CASE WHEN ABS(DATEDIFF(DAY, DueDate, DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())))) <= 10 THEN 'True'
      ELSE 'False'
	  END) AS [DueDateWithinTenDaysOfTodaysDate],
    (CASE WHEN DATEDIFF(DAY,DueDate,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))) > 0 THEN 'Yes'
	   ELSE 'No'
	  END) AS [ProjectOverdue],
     DATEDIFF(DAY,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())),DueDate) AS [NoOfDays]
FROM

(YOUR TABLES)

ORDER BY DATEDIFF(DAY,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())),DueDate)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bigeven2002Author Commented:
Alright I will try this out tomorrow and report back.  Thanks.
0
bigeven2002Author Commented:
Alfred, the query is working grreat so far.  There is just one more detail that needs addressed.  On the last part where ABS is used, the query returns True for negative numbers <= 10 as well.  

So it still works correctly in regard to returning true for dates marked 6/3/15 to 6/13/15 which is 0 to 10.  But it is also marking true for dates 5/24/15 to 6/2/15 which is -1 to -10.

How do I instruct the query to also return false for the past dates (-1 to -10)?
0
bigeven2002Author Commented:
Well never mind I found it.  I added a SIGN command to the condition so:
CASE WHEN ABS(...) <=10 AND SIGN(...) <> -1

This returned false for all negative values as expected.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.