Solved

Sub Requirements in SQL Query

Posted on 2009-05-14
2
136 Views
Last Modified: 2012-05-07
Here is what I have so far, however there are a few mods I need and am not sure of.  Instead of WHERE     (Status <> 'Closed') AND ([Date Opened] <= DATEADD(dd, - 8, DATEDIFF(dd, 0, GETDATE()))) I need this to check the Priority and then be able to set the dd, -8 to different amounts based upon Priority level, and I need to set a couple different requirements at once. For instance if it is priority 2 then I want to set the dd, -2 while priority 4 is dd, -8

SELECT     *
FROM         (SELECT     Incident_ID AS [Ticket Number], CONVERT(datetime, DateClosed / 86402.027 + 25567) AS [Date Closed], 
                                              AssignedGroup AS [Assigned Group], AssignedTechnician AS [Assigned Technician], 
                                              CASE Priority WHEN 4 THEN 'Low' WHEN 3 THEN 'Medium' WHEN 2 THEN 'High' END AS Priority, 
                                              CASE Status WHEN 3 THEN 'Closed' WHEN 8 THEN 'Drill Status' WHEN 4 THEN 'Hold' WHEN 1 THEN 'Assigned' WHEN 0 THEN 'New' WHEN
                                               9 THEN 'Project' END AS Status, CONVERT(datetime, Date_Opened / 86402.027 + 25567) AS [Date Opened], 
                                              CASE COMPLEXITY WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [Suspense Missed]
                       FROM          dbo.TTS_Main) derived
WHERE     (Status <> 'Closed') AND ([Date Opened] <= DATEADD(dd, - 8, DATEDIFF(dd, 0, GETDATE()))) AND (Status <> 'Project')

Open in new window

0
Comment
Question by:141ANG
2 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24387462
Like this ?
SELECT     *
FROM         (SELECT     Incident_ID AS [Ticket Number], CONVERT(datetime, DateClosed / 86402.027 + 25567) AS [Date Closed], 
                                              AssignedGroup AS [Assigned Group], AssignedTechnician AS [Assigned Technician], 
                                              CASE Priority WHEN 4 THEN 'Low' WHEN 3 THEN 'Medium' WHEN 2 THEN 'High' END AS Priority, 
                                              CASE Status WHEN 3 THEN 'Closed' WHEN 8 THEN 'Drill Status' WHEN 4 THEN 'Hold' WHEN 1 THEN 'Assigned' WHEN 0 THEN 'New' WHEN
                                               9 THEN 'Project' END AS Status, CONVERT(datetime, Date_Opened / 86402.027 + 25567) AS [Date Opened], 
                                              CASE COMPLEXITY WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [Suspense Missed]
                       FROM          dbo.TTS_Main) derived
WHERE   (Status <> 'Closed')
AND	(Status <> 'Project')
AND 	([Date Opened] <= CASE	WHEN Priority = 2 THEN DATEADD(dd, - 2, DATEDIFF(dd, 0, GETDATE()))
				WHEN Priority = 3 THEN DATEADD(dd, - 5, DATEDIFF(dd, 0, GETDATE()))
			  	WHEN Priority = 3 THEN DATEADD(dd, - 8, DATEDIFF(dd, 0, GETDATE()))
			  END
	)

Open in new window

0
 
LVL 1

Author Closing Comment

by:141ANG
ID: 31581559
This worked great, thankyou
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email Header Detail 12 76
SYbase 4 43
SSRS: Why is Visual Studio stripping these properties? 2 39
SQL Select Query help 1 34
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

680 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