|
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.
|
|
|
|
|
That source didnt do the trick either 
Ive been searching on this but not to much luck, theres plenty on joining the 2 tables but 3 isnt particularly good 
|
|
|
|
|
I think I should have put a LEFT OUTER JOIN for both of the joins in your statement. Try that and see if it gets you anywhere.
|
|
|
|
|
I think I got it (just change date values in according to your format string, instead yyyy-MM-dd set your format)
SELECT CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN<br />
(SELECT TOP 1 t1.DTID<br />
FROM DrugTherapy AS t1<br />
WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, dbo.DrugTherapy.DrugTherapy, <br />
COUNT(dbo.ReferralDrugTherapy.DTID) AS numbers<br />
FROM dbo.ReferralDrugTherapy FULL OUTER JOIN<br />
dbo.DrugTherapy ON dbo.ReferralDrugTherapy.DTID = dbo.DrugTherapy.DTID <br />
<br />
left outer join CardiacReferrals on dbo.ReferralDrugTherapy.referralid = CardiacReferrals.referralid<br />
and CardiacReferrals.datesubmitted between '2008-01-01' and '2008-02-21' <br />
GROUP BY dbo.ReferralDrugTherapy.DTID, dbo.DrugTherapy.DrugTherapy, dbo.DrugTherapy.DTID
I Love T-SQL
|
|
|
|
|
Not quite, it doesnt seem to matter what date range you put it, it always pulls back all the results for each drug type. I tried changing it slightly so the dates in a where to
SELECT CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN<br />
(SELECT TOP 1 t1.DTID<br />
FROM DrugTherapy AS t1<br />
WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, DrugTherapy.DrugTherapy, <br />
COUNT(ReferralDrugTherapy.DTID) AS numbers<br />
FROM ReferralDrugTherapy FULL OUTER JOIN<br />
DrugTherapy ON ReferralDrugTherapy.DTID = DrugTherapy.DTID LEFT OUTER JOIN<br />
CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID<br />
WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to)<br />
GROUP BY ReferralDrugTherapy.DTID, DrugTherapy.DrugTherapy, DrugTherapy.DTID
but all that did was give me the same problem where if it doesnt find anything it brings back nothing instead of 0 values. Argh! 
|
|
|
|
|
write date values which you are giving?
I Love T-SQL
|
|
|
|
|
Well say if i put in 01/01/2008 - 01/05/2008 it pulls back records as there are ones within that range. (But also will not display those that dont have anything as 0, just wont display) But say i put in 01/05/2008 - 02/05/2008 then it pulls back nothing
|
|
|
|
|
between dates 01/05/2008 - 02/05/2008 you need this result :
DTID DrugTherapy CountNumbers<br />
2 Drug2 0<br />
3 Drug3 0<br />
1 Drug1 5<br />
4 Drug4 1
I Love T-SQL
|
|
|
|
|
Yes, so if there is none made, it to display a 0 so i can display reports on a page.
Thanks
|
|
|
|
|
change date format to dd/MM/yyyy from regoinal Settings on control panel, then
on date parameters ad values like this '01/01/2008' - '02/01/2008'
by that changes,query works perfeclty for me
here it is
SELECT CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN
(SELECT TOP 1 t1.DTID
FROM DrugTherapy AS t1
WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, dbo.DrugTherapy.DrugTherapy,
COUNT(dbo.ReferralDrugTherapy.DTID) AS numbers
FROM dbo.ReferralDrugTherapy FULL OUTER JOIN
dbo.DrugTherapy ON dbo.ReferralDrugTherapy.DTID = dbo.DrugTherapy.DTID
left outer join CardiacReferrals on dbo.ReferralDrugTherapy.referralid = CardiacReferrals.referralid
and CardiacReferrals.datesubmitted between '01/01/2008' and '01/05/2008'
GROUP BY dbo.ReferralDrugTherapy.DTID, dbo.DrugTherapy.DrugTherapy, dbo.DrugTherapy.DTID
I Love T-SQL
|
|
|
|
|
My date settings are already set to that.
The query that you have given me also just gives the total made, no matter what dates you enter you always get the total, it doesnt calculate any differences.
Thanks for your help so far!
|
|
|
|
|
I will lookup later again and I will notify you
I Love T-SQL
|
|
|
|
|
Many thanks, if i crack it in the mean time without blowing my head off ill let you know!
|
|
|
|
|
ok, if I crack it too I will tell you. On my mind i have solution but I must seriozly deal with it, coz until now I didn't have time to deal with it seriozly.
See u later.
I Love T-SQL
|
|
|
|
|
declare @fromdate as varchar(15)<br />
declare @todate as varchar(15)<br />
set @fromdate = '01/02/2008'<br />
set @todate = '05/02/2008'<br />
select ReferralDrugTherapy.dtid ,count(ReferralDrugTherapy.dtid) as countAll,DrugTherapy.DrugTherapy <br />
from ReferralDrugTherapy,CardiacReferrals,DrugTherapy<br />
where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted <br />
between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid<br />
group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy<br />
union all<br />
select dtid,0,DrugTherapy.DrugTherapy from DrugTherapy where dtid not in (<br />
select ReferralDrugTherapy.dtid from ReferralDrugTherapy,CardiacReferrals,DrugTherapy<br />
where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted <br />
between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid<br />
group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy)<br />
order by ReferralDrugTherapy.dtid asc
I Love T-SQL
|
|
|
|
|
Hi and thanks.
Afraid not though....
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
