T-SQL (SS2K8) :: Replace Multiple Characters Based On Table Values

Dec 12, 2014

There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]

[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000

For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400

The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...

DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';

SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]

SET @Total='select '+@formula

EXECUTE sp_executesql @Total;
PRINT @Total;

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?

Jun 21, 2012

I am trying to replace all special characters in a column with one special character.

Example:

Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"

I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.

I am expecting the following results:

Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Multiple Values Based On Parameter

Jul 22, 2014

I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below

CLASS Days
A sun
A Mon
A wed

View 6 Replies View Related

T-SQL (SS2K8) :: Replace All New Line Characters

May 19, 2014

I am working with a large amount of text data in a single column. I am in the process of pumping that column of data into a delimited text file. The data in the column has new line characters that I need to remove. I need to data to be in a single line in the text file.

So far the things that I have tried are not working. What I try for removing the new line characters?

View 3 Replies View Related

Transact SQL :: Update One Table Based On Another Table Values For Multiple Values

Apr 26, 2015

I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B. 

View 5 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

SQL Server 2014 :: How To Update Values Based On Column Into Multiple Columns In Another Table

Jul 31, 2015

I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.

CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE

One way to achieve this is to write two update statements. After update, the output you see is my desired output

UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output

my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.

View 5 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

Transact SQL :: Replace Column Value From ASCII Characters To Non ASCII Characters In Table?

Oct 22, 2015

I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.

Note – values in column must be same

View 10 Replies View Related

Replace Bad Characters In Table

Dec 10, 2013

I've got a lot of %20 characters in my tables, and need to remove them however I get the error:

Argument data type ntext is invalid for argument 1 of replace function.

My Code:

UPDATE cmsPropertyData
SET dataNtext = REPLACE(dataNtext,'%20','')
WHERE cmsPropertyData.contentNodeId = 1151

View 2 Replies View Related

Replace Characters On Condition In Table And Cells

Apr 14, 2008

need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters


table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

View 5 Replies View Related

Replace Characters On Condition In Table And Cell

Apr 11, 2008

need help
condition 1
replace characters on condition in table and cells
but only if
how to do it - if i put * (asterisk) like in employee 111 in day1 - the the upper characters the (A S B)
i replace characters with '-'
and it must work dynamically
condition 2
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above any cell for example( employee id=222 name =bbbb day1)
i replace characters with '0' and '#'
and it must work dynamically
table before the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
A
S
B
e
t
y
R
Y
M



j
o
p

111
aaaa

1
*


*


*





*



222
bbbb

2
1



1











222
bbbb

1
A
-
-
-
B
-










333
cccc

2
















333
cccc

1
















444
dddd

2








3





4

444
dddd

1






-
-
C





C

555
EEE

2
A
G
C













555
EEE

1
*



































table after the replace




id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2
-
-
-
-
-
-
-
-
-



-
-
-

111
aaaa

1
null


null


null





null



222
bbbb

2
0



0











222
bbbb

1
#
-
-
-
#
-










333
cccc

2
















333
cccc

1
















444
dddd

2








0





0

444
dddd

1






-
-
#





#

555
EEE

2
-
-
-













555
EEE

1
null































tnx for the help

View 11 Replies View Related

Transact SQL :: Replace Multiple Values Without Looping

Jul 13, 2015

I need one query:

create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>>  Text Text Text <<Salary>>  Text Text Text')

[Code] ....

Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')

I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Replace Multiple Values Without Looping

Jul 13, 2015

I need one query...

create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task

[Code] ....

Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as

[Code] ....

View 7 Replies View Related

REPLACE Funtion For Multiple Values In SP/Reporting Services

Mar 4, 2008

Running into a problem with trying to pull multiple values in a stored procedure.
Ran Profiler for trace on what variable is coming back as when running in Reporting Services.
Profiler shows this:


exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier'

Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks.
What I have:


Set @Product=

Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))


This is probably a little too much coding but I've tried several ways.

In the SP the code is as follows:


CREATE PROCEDURE [dbo].[apGetCompanysByRep]

@Magazine varchar(6) = null

, @DirectsMagazine varchar(6) = null

, @Category varchar(50) = null

, @SubCategory varchar(50) = null

--, @FirstName varchar(30)

, @LastName varchar(30)= null

, @Product varchar(1100)

AS

declare @SQL varchar(2000)

Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))



set @SQL = '





Thanks for any assistance anyone can give.

View 9 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

T-SQL (SS2K8) :: Find Alt Code Characters In Table

Feb 12, 2015

I have a table with code and description as below

create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','as╚as')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go

I want to identify the list of alt codes available in the table.

View 6 Replies View Related

T-SQL (SS2K8) :: Search Special Characters In Column Of Table

Jul 14, 2014

I am using SQL Server 2008. In one of my table, one column has values like

"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"

So to fetch the data having only special characters in it, I used below query

Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].

View 2 Replies View Related

T-SQL (SS2K8) :: Searching For Allowed Characters From Table Using Function?

Aug 10, 2015

I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.

For example -

'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'

Wanted results – the single quotes are there to show the space for the replaced characters.

'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'

Example SQL data

CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF

[code]....

The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.

View 9 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

Selecting Rows From A Table Based On First 2 Characters Of 12 Char Column

Oct 21, 2013

I have to select rows from a table

if the first 2 characters of a 12 char column are
'GB'

Select BFKEYC from table where

I have a hokey way of doing it but it looks embarrassing:

BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'

View 8 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 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

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.

Aug 7, 2007

Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:

I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:

(AISC_Shapes_Table)

AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100

(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)

I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:




Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))



It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.

Regards,

Steve G.



View 4 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

Select Values From One Table Based Upon Values In Another...

May 19, 2006

How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?

View 3 Replies View Related

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

Jan 15, 2015

I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now

Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1

Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.

Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1

I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved