Custom / Advanced Where Statement


Im new to writing SQL, and most of my needs so far have been met in the wonderful w3 SQL tutorial. However, I have a dee I havent been able to find a way to do, and I can't find info on it in the forums either.

I have 3 inner joined tables in the part of the code Im struggling with.ClientsView
EmployeeClients
Employees For a given client (identified by ClientsView.client_id) there are many employees linked to the client. I want to search those employees and return in a single column Primary_Staff the employee assigned to the client who meets certain criteria. In all cases the field EmployeeClients.primary_flag will need to be YES. The kicker is that what I want it to look for is first to see if an employee is found where the Employee.profile_code = MHP. If so, that is the employee I want listed in the new Primary_Staff field. If no one is found with that profile code, then I want it to search the employees to see if any is found where Employee.profile_code = On Call MHP. Then Employee.profile_code = LPE. And a few others, but thats enough for the example. If it goes through the list of profile codes Im looking for and doesnt find anyone, then I just want it to say Unassigned.

Posted On: Sunday 30th of December 2012 10:54:55 PM Total Views:  245
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Help with two related where clauses

I have a couple of queries in a union all that need to get all records, some from the first where clause, and everything else in the other. Here is the original where clause: from license_status ls, pos_driver pd, pos_policy pp where pp.pos_id = @pos_id and pd.pos_id = pp.pos_id and pd.driver_number = @driver and ls.company = pp.company and ls.license_state = pd.license_state I have added a few additional elements to limit records to the state of washington, for a particular company, and a specific answer to a question. Here is the new one: from license_status ls, pos_driver pd, pos_policy pp, pos_answers pa where pp.pos_id = @pos_id and pp.policy_state = 'WA' and pp.company = 5 and pd.pos_id = pp.pos_id and pd.driver_number = @driver and ls.company = pp.company and ls.license_state = pd.license_state and pp.pos_id = pa.pos_id and pa.question_number = 8 and pa.yes_no = 'YES' So now I need to rewrite the original where clause to get all the records this new where clause excludes. I started by adding this: and pp.policy_state = 'WA' But realized there would be some policies in WA that did not meet the other criteria. So how do I go about this without writing a ton of permutations to get all the possibilities
VIEWS ON THIS POST

95

Posted on:

Thursday 25th October 2012
View Replies!

Complicated where issue

, I have a report that has 5 options. Each option can be Yes, No or N/A. These values are represented by 1(Yes) 0(No) and N/A means it can be either and is sent as a -1. If they select Yes on two of these options then I need to bring back records that have a 1 of the options selected as Yes. The problem I'm having is my lack of SQL experience and I can't think of a way to say that in SQLese. What I have now is: Code: WHERE (Table.Option1 = @Option1 or @Option1 = -1) AND Table.Option2 = @Option2 or @Option2 = -1) ...etc The problem I see with this is that all of the options on the record have to match the options no the report criteria when they need to be one or the other. BUT I can't just replace the ANDs with ORs because there will almost always be one of the options set to -1 so it will return everything. Can someone help me build this SQL so I can get all the records that have one of the options but not require all of them to match but not take back everything just because one of the options can be anything
VIEWS ON THIS POST

218

Posted on:

Thursday 25th October 2012
View Replies!

Help with dynamic where clause in stored procedure

I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run. at this point i keep getting errors in the section bolded below it never seems to recognize anything after the if @myColours 'Select' CREATE Procedure PG_getAdvWheelSearchResults3 ( @SearchDiameter NVarchar( 20 ), @SearchWidth NVarchar( 20 ), @minOffset int , @maxOffset int , @boltpattern1 NVarchar( 20 ), @VendorName NVarchar( 40 ), @myColours NVarchar( 40 ) ) As BEGIN TRANSACTION SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image, dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2 FROM WHEEL_CHARACTERISTICS INNER JOIN dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y') AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039') AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter) AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth) AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1) if @myColours 'Select' and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours end if AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset) ORDER BY CLIENT_WHEEL_PRICES5.Price asc COMMIT TRANSACTION GO Anyone know how i should word the if...statements I have not found anything that works yet.
VIEWS ON THIS POST

171

Posted on:

Thursday 25th October 2012
View Replies!

Adding column amounts per customerID

Hi Code: SELECT C.customerID, quantity, unitprice, (SELECT quantity * unitprice), OD.productID, FROM customers C INNER JOIN Orders O ON C.customerID = O.customerID INNER JOIN [order details] OD ON O.orderID = OD.orderID ORDER BY C.CustomerID The Output looks a little like this: Code: customerID quantity unitprice productID ---------- -------- --------------------- --------------------- ----------- ALFKI 15 45.6000 684.0000 28 ALFKI 21 18.0000 378.0000 39 ANATR 1 28.8000 28.8000 69 ANATR 7 9.2000 64.4000 19 ANATR 10 34.8000 348.0000 72 ANTON 24 16.8000 403.2000 11 ANTON 15 46.0000 690.0000 43 AROUT 25 3.6000 90.0000 24 AROUT 16 19.0000 304.0000 36 BERGS 16 15.5000 248.0000 44 BERGS 15 44.0000 660.0000 59 BLAUS 3 10.0000 30.0000 21 BLAUS 21 34.0000 714.0000 60 BLONP 35 12.5000 437.5000 31 BLONP 15 19.5000 292.5000 57 BOLID 24 17.6000 422.4000 4 BOLID 16 15.6000 249.6000 57 BONAP 40 36.8000 1472.0000 43 BONAP 20 7.7500 155.0000 75 BONAP 8 30.0000 240.0000 7 BONAP 20 6.0000 120.0000 13 BONAP 20 25.0000 500.0000 6 BONAP 20 23.2500 465.0000 14 BONAP 10 9.2000 92.0000 19 BOTTM 16 24.8000 396.8000 10 BOTTM 9 46.0000 414.0000 43 BOTTM 30 4.5000 135.0000 24 BOTTM 21 49.3000 1035.3000 62 BOTTM 15 2.5000 37.5000 33 I would like to add the totals from each customerID and then show the customerID once with the final total amount. The above SELECT statement was the closest I could come. The help is always appreciated! Justin
VIEWS ON THIS POST

121

Posted on:

Friday 26th October 2012
View Replies!

where based on length of field.

all. I'm looking around for a solution on how to pull records back based on legnth. So, if field A is more then 2 characters, return that row.
VIEWS ON THIS POST

100

Posted on:

Friday 26th October 2012
View Replies!

Any benefit from filtering in join vs. the where clause?

Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined. Filtering in where clause.... Code: select ... From tProject p with (noLock) join tProjectCall pc with (noLock) on P.ID = pc.project_id join tStore S with (noLock) on pc.store_id = s.id join tZip Z with (noLock) on Z.zip5 = s.zip5 join tManager M on M.ID = case ... end left join ( select projectCall_RecNum as RecNum, sum(answer) as HoursUsed from tCall C where Answer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum where pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and s.closed = 0 and s.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) Filtering in joins... Code: select ... From tProject p with (noLock) join tProjectCall pc with (noLock) on P.ID = pc.project_id and pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) join tStore S with (noLock) on pc.store_id = s.id join tZip Z with (noLock) on Z.zip5 = s.zip5 and s.closed = 0 and s.deleted = 0 join tManager M on M.ID = case ... end left join ( select projectCall_RecNum as RecNum, sum(answer) as HoursUsed from tCall C where Answer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum
VIEWS ON THIS POST

127

Posted on:

Friday 26th October 2012
View Replies!

SQL Server 2005

, I am installing sql server 2005 on windows xp professional. MICROSOFT SOFTWARE LICENSE TERMS MICROSOFT SQL SERVER 2005 STANDARD AND ENTERPRISE EDITIONS But i am getting this error Code: sql server edition operatigng system compatibility - SQL Server Edition Operating System Compatibility (Warning) Messages SQL Server Edition Operating System Compatibility Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online. any ideas how to fix this error todd
VIEWS ON THIS POST

74

Posted on:

Wednesday 7th November 2012
View Replies!

Conditional Triggers

Is there any possibility to run the Trigger with some conditions\t Situation:I have a form which gets value only 'A' or 'B' from the textbox. If it is 'A' then my trigger(PubTrigger) will be enabled else if 'B' my trigger should be disabled. Pls note: Everything should be in back end [SQL server 2005] and not in front end. And pls help as soon as possible i ve tried everything but being a newbie i couldnt just break through ! Advance
VIEWS ON THIS POST

52

Posted on:

Wednesday 7th November 2012
View Replies!

Failed to generate a user instance of SQL Server

Okay, so i have SQL Express, and SQL Server 2k5 installed, this problem didnt occur until after i installed SQL 2k5. now every time a user using the login forms (.net pre-build templates) i get the following problem. i have poked a round a few forums, but i cant figure out what's up. any help would be GREAT! Server Error in '/' Application. -------------------------------------------------------------------------------- Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.] System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) +735091 System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.Com pleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(Htt pContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConne ction(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPassw ordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPas sword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPas sword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.Validate User(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(Aut henticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Obje ct source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(Command EventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
VIEWS ON THIS POST

114

Posted on:

Sunday 11th November 2012
View Replies!

Check Constraint

I want to create a constraint on a column of a child table to check if the date i'll enter is greater than the date column in the parent table. How can I use CHECK CONSTRAINT to compare values present in columns of 2 different tables Note: My tables have the same primary key.
VIEWS ON THIS POST

83

Posted on:

Thursday 15th November 2012
View Replies!

Check if table exists

Is it possible to have an SQL syntax that returns TRUE if a table exists and FALSE if not I'm working in VB.NET and would like to have a check before I'm trying to write to the table. The check should be totally separate from the write function; since I want to give a message to the user if the table doesn't exist with a button where he can click to create the table. Thus queries like "IF EXISTS (SELECT * FROM [MyTable]) SELECT * FROM [MyTable]" are not really a solution
VIEWS ON THIS POST

91

Posted on:

Tuesday 20th November 2012
View Replies!

HTTP 500.100 - Internal Server Error - ASP error

Hi I am using SQL server for database.When i am trying to save data through asp page i am getting the error.I have checked the connection string it is ok.I dont know whats wrong. This is the error msg Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. Do anyone have any Idea
VIEWS ON THIS POST

210

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Server Sum hours - problem - Page 2

Oh I am sorry to hear you have the flu.. I really should just leave you alone. I created the schedule table and defined the # of hours as Time. I was thinking about recreating the table and changing hours to datetime even though that doesn't make sense to me to have a date on the number of hours. I think if I defined it as float I would be able to calculate it better by just using minutes as calculation for the difference in time. I want to thank you for all your code and suggestions. It has helped me by realizing I have alternatives to getting to my end result that I need.
VIEWS ON THIS POST

134

Posted on:

Monday 26th November 2012
View Replies!