Link to home
Start Free TrialLog in
Avatar of tanj1035
tanj1035

asked on

SQL, How to add "AVG" on a function in the select statement

Hi Experts,

I want to add "AVG" on the function " dbo.workingminutesbetweendates"  and I am looking for the result like

Sent by Dealer Contact ID     Avg Adjusted
       3152                                        100

But, My query returned
Sent by Dealer Contact ID     Avg Adjusted
       3152                                        53
       3152                                        4910
       3152                                         0

Can you help me on my query? Thanks.



select dr.sentbydealercontactid,
   CASE
                  WHEN drs2.seqnum IS NULL THEN NULL
                  WHEN drs2.seqnum IS NOT NULL
                       AND Datediff(mi, drs2.timestamp, drs.timestamp) > dbo.Workingminutesbetweendates(drs2.timestamp, drs.timestamp) THEN avg(dbo.Workingminutesbetweendates(drs2.timestamp, drs.timestamp))
                  ELSE avg(Datediff(mi, drs2.timestamp, drs.timestamp))end
                  AS AvgAdjusted
from makemydeal_com..dealresponse2 dr 
inner join [MMD_Feed].[dbo].[DealresponseSequence] drs on dr.dealresponseid = drs.dealresponseid
 LEFT JOIN [MMD_Feed].[dbo].[DealresponseSequence] drs2
              ON drs2.seqnum + 1 = drs.seqnum
                 AND drs.dealid = drs2.dealid
Where cast(dr.timestamp as date) >= '2015-06-01' and cast(dr.timestamp as date) <='2015-07-14'
and dr.sentby is not null
and dr.sentbydealercontactid = 3152
group by dr.sentbydealercontactid,drs2.timestamp,drs.timestamp,drs2.seqnum

Open in new window

Avatar of ste5an
ste5an
Flag of Germany image

You would normally place it around the CASE expression:

SELECT  dr.sentbydealercontactid ,
        AVG(CASE WHEN drs2.seqnum IS NULL THEN NULL
                 WHEN drs2.seqnum IS NOT NULL
                      AND DATEDIFF(mi, drs2.timestamp, drs.timestamp) > dbo.Workingminutesbetweendates(drs2.timestamp, drs.timestamp)
                 THEN dbo.Workingminutesbetweendates(drs2.timestamp, drs.timestamp)
                 ELSE DATEDIFF(mi, drs2.timestamp, drs.timestamp)
            END) AS AvgAdjusted
FROM    makemydeal_com..dealresponse2 dr
        INNER JOIN [MMD_Feed].[dbo].[DealresponseSequence] drs ON dr.dealresponseid = drs.dealresponseid
        LEFT JOIN [MMD_Feed].[dbo].[DealresponseSequence] drs2 ON drs2.seqnum + 1 = drs.seqnum
                                                                  AND drs.dealid = drs2.dealid
WHERE   CAST(dr.timestamp AS DATE) >= '2015-06-01'
        AND CAST(dr.timestamp AS DATE) <= '2015-07-14'
        AND dr.sentby IS NOT NULL
        AND dr.sentbydealercontactid = 3152
GROUP BY dr.sentbydealercontactid ,
        drs2.timestamp ,
        drs.timestamp ,
        drs2.seqnum;

Open in new window

Avatar of tanj1035
tanj1035

ASKER

Thanks for your quick reply.

I tried your query, and it still returns
Sent by Dealer Contact ID     Avg Adjusted
        3152                                        53
        3152                                        4910
        3152                                         0

I suspect because the "timestamp" in the group by,  it returns "avg adjusted" for each time stamp.
If I want to get an "avg adjusted" for that time period, how can I change the query? Thanks.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for both of your replies. Both of them are working.  From a performance prospective, whether CTE or sub-query in the select statement runs faster?
They should be indentical. Cause a CTE is only syntactic sugar in this case.