|
Bulk copy is fragile, what we do is ELT, Extract, Load then Transform, change all your staging fields to varchar, use bulk copy to shove the data into the table no matter what the format.
Then use a stored proc to transform the data, you can control and manage a proc better than any load process. This same argument applies to SSIS, get the data in then clean it up. We have found this solution both robust and fast, IMHO transforms during load are a disaster.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using this code to determine last patient pulse in my vital_signs table.
-- determine pusle;
SET @pulse = (SELECT TOP 1 pulse FROM vital_signs WHERE file_no = @file_no AND visit_id = @visit_id AND pulse IS NOT NULL ORDER BY vital_id DESC)
IF (@is_child = 'True' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MAX')))
BEGIN
SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'M' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MAX')))
BEGIN
SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'F' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MAX')))
BEGIN
SET @normal_pulse = 'False'
END
It was so easy as you can see 
now I want to do the same for the temprature
but the temperature is more than one field unlike the pulse.. I have the following fields:
temperature_oral
temperature_axillary
temperature_rectal
temperature_tympanic
temperature_skin
How can I do the same code above to check the last temp taken from any field. Only one temp will be taken at at time so i need to get that value and the will compare using the above code...
|
|
|
|
|
The schema you have will lead to trouble. Have the temperature table as a "transaction" sort of table. Then just query for the most recent. Do the same for the pulse, blood pressure, etc.
In fact, probably have one table for all the vital signs, one record per value:
ID, PatientID, TakerID, TimeStamp, ReadingType, Value
|
|
|
|
|
but changing the table structure now is a major work! any solution with the current structure?
|
|
|
|
|
No, fix it now before it becomes really difficult.
You can always create views to mimic the current schema.
|
|
|
|
|
You are now starting to live with the consequences of lousy data design. This query would be dead simple with a correct data structure.
jrahma wrote: but changing the table structure now is a major work
If you think it will get any better or take less effort in the future you are insane. The work arounds to service rotten design will mount the longer you leave it, then you need layer more crap on the workarounds, shortly you have a completely unworkable database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm a bit confused. Is there a timestamp field in your table ?
If yes, then you want to find the MAX(timestamp) record and find the value of the temperature.
Maybe something with a case statement. (Concept only, syntax is close but not correct)
SELECT TempTaken =
CASE Temperature_oral
WHEN >0 THEN Temperature_Oral
Else
case Temperature_auxillary
When > 0 Then Temperature_Auxillary
End
End
FROM Vital_signs
Repeat the series of case statements for each of the temperature methods.
Good luck. 
|
|
|
|
|
I have query like below
declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)
select @str_CustomerID= scope_identity()
after execution it returns null in my parameter
i want to get value of identity how can i do that
the main issue over here is "IMDECONP38"- server name that i used if i remove this i can able to get the value of identity in my parameter
|
|
|
|
|
Are you running this script connected to server "IMDECONP38" or is that a linked server from which you are running the query?
|
|
|
|
|
|
scope_identity and @@Identity only works on the server its executed on, so it won't return a value from a linked server.
You would need to create a stored proc on the linked server that returns the scope_identity value. You could then call that from your query.
See here for more information:
http://msdn.microsoft.com/en-us/library/ms187342.aspx
|
|
|
|
|
My company has some centers on a VPN connection and I am wondering if there is a way to slim the dataset... I am already selecting minimal columns and rows.
The query gets passed to the server rather quickly but when the data is being passed back it is rather slow.
Any help appreciated.
EDIT~~~this is a simple query with no joins and a single where and the column in the where is indexed.
Humble Programmer
modified on Friday, April 9, 2010 5:15 PM
|
|
|
|
|
If you're only requesting the data you need, there's not much left to play with.
Turn protocol compression on if you don't already and see if that helps. There are also some variables[^] you can set that might give better performance. net_buffer_length is probably the most important.
|
|
|
|
|
I want to join these 2 tables Products_Table & Interest_Table.
When somebody is logged in they get a flag next to the product that they said they where interested in. I request the cookie to get the pwl variable in the Interest_Table.
I’m looking for the sql statement that works in access..
Products_Table
Prod_ID Prod_Name
1 Dog
2 Cat
3 Horse
4 Truck
5 Car
Interest_Table
ID Pwl Flag
1 Bob Flagged
1 Tom Flagged
4 Bob Flagged
5 Tom Flagged
When Tom is logged in he will get this list.
1 Dog Flagged
2 Cat
3 Horse
4 Truck
5 Car Flagged
When Bob is logged in he will get this list.
1 Dog Flagged
2 Cat
3 Horse
4 Truck Flagged
5 Car
When nobody is logged in they get this list.
1 Dog
2 Cat
3 Horse
4 Truck
5 Car
|
|
|
|
|
Why do you insist that the database does something that the UI has the information for! The database does not know what should be flagged, you get it from a cookie. So when you have the datatable back from the database you can then add the column and populate the flagged records.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hy,
i have a requirement in which i have update a coulmn based on date time Cloumn(Column A)..Its like the column(Column B) would get updated after 9 hours of the date time column(A).
Ex if the datetime column value(A) is '3/15/2010 2:32:27 PM'
then i have to check wether the diifeereence between current time and the column time is 9 hours or not.
What query should i write in oreder to do this
Thanks & Regards,
raghvendra panda
|
|
|
|
|
If you are using SQL Server, you can do:
select datediff(hh, ColumnA, getDate()) from TableX
This will give you the date difference in hours.
|
|
|
|
|
Hey for help check the link below......
Link
|
|
|
|
|
I want to read from DB using multiThread, but I do not konw how to prevent from reading data repeatly?
I use vc++ to process this task, can anyone tell me how to realise it?
|
|
|
|
|
I am not quite sure about understanding your post. Are you trying to avoid different threads reading same data again and again?
If yes, then there is not much you should do at the DB end. This should be handled in the code.
|
|
|
|
|
Please read the forum guidelines. Choose one board for your question.
|
|
|
|
|
Hi All,
My database for student installment is like this.
ID StudentID Month Year Amount
1 001 4 2009 1000
2 001 5 2009 1000
3 001 6 2009 1000
4 001 7 2009 1000
5 001 8 2009 1000
6 001 9 2009 1000
7 001 10 2009 1000
Now i want the query to display it like
StudentID Apr2009 May2009 Jun2009 July2009
001 1000 1000 1000 1000
Any IDEA???
Plz share ur experiences.
Thansk
Yogesh Agarwal
|
|
|
|
|
If you using Sql Server 2005/8, have a look at the PIVOT command.
|
|
|
|
|
|
Check out the following link
PIVOT
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
