Replace NULL in a SELECT

[earljgray]

Here's the issue: we have a query comprised of several CTEs that eventually reduce to a final select.
The scenario is: An employee's activity can be in one or more of 5 stages of activity.

Employee Activity Stage

Joe Activity A 1
Joe Activity A1 2
NULL No Activity 3
Joe Activity G 4
Joe Activity K 4
NULL No Activity 5

It's OK to have NULL in the activity - a CASE statement tests for NULLS and pops in the
'No Activity' text, but each record has to have the employee name in it.

The scenario above is reduced to its specifics for an example. The SELECT uses a CTE derived table which is joined to a temp table which collects the Stage Number - the join is on the stage number to generate at least 1 record for each stage. We can generate all 5 stage numbers in an employee record collection - what we need assistance in is getting the employee name in every record.

In brief - an employee may have 3 activities in 3 stages. We have to show the stage number and employee name for all 5 stages, even though the employee has no activity for a particular stage. We're at the point where we can generate all the required stages, with their appropriate stage number, but we have been unsuccessful in populating the 'generated' record with the employee name.

Suggestions?

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[ElEye]

Would COALESCE apply?

Dave
[

http://flashspot.com

]

 

[gmmastros]

I think you will get better advice if you display sample data from the tables involved and the query you have so far.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[earljgray]

Thank you for your responses....

Here is the code snippet
create table #num (Stage INT)
insert into #num Values (1)
insert into #num Values (2)
insert into #num Values (3)
insert into #num Values (4)
insert into #num Values (5)

;with cte_Raw as (
SELECT
reps.CSRReps_user_role_id
,goal.fk_GoalGroup_Id
,goal.Goal_Id
,goal.Goal_Name
,prod.ERM_sales_cycle_stage_id
,[Open_Sales] = SUM(
Commercial_Segment_Reporting.dbo.fn_CSRProductSaleTotal(prod.ERM_deal_product_id, goal.fk_GoalBasis_Id, pgmap.fk_Goal_Id, reps.CSRReps_user_role_id)
* ISNULL(dpd.credit_override_percent, dpd.credit_percent)
)
,[Count_Open_Sales] = Count (prod.ERM_deal_product_id)
,[GoalGroup] = (CASE
WHEN Goal_Id IN (19,125,111,112,6,82) THEN 1
WHEN Goal_Id IN (21,186) THEN 2
WHEN Goal_Id IN (20,27) THEN 3
WHEN Goal_Id IN (107,2,44,81,105,106,80,86,96,109,108,90) THEN 4
ELSE 0
END)

FROM
[Reporting Control].dbo.RC_t_CSRReps as reps -- Only reps in CSR
INNER JOIN
ERM.dbo.DEAL_INVOLVED as dpd -- Reps on closed product sales
ON reps.CSRReps_user_role_id = dpd.involved_user_role_id
INNER JOIN
[Commercial_Data_Store].dbo.CDS_ts_ERMD_Open_Sales_Details as prod -- Detail of all closed sales
ON dpd.deal_id = prod.ERM_deal_id -- Product details which have been disposed
INNER JOIN
[Commercial_Segment_Reporting].dbo.CSR_GI_tx_RepGoalSets as rgset
ON reps.CSRReps_user_role_id = rgset.RepGoalSets_user_role_id -- Only for Goals CSR rep has
INNER JOIN
[GoalsAndIncentive].dbo.GI_tx_ProductToGoalMapping as pgmap
ON rgset.RepGoalSets_Goal_Id = pgmap.fk_Goal_Id -- Goals rep has
AND prod.ERM_product_id = pgmap.fk_product_id -- Only products mapped to Goals rep has
INNER JOIN
[GoalsAndIncentive].dbo.GI_tl_Goal as goal -- Goal category lookup table
ON pgmap.fk_Goal_Id = goal.Goal_Id
INNER JOIN
[GoalsAndIncentive].dbo.GI_tl_GoalBasis as gbasis -- The Basis for the goal such as referral and whether referral requires a win
ON goal.fk_GoalBasis_Id = gbasis.GoalBasis_Id

WHERE
prod.ERM_start_date>='1/1/2014' -- Products closed in reporting period (now the full 5 years)
AND gbasis.GoalBasis_IsFeeBalanceGoalType = 1 -- Only Fee, Balance, Deposits, ABL & VC goals
AND prod.ERM_action_id NOT IN(4,14,16,17) -- No Rebid sales (only for TM) and not Lease Line sales
AND CSRReps_user_role_id = 41
GROUP BY
reps.CSRReps_user_role_id
,goal.fk_GoalGroup_Id
,goal.Goal_Id
,goal.Goal_Name
,prod.ERM_sales_cycle_stage_id
)
Select
--GoalGroup
stage
,CSRReps_user_role_id
,ERM_sales_cycle_stage_id = isNULL(ERM_sales_cycle_stage_id,stage)
,'Open_Sales_Loan_Leases_Sum' = SUM(CASE WHEN GoalGroup IN (1) THEN Open_Sales ELSE 0 END)
,'Open_Sales_New_Deposits_Sum' = SUM(CASE WHEN GoalGroup IN (2) THEN Open_Sales ELSE 0 END)
,'Open_Sales_New_Credit_Fees_Sum' = SUM(CASE WHEN GoalGroup IN (3) THEN Open_Sales ELSE 0 END)
,'Open_Sales_New_NonCredit_Fees_Sum' = SUM(CASE WHEN GoalGroup IN (4) THEN Open_Sales ELSE 0 END)

,'Open_Sales_Loan_Leases_Count' = SUM(CASE WHEN GoalGroup IN (1) THEN Count_Open_Sales ELSE 0 END)
,'Open_Sales_New_Deposits_Count' = SUM(CASE WHEN GoalGroup IN (2) THEN Count_Open_Sales ELSE 0 END)
,'Open_Sales_New_Credit_Fees_Count' = SUM(CASE WHEN GoalGroup IN (3) THEN Count_Open_Sales ELSE 0 END)
,'Open_Sales_New_NonCredit_Fees_Count' = SUM(CASE WHEN GoalGroup IN (4) THEN Count_Open_Sales ELSE 0 END)
from
cte_Raw
right JOIN #num on stage = ERM_sales_cycle_stage_id
group by
CSRReps_user_role_id
, ERM_sales_cycle_stage_id
,GoalGroup
,stage
Order by
ERM_sales_cycle_stage_id

The result:

stage CSRReps_user_role_id ERM_sales_cycle_stage_id Open_Sales_Loan_Leases_Sum Open_Sales_New_Deposits_Sum Open_Sales_New_Credit_Fees_Sum Open_Sales_New_NonCredit_Fees_Sum Open_Sales_Loan_Leases_Count Open_Sales_New_Deposits_Count Open_Sales_New_Credit_Fees_Count Open_Sales_New_NonCredit_Fees_Count
1 NULL 1 0.000000000 0.000000000 0.000000000 0.000000000 0 0 0 0
2 41 2 0.000000000 0.000000000 0.000000000 20250.000000000 0 0 0 1
3 41 3 2449753.000000000 0.000000000 0.000000000 0.000000000 5 0 0 0
3 41 3 0.000000000 0.000000000 4000.000000000 0.000000000 0 0 5 0
3 41 3 0.000000000 0.000000000 0.000000000 5000.000000000 0 0 0 4
4 NULL 4 0.000000000 0.000000000 0.000000000 0.000000000 0 0 0 0
5 NULL 5 0.000000000 0.000000000 0.000000000 0.000000000 0 0 0 0

We need to get 41 into the second column replacing the NULL


Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[philhege]

If your CTE is limited to the value 41, you can use the same sort of ISNULL statement that you use for ERM_sales_cycle_stage_ID. Assuming this is a procedure or a parameterized view, you can pass the user_role_id to the process. Otherwise, it's a lot more convoluted.

-----------
With business clients like mine, you'd be better off herding cats.