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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Approaches to a query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 08/13/2013 :  11:02:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 08/13/2013 :  11:21:06  Show Profile  Reply with Quote
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)
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 08/13/2013 :  11:28:51  Show Profile  Reply with Quote
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.
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 08/13/2013 :  12:05:31  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 08/13/2013 :  13:07:21  Show Profile  Reply with Quote
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
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 08/13/2013 :  13:36:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000