I gave only the select statement and the join that is causing the problem,

If you want I can give you the whole code also

Here you go:

SELECT DISTINCT

a.AppID

, CONVERT(varchar(10), a.DateAppRec, 101) AS DateAppRec

, ch.NameLong AS Dealer

, CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE channelid = ch.assignedbranch)...

Can somebody help me with the case statement?

So I figured it out the select statement that is causing the problem:

SELECT

CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE ch.channelid = ch.assignedbranch) END AS...

Thanks Scott, I appreciate your help, but not passing the year, wouldn't it do a count of all the years for that month, for example in future if I do:

EXEC...

So I guess using only this would be a better idea:

(

EXEC Applications @Month = 10

this would give me records for October for the current year...

somehow this is also not giving me accurate results for September, 2015

ALTER PROC Application

(

@Month AS DATETIME

)

AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0))

AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month) +...

Thanks Scott,

What I am trying to do is call it in report where user can select the month and year from the drop down menu, If I just use the...

Scott, would the following work, its kind of giving me the correct results:

ALTER PROC Applications

(

@Month AS DATETIME,

@Year AS DATETIME

)

AS

BEGIN

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...

Not just the month, but also by the year.

thanks

Figured it out, just had to add DESC

Thanks Jack it works !!!!!!!!!!

Also what I need to do with State, now I have to convert state_id into state '03' into 'GA'

Thanks Jack, I already had written 50 case statements before your post 😉 your method also works,

Now going on to the second issue, I also want to add the records...

Thanks Michael, I have standard edition..that might be it.

After all the hardwork, I realize I don't have the 'Data driven subscription' option. I only see 'new subscription'

Would anyone know what the problem might be/ or my edition does...