Help performing a count
-
select DrugTherapy.dtid,DrugTherapy.drugtherapy, (select count (dtid) from ReferralDrugTherapy where dtid = DrugTherapy.dtid) 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 :)
-
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 FROM DrugTherapy LEFT OUTER 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.DrugTherapyAn 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. -
How about if you try to make that first join an outer join, like this:
SELECT DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS Expr1 FROM DrugTherapy LEFT OUTER 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.DrugTherapyAn 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 :sigh: Ive been searching on this but not to much luck, theres plenty on joining the 2 tables but 3 isnt particularly good :confused:
-
That source didnt do the trick either :sigh: Ive been searching on this but not to much luck, theres plenty on joining the 2 tables but 3 isnt particularly good :confused:
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 (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 '2008-01-01' and '2008-02-21' GROUP BY dbo.ReferralDrugTherapy.DTID, dbo.DrugTherapy.DrugTherapy, dbo.DrugTherapy.DTID
I Love T-SQL
-
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 (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 '2008-01-01' and '2008-02-21' 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 (SELECT TOP 1 t1.DTID FROM DrugTherapy AS t1 WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS numbers FROM ReferralDrugTherapy FULL OUTER JOIN DrugTherapy ON ReferralDrugTherapy.DTID = DrugTherapy.DTID LEFT OUTER JOIN CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID, DrugTherapy.DrugTherapy, DrugTherapy.DTIDbut all that did was give me the same problem where if it doesnt find anything it brings back nothing instead of 0 values. Argh! :wtf: -
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 (SELECT TOP 1 t1.DTID FROM DrugTherapy AS t1 WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, DrugTherapy.DrugTherapy, COUNT(ReferralDrugTherapy.DTID) AS numbers FROM ReferralDrugTherapy FULL OUTER JOIN DrugTherapy ON ReferralDrugTherapy.DTID = DrugTherapy.DTID LEFT OUTER JOIN CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID, DrugTherapy.DrugTherapy, DrugTherapy.DTIDbut all that did was give me the same problem where if it doesnt find anything it brings back nothing instead of 0 values. Argh! :wtf: -
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
-
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 2 Drug2 0 3 Drug3 0 1 Drug1 5 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
-
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!
-
Many thanks, if i crack it in the mean time without blowing my head off ill let you know!
-
Many thanks, if i crack it in the mean time without blowing my head off ill let you know!
I guess this time I got the right solution
declare @fromdate as varchar(15) declare @todate as varchar(15) set @fromdate = '01/02/2008' set @todate = '05/02/2008' select ReferralDrugTherapy.dtid ,count(ReferralDrugTherapy.dtid) as countAll,DrugTherapy.DrugTherapy from ReferralDrugTherapy,CardiacReferrals,DrugTherapy where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy union all select dtid,0,DrugTherapy.DrugTherapy from DrugTherapy where dtid not in ( select ReferralDrugTherapy.dtid from ReferralDrugTherapy,CardiacReferrals,DrugTherapy where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy) order by ReferralDrugTherapy.dtid asc
I Love T-SQL
-
I guess this time I got the right solution
declare @fromdate as varchar(15) declare @todate as varchar(15) set @fromdate = '01/02/2008' set @todate = '05/02/2008' select ReferralDrugTherapy.dtid ,count(ReferralDrugTherapy.dtid) as countAll,DrugTherapy.DrugTherapy from ReferralDrugTherapy,CardiacReferrals,DrugTherapy where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy union all select dtid,0,DrugTherapy.DrugTherapy from DrugTherapy where dtid not in ( select ReferralDrugTherapy.dtid from ReferralDrugTherapy,CardiacReferrals,DrugTherapy where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy) order by ReferralDrugTherapy.dtid asc
I Love T-SQL
-
No need to apologies, your help has been much appreciated, your not the only person it seems to have stumped! :sigh:
AdamskiR wrote:
your help has been much appreciated
It's my pleasure trying to help others... Let me know if you find solution? If you have time to post explanation again then do it and after couple of hours I will try again to find solution. If you post explanation write data how are stored on table, and write result which you want to get. :)
I Love T-SQL