|
|
i know you will disappoint me for having your email address for being friends together , but i ask for it and i know your respond , but i don't hate you i really loved to talk with you even in a qaurrel 
Human knowledge belongs to the world.
|
|
|
|
|
sure you can have my email address but i have not time to waste with you I have much much much more important things to do instead chating delay with you. here is my msn s_voca@hotmail.com
I Love T-SQL
|
|
|
|
|
as you told me before , i never tried to design this database in question because i don't have time to but i only solve problems in database through my mind these days i don't have access to computer with sql server installed that is why i posted to have some ideas but i never tried to design it ,that doesn't mean i failed because i failed to design it in my mind but not on the sql server , do you understand me ???? second i wanted to do it in the professional way.
Human knowledge belongs to the world.
|
|
|
|
|
thank god , i didn't fail in programming ,i just want to empower myself with database design , that is all but i never failed in doing any project . what about you blue ?
if you have a mail address we can chat together
Human knowledge belongs to the world.
|
|
|
|
|
|
Sounds simple. You need three tables here
Materials table : <br />
[Material ID]<br />
[Name]<br />
....<br />
[Dependent Material ID]<br />
<br />
(Dependent Material ID points recursively to Material ID. For example<br />
MaterialID Name DependentMaterialID<br />
1 a NULL<br />
2 b 1<br />
3 c 2<br />
<br />
would mean that material 2 is dependent on (or the child of) material 1 and material 3 depends on material 2.<br />
)<br />
<br />
Students Table:<br />
[Student ID]<br />
[Name]<br />
...<br />
<br />
StudentMaterial Table:<br />
[Student ID]<br />
[Material ID]<br />
[Semester ID]<br />
[Pass/Fail]
Given this table design you've to implement you business logic, namely checking if a student has passed or failed, assigning previous semester's materials to the next semester etc must be implemented in a stored procedure or within your application.
This may not be the best I can offer, but with the limited view of the problem you've given me, this is the easiest design that comes to my mind.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
The problem is your are giving too wide a scope for the question. These forums are more for people who have written code, get errors and unrespected results. You post THE CODE and then we review and try to help you fix the problem.
You basically asked us to hand-hold you through the problem. You don't learn unless you TRY. It doesn't sound like you did that; it sounds like you got confused and you don't want to solve the problem yourself.
Second, you asked this question before and this is BIG NO-NO. Please read the forum guidelines to get a better understanding on expected behavior and you will not see so much negativity.
|
|
|
|
|
If you post a question about database design that is so simple it looks like homework do not take offence when I suggest
Ashfield wrote: Have a try, and post any REAL problems and you will be helped.
Wish I hadn't bothered now......
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
I have a doubt,in sql server 2000 suppose i having a name senthil kumar it should convert to like this Senthil Kumar, S and K should be capital letter other alphabets should be small letter.Can anyone help me please?
Thanks & Regards
Senthil Kumar.T.S
|
|
|
|
|
this [^]will help you...
I Love T-SQL
|
|
|
|
|
How can i write a procedure to run daily at a given time.
for eg.
I need to execute a procedure at 10:00 AM,10:10 AM and 10:15 AM daily.
How can i schedule this in a stroed procedur.
Thankyou,
Yesu
|
|
|
|
|
Yes you can. Check this result[^], I am sure you gonna find what you are looking for.
I Love T-SQL
|
|
|
|
|
sorry..i didt got the requried matter..Please give a clear explanation
Thankyou,
Yesu
|
|
|
|
|
Create a stored procedure, then using SQL Server Agent(under Management, Sql Server Agent, Jobs) schedule the stored procedure to run at the desired time(s).
You cannot within a stored procedure specify what time it is to run at:
Unless you schedule a stored procedure to run every minute and within it check the time (otherwise know as BDDM - baaaaaaaaaaaaaaaad database design methodology!)
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Hi
Im pulling my hair out here trying to produce a count between dates. I have a form which is filled out online and the values put into a main table. One of the options to complete on the form is pulled from a look up table and you can select more than one option. These values go into another table with the reference number from the main referral to tie the 2 together. So i have:
.: Look up therapy table :.
DTID - int
Therapy - varchar
.: Therapy Table :.
DTRID - int
ReferralID - int
DTID - int
.: Main table :.
ReferralID - int
DateSubmitted - datetime
plus various other columns.
So i want to do a count on the amount of different therapys have been selected. I can get it to show ones that have been entered, but i want it to display 0 values to, which i cant seem to do.
Any help would be great.
Thanks 
|
|
|
|
|
Why don't you give a sample of what data is in each table, and then the SQL statement that you have tried, and how/why it isn't working. Because it is very hard to understand what your question is from what you have given us. My best guess is to try somthing like this:
SELECT therapy, count(*) FROM tblMain, tblTherapy, tblLookupTherapy WHERE tblMain.ReferralID=tblTherapy.ReferralID AND tblTherapy.DTID=tblLookupTherapy.DTID WHERE tblMain.DateSubmitted>4/24/2008 GROUP BY therapy
|
|
|
|
|
Thanks for your reply.
For example
In the lookup table - DrugTherapy
DTID(int) DrugTherapy(varchar)
1 Drug1
2 Drug2
3 Drug3
4 Drug4
Drug referrals made - ReferralDrugTherapy
DTRID(int) ReferralID(int) DTID(int)
1 1 1
2 1 1
3 2 4
4 2 3
5 3 2
6 4 2
7 5 1
Main Table CardiacReferrals
ReferralID(int) Ward(int) Comments(varchar) DateSubmitted(datetime)
1 1 test 01/01/2008
2 2 test1 22/01/2008
3 3 test2 01/02/2008
4 1 test543 21/02/2008
5 2 test45 01/02/2008
6 3 testtgdf 25/02/2008
7 9 testgdf 05/03/2008
8 5 testggd 11/03/2008
9 6 testfg 12/03/2008
10 4 testgd 07/04/2008
I have tried the following which works if there is values for the record, but if there isnt nothing is diplayed, what i want is for it to display 0 still if there is nothing there:
SELECT DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS Expr1<br />
FROM DrugTherapy INNER JOIN<br />
ReferralDrugTherapy ON DrugTherapy.DTID = ReferralDrugTherapy.DTID INNER JOIN<br />
CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID<br />
WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) OR<br />
(CardiacReferrals.DateSubmitted IS NULL)<br />
GROUP BY DrugTherapy.DrugTherapy
Any help much appreciated.
|
|
|
|
|
COUNT(isnull(ReferralDrugTherapy.DTID,0))
I Love T-SQL
|
|
|
|
|
Thanks, but that hasn't worked either im afraid
|
|
|
|
|
As I undestand when the table ReferralDrugTherapy have not records you got null value and instead null you want to display zero (0)?
I Love T-SQL
|
|
|
|
|
Yes, so for example, i want to enter 2 range dates in say 01/01/2008 - 01/05/2008 and then to pull back for example
Drug1 1
Drug2 3
Drug3 2
Drug4 5
At the moment if i put in a large date range it brings back the records because it has some, but if no record is found for say Drug3 it doesnt get pulled back i might get
Drug1 1
Drug4 5
But what i would like is
Drug1 1
Drug2 0
Drug3 0
Drug4 5
|
|
|
|
|
select DrugTherapy.dtid,DrugTherapy.drugtherapy,<br />
(select count (dtid) from ReferralDrugTherapy where dtid = DrugTherapy.dtid) <br />
from DrugTherapy
I Love T-SQL
|
|
|
|
|
Im slightly confused now, did you mean to add that bit to replace the top like so?
select DrugTherapy.dtid,DrugTherapy.drugtherapy,
(select count (dtid) from ReferralDrugTherapy where dtid = DrugTherapy.dtid)
from DrugTherapy INNER JOIN
ReferralDrugTherapy ON DrugTherapy.DTID = ReferralDrugTherapy.DTID INNER JOIN
CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID
WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) OR
(CardiacReferrals.DateSubmitted IS NULL)
GROUP BY DrugTherapy.DrugTherapy
Or something else? That query alone works, but how would i enter my date ranges still?
Many thanks
|
|
|
|
|
How about if you try to make that first join an outer join, like this:
SELECT DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS Expr1<br />
FROM DrugTherapy LEFT OUTER JOIN<br />
ReferralDrugTherapy ON DrugTherapy.DTID = ReferralDrugTherapy.DTID INNER JOIN<br />
CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID<br />
WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) OR<br />
(CardiacReferrals.DateSubmitted IS NULL)<br />
GROUP BY DrugTherapy.DrugTherapy
An inner join will join two tables together and return only the records where they match in the "ON" portion. A left outer join will join two tables and return the records where they match in the "ON" portion but will also return any records that didn't match in the table on the left of the "LEFT OUTER JOIN" statement. Be aware that this may bring back null values for fields that you reference in the table on the right side of the join.
If you want to do some more research on this, I'd suggest searching for the keywords "OUTER JOIN SQL" on google.
Hope this helps.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
