Results 1 to 3 of 3

Thread: Sql Query Help

  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Sql Query Help

    I have a query running with my sql statement like this:

    SELECT workflow.Source, Count(workflow.Source) AS NoContactsBySource, Sum(IIf([Owner]>'',1,0)) AS Completed, Sum(IIf([Right Party Contact]=-1,1,0)) AS RPC, Sum(IIf([Mortgage Sale]=-1,1,0)) AS Application, [RPC]/[Completed] AS RPCcalls, [Application]/[NoContactsBySource] AS OPPpenetration, [Application]/[Rpc] AS RPCtoappPenetration, Sum(IIf([Non Sales Call]=-1,1,0)) AS NonApp
    FROM workflow
    WHERE (((workflow.[Preferred Contact Date]) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY workflow.Source;

    as you can see it counts all the source fields but what i want it to do is not count the source field if it has a checkbox ticked in another field on the same record. the field with the tick box is named "dead lead" and i understand -1=true 0=false but how would the sql statement be written with that in mind?
    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where [dead lead] = false
    OR
    where [dead lead] = true

    incidentally you are making a rod for you back by having spaces between column names
    DeadLead (or even dead_lead) in my books is preferable to [dead lead]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    What i was wondering is whether it could be somethign like this but it doesnt work...
    SELECT workflow.Source, Count(workflow.Source) AS (NoContactsBySource)-[Dead Lead], Sum(IIf([Owner]>'',1,0)) AS Completed, Sum(IIf([Right Party Contact]=-1,1,0)) AS RPC, Sum(IIf([Mortgage Sale]=-1,1,0)) AS Application, [RPC]/[Completed] AS RPCcalls, [Application]/[NoContactsBySource] AS OPPpenetration, [Application]/[Rpc] AS RPCtoappPenetration, Sum(IIf([Non Sales Call]=-1,1,0)) AS NonApp
    FROM workflow
    WHERE (((workflow.[Preferred Contact Date]) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY workflow.Source;

    is there a way to have the Nocontactsbysource column have the figure - the dead lead column?

    or the report to generate the figure? but when i've tried doing that it says the control source is wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •