Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)
I need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
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.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1 if n4-n3=1 then i=i + 1 if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int set @test1=0 select top 10 n1,n2,n3,n4,n5,n6, case when ( n1=2 and n2>5 ) then @test1+1 else @test1 end as t2 from allNumbers
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
Hi, I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work. Heres what i want to do:
This is the how the table is: EmpID Designation
678 CFA
679 CFA
680 CFA
685 CFP
685 CIMA
685 IMCA
I want it to display as: EmpID Designation1 Designation2 Designation3 678 CFA 679 CFA 680 CFA 685 CFP CIMA IMCA
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Can I write a having statement for multiple columns? Here's my situation: I want to select duplicates from a table based off of 3 fields:Normally one would use HAVING COUNT(*) > 1however, I need to INSERT INTO my table based on duplicates of the 3 fields but also insert the key from the first table, ie:insert into #TempTable (key, field1, field2, field3)select key, field1, field2, field3 from Table1 order by field1, field2, field3 Having COUNT(field1)>1 and COUNT(field2)>1 and COUNT(field3)>1 My question is this: Will this having statement compare all three fields of this row to all three fields of the other rows, or does it do each column independantly?for example:row 1: a b g row 2: a c k row 3: j c k These rows aren't the same, even though there are 2 a's, I want it to look at the entire row, and not return all three of these.
Hey all, I was curious if this was possible... I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement
so here is an idea of what I'm talking about
SELECT COUNT(*) as HighStock FROM products WHERE qty > 100
now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...
is there a way to get all that in one result set? Something like
SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20
so then my result would be HighStock LowStock 50 10
insert into @DeNormalisedTable select '1,Davolio,Nancy' union all select '2,Fuller,Andrew' union all select '3,Leverling,Janet' union all select '4,Peacock,Margaret' union all select '5,Buchanan,Steven' union all select '6,Suyama,Michael' union all select '7,King,Robert' union all select '8,Callahan,Laura' union all select '9,Dodsworth,Anne'
select * from @DeNormalisedTable -- Comma Seperated Values
while exists (Select * from @DeNormalisedTable where data>@s) Begin Select @s=min(data) from @DeNormalisedTable where data>@s select @data=''''+replace(@s,',',''',''')+'''' insert into #NormalisedTable exec('select '+@data) End
select * from #NormalisedTable -- Data in Normalised Table
I have a table which has 4 columns A,B,C,D. it is somewhat like this:
A B C
S U 8 S U 10 S U 3 s V 14 S V 36 T U 25 T U 34 T U 9 T V 5 T V 1
This is the way i have in my database. I need to check which is the max in column C for values in Column A and Column B. First i need to start from column A. If it is S, then i need to see column B, if it is U then max[Column C] for only U & S. then when it becomes V, I need to get max [Column C] for only V & S. So on and so forth. So finally my output should be:
I have seen questions posted a number of times where someone wants to find the maximum or minimum value from a set of columns in a single row of a table.
This script demonstrates two methods for finding the maximum value in a row across a set of columns in the row when any or all of the columns are allowed to be null or equal.
Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery.
Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up. I think 5 or 6 columns may be about the limit of complexity of coding that you would want to take on. One advantage of this script is that you can use the simpler to code Method 1 to test the more complex code for the Method 2 if you choose to implement it as a CASE statement.
If you have another method you would like to contribute, feel free. Also, if anyone wants to post performance test results, that would be nice.
print 'Create table to hold test data' create table #t ( number int not null primary key clustered, Val1 int, Val2 int, Val3 int, Val4 int ) GO print 'Load test data' insert into #t select number, -- Generate random numbers -- with about 1/7th null case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end from -- Load one million rows of test data. -- Number table function here -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 dbo.F_TABLE_NUMBER_RANGE(1,1000000) go print 'Find rows that do not match for Method 1 and Method 2' select out1.*, out2.* from ( -- Method 1, using a subquery with a max select a.number, a.Val1, a.Val2, a.Val3, a.Val4, [Max_of_Val1_to_Val4] = ( select X1= max(bb.xx) from ( select xx = a.Val1 where a.Val1 is not null union all select xx = a.Val2 where a.Val2 is not null union all select xx = a.Val3 where a.Val3 is not null union all select xx = a.Val4 where a.Val4 is not null ) bb )
from #t a ) out1 join ( -- Method 2, using a case select a.number, a.Val1, a.Val2, a.Val3, a.Val4, [Max_of_Val1_to_Val4] = case when a.Val1 is not null and (a.Val1 >= a.Val2 or a.Val2 is null) and (a.Val1 >= a.Val3 or a.Val3 is null) and (a.Val1 >= a.Val4 or a.Val4 is null) then a.Val1 when a.Val2 is not null and (a.Val2 >= a.Val1 or a.Val1 is null) and (a.Val2 >= a.Val3 or a.Val3 is null) and (a.Val2 >= a.Val4 or a.Val4 is null) then a.Val2 when a.Val3 is not null and (a.Val3 >= a.Val1 or a.Val1 is null) and (a.Val3 >= a.Val2 or a.Val2 is null) and (a.Val3 >= a.Val4 or a.Val4 is null) then a.Val3 when a.Val4 is not null and (a.Val4 >= a.Val1 or a.Val1 is null) and (a.Val4 >= a.Val2 or a.Val2 is null) and (a.Val4 >= a.Val3 or a.Val3 is null) then a.Val4 else null end from #t a ) out2 on out1.number = out2.number where -- Look for results that do not match (out1.[Max_of_Val1_to_Val4] is null and out2.[Max_of_Val1_to_Val4] is not null) or (out1.[Max_of_Val1_to_Val4] is not null and out2.[Max_of_Val1_to_Val4] is null) or out1.[Max_of_Val1_to_Val4] <> out2.[Max_of_Val1_to_Val4] go print 'Find count of rows with different columns null' print 'Should have a rowcount of 16 to test all conditions' select Null_Column_Conditions = case when Val1 is null then 0 else 1000 end+ case when Val2 is null then 0 else 0100 end+ case when Val3 is null then 0 else 0010 end+ case when Val4 is null then 0 else 0001 end, count(*) from #t group by case when Val1 is null then 0 else 1000 end+ case when Val2 is null then 0 else 0100 end+ case when Val3 is null then 0 else 0010 end+ case when Val4 is null then 0 else 0001 end order by 1 go drop table #t
Results:
Create table to hold test data Load test data
(1000000 row(s) affected)
Find rows that do not match for Method 1 and Method 2
(0 row(s) affected)
Find count of rows with different columns null Should have a rowcount of 16 to test all conditions Null_Column_Conditions ---------------------- ----------- 0 395 1 2444 10 2560 11 14760 100 2400 101 14955 110 14843 111 90206 1000 2518 1001 14857 1010 14989 1011 90256 1100 15100 1101 89659 1110 89783 1111 540275
I've been working on this project for a few months now. This is my first SS2005 project. I'm using SSIS, SSAS, and SSRS. I deal with volume statistics on labratory tests. The three main identifying columns of a test are: Bill_Item_Id, Mnemonic, and [Test Name]. My main dimension table uses the primary key column: Bill_Item_ID. I recently learned that I can't use this column as a primary key because there are a couple tests that have the same Bill_Item_Id, but different Test Names. I know that I can't have multiple primary keys, but is there a way I could say that no two rows in this table will have the same Bill_Item_ID, Mnemonic, AND [Test Name]? Then my fact table would have all three columns and match on those three instead of just one. I realize this will take a lot of work to rework SSAS and SSIS to accomadate for this, but unfortunately no one brought this tidbit of information to my attention.
