Help performing a count

[Blue_Boy]

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

[AdamskiR]

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!

[Blue_Boy]

I will lookup later again and I will notify you

---

I Love T-SQL

[AdamskiR]

Many thanks, if i crack it in the mean time without blowing my head off ill let you know!

[Blue_Boy]

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

[Blue_Boy]

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

[AdamskiR]

Hi and thanks. Afraid not though....

[Blue_Boy]

:( Sorry for my unusefull answers.

---

I Love T-SQL

[AdamskiR]

No need to apologies, your help has been much appreciated, your not the only person it seems to have stumped! :sigh:

[Blue_Boy]

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]

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.DTID Many many thanks to everyone :-\

[Blue_Boy]

Cool,Great and Good News... :-D

---

I Love T-SQL Don't torture yourself,let the life to do it for you.