Approaches to a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Approaches to a query

Author

Topic

jrobin747
Starting Member

48 Posts

Posted - 2013-08-13 : 11:02:04

I'm trying to create a query to find out how many days a person has gone without a sale. Using parts to a query I've done previously, I believe that I would just need to add a few things to get what I looking for. I'm not sure how to proceed.

I used the bleow query to determine how many sales a person earned between a time period

ApplicationID is an Application/Potential Sale
ApplicationStatus=3 means that it's an approved sale
LastUpdated is the most current date of verification

SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, stm.SalesteamID, stm.UserID,
COUNT (MerApp.ApplicationID)AS [Number of Sales
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.LastUpdateOn BETWEEN '2013-03-13' AND '2013-08-01' AND MerApp.ApplicationStatus=3
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-13 : 11:21:06

Are you trying to find:

a) As of today (or any given date), how many days has it been since each Sales Team Member made a sale, OR

b) What is the largest period of time without a sale for each Sales Team Member over a given period (for 2013 for example)

jrobin747
Starting Member

48 Posts

Posted - 2013-08-13 : 11:28:51

Good thought provoking question. I am counting the different from current date and lastUpdateOn

So Im thinking I do a datediff() function

Which would include GETDATE () function and lastUpdateOn

Is that a good guess.

jrobin747
Starting Member

48 Posts

Posted - 2013-08-13 : 12:05:31

I tried this query and I got numbers but my numbers of apps are not clustered or grouped with my sales team member.

For example sales team member #1 shows up twice with number of apps being 1 for each time his name shows up when it should be
John Doe 2

and not
John Doe 1
John Doe 1

So I got some more adjusting to do.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-13 : 13:07:21

quote:
Originally posted by jrobin747

I tried this query and I got numbers but my numbers of apps are not clustered or grouped with my sales team member.

For example sales team member #1 shows up twice with number of apps being 1 for each time his name shows up when it should be
John Doe 2

and not
John Doe 1
John Doe 1

So I got some more adjusting to do.

Which query are you referring to?

Try one of these:

SELECT  ( usr.FirstName + ' ' + usr.LastName ) AS [Sales Team Member] ,
        MerApp.Assignedto ,
        stm.SalesteamID ,
        stm.UserID ,
        COUNT(MerApp.ApplicationID) AS [Number of Sales],
        DATEDIFF(DAY,MAX(MerApp.LastUpdateOn),GETDATE()) AS DaysSinceLastSale
FROM    MerchantApplication MerApp
        INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
        INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE   MerApp.LastUpdateOn BETWEEN '2013-03-13'
                            AND     '2013-08-01'
        AND MerApp.ApplicationStatus = 3
GROUP BY MerApp.AssignedTo ,
        usr.LastName ,
        usr.FirstName ,
        stm.UserID ,
        stm.SalesteamID ,
        MerApp.ApplicationStatus;

SELECT  ( usr.FirstName + '' + usr.LastName ) AS [Sales Team Member] ,
        COUNT(MerApp.ApplicationID) AS [Number of Sales],
        DATEDIFF(DAY,MAX(MerApp.LastUpdateOn),GETDATE()) AS DaysSinceLastSale
FROM    MerchantApplication MerApp
        INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
        INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE   MerApp.LastUpdateOn BETWEEN '2013-03-13'
                            AND     '2013-08-01'
        AND MerApp.ApplicationStatus = 3
GROUP BY 
        usr.FirstName + '' + usr.LastName

jrobin747
Starting Member

48 Posts

Posted - 2013-08-13 : 13:36:34

Thanks James. I believe both will do fine.

Why was MAX used. I didn't see any reference to it here:
http://www.w3schools.com/sql/func_datediff.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources