SQL Server Forums
Profile | Register | 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
 New Topic  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

3719 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

3719 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000