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

tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

tanj1035Author Commented:
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.
ste5anSenior DeveloperCommented:
E.g.

WITH    Data
          AS ( 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 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
             )
    SELECT  D sentbydealercontactid ,
            AVG(D.AvgAdjusted)
    FROM    Data D
    GROUP BY D.sentbydealercontactid;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Patrick MatthewsCommented:
Note that even with ste5an's adjustment, you still could have the additional rows in your result set.  That is because you are including those timestamp columns in your GROUP BY clause.

Try this:

SELECT z.sentbydealercontactid,
    AVG(z.Adjusted) AS AvgAdjusted
FROM (
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
            dbo.Workingminutesbetweendates(drs2.timestamp, drs.timestamp))
        ELSE Datediff(mi, drs2.timestamp, drs.timestamp) end AS Adjusted
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
) AS z
GROUP BY z.sentbydealercontactid

Open in new window

tanj1035Author Commented:
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?
ste5anSenior DeveloperCommented:
They should be indentical. Cause a CTE is only syntactic sugar in this case.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.