|
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
|
|
|
|
|
hi everyone,
I have 2 tables like StudentDetail and TuitionLevel
StudentDetail
id Name Surname StudentUniqueId Subject
1 sam smith G1234U890 E
2 sam smith G1234U890 M
3 Paul Day R4321B009 E
E as English
M as Maths
TuitionLevel
id PreLevel PostLevel
1 3 4
2 5 4
3 2 2
I want to display result like this
Name MathsPre MathsPost EngPre EngPost
sam 3 4 5 4
Paul 2 2
I have done in the following way but this is not what i need.
SELECT [Name],Surname,StudentUniqueId,
Case when motot.Subject = 'E' Then 'English' Else 'Maths' end AS Subject,
Pre,
IsNull(Post,'') as Post
FROM StudentDetail sd
INNER JOIN TuitionLevel TL on TL.id =sd.id
ORDER BY StudentUniqueId
any help would be much appreciated.
thanks in adv.
modified on Wednesday, April 7, 2010 11:16 AM
|
|
|
|
|
Learner520 wrote: INNER JOIN TuitionLevel TL on TL.id =sd.id
What does the tuitionID have to do with the studentID? Also, what database are you communicating with?
If my answer has helped you, one of my articles may also be a help. Also remember that your best friend's name is google.
|
|
|
|
|
This article may be of use to you, assuming you are using SQL 2005 or later Pivot two or more columns in SQL Server 2005[^]
And yes that join is suspicious. Actually your data structure is screwed, not that you have sma smith stored twice, this is wrong. Suggested restructure:
Student
StudentID
StudentName
Subject
SubjectID
Subject
TuitionLevels
LevelID
Description
LinkTable
StudentID
SubjectID
LevelID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft Holmes,
Thanks for your reply. It was my mistake I didnot normaliz data but I did on purpose but it did not work.
Now I have normalized it and I'm trying to figure out how to display it through pivot.
But still need your help. Any suggestion or hint how to do that would be much appreciated.
thanks
TuitionLevel
tuitionlevelid tuitionleveldesc
1 1a
2 1b
3 1c
4 1
Student
UPN StudentName
A1234 Alan
A1233 Sam
A1232 Carol
Subject
Subjectid SubjectDesc
1 E
2 M
UPN subjid pretui_id posttui_id
A1234 1 1 2
A1234 2 3 4
A1233 1 7 3
A1233 2 1 4
A1232 1 5 6
A1232 2 6 6
output:
UPN StudentName PreMaths postMaths preEng PostEng
A1234 Alan 1a 1b 1c 1
Any help would be much appreciated.
thanks
|
|
|
|
|
If you work through the article I basically do it in 2 steps. Build up the query that will service the pivot.
Get your student details, the course details and your performance information into a normal result set, do not get any extra data like ID information that is not needed.
Get a distinct list of the course names (either from the result set or all the courses), these will be your pivoted columns.
I would do a hard coded mini query to get a feel for it, once you get a result by hard coding the pivot columns you can turn it into dynamic sql. To debug the dynamic sql print the text and try and run that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
