473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Adding an exception for One status in my Query

123 100+
I am creating a query statement where the customer is only concerned with one of the status to have a filter date on them. The rest of the status types he wants to see all no matter what the date of them is.

Here is the query statment I have in VBA:
Expand|Select|Wrap|Line Numbers
  1. "  SELECT     dbo.Defects.DefectId, dbo.Defects.Name, dbo.WorkflowSteps.StepName,dbo.PriorityTypes.Name AS PriorityName, dbo.SeverityTypes.Name AS Severity,  " _
  2.         , "   dbo.Defects.BuildNumber, dbo.DefectCustomFields.Custom_86 AS TargetBuildNumber, dbo.Defects.BuildNumberOfFix,      CONVERT(varchar(20), dbo.Users.FirstName) + ' ' + CONVERT(varchar(20), dbo.Users.LastName) as Creator, " _
  3.         , "       CONVERT(varchar(20), Users_1.FirstName) + ' ' + CONVERT(varchar(20), Users_1.LastName)    AS AssignedUser, dbo.Projects.Name AS ProjectName " _
  4.         , "  FROM         dbo.Projects INNER JOIN " _
  5.         , "    dbo.Defects ON dbo.Projects.ProjectId = dbo.Defects.ProjectId INNER JOIN " _
  6.         , "                        dbo.Users ON dbo.Defects.CreatorId = dbo.Users.UserId INNER JOIN " _
  7.         , "                        dbo.PriorityTypes ON dbo.Defects.PriorityTypeId = dbo.PriorityTypes.PriorityTypeId INNER JOIN " _
  8.         , "                        dbo.SeverityTypes ON dbo.Defects.SeverityTypeId = dbo.SeverityTypes.SeverityTypeId INNER JOIN " _
  9.         , "                        dbo.StatusTypes ON dbo.Defects.StatusTypeId = dbo.StatusTypes.StatusTypeId INNER JOIN " _
  10.         , "                        dbo.WorkflowSteps ON dbo.Defects.WorkflowStepId = dbo.WorkflowSteps.WorkflowStepId INNER JOIN" _
  11.         , "                        dbo.DefectCustomFields ON dbo.Defects.DefectId = dbo.DefectCustomFields.DefectId  LEFT OUTER JOIN" _
  12.         , "                       dbo.Users AS Users_1 ON dbo.Defects.AssignedToId = Users_1.UserId" _
  13.         , "  WHERE     (dbo.Defects.StatusTypeId IN (5)) AND (dbo.Projects.ProjectId IN (" + projIds + ")) " _
  14.         , " ORDER BY dbo.Defects.DefectId DESC  " _
  15.         , "   ")
  16.  
How do I add an exception just for one status?
Jul 16 '07 #1
4 1200
mwasif
802 Expert 512MB
It will be better if you provide the query without VBA code, some db structure and kindly use proper mysql code tags.
Jul 16 '07 #2
Newbie19
123 100+
Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.Issues.ID as ID , dbo.Types.Name as Types , dbo.Issues.Synopsis as Summary , dbo.States.Name as State , dbo.Users.Name as [User], dbo.Projects.Name as Project 
  2.  FROM       dbo.Issues INNER JOIN 
  3.           dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN 
  4.            dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN
  5.            dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN 
  6.             dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID 
  7.     WHERE     (dbo.States.Id IN (22, 19, 11, 17, 28, 23)) AND (dbo.issues.projectId IN (75) 
  8.   ORDER BY dbo.Issues.ID, dbo.Types.Name, dbo.states.Name DESC
Jul 16 '07 #3
mwasif
802 Expert 512MB
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo.Issues.ID as ID , dbo.Types.Name as Types , dbo.Issues.Synopsis as Summary , dbo.States.Name as State , dbo.Users.Name as [User], dbo.Projects.Name as Project 
  2. FROM dbo.Issues INNER JOIN 
  3. dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN 
  4. dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN
  5. dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN 
  6. dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID 
  7. WHERE (dbo.States.Id IN (22, 19, 11, 17, 28, 23)) AND (dbo.issues.projectId IN (75)  AND ( (status=1 and date_col='2007-07-16') OR status<>1 )
  8. ORDER BY dbo.Issues.ID, dbo.Types.Name, dbo.states.Name DESC
Jul 16 '07 #4
Newbie19
123 100+
Thanks for the help, I'll try that.
Jul 16 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Alec Christie | last post by:
Hi All, I currently have 3 tables, Clients, Events and Event Status as follows: Clients: ClientID, Name, Address Events: EventID, Name, Date Event Status: StatusID, EventID, ClientID,...
5
by: hasanainf | last post by:
Hi, Need help on this report My report displays as follows Receipt Number Checkout Date Payment Date 13580 30-06-2005 02-07-2005 13581 01-07-2005 02-07-2005
3
by: Sean Tynan | last post by:
I want to find out the best way for a method to notify calling code of situations such as validation errors, etc. that may occur during method execution. e.g. say I have a method (business...
3
by: lagj | last post by:
Using MySQL 5.0.15 I am no expert so maybe I am deeply misundertanding how this should work. I have a relatively complex quey with joins and a subquery (it is probably far from optimized, but...
4
by: Alan Silver | last post by:
Hello, I have an error logging system on one of my sites, so that if an unexpected exception occurs, I get an e-mail sent. The e-mail includes the name of the page on which the exception...
5
by: Prasad | last post by:
Hi all, I wanted to know whether my page is connecting to server or not using xmlHttp request. For that , I am using one condition in onreadystatechange function as.. function xxx() {...
2
by: dhutton | last post by:
Hello everyone, My query below works fine - but I'm having problems with identifying each of the 4 reports that are run in here. I need to have a column that has the name of the report run which...
3
by: Allie | last post by:
Hi, all. I've never been any good at handling exceptions... So I need your help. Within a try{ } block, I have to call a function that has to throw its own exception. How should I go about...
5
by: Mysterydave | last post by:
Hi, If I have a cross tab query which counts an and pivots on a for a , how do I add a new row heading that will sum only a certain few status's. E.g. The field has "A", "B", "C", "D". I want...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.