DateDiff Syntax Error

[Miked811]

Hi,

What is wrong with this statement?

SELECT Campaign.BillingState, Count(Campaign.BillingState)
FROM Campaign INNER JOIN Mcalcode ON
Campaign.CallResultCode = Mcalcode.CallResultCode
GROUP BY Campaign.BillingState, Mcalcode.Redialable, DateDiff(d,DateTimeofCall,getDate()) > 24
HAVING Mcalcode.Redialable=1

I get an error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '>'.

Without the ">" condition the Query works but not the type of output I need. It does not do the Aggregate thing.

Thanks

Mike

 

[bborissov]

This is wrong:

GROUP BY ....., DateDiff(d,DateTimeofCall,getDate()) > 24

What you are trying to do?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Miked811]

Hi,

GROUP BY Campaign.BillingState, Mcalcode.Redialable, DateDiff(d,DateTimeofCall,getDate()) > 24
HAVING Mcalcode.Redialable=1

I'm trying to get the aggregate Count() of BillingState field where DateTimeOfCall is less than 24 hours. The ideal result should be, e.g.

CA 3
MS 2
NY 5

If I take out the condition in the "Group By", this is what I get, which I don't need, the above is my requirement:

CA 1
CA 1
CA 1
NY 1
NY 1
MS 1
MS 2
MS 1
MS 1

Thanks

Mike

 

[gmmastros]

where DateTimeOfCall is less than 24 hours

Have you considered using a where clause?

SELECT Campaign.BillingState, 
       Count(Campaign.BillingState)
FROM   Campaign 
       INNER JOIN Mcalcode  
         ON Campaign.CallResultCode = Mcalcode.CallResultCode
WHERE  DateDiff(d,DateTimeofCall,getDate()) > 24
GROUP BY Campaign.BillingState, Mcalcode.Redialable
HAVING Mcalcode.Redialable=1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

There's never a reason to use a HAVING clause unless you're trying to sort by SUMs, COUNTS or aggregates, in my opinion. You can do GROUP BY without a HAVING and use a WHERE clause instead.

BTW, the DateDIFF should be in a WHERE or HAVING clause, not in a GROUP BY



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Oh wait.... There's probably a time component to the DateOfCall field, so...

SELECT Campaign.BillingState, 
       Count(Campaign.BillingState)
FROM   Campaign 
       INNER JOIN Mcalcode  
         ON Campaign.CallResultCode = Mcalcode.CallResultCode
WHERE  DateDiff(d,DateTimeofCall,getDate()) > 24
GROUP BY Campaign.BillingState, Mcalcode.Redialable, [!]DateDiff(Day, 0, DateTimeOfCall)[/!]
HAVING Mcalcode.Redialable=1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I'm trying to get the aggregate Count() of BillingState field where DateTimeOfCall is less than 24 hours.

Your original example had:
DateDiff(d,DateTimeofCall,getDate()) > 24
Which basically says, greater than 24 days ago.

If you really want the last 24 hours then try this query.

SELECT   Campaign.BillingState, 
         Count(Campaign.BillingState)
FROM     Campaign 
         INNER JOIN Mcalcode  
           ON Campaign.CallResultCode = Mcalcode.CallResultCode
WHERE    Mcalcode.Redialable=1
         And DateDiff([!]Hour[/!],DateTimeofCall,getDate()) [!]<=[/!] 24
GROUP BY Campaign.BillingState
Order By Campaign.BillingState

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Miked811]

Hi Guys,

George, this worked:

SELECT Campaign.BillingState,
       Count(Campaign.BillingState)
FROM   Campaign
       INNER JOIN Mcalcode  
         ON Campaign.CallResultCode = Mcalcode.CallResultCode
WHERE  DateDiff(d,DateTimeofCall,getDate()) > 24
GROUP BY Campaign.BillingState, Mcalcode.Redialable
HAVING Mcalcode.Redialable=1

So what is exactly the difference with WHERE and HAVING? I tried to put it in the HAVING clause but it gave me a different result. I did not thought that when I use HAVING, I can't use WHERE. Is it a rule of thumb that when getting an aggregate, that, I need to use HAVING with "GROUP BY". Thanks a lot George.

CATADMIN and bborissov: Thanks for your help too...

Thank you very much all...

 

[Catadmin]

Any time you use COUNT, SUM, MAX, MIN, AVG and a half dozen other keywords that do aggregation in a SELECT statement, you must always use a GROUP BY statement for all non-aggregated values.

However, you can always use WHERE and ORDER BY with a GROUP BY if you need or want them. The HAVING is also optional, but I use it when I have an aggregate conditional that isn't in the SELECT.

For instance:

Select VehicleModel, VehicleYear, MSRP
from table
Group by VehicleModel, VehicleYear, MSRP
Having Count(VehicleModel) > 2

Should give me all Vehicle models with more than 2 records in Table.

Aggregates are the only time I use the HAVING clause. When I have <field> = <value>, I always use a WHERE clause instead.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Miked811]

Thank you very much. That answered my query. Cheers....