Transact SQL :: How To Get Results Based On Conditional Where Clause
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID FROM tblGRNItem INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp WHERE tblGRNItem.DateCreated BETWEEN (SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below, 2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1'; @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0 DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY, schoolID int NOT NULL, UNIQUE(schoolID,lockID) )
[Code] ....
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?
Hi, [SQL 2005 Express] I would like a DropDownList to be populated differently depending on the selected value in a FormView. If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3. Here is the SQL for case 1: SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID). Here's the SQL for case 2: SELECT AdviserID, AdviserName FROM Advisers WHERE (CompanyID = @CompanyID) AND (TypeID = 3). Here's my best (failed) attempt to get what I want: SELECT AdviserID, AdviserName FROM Advisers WHERE IF @CompanyID = 2 THEN BEGIN (CompanyID = @CompanyID) END ELSE BEGIN (CompanyID = @CompanyID) AND (TypeID = 3) END I've also tried: SELECT AdviserID, AdviserName FROM Advisers WHERE CASE @CompanyID WHEN 2 THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END and SELECT AdviserID, AdviserName FROM Advisers WHERE CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID) ELSE (CompanyID = @CompanyID) AND (TypeID = 3) END I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure. Thanks very much. Regards Gary
Now based on a parameter I want to include a predicate in the select statement.
Basically I am trying to write something similar to the one below but possible only usinf one select statement.
if @queryString is null then
select query ,sum(frequency)
from qt
group by query else select query ,sum(frequency)
from qt
group by query where query = @queryString.
Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.
Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement? I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion. Any advice is greatly appreciated.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
Consider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared
After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.
I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.
SELECT uniqueID, lob, xdate FROM mytable WHERE
CASE WHEN @myparam = 'ALL'
THEN
xdate >= '2007-09-01' ELSE
xdate >= '2007-09-01' or lob = @myparm END
I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.
I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.
LEFT OUTER JOIN City ON City.CityID = Class.CityID
LEFT OUTER JOIN ClassInstructor ON ClassInstructor.ClassID = Class.ClassID
LEFT OUTER JOIN Instructor ON Instructor.InstructorID = ClassInstructor.InstructorID
WHERE Class.ClassID = @ClassID
AND ClassInstructor.IsPrimary=1
The problem is that if the class doesn't have an instructor yet, the query doesn't return results because even though I am using a LEFT JOIN on the class table, the "ClassInstructor.IsPrimary=1" statement will cause no results. I want results from the other class fields even if the class doesn't have an instructor.
I tried this and it works but seems kinda inefficient:
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
My simple QueryA
SELECT colA, colB, colC, colD FROM tableA WHERE colD IS NOT NULL
My simple QueryB
SELECT colA, colB, colC, colD FROM tableA WHERE colC IS NOT NULL
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
I know it can be done using dynamic SQL construction. But any other ways ?
Also can someone also give in the solution in PL/SQL.
Hello everybody,After several attempts of writing the query, I had to post myrequirement in the forum.Here is what I have, what I need and what I did.Table ACol1 Col21 Nm12 Nm23 Nm3Table BCol1 Col210 10020 200Table CCol1 (A.Col1) Col2 (B.Col1)1 102 10Table DCol1 (A.Col1) Col21 Value12 Value2I need results based on below criteria,1.Criteria - B.Col2 = 100ResultsetA.Col1 D.Col11 Value12 Value22.Criteria - B.Col2 =""A.Col1 D.Col11 Value12 Value23 NULL3.Criteria - B.Col2 =200Empty resultsetHere is the query I tried, but looks its not working. Probably there isa better way to do this.DDL and DML statements:create table #tab1 (a1 int, a2 nvarchar(20))create table #tab2 (b1 int, b2 int)create table #tab3 (c1 int, c2 int)create table #tab4 (d1 int, d2 nvarchar(20))insert into #tab1 values (1, 'nm1')insert into #tab1 values (2, 'nm2')insert into #tab1 values (3, 'nm3')insert into #tab2 values (10, 100)insert into #tab2 values (20, 200)insert into #tab3 values (1, 10)insert into #tab3 values (2, 10)insert into #tab4 values (1, 'value1')insert into #tab4 values (2, 'value2')selecta.a1, d.d2from #tab1 aleft join #tab3 bon a.a1 = b.c1left join #tab2 con b.c2 = c.b1left join #tab4 don a.a1 = d.d1wherec.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 dwhere a.a1 = d.d1and c.b2 = [100 or 200 or ''] )The above query works well to give results for Criteria 1 and Criteria3, but doesn't return for '' (criteria 2). I couldn't manage crackingthe solution. I shall try once again, but meanwhile if anyone couldhelp me in this, that would be great.Thanks.
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields: 1. MatchID (key) 2. MemberID (Unique ID of member from MemberList table) 3. ResidentID (Unqiue ID of matching member from CityList table). 4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)
In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.
Basically, I would like to implement logic that would work like this:
SELECT Date, Store#, Item#, CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales) ELSE %Total Sales END
FROM (some tables and joins) GROUP BY Date, Store#, Item#
However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
My Query: SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example). I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.
Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.
Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.
- Sales Volume: Information about sold articles to a customer incl. Selling date - Survey: irregular answered survey questions about customers incl. date of answer and three Dimensions: - Customer - Date - Survey Answer: Information about possible Answer values (e.g. Yes / No)
We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.
For example: Selected Time period: Jan - Jul 2015 Sales Volume Customer X - Jan - Jul 2015: 1000 Litres Sales Volume Customer Y - Jan - Jul 2015: 500 Litres
Surveys answered:
15th Jan 15: Customer X, Survey Question A: Yes 2nd Mar 15: Customer X, Survey Question A: No 20th Apr 15: Customer X, Survey Question A: Yes
10th Feb 15: Customer Y, Survey Question A: Yes 20th Jul 15: Customer Y, Survey Question A: No
Latest survey answer (Jan-Jul) Customer X, Question A: Yes Latest survey answer (Jan-Jul) Customer Y, Question A: No
Excel Pivot should show something like this:
Question | Latest Answer: Yes | No |
------------------------------------------------------------------------------------ A | 1000 Litres | 500 Litres |
I am trying to return the 100th ranking in my SQL, ie
SELECT DailyValueChange, BUSINESS_DATE, RANK() OVER (order by DailyValueChange) AS RANK_Vals
FROM Table
WHERE (BUSINESS_DATE = @CurrentBusDate) AND (RANK_Vals = 100)
However when I try to update the Stored Procedure it tells me RANK_Vals is an invalid column name, which is not the case as if I run it without the Where clase it runs and returns all results.
Any advice on how to get around this would be greatly appreciated.
