Help performing a count
-
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
-
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 :-\