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
 Return a zero value in place of null
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

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

3873 Posts

Posted - 08/21/2013 :  13:21:52  Show Profile  Reply with Quote
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

USA
48 Posts

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

3873 Posts

Posted - 08/21/2013 :  13:36:56  Show Profile  Reply with Quote
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

USA
48 Posts

Posted - 08/21/2013 :  15:12:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 08/21/2013 :  15:26:52  Show Profile  Reply with Quote
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

USA
48 Posts

Posted - 08/21/2013 :  15:29:54  Show Profile  Reply with Quote
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

USA
48 Posts

Posted - 08/21/2013 :  18:09:06  Show Profile  Reply with Quote
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
  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