if statement question... 1

[burnside]

if i want to sum if TST.stateid = 1 how would i put it into this clause...

'this gets forward month deals
&"SUM( IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countfwdealuse "_

 

[TonyGroves]

Do you mean that TST is one of the tables in the join and you want as an extra field in the result set a count of the number of records where TST.stateid = 1? If so, you could use:
[tt]
SUM(TST.stateid=1) AS sumstateid
[/tt]or[tt]
SUM(IF(TST.stateid=1,1,0)) AS sumstateid
[/tt]or[tt]
SUM(CASE WHEN TST.stateid=1 THEN 1 ELSE 0 END)
AS sumstateid
[/tt]

 

[burnside]

hi tony - thanks for the sql IF reference
but i want to sum on TST.stateid =1
AND
if tbl_deal.dealdeldate is within current month-
&"SUM( IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countfwdealuse "_

have tried

&"SUM( IF (TST.stateid=1,1,0( 100*Year(dealdeldate)+Month(dealdeldate) = 100*Year(curDate())+Month(curDate()), 1, 0 ))) AS countcurdealuse, "_

but cant get it right

 

[r937]

select sum(case when TST.stateid=1
                 and 100*Year(dealdeldate)
                       +Month(dealdeldate) 
                   = 100*Year(curDate())
                       +Month(curDate())
                then 1 else 0 end) 
        as countcurdealuse
  from ...

r937.com | rudy.ca

 

[burnside]

cheers rudy