Help performing a count

AdamskiR

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.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! :wtf:

Blue_Boy

write date values which you are giving?

I Love T-SQL

AdamskiR

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

Blue_Boy

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

AdamskiR

Yes, so if there is none made, it to display a 0 so i can display reports on a page. Thanks

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.