|
Hi,
I have a project FamilyTree,it's like a Chain Relation Management.But i have no idea that how i will have to manage Database.Can anybody tell me some ideas for database.
VD
|
|
|
|
|
My first thought was this:
create table family_relatives (
relative_id integer primary key,
spouse references family_relatives,
mother references family_relatives,
father references family_relatives,
-- in case they don't know the exact birthdate
birthyear integer,
birthday date,
-- sadly, not everyone is still with us
deathyear integer,
first_names varchar(100) not null,
last_name varchar(100) not null,
sex char(1) check (sex in ('m','f')),
-- note the use of multi-column check constraints
check ( birthyear is not null or birthday is not null)
); but it lacks a bit. There's no control that your father is male and that your mother is female. And it doesn't keep track on ex-spouses. So one might want to drop the spouse column and add a partner table instead.
Keeping track on that your parents are born before you and not dead at your birth is another one. Or at least the mother, the father might actually be dead at your birth
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
SQL Servers hierarchy ID might be an interating tool to use for this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
How can i use this SQL Server hierarchy ID,please can you explain it.
Thanks
|
|
|
|
|
|
HierarchyID maitains a parent - child relationhip and may make structuring your data simpler. Do a bit of research in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a program that uses SqlBulkCopy to insert data into a staging table for processing. 5 different computers run the same .NET and database code and only one or a couple occasionally will receive the following error:
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."
The exact same data will import correctly for other users as well as myself. There are only valid dates in the fields and I verified that their local database tables match the column order I am expecting. The only thing I can think of is that a memory issue is coming into play here and the data is corrupting internally before import. Is there something else I can look at? I dont understand why the same code has different results.
CleaKO
"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
