Case When statement - SQL Server Forums

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

Case When statement

Author

Topic

jrobin747
Starting Member

48 Posts

Posted - 2013-08-22 : 10:26:53

In my temptable I'm still trying to replace NULL values with 0 (zero)
I tried ISNULL and that didn't work.

Posted - 08/21/2013 : 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
)

UPDATE #TEMPDASHBOARD
SET #TEMPDASHBOARD.LeaseDollarsSubmitted =LDS.LeaseDollarsSubmitted

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

So maybe I can do
CASE WHEN LeaseDollarsSubmitted = NULL THEN 0

I don't know.

Or should I have put the ISNULL when I updated #TEMPDASHBOARD because that is where the NULL values show up. They don't show up in #TEMPLEASEDOLLARSSUBMITTED table?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-22 : 10:56:34

You can try

COALESCE(COLUMN_NAME, 0)

or

(CASE WHEN COLUMN_NAME IS NULL THEN 0 ELSE COLUMN_NAME END)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-22 : 11:27:26

SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID,
ISNULL(SUM (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

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

jrobin747
Starting Member

48 Posts

Posted - 2013-08-22 : 12:15:14

Thanks guys. Those suggestions are not working for me. #Tempdashboard is the main table of which my other temp tables are being dumped in.

It's in #Tempdashboard that I get the NULL values. Im updating #tempdashboard like this.

UPDATE #TEMPDASHBOARD
SET #TEMPDASHBOARD.LeaseDollarsSubmitted =LDS.LeaseDollarsSubmitted

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

jrobin747
Starting Member

48 Posts

Posted - 2013-08-22 : 12:56:27

I think I got it correctly. I have zeros in place of NULL.
SELECT *
FROM #TEMPLEASEDOLLARSSUBMITTED
DROP TABLE #TEMPLEASEDOLLARSSUBMITTED

SELECT UserId, SalesTeamID, SalesTeamName,
ISNULL (DaysWithOutSale,0) AS [Days Without Sale],
ISNULL (DailyAppsIn,0)AS [Weekly Leases In],
ISNULL (WeeklyAppsIn,0)AS [Weekly Leases In],
ISNULL (WeeklyLeasesIn,0)AS [Weekly Leases In],
ISNULL (LeasesFundedCount,0)AS [Leases Funded Count],
ISNULL (LeaseDollarsFunded,0)AS [Lease Dollars Funded],
ISNULL (LeaseDollarsSubmitted,0)AS [Lease Dollars Submitted]

FROM #TEMPDASHBOARD
WHERE DailyAppsIn IS NOT NULL AND WeeklyAppsIn IS NOT NULL

DROP TABLE #TEMPDASHBOARD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources