Transact SQL :: Query For Multiple Optional Conditions?

Jun 1, 2015

I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.

select * from table where
IND = 'Y'
and
(
(STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1
OR
(STATE = @STATE and LOB =@LOB)--Condition 2
OR
(STATE = @STATE and COMPANY=@COMPANY)--Condition 3
OR
(STATE = @STATE)--Condition 4
)

View 4 Replies

SQL Query Multiple/Optional Feilds

Dec 3, 2004

Hello Group
I am having a little trouble with a search. I have 12 checkbox controls and 12 textbox controls. I want to be able to choose which textbox to query from. Most of the time the search may be from one or a couple of textboxes. My problem is I can get the SQL String right. Below is what I have for a string. I know I could write an SQL string for every possible combination and put in all 144 in a SELECT CASE, but I would think there is an easier way to do this. Is there anything that would let me build a SQL string depending what checkbox are checked true?

Dim SQL_SL As String = "SELECT * FROM BuildZone" & _
" WHERE Parcel = '" & strPercelS & "'" & _
" ORDER BY Parcel Asc" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldNameLast = '" & strNameLastS & "'" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldDateMonth = " & intDateYearS & "" & _
" AND fldDateYear = " & intDateYearS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldNameLast = '" & strNameLastS & "'" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldPermitNum = " & intPermitS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldStructureType = " & intStructureTypeS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldConstructionCost >= " & intCCost1S & "" & _
" AND fldConstructionCost <= " & intCCost2S & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldRange = " & intRangeS & "" & _
" AND fldTownship = " & intTownshipS & "" & _
" AND fldSection = " & intSectionS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldZoningDistricts = " & intZoneS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldTwsp = " & intTwspS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldRWD = " & intRWDS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldWW = " & intWWS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldAccessApplic = " & intAccessAppS & ""

Thanks

View 2 Replies View Related

Using Stored Procedures/parameters When WHERE String Has Optional Conditions

Sep 15, 2005

I've created a search page in my asp.net app that allows the user to enter optional parameters to narrow down the result set. It looks something like:Find all parts where: manuafacturer: <dropdownlist>ANY | manufacturer 1 |... </dropdownlist> model: <dropdownlist>ANY | model 1 |... </dropdownlist> cost: between <textbox> and <textbox> dollarsCurrently I create the SQL command on the fly building the WHERE based on what the user selects. For example if in the form above they select manufacturer = manufacturer1 model = ANY cost = between 10 and 15the WHERE string is ... WHERE manufacturer='manufacturer1' AND cost BETWEEN 10 AND 15Since the user doesn't care about model I leave it out of the WHERE. OK so here is my question. I want to move my queries to strored procedures however I'm not sure how to create the query since it changes based on what the user enters. Using the example above I'm assuming I can create one query with 4 parameters however what value would I use for ANY? parameter1 (manufacturer) = "manufacturer1" parameter2 (model) = ??? parameter3 (price low) = 10 parameter4 (proce high) = 15I see there is an ANY operator in T-SQL but it doesn't look like the right thing to use. Should I use LIKE '%'? Seems that using LIKE would result in addition overhead.ThanksSimon

View 2 Replies View Related

Multiple Conditions In A Query

Feb 2, 2008

Hi,

I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.:
1 - product A
2 - product B
3- product C
4 - product D
....
10 - product J
and so on

I need to create a stored procedure that querys only the product types selected by the user.

The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.

For the first user selection a have something like this
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10
For the second,
SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3

Is it possible to have a stored procedures that runs a query with this random scenario?

please help

Thanks

JPP

View 8 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)

No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'

-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')

So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

Multiple/optional Parameters

Sep 11, 2007

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

View 6 Replies View Related

Transact SQL :: Concatenate String Using Conditions?

Sep 19, 2015

I have a SQL table like this

col1 col2 col3
1 0 0
1 0 1
1 1 1
0 1 0

I am expecting output as

col1 col2 col3 NewCol
1 0 0 SL
1 0 1 SL,PL
1 1 1 SL,EL,PL
0 1 0 EL

condition if col>0 then SL else '', if col2>0 EL else '', if col3>0 PL else ''

View 5 Replies View Related

Transact SQL :: Update Table From Another With Conditions

Dec 2, 2015

I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :

        opdragaliasnaaminsit.Connection = konneksie
        opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
        "SET aliasnaam = T2.aliasnaam" & _
        " FROM vyfjaarontledings T1" & _
        " INNER " & _
        "JOIN blokke T2 " & _
        " ON T1.plaasno = T2.plaasno " & _
         "WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()

I am trying to update aliasnaam in vyfjaarontledings from blokke.

View 10 Replies View Related

Transact SQL :: Select Seems To Be Ignoring WHERE Conditions

Apr 23, 2015

SQL Version: 2008 (not r2)
Problem: My Select statement seems to be unaffected by some of the conditions in the WHERE Clause. For instance

JCCD.Mth >= cutoffs.FiscalYear_FirstMonth (value '20130101') AND JCCD.Mth <= @WIPMonthCurrent (value '20130101')AND LTRIM(RTRIM(JCCD.Job)) = '71-' (see output and code below)
SQL Code:
declare @WIPMonthCurrent date = '20130101'
SELECT
JCCD.JCCo, JCCD.Job, JCCD.Mth, JCCD.Source, sum(JCCD.ActualCost) AS CostToDate

[code]....

View 5 Replies View Related

Transact SQL :: How To Count And Sum A Date Based On Conditions

Jun 18, 2015

I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:

SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END

My problem is the WHEN.

View 6 Replies View Related

Transact SQL :: CASE - Maximum Number Of WHEN / THEN Conditions

Oct 20, 2015

I need to update many rows in some table. I've made such SQL query:

UPDATE [%TableName%]
SET [%FieldName%] = CASE
            WHEN ID = 1 THEN '1'
            WHEN ID =2 THEN '2'
            ....
       END;
       [%FieldName2%] = CASE
            WHEN ID = 1 THEN '1'
            WHEN ID = 2 THEN '2'
            ....
       END;
WHERE ID IN {1, 2, ...}

Are there some limitations for CASE operator? How many "when - then" conditions can I include in query?

View 6 Replies View Related

Multiple AND Conditions

Sep 12, 2006

Hi All.

Is there a way to have multiple AND conditions on the same field in a database.

EXAMPLE

SELECT * FROM tbl
WHERE field1 = 1 AND field1 = 2 AND field1 = 5

Thanks

View 9 Replies View Related

Multiple Conditions In IF Statement In T-SQL?

Jul 20, 2005

Hi All,Just wondering if anyone can tell me if you can test for multipleconditions as part of an "IF" statement in T-SQL in SQL Server 2000?ie something like:IF @merr = 1 or @merr=2BeginSELECT statementUPDATE statementEndAny help much appreciated!Much warmth,M.

View 1 Replies View Related

Select With Multiple Conditions

May 12, 2008

Select c.Plan, c.ClaimNumber
from tbFLags c inner join tbMembers m
On c.Claim = m.HistoryNum
where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'

Now I want to add these into this statement, what should be done.

Members meeting any of the 3 sets of criteria should not be selected

1) tbFlags.Hosp='1'

2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')

3) Deceased = tbMembers.DOD is not null.

View 27 Replies View Related

Analysis :: Use IIF For Multiple Conditions - MDX

Jun 2, 2015

I am have a dimension to use as a slicer, I am trying to associate that dimension with a measure group using a calculated measure. I am using the below query:

With member [THD Inventory Balance] as
IIF(
[THD Conversion Units].[Conversion Units].CURRENTMEMBER = [THD Conversion Units].[Conversion Units].&[EA], [Measures].[thdpos - Inventory Balance EA], 0)
IIF(
[THD Conversion Units].[Conversion Units].CURRENTMEMBER = [THD Conversion Units].[Conversion Units].&[LF],

[Code] ....

But it fails with the error: The syntax for 'IIF' is not correct.

View 6 Replies View Related

Multiple OR LIKE Conditions In Sproc

Nov 12, 2007

I have a ListBox controls that contains about 5 items. A stored procedure is executed based on selections of other controls ont he screen, but I cann't figure out how to properly get the dynamically selected conditions passed to the sproc from C#.

If a user selects 3 of the five items, the sproc needs to build something like this:

WHERE Region LIKE Item1 OR Region LIKE Item2 OR Region LIKE Item3

I cannot figure out how to do this. It works properly if I just make the ListBox SelectionMode as Single and pass that one selected item's value.

Any help is greatly appreciated.

Thanks,
Chris

View 3 Replies View Related

Transact SQL :: Running Query For Multiple Times Through The Day?

Jun 12, 2015

I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .

there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job

INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
SELECT
--DISTINCT
p.SPID,
p.LogiName,
p.HostName,

[Code] ....

View 8 Replies View Related

Transact SQL :: How To Use Multiple Values In Where Condition In Query

Jun 17, 2015

I have a query where i would calculate the counts of three Servers

declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'

[Code] ...

Here i need output like below

Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600

So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?

View 3 Replies View Related

Transact SQL :: How To Extract Dates From A Single Column Based On Conditions

Oct 13, 2015

The data I have is as follows -

ID1    ID2     Date            Action
100   500     09/08/14     Open
100   500     09/24/14     Close
101   510     07/10/15     Open
101   510     07/19/15     Close

The output I want in a single result set is -

ID1    ID2     Open_Date   Close_Date
100    500    09/08/14      09/24/14
101    510    07/10/15      07/19/15

Any way to do this in T-SQL .

View 10 Replies View Related

Generating Data Using Multiple Conditions

Nov 22, 2007

Hi,

I am attempting to explain my probelm again. Please read it:

I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType

For eg:

For Eg: ( Assume Call Detail id is changing for all the days)
Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls
Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls
Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee
Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.

So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because :
3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls
2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.

Here is what I wrote with the help of a mod over here:

Code Block
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA023', 12, 195, Null

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 1, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 2, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 1, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '12/5/2007 12:12:34 PM'

-- Query written with help of a helpful person here
UPDATE r
SET r.UniqueNo = dt.CallGroup
FROM @Request r
JOIN @Call c
ON r.CallID = c.CallID
JOIN (SELECT
CallDetailID,
EntryDt,EmpID,
CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt )
FROM @CallDetail
) dt
ON c.CallDetailID = dt.CallDetailID
select * from @Request

OUTPUT IS
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 2

Where as it should be

324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 1

as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.

Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid.
Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )

thanks for any help.

View 4 Replies View Related

Joining Multiple Tables With Conditions?

Mar 19, 2008

Hi,
I have a tbl called BANK.

This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.

I also have two other tables called Suppliers and SubContractors.

For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)

I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?

So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.

How would I best write that?

thanks for any help!

View 5 Replies View Related

Multiple Conditions In An UPDATE Clause

Dec 12, 2007

I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.

ie. 11100 all change to 8100
11200 all change to 8200

I know how to do a simple UPDATE
UPDATE GLTRANS
SET ACCNO = '8100'
WHERE ACCNO = '11100'

But how can i combine into 1 script rather than having to continually change this script 300 times??

Thanks
Wilbur

View 6 Replies View Related

Transact SQL :: Updating Multiple Tables In A Single Query?

Apr 27, 2015

Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.

View 8 Replies View Related

How Do I Insert Into Multiple Tables Bases On Conditions?

Jan 6, 2004

I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.

next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.

My code works as long as there isn't an existing record in the tables. Can someone give me a hand?

Here's what I have (and it doesn't work)

CREATE PROCEDURE b4b_sw_request

@FName as varchar(50)= NULL,
@LName as varchar(50)=NULL,
@Address1 as varchar(100) = NULL,
@Address2 as varchar(100) = NULL,
@City as varchar(50) = NULL,
@State as char(2) = NULL,
@Zip as char(5) = NULL,
@Email as varchar(100) = NULL,
@Send_Updates as smallint = '0'

AS

IF EXISTS
(SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @FName AND LName = @LName AND Zip = @Zip)
BEGIN
RETURN
END

ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
Values
(@FName, @LName, @Address1, @Address2, @City, @State, @Zip, @Email)
END

IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @@Identity)
BEGIN
RETURN
END

ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@@Identity, @Send_Updates)
END
GO

View 2 Replies View Related

Reporting Services :: Multiple Conditions In IIF Statement?

Jul 8, 2015

I have a table with the following fields and data:

Offense, Year, Month, Count, Amount
F 2007 January 11 $49,238.00
F 2007 February 12 $24,000.00
F 2007 March 31 $55,184.00
F 2007 April 19 $64,647.00
F 2007 May 33 $100,010.00
F 2007 June 16 $59,678.00
F 2007 July 22 $39,700.00
F 2007 August 3 $9,000.00
F 2007 September 4 $75,000.00
F 2007 October 8 $19,250.00
F 2007 November 50 $106,153.00
F 2007 December 26 $80,000.00

I have data for years 2007-2014. I am designing an SSRS report, and want to use IIF statement to return data. Using the following:

=IIF(Fields!Year.Value="2007" AND Fields!Month.Value="February", "return value of count field", "n/a")

The above does not work, it doesn't return the value of Count field, it does return the n/a.

View 14 Replies View Related

Select Rows Based On Multiple Date Conditions

Jun 5, 2008

Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

View 2 Replies View Related

Trigger To Set Status Code Using Multiple Conditions (EXISTS)

Sep 6, 2007

My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:

CREATE TRIGGER dbo.JulieTrigger1

ON dbo.Table1

AFTER INSERT,UPDATE

AS

BEGIN

BEGIN TRY

/*Update Table1.Status to POTENTIAL (id 23) status */

UPDATE TABLE1

SET status_id = 23

WHERE EXISTS (SELECT *

FROM TABLE1 a INNER JOIN TABLE2 b

ON b.order_id = a.order_id

WHERE a.start_dt IS NULL

AND b.current_status_ind = 1

AND b.lead_status_id NOT IN (15,16)

AND a.order_id = TABLE1.order_id)

/*Update Table1.Status to ACTIVE (id 24) status */

UPDATE TABLE1

SET status_id = 24

WHERE EXISTS (SELECT *

FROM TABLE1 a

WHERE fill_ind = 1

AND (end_dt IS NULL OR end_dt > getdate() )

AND a.job_order_id = TABLE1.job_order_id)

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE()

IF @@TRANCOUNT > 0

ROLLBACK TRAN

-- Return the error to the calling object

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

)

END CATCH

SET NOCOUNT ON;

END

GO

Thanks in advance for any help!
-Julie

View 1 Replies View Related

Reporting Services :: SSRS - IIF / Switch / Replace Multiple Conditions In A Field

Nov 18, 2015

Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.

I'm getting lost in this expression:

=SWITCH
( (InStr(Fields!Info.Value, CHR(13)&CHR(10) > 0, REPLACE(Fields!Info.Value, CHR(13)&CHR(10)), "x0Dx0A")), (InStr(Fields!Info.Value, CHR(13) > 0, REPLACE(Fields!Info.Value, CHR(13)), "x0D")), (InStr(Fields!Info.Value, CHR(10)
> 0, REPLACE(Fields!Info.Value, CHR(10)), "x0A")) )

Error is:

System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,

[Code] .....

View 2 Replies View Related

Optional Parameter In Sql Query

Nov 6, 2005

Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all

View 3 Replies View Related

Search Query With Optional Parameters

Jun 12, 2006

I need to create a stored procedure that will search some tables.

The stored procedure will be passed some parameters that may or may not have a value.

I have googled the best way to do this.
I found this post as an example: Optional Search Parameters

and also found this example : Optional Parameters in T-sQL

I am trying to figure out the best way to do this.

In the past I would build a dynamic query like the following.

SQL Code:

Original
- SQL Code

CREATE PROCEDURE [dbo].[Search_Results]

@SUBCITY VarChar(100) = 'Any'

AS

------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------

Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*

FROMTable1

WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '

ORDER BY Column ASC'

Execute (@SQLString)

GO

CREATE PROCEDURE [dbo].[Search_Results] @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN SET @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = ' SELECT * FROM Table1 WHERE Table1.ID IS NOT NULL ' + @SUBCITYString + ' ORDER BY Column ASC' Execute (@SQLString) GO

However this is really cumbersome to create and is not fun debugging!

Does one of these ways have an advantage over the other? Or is there another way to do this?

Thank you!

View 2 Replies View Related