|
Storing the patient details in the same table as the test data would be one of the most basic errors you can make. Can I get this across better - DO NOT STORE THE PATIENT DETAILS IN THE TESTING TABLE.
I will guarantee you this, 2 things, as Manas pointed out your client WILL ask for such a report and 2nd anyone looking at your structure will KNOW that you have no idea about database design.
Please get a beginners book on data design NOW, find out what normalisation is, get to know what a relational DB actually does and then get stuck in.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a seperate table for patient data. I am not planning on adding this to that table heaven forbid. I have another table that deals with testing and such. I am debating on whether or not to add another table for each test.
|
|
|
|
|
Heaven forbid indeed, it sounds like you already know what a nightmare that would be, sorry foe getting the wrong handle on the issue.
I would have only 1 table for tests and either have a test type if they vary or a sequence/date if they are repeated. You can alwys use a dataview to filter and sort the data prior to binding it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The most important part of your application is that you store data properly. That is the whole point of writing this. If you stuff your data model up to make it a little easier for you to write some UI, you will definitely regret it later on when you have to work around these bad decisions when adding reporting, etc. You will also expose yourself to horrible bugs - like "Patient changed name, can't find any blood pressure records".
Get your domain model on paper first. Then get your database design right. Then get your data layer mapped to it properly. Then worry about the UI.
|
|
|
|
|
Excellent answer. As a rule of thumb I would say that always use at least third normal form in db design when creating oltp system.
Mika
|
|
|
|
|
Hi guys,
My weak T-SQL skills have let me down once again 
I have 2 tables, ACCOUNTS and ADDRESSES within a database that I need to return data from.
I each account in the ACCOUNTS table can have zero or more addresses and I need to select from the ADDRESSES TABLE two specific addresses, if available.
Before I carry on, I should point out I indent to use a Stored Procedure.
So I need to SELECT an account from ACCOUNTS and the Primary address from ADDRESSES (signified by column IsPrimary = True) and the Invoice Address from ADDRESSES (where the Type = 'INVOICE'). The result should be one row per account and should always return the account even if there is no Primary or Invoice address.
I am eternally grateful for any assistance, and examples on how to achieve the desired result.
Thanks
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Use LEFT OUTER JOIN; something like:
SELECT Accounts.ID
,Accounts.Name
,Paddress.whatever
,Iaddress.whatever
FROM Accounts
LEFT OUTER JOIN Addesses Paddress
ON Accounts.Id=Paddress.AccountId
AND Paddress.IsPrimary=1
LEFT OUTER JOIN Addesses Iaddress
ON Accounts.Id=Iaddress.AccountId
AND Iaddress.Type='Invoice'
|
|
|
|
|
I knew it would be simple, I am learning, I promise.
Thanks for the quick response.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I have now inspected the sample SQL code you provided and have identified where I was failing. In short the AND clause of each of the LEFT OUTER JOIN. I was originally attempting to use a WHERE clause at the end of the SELECT.
Thanks again
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I expect either would do, but I prefer to put it in ON in the (perhaps mistaken) belief that it is more efficient (in some cases).
|
|
|
|
|
They WHERE option would return a different result to the cluase on the JOIN. If I was filter on the WHERE then a Primary AND Invoice address would need to be present for a result to be returned. Your example will return a result even is there is not a Primary or Invoice address.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
hi all
im converting a load of queries from MySQL to MSSQL and have run into a problem and was wondering if anyone had any ideas.....
I currently have:
SELECT 'PI' AS transaction_type, t1.supplier_code, t1.nominal_code, '01-01-2006' AS my_dispatch_date, t2.name AS resourcename, t2.reference, '1.20' AS royalty_amount,
t3.code, '1.21' AS tax_amount, '1' AS blank_field_2, '_' AS blank_field_3, '_' AS blank_field_4
FROM order_line AS t4 INNER JOIN
resource AS t2 ON t4.resource_id = t2.resource_id INNER JOIN
supplier_codes AS t1 ON t2.reference = t1.reference AND t4.dispatch_date >= t1.payment_start_date AND t4.dispatch_date <= t1.payment_end_date INNER JOIN
partners AS t5 ON t1.partner_id = t5.partner_id INNER JOIN
tax_codes AS t3 ON t5.tax_code = t3.id INNER JOIN
partner_type AS t6 ON t1.partner_type_id = t6.partner_type_id
WHERE (t4.status = 1) AND (t4.dm <> 'ZX') AND (t4.dispatch_date >= @from) AND (t4.dispatch_date <= @to)
ORDER BY t4.dispatch_date, t2.reference
but i need to group by t1.reference, t1.supplier_code but obviously it keeps telling me I can't do that, my brains a bit fried atm, if anyones got any great ideas it would be really helpful
ta
tim
|
|
|
|
|
To use group by, you need an aggregate... sum(...) or count(...) or avg(...), etc.
Tim
|
|
|
|
|
problem is...I dont want to do anything with them apart from get the values in the same way you do in MySQL, I just want to get rid of the duplicates that get pulled based on certain criteria.
this is what I'm converting:
SELECT
'PI' as transaction_type,
supplier_codes.supplier_code,
supplier_codes.nominal_code,
COUNT(o.orderline_id) as sale_volume,
IF ( EXTRACT(MONTH FROM STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w')) = MONTH(dispatch_date),
DATE_FORMAT(STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w'),'%d/%m/%Y'),
DATE_FORMAT(STR_TO_DATE(CONCAT('01/',EXTRACT(MONTH FROM dispatch_date),'/',YEAR(dispatch_date)),'%d/%m/%Y'),'%d/%m/%Y')) as my_dispatch_date,
resource.name as resourcename,
resource.reference,
SUM(supplier_codes.license_fee) as royalty_amount,
tax_codes.code,
ROUND(((SUM(supplier_codes.license_fee) / 100) * tax_codes.multiplier),2) as tax_amount,
"1" as blank_field_2,
"_" as blank_field_3,
"_" as blank_field_4
FROM order_line o
LEFT JOIN resource ON o.resource_id = resource.resource_id
LEFT JOIN supplier_codes ON resource.reference = supplier_codes.reference
LEFT JOIN partners ON supplier_codes.partner_id = partners.partner_id
LEFT JOIN partner_type ON supplier_codes.partner_type_id = partner_type.partner_type_id
LEFT JOIN tax_codes ON partners.tax_code = tax_codes.id
WHERE supplier_codes.payment_start_date <= dispatch_date
AND supplier_codes.payment_end_date >= dispatch_date
AND o.status = 1
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') >= '$from'
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') < '$to'
AND o.dm !='ZX'
GROUP BY reference, supplier_code, my_dispatch_date
ORDER BY dispatch_date, reference
dont worry about the difference in some of the vars in it, its only the group by thats doing my head in
|
|
|
|
|
Ok.... in SQLServer, a group by is used if there are any aggregate fields (sum, count, etc).
In your original query (from MySQL), there is a COUNT and 2 SUM aggregate fields.
If you want the sales volumne (COUNT), royalty amount (SUM) and tax amount (SUM) in your SQLServer query, GROUP BY any non-aggregate fields:
GROUP BY supplier_codes.supplier_code, supplier_codes.nominal_code, resource.name, resource.reference, tax_codes.code
The GROUP BY clause must go after the WHERE and before the ORDER BY
If you are currently getting duplicate records, change the SELECT to SELECT DISTINCT.
Hope that helps...
Tim
|
|
|
|
|
cheers tim
that helped a lot,i've been looking at this (migration overall) for about a week now and i can't really see the trees for the woods anymore, my brains withered.
ta
tim
|
|
|
|
|
Hi,
I am looking to return all the rows where the OldReferenceNumber field is equal to the value of the parameter, currently it returns nothing, there are a couple of fields that are null:
DECLARE @OldReferenceNumber VARCHAR(50);
SET @OldReferenceNumber = NULL;
SELECT
OldReferenceNumber
FROM
Product
WHERE
OldReferenceNumber = @OldReferenceNumber;
Please can some one help me?
Thanks
Brendan
|
|
|
|
|
You cannot reference nulls with =
WHERE
coalesce(OldReferenceNumber,'') = coalesce(@OldReferenceNumber,'')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks, this works just fine. I just need to modify it a little bit. Lets say the colum does not contain a NULL value, and maybe some text like Old Reference Number. I need this WHERE clause modified so that if the user types in old ref that it still returns the row. Normally I would use a LIKE with a '%' but I can not seem to get it to work here.
Thanks
Brendan
|
|
|
|
|
SET @OldReferenceNumber = NULL;
OldReferenceNumber = @OldReferenceNumber;
That won't work
|
|
|
|
|
SET @OldReferenceNumber = NULL;
OldReferenceNumber = @OldReferenceNumber;
That won't work, you need
OldReferenceNumber IS NULL
Or some "not exist" value
SET @OldReferenceNumber = -1;
ISNULL(OldReferenceNumber,-1) = @OldReferenceNumber;
|
|
|
|
|
Or as I said originally,
WHERE coalesce(OldReferenceNumber,'') = coalesce(@OldReferenceNumber,'')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yeah, but that's ugly. 
|
|
|
|
|
Ugly but correct. Coalesce is the new ISNULL 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
