|
Very simple, like this:
ALTER PROCEDURE CountCases
(
@location varchar(100),
@symptomname varchar(100)
)
AS
SELECT count(visitentrysymptom.symptomID) "number"
FROM symptom inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in (select name from symptom where name= @symptomname)
and visitentrysymptom.date in (select date from visitentrysymptom where month(date) = 3 and year(date) = 2008)
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi. How can I store PDB files in my MS SQL Database? I want to have a compilation of those files for quick retrieval. There's only an image and xml file in the datatype list. What about other files?
|
|
|
|
|
Take a look at the Binary and VarBinary datatypes.
|
|
|
|
|
|
Hi!
I work with Sqlserver2005 and I have a table which I update, I need to insert the data before update in a table History.
is a way to do that.
Thank you in advance.
|
|
|
|
|
Insert into TableName Values('val1','val2'.....)
Update TableName set Col1='val1', Col2='val2'..... where ColName=ValCondition
For better answer,post more detailed questin.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks Blue_Boy;
sorry my english is bad !
I have two tables Table1 and historical_table1. With the same properties and the data are displayed in a gridview in editable mode . when the user modify a line of table1 I want that the first data (before update) will be inserted in historical_table1 automatically.
Thanks .
|
|
|
|
|
Use triggers[^].
While updating data in table Table1 (I sugggest you to rename this table 'Table1' and give it any meaningful name) execute trigger which do insert in historical_table1.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks a lot Blue_Boy;
I must document on triggers because its the first time I heard trigger in database, thank you very much.
if you know a course for beginners on the triggers thank you pass me the link.
|
|
|
|
|
No problem,I am glad to help you.
Check this link.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi All,
I would like to create a user when he logs in , i can hide all the tables that has been Created Before.All the database Table and Stored Procedures will be Hidden.
Can you please advice?
Many thanks
|
|
|
|
|
He cannot be logging directly into the database to do this, so you need to create a login page and use a known user name to see if he already exists. If not then you the need to create the login and user with the appropriate permissions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%'
('Smith (Hon. Member)John' will come from the user's input)
Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods).
I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces?
Hope I have explained this OK. Thanks in advance for any help.
|
|
|
|
|
Not ideal, but you can nest the replace statments for a quick fix:
replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','')
produces: SmithHonMemberJohn
otherwise you could always write a function to remove any non alphanumeric characters.
I don't know of any pattern matching within the replace statement though.
|
|
|
|
|
I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!
|
|
|
|
|
Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:
SELECT tblContacts.*
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Great
Thank you both for your help.
|
|
|
|
|
Hi,
This must be real simple, but I'm having a blackout.
I have two tables:
Names:
ID Name
1 John
2 Peter
3 Carl
4 Bernard
Nicknames:
ID NameID NickName
1 1 Bonny
2 1 Bobby
3 1 Jo
4 2 Pete
5 3 Bab
6 3 Foo
Now I have a search running, which looks for a certain string in both the nickname field as well as in the name field.
But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.
NameID NickNameID Name Nickname
4 null Bernard null
1 1 John Bonny
3 5 Bernard Bab
I fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution.
Does anybody have the correct solution to this issue?
|
|
|
|
|
You say that the select should return only one nickname. In your example, why did the select return Bonny for Bernard, not Bobby. Is there somekind of logic or is it simply random.
|
|
|
|
|
Random, I don't care which one he returns.
|
|
|
|
|
Okey, then you could use normal outer join between the tables and for example check that there's no greater nicknameid for the same name (using for example correlated not exists clause).
|
|
|
|
|
gnjunge wrote: I have a dirty subselect
I hope this T-SQL is not "dirty" but I couldn't make shorter,I hope too this T-SQL will help you.
select id,<br />
(select top 1 A.ID from nicknames as A where A.nameID = names.ID) as NicknameID,<br />
name,<br />
(select top 1 A.nickname from nicknames as A where A.nameID = names.ID) as Nickname<br />
from names<br />
where (names.name like 'b%')<br />
or names.id in (select C.nameid from nicknames as C where C.nickname like 'b%' ) </code><br />
<br />
Result is :<br />
<br />
<code>1 1 John Bonny<br />
3 5 Carl Bab<br />
4 NULL Bernard NULL
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This seems like it would be very slow, or isn't it? I guess in the end the names table would contain around 10.000 records,whereas the nicknames table would contain around 50.000
|
|
|
|