Convert(smallint,AccountInfo.NTP)>Convert(smallint, Y.Callnum)

[christer102]

I have the above statement in a where clause, but it doesn't work. The accountinfo.NTP is an INT value and has the value of 4, and Y.CALLNUM is a calculated value (COUNT) and has the value of 1. So if 4 is greater than 1 how come it doesn't recognize it?? I tried everything. It works if I remove the above statement so nothing wrong with the reqest of the query.

insert into OpenItems
(ACCREC, datefield, description, CREATEDBY, NOTESCATEGORY, FOLLOWUPDATE, OPENCLOSED, LATESTUPDDATE, ASSIGNEDTO, ATM)
(SELECT accountinfo.accrec, getdate(), '30 days until renewal', 'SYS' AS userval, 'Auto' AS TYPE,
k.newfollowupdate, 'open' AS OPCLOSED, GETDATE() AS date2, (case when AccountInfo.CLIENTCONTACT=1 then k.csrval else k.pr end) as assignedto, '1' AS ATM
FROM (SELECT MAX(CO_AMT) AS maxcom, LEFT(POL_IDX, 7) AS rec2, DATEDIFF([month], GETDATE(), EXP2) AS Expr1, EXP2, dateadd(month, -1, EXP2) AS newfollowupdate, PR, min(csr) as CSRVAL
FROM POLICY
WHERE (LEFT(POL_IDX, 7) = '9PERGR0') AND (DATEDIFF([month], GETDATE(), EXP2) > 2)
GROUP BY LEFT(POL_IDX, 7), EXP2, pr) k RIGHT OUTER JOIN
AccountInfo ON k.rec2 = AccountInfo.ACCREC LEFT OUTER JOIN
(SELECT accrec, MAX(taskcompldate) AS MCOMPLDATE
FROM OpenItems
WHERE ATM = 1 AND openclosed = 'closed'
GROUP BY ACCREC) b ON AccountInfo.ACCREC = b.accrec LEFT OUTER JOIN
(SELECT OpenItems.ACCREC, COUNT(OpenItems.ACCREC) AS Cnum
FROM OpenItems
WHERE (OpenItems.ACCREC = '9PERGR0') AND (OpenItems.ATM = 1) AND (datediff(month, getdate(), OpenItems.FOLLOWUPDATE) >= 0)
GROUP BY OpenItems.ACCREC) A ON AccountInfo.ACCREC = A.ACCREC LEFT OUTER JOIN
(SELECT OpenItems.ACCREC, Convert(int, COUNT(OpenItems.ACCREC)) AS Callnum
FROM OpenItems
WHERE (OpenItems.ACCREC = '9PERGR0') AND (OpenItems.ATM is not null) AND (datediff(month, getdate(), OpenItems.FOLLOWUPDATE) >= 0)
GROUP BY OpenItems.ACCREC) Y ON AccountInfo.ACCREC = A.ACCREC COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (AccountInfo.ACCREC = '9PERGR0') and AccountInfo.NTP>0 and (A.Cnum = 0 OR
A.Cnum IS NULL) AND Convert(smallint,AccountInfo.NTP)>Convert(smallint, Y.Callnum) and datediff(month,getdate(),k.newfollowupdate)>1 and datediff(month,b.MCOMPLDATE,k.newfollowupdate)>1

GROUP BY AccountInfo.NTP, A.Cnum, Y.Callnum, AccountInfo.ACCREC, b.MCOMPLDATE, k.EXP2, k.pr, k.newfollowupdate, k.csrval, AccountInfo.CLIENTCONTACT

)

 

[LNBruno]

You say that:

accountinfo.NTP is an INT value and has the value of 4, and Y.CALLNUM is a calculated value (COUNT) and has the value of 1

In your code, your count of CALLNUM is converted to an INT:

Convert(int, COUNT(OpenItems.ACCREC)) AS Callnum

So really, there is no reason for you to attempt converting these two values into a smallint data type in your WHERE clause. Especially when there is so much more optimization that needs to be done here....

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[LNBruno]

At a minimum, use formatting to make it more readable!

INSERT INTO OpenItems
   (
   ACCREC
   , datefield
   , description
   , CREATEDBY
   , NOTESCATEGORY
   , FOLLOWUPDATE
   , OPENCLOSED
   , LATESTUPDDATE
   , ASSIGNEDTO
   , ATM
   )
(
   SELECT 
      accountinfo.accrec
      , getdate()
      , '30 days until renewal'
      , 'SYS' AS userval
      , 'Auto' AS TYPE
      ,  k.newfollowupdate
      , 'open' AS OPCLOSED
      , GETDATE() AS date2
      , (case 
            when AccountInfo.CLIENTCONTACT=1 
            then k.csrval 
            else k.pr 
         end) as assignedto
      , '1' AS ATM 
   FROM 
      (
         SELECT 
            MAX(CO_AMT) AS maxcom
            , LEFT(POL_IDX, 7) AS rec2
            , DATEDIFF([month], GETDATE(), EXP2) AS Expr1
            , EXP2
            , dateadd(month, -1, EXP2) AS newfollowupdate
            , PR
            , min(csr) as CSRVAL
         FROM POLICY
         WHERE (LEFT(POL_IDX, 7) = '9PERGR0') 
            AND (DATEDIFF([month], GETDATE(), EXP2) > 2)
         GROUP BY LEFT(POL_IDX, 7), EXP2, pr
      ) k 
   RIGHT OUTER JOIN AccountInfo 
      ON k.rec2 = AccountInfo.ACCREC 
   LEFT OUTER JOIN
      (
         SELECT 
            accrec
            , MAX(taskcompldate) AS MCOMPLDATE
         FROM OpenItems
         WHERE ATM = 1 
            AND openclosed = 'closed'
         GROUP BY ACCREC
      ) b
         ON AccountInfo.ACCREC = b.accrec 
   LEFT OUTER JOIN
      (
         SELECT 
            OpenItems.ACCREC
            , COUNT(OpenItems.ACCREC) AS Cnum
         FROM OpenItems
         WHERE (OpenItems.ACCREC = '9PERGR0') 
            AND (OpenItems.ATM = 1) 
            AND (datediff(month, getdate(), OpenItems.FOLLOWUPDATE) >= 0)
         GROUP BY OpenItems.ACCREC
      ) A 
         ON AccountInfo.ACCREC = A.ACCREC 
   LEFT OUTER JOIN
      (
         SELECT 
            OpenItems.ACCREC
            , Convert(int, COUNT(OpenItems.ACCREC)) AS Callnum
         FROM OpenItems
         WHERE (OpenItems.ACCREC = '9PERGR0') 
            AND (OpenItems.ATM is not null) 
            AND (datediff(month, getdate(), OpenItems.FOLLOWUPDATE) >= 0)
         GROUP BY OpenItems.ACCREC
      ) Y 
         ON AccountInfo.ACCREC = A.ACCREC 
   COLLATE SQL_Latin1_General_CP1_CI_AS
   WHERE (AccountInfo.ACCREC = '9PERGR0') 
      and AccountInfo.NTP>0  
      and (A.Cnum = 0 OR A.Cnum IS NULL) 
      AND Convert(smallint,AccountInfo.NTP)>Convert(smallint, Y.Callnum) 
      and datediff(month,getdate(),k.newfollowupdate)>1 
      and datediff(month,b.MCOMPLDATE,k.newfollowupdate)>1
   GROUP BY AccountInfo.NTP, A.Cnum, Y.Callnum, AccountInfo.ACCREC
      , b.MCOMPLDATE, k.EXP2, k.pr, k.newfollowupdate, k.csrval
      , AccountInfo.CLIENTCONTACT
 )

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[ESquared]

Also, Expr1 in derived table k is never used in the query.

And I have to say that query is a real fun-looking one.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]