Help performing a count
-
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
-
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
We got there in the end!
SELECT d.DrugTherapy, ISNULL(c.count, 0) AS count FROM DrugTherapy AS d LEFT OUTER JOIN (SELECT COUNT(ReferralDrugTherapy.DTRID) AS count, ReferralDrugTherapy.DTID FROM CardiacReferrals INNER JOIN ReferralDrugTherapy ON CardiacReferrals.ReferralID = ReferralDrugTherapy.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID) AS c ON c.DTID = d.DTIDMany many thanks to everyone :-\ -
We got there in the end!
SELECT d.DrugTherapy, ISNULL(c.count, 0) AS count FROM DrugTherapy AS d LEFT OUTER JOIN (SELECT COUNT(ReferralDrugTherapy.DTRID) AS count, ReferralDrugTherapy.DTID FROM CardiacReferrals INNER JOIN ReferralDrugTherapy ON CardiacReferrals.ReferralID = ReferralDrugTherapy.ReferralID WHERE (CardiacReferrals.DateSubmitted BETWEEN @from AND @to) GROUP BY ReferralDrugTherapy.DTID) AS c ON c.DTID = d.DTIDMany many thanks to everyone :-\