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
 Return a zero value in place of null

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 NumberOfLeases
FROM
YourTable
Go to Top of Page

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 applications

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 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.UserID
WHERE 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.SalesteamID
ORDER BY usr.FirstName, usr.LastName
Go to Top of Page

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_strEndDate
WHERE stm.ActiveStatus = 1
AND usr.ActiveStatus = 1
GROUP BY usr.FirstName ,
usr.LastName ,
MerApp.Assignedto ,
stm.SalesteamID
ORDER BY usr.FirstName ,
usr.LastName
Go to Top of Page

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 #temptable
I created queries for different temptables to get column results for the main temp table.
UserID SalesTeamID SalesTeamName DayWithoutSale WeeklyApps Leases
11 2 Roman 37 2 NULL
28 1 Quentin NULL 10 5

There 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.

Go to Top of Page

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 #temptable
I created queries for different temptables to get column results for the main temp table.
UserID SalesTeamID SalesTeamName DayWithoutSale WeeklyApps Leases
11 2 Roman 37 2 NULL
28 1 Quentin NULL 10 5

There 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;
Go to Top of Page

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.TeamDescription

FROM 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.SalesTeamID
WHERE STM.activeStatus=1
GROUP 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.SalesTeamID
WHERE MerApp.LastUpdateOn BETWEEN @x_strStartDate AND @x_strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription
)

UPDATE #TEMPDASHBOARD
SET #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.SalesTeamID
WHERE MerApp.LastUpdateOn BETWEEN @x_strStartDate AND @x_strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription
)

UPDATE #TEMPDASHBOARD
SET #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 DaysSinceLastSale
FROM 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=1
GROUP BY usr.FirstName, usr.LastName, usr.UserID
)

UPDATE #TEMPDASHBOARD
SET #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.UserID
WHERE 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 #TEMPDASHBOARD
SET #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.UserID
WHERE 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 #TEMPDASHBOARD
SET #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.UserID
WHERE MerAppEqL.FundedDate IS NOT NULL
AND stm.ActiveStatus=1
AND usr.ActiveStatus=1

GROUP BY Usr.Userid, usr.LastName, usr.FirstName
)

UPDATE #TEMPDASHBOARD
SET #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.UserID
WHERE 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 #TEMPDASHBOARD
SET #TEMPDASHBOARD.LeaseDollarsSubmitted =LDS.LeaseDollarsSubmitted

FROM #TEMPLEASEDOLLARSSUBMITTED LDS, #TEMPDASHBOARD
WHERE LDS.UserID = #TEMPDASHBOARD.UserID

SELECT *
FROM #TEMPAPPSDAILYCOUNT
DROP TABLE #TEMPAPPSDAILYCOUNT

SELECT *
FROM #TEMPAPPSWEEKLYCOUNT
DROP TABLE #TEMPAPPSWEEKLYCOUNT

SELECT *
FROM #TEMPDAYSWOSALE
DROP TABLE #TEMPDAYSWOSALE

SELECT *
FROM #TEMPWEEKLYLEASESIN
DROP TABLE #TEMPWEEKLYLEASESIN

SELECT *
FROM #TEMPLEASESFUNDEDCOUNT
DROP TABLE #TEMPLEASESFUNDEDCOUNT

SELECT *
FROM #TEMPLEASEDOLLARSFUNDED
DROP TABLE #TEMPLEASEDOLLARSFUNDED

SELECT *
FROM #TEMPLEASEDOLLARSSUBMITTED
DROP TABLE #TEMPLEASEDOLLARSSUBMITTED

SELECT *
FROM #TEMPDASHBOARD
WHERE DailyAppsIn IS NOT NULL AND WeeklyAppsIn IS NOT NULL
DROP TABLE #TEMPDASHBOARD
Go to Top of Page

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.UserID
WHERE MerAppEqL.SubmitDate BETWEEN @x_strStartDate AND @x_strEndDate
AND stm.ActiveStatus=1
AND usr.ActiveStatus=1

GROUP BY usr.FirstName, usr.LastName, Usr.UserID
)
Go to Top of Page
   

- Advertisement -