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.
Author |
Topic |
jrobin747
Starting Member
48 Posts |
Posted - 2013-08-21 : 13:14:07
|
I've successfully was able to dump multiple temp tables into one big temp table. what a struggle.In my temp table I have a column of sales team members followed by columns of Number of Applications Number of Lease etc.Some of my sales team members don't have a count of leases so the value showing up them is NULL where of those who have a count have a number.How do I get the table to enter a value of 0 (zero) instead of NULL when there is no value available for the sales team member for a particular column?Some sales team members are not expected to have a value. So that is okay. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-21 : 13:21:52
|
You can either update the table, or set the nulls to zero when you query, for example like this:SELECT SalesPersonID, ISNULL(NumberOfLeases,0) as NumberOfLeasesFROM YourTable |
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-08-21 : 13:30:37
|
Thanks James. How would I do it(return 0 in place of NULL) for this query that counts the number of funded applicationsDECLARE @x_strStartDate VARCHAR (10), @x_strEndDate VARCHAR (10)SET @x_strStartDate= '07/14/2013'SET @x_strEndDate= '08/15/2013'SELECT (FirstName + '' + LastName)AS [Sales Team Member], stm.SalesteamID, MerApp.Assignedto, COUNT (MerAppEqL.EquipmentLeaseID)AS [Leases Funded]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.FundedDate BETWEEN @x_strStartDate AND @x_strEndDate AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY usr.FirstName, usr.LastName, MerApp.Assignedto, stm.SalesteamIDORDER BY usr.FirstName, usr.LastName |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-21 : 13:36:56
|
You probably want to do an outer join to the MerchantAppEquipmentLease, like shown below:DECLARE @x_strStartDate VARCHAR(10) , @x_strEndDate VARCHAR(10)SET @x_strStartDate = '07/14/2013'SET @x_strEndDate = '08/15/2013'SELECT ( FirstName + '' + LastName ) AS [Sales Team Member] , stm.SalesteamID , MerApp.Assignedto , COUNT(MerAppEqL.EquipmentLeaseID) AS [Leases Funded]FROM Users Usr INNER JOIN SalesTeamMembers Stm ON Stm.UserID = Usr.UserID LEFT JOIN MerchantApplication MerApp ON MerApp.Assignedto = Stm.UserID LEFT JOIN MerchantAppEquipment MerAppEqt ON MerAppEqt.ApplicationID = MerApp.ApplicationID LEFT JOIN MerchantAppEquipmentLease MerAppEqL ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber AND MerAppEqL.FundedDate BETWEEN @x_strStartDate AND @x_strEndDateWHERE stm.ActiveStatus = 1 AND usr.ActiveStatus = 1GROUP BY usr.FirstName , usr.LastName , MerApp.Assignedto , stm.SalesteamIDORDER BY usr.FirstName , usr.LastName |
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-08-21 : 15:12:52
|
Thank James. The query is correct as I had it. It only pulled the names in that date range. So I get a count for each person. The below is a sample of the #temptableI created queries for different temptables to get column results for the main temp table.UserID SalesTeamID SalesTeamName DayWithoutSale WeeklyApps Leases11 2 Roman 37 2 NULL28 1 Quentin NULL 10 5There are no NULL returns in the queries when they are in their own specific temp table. I get NULLS when main temp table is updated with all the other temp tables.I hope that is clearer. What you wrote the first time I couldn't see how to apply it. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-21 : 15:26:52
|
quote: Originally posted by jrobin747 Thank James. The query is correct as I had it. It only pulled the names in that date range. So I get a count for each person. The below is a sample of the #temptableI created queries for different temptables to get column results for the main temp table.UserID SalesTeamID SalesTeamName DayWithoutSale WeeklyApps Leases11 2 Roman 37 2 NULL28 1 Quentin NULL 10 5There are no NULL returns in the queries when they are in their own specific temp table. I get NULLS when main temp table is updated with all the other temp tables.I hope that is clearer. What you wrote the first time I couldn't see how to apply it.
I couldn't see how you would get nulls in your result. You have count of specific column, and that will some number that is 0 or greater. It wouldn't return null. Can you show an example of how you are getting the null?See this example:CREATE TABLE #tmp(id INT, val INT);INSERT INTO #tmp VALUES (1,1),(1,2),(2,NULL);SELECT id, COUNT(val) FROM #tmp GROUP BY id;DROP TABLE #tmp; |
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-08-21 : 15:29:54
|
I'm sure there is a better way to do this./* **************************************************************Get Sales Member Name, Sales Team Name, Sales Team ID, UserID***************************************************************/CREATE TABLE #TEMPDASHBOARD ( UserID INT, SalesteamID INT, SalesName VARCHAR(50), SalesTeamName VARCHAR(50), --TeamDescription from SalesTeam DaysWithOutSale INT, DailyAppsIn INT, WeeklyAppsIn INT, WeeklyLeasesIn INT, LeasesFundedCount INT, LeaseDollarsFunded MONEY, LeaseDollarsSubmitted MONEY, )DECLARE @x_strStartDate VARCHAR (10), @x_strEndDate VARCHAR (10) SET @x_strStartDate= '03/10/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPDASHBOARD (SalesName, SalesteamID, UserID, SalesTeamName) ( SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.SalesteamID, usr.UserID, satm.TeamDescriptionFROM Users usr INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID LEFT JOIN MerchantApplication mapp ON mapp.Assignedto = usr.UserID LEFT JOIN MerchantAppStatus mas ON mas.MerchantAppID = mapp.ApplicationID INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamIDWHERE STM.activeStatus=1GROUP BY usr.UserID, usr.FirstName, usr.LastName, stm.SalesteamID, satm.TeamDescription ) /* **************************************************************Get Daily Count of Applications***************************************************************/CREATE TABLE #TEMPAPPSDAILYCOUNT ( SalesName VARCHAR(50), UserID INT, DailyAppsIn INT, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPAPPSDAILYCOUNT(SalesName, UserID, DailyAppsIn) ( SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID, COUNT (MerApp.ApplicationID)AS [Number of Apps]FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamIDWHERE MerApp.LastUpdateOn BETWEEN @x_strStartDate AND @x_strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription ) UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.DailyAppsIn =TDC.DailyAppsIn FROM #TEMPAPPSDAILYCOUNT TDC, #TEMPDASHBOARD WHERE tdc.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get Weekly Count of Applications***************************************************************/CREATE TABLE #TEMPAPPSWEEKLYCOUNT ( SalesName VARCHAR(50), UserID INT, WeeklyAppsIn INT, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPAPPSWEEKLYCOUNT(SalesName, UserID, WeeklyAppsIn) ( SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID, COUNT (MerApp.ApplicationID)AS [Number of Apps]FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamIDWHERE MerApp.LastUpdateOn BETWEEN @x_strStartDate AND @x_strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription )UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.WeeklyAppsIn =TWC.WeeklyAppsIn FROM #TEMPAPPSWEEKLYCOUNT TWC, #TEMPDASHBOARD WHERE TWC.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get without a sale***************************************************************/CREATE TABLE #TEMPDAYSWOSALE ( SalesName VARCHAR(50), UserID INT, DaysWithOutSale INT, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPDAYSWOSALE(SalesName, UserID, DaysWithOutSale)(SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], usr.UserID, DATEDIFF(DAY,MAX(MerAppSt.StatusChange),GETDATE()) AS DaysSinceLastSaleFROM MerchantAppStatus MerAppSt INNER JOIN MerchantApplication MerApp ON MerAppSt.MerchantAppID = MerApp.ApplicationID INNER JOIN Users usr ON MerApp.Assignedto = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID WHERE MerAppSt.StatusChange BETWEEN @x_strStartDate AND @x_strEndDate AND MerAppSt.ApplicationStatus = 3 AND stm.ActiveStatus=1 AND usr.ActiveStatus=1GROUP BY usr.FirstName, usr.LastName, usr.UserID )UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.DaysWithOutSale =DWOS.DaysWithOutSale FROM #TEMPDAYSWOSALE DWOS, #TEMPDASHBOARD WHERE DWOS.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get weekly leases in***************************************************************/CREATE TABLE #TEMPWEEKLYLEASESIN ( SalesName VARCHAR(50), UserID INT, WeeklyLeasesIn INT, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPWEEKLYLEASESIN(SalesName, UserID, WeeklyLeasesIn)(SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, COUNT (MerAppEqL.MonthlyPayment)AS [Weekly Leases]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.SubmitDate BETWEEN @x_strStartDate AND @x_strEndDate AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY usr.FirstName, usr.LastName, Usr.UserID )UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.WeeklyLeasesIn =WLI.WeeklyLeasesIn FROM #TEMPWEEKLYLEASESIN WLI, #TEMPDASHBOARD WHERE WLI.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get Leases Funded Count***************************************************************/CREATE TABLE #TEMPLEASESFUNDEDCOUNT ( SalesName VARCHAR(50), UserID INT, LeasesFundedCount INT, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPLEASESFUNDEDCOUNT (SalesName, UserID, LeasesFundedCount)(SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, COUNT (MerAppEqL.EquipmentLeaseID)AS [Leases Funded]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.FundedDate BETWEEN @x_strStartDate AND @x_strEndDate AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY usr.FirstName, usr.LastName, Usr.UserID )UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.LeasesFundedCount =LFC.LeasesFundedCount FROM #TEMPLEASESFUNDEDCOUNT LFC, #TEMPDASHBOARD WHERE LFC.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get Lease Dollars Funded***************************************************************/CREATE TABLE #TEMPLEASEDOLLARSFUNDED ( SalesName VARCHAR(50), UserID INT, LeaseDollarsFunded MONEY, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPLEASEDOLLARSFUNDED (SalesName, UserID, LeaseDollarsFunded)(SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, SUM (MerAppEqL.MonthlyPayment)AS [$ Leases Funded]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.FundedDate IS NOT NULL AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY Usr.Userid, usr.LastName, usr.FirstName )UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.LeaseDollarsFunded =LDF.LeaseDollarsFunded FROM #TEMPLEASEDOLLARSFUNDED LDF, #TEMPDASHBOARD WHERE LDF.UserID = #TEMPDASHBOARD.UserID/* **************************************************************Get Lease Dollars Submitted***************************************************************/CREATE TABLE #TEMPLEASEDOLLARSSUBMITTED ( SalesName VARCHAR(50), UserID INT, LeaseDollarsSubmitted MONEY, ) SET @x_strStartDate= '03/04/2013' SET @x_strEndDate= '08/10/2013' INSERT INTO #TEMPLEASEDOLLARSSUBMITTED (SalesName, UserID, LeaseDollarsSubmitted)(SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, SUM (MerAppEqL.MonthlyPayment)AS [Leases $ Submitted]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.SubmitDate BETWEEN @x_strStartDate AND @x_strEndDate AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY usr.FirstName, usr.LastName, Usr.UserID)UPDATE #TEMPDASHBOARDSET #TEMPDASHBOARD.LeaseDollarsSubmitted =LDS.LeaseDollarsSubmitted FROM #TEMPLEASEDOLLARSSUBMITTED LDS, #TEMPDASHBOARD WHERE LDS.UserID = #TEMPDASHBOARD.UserIDSELECT *FROM #TEMPAPPSDAILYCOUNTDROP TABLE #TEMPAPPSDAILYCOUNTSELECT *FROM #TEMPAPPSWEEKLYCOUNTDROP TABLE #TEMPAPPSWEEKLYCOUNTSELECT *FROM #TEMPDAYSWOSALEDROP TABLE #TEMPDAYSWOSALESELECT *FROM #TEMPWEEKLYLEASESINDROP TABLE #TEMPWEEKLYLEASESINSELECT *FROM #TEMPLEASESFUNDEDCOUNTDROP TABLE #TEMPLEASESFUNDEDCOUNTSELECT *FROM #TEMPLEASEDOLLARSFUNDEDDROP TABLE #TEMPLEASEDOLLARSFUNDEDSELECT *FROM #TEMPLEASEDOLLARSSUBMITTEDDROP TABLE #TEMPLEASEDOLLARSSUBMITTEDSELECT *FROM #TEMPDASHBOARDWHERE DailyAppsIn IS NOT NULL AND WeeklyAppsIn IS NOT NULLDROP TABLE #TEMPDASHBOARD |
|
|
jrobin747
Starting Member
48 Posts |
Posted - 2013-08-21 : 18:09:06
|
I tried ISNULL and it didn't work. I was told by a senior developer I work with that I should do something like a case when. whatever that is.INSERT INTO #TEMPLEASEDOLLARSSUBMITTED (SalesName, UserID, LeaseDollarsSubmitted)(SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, SUM (ISNULL(MerAppEqL.MonthlyPayment,0))AS [Leases $ Submitted]FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber INNER JOIN MerchantApplication MerApp ON MerAppEqt.ApplicationID = MerApp.ApplicationID INNER JOIN SalesTeamMembers Stm ON MerApp.Assignedto = Stm.UserID INNER JOIN Users Usr ON Stm.UserID = Usr.UserIDWHERE MerAppEqL.SubmitDate BETWEEN @x_strStartDate AND @x_strEndDate AND stm.ActiveStatus=1 AND usr.ActiveStatus=1 GROUP BY usr.FirstName, usr.LastName, Usr.UserID) |
|
|
|
|
|
|
|