|
We use #2, the dynamic connection string and pass in the server as a parameter. Works fine for us, the developer is so used to the process of changing servers when modifying a report that it is now automatic. A total PITA for the rest of us who rarely have to get into the reports. B/C as report can run against any of 4-5 servers this work perfectly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a table with three columns: EmployeeID, Target, Date
Every employee is assigned a target (amount in dollars) to try to hit on every date. How do I get the latest target each employee has been assigned ordered by the employeeID. I tried the following but it is not accurate:
SELECT EmployeeID, MAX(Date), Target
FROM Target
GROUP BY EmployeeID, Target
ORDER BY EmployeeID
This query does not only return the latest target date.
|
|
|
|
|
CodingYoshi wrote: How do I get the latest target each employee
You can either add HAVING clause after GROUP BY or eliminate previous dates like:
SELECT EmployeeID, Date, Target
FROM Target
WHERE Date = (SELECT MAX(DATE) FROM Target)
ORDER BY EmployeeID
The previous example is assuming that the date is the same for all. If it's not, use correlated subquery like
...
WHERE Date = (SELECT MAX(DATE) FROM Target t2 WHERE t2.EmployeeID = target.EmployeeID)
...
|
|
|
|
|
I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"<br />
What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:
<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" <br />
modified on Wednesday, December 3, 2008 11:31 AM
|
|
|
|
|
You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I'm not sure of the exact syntax for VBA) - something like :
Public Function MakeSqlSafe(strData) as string
Return strData.Replace("'", "''")
end function
and then pass each one of your inputs through the function, e.g.
'" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "'
Hope this helps.
|
|
|
|
|
Thank you, liqz for your assistance.
The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub".
The code is as follows in VBA:
<br />
Public Function MakeSqlSafe(strData) As String<br />
strNewStrData = Replace(strData, "'", "''")<br />
Return<br />
End Function<br />
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND IsNull([ALT_GROUPING]) " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'"<br />
Any assistance in resolving this item would be greatly appreciated!!!
|
|
|
|
|
Ah, try changing the function to :
Function MakeSqlsafe(strData)
MakeSqlSafe = Replace(strData, "'", "''")
end Function
|
|
|
|
|
It worked!!! Woo Hoo!!!
Thank you liqz, for your assistance in resolving this item!!!
|
|
|
|
|
Although replacing single apostrophe with double apostrophes will correct your problem, I think you should use parameters instead. Concatenating literal strings leaves you open to sql injections. Also using parameters gives a performance advantage.
|
|
|
|
|
Hi, below is my database schema and some of my sample data
patient(patientID,name,email, address)
visitentry(visitentryID,medicalnotes, patientID)
symptom(symptomID,name,description)
visitentrysymptom(symptomentryID, patientID, symptomID,date)
Patient Table
1 || myname || email@email.com || blk 123 tampines
2 || myname2 || another@email.com || blk 543 pasir ris
Visit Entry Table
1 || high fever 40degree || 1
Symptom Table
1 || fever || feeling warm in body
2 || bleeding || red substance
3 || flu || sneezing with mucas
Visit Entry Symptom Table
1 || 1 || 2 || 02/12/2008
2 || 1 || 3 || 02/12/2008
Okay so what i wan to do now is to Count the number of fever, bleeding and flu base on the patient address (using the LIKE to extract TAMPINES) and the current month
I came up with this statement
SELECT count(visitentrysymptom.symptomID)
FROM symptom inner join visitentrysymptom
on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
But my statement does not check the patient address(TAMPINES) and the current month
|
|
|
|
|
SELECT count(visitentrysymptom.symptomID)
FROM symptom
inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
and visitentrysymptom.patientID in (select patientID from patient where address like '%TAMPINES%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
THANKS ALOT!!!
I have modified ur query to check the date as well
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 date= '03/12/2008 22:39:09')
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
How do i make the date to check only the month(current month)
modified on Wednesday, December 3, 2008 10:50 AM
|
|
|
|
|
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
|
|
|
|