Update statement returning 0 rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Update statement returning 0 rows?

Author

Topic

Goldengame
Starting Member

2 Posts

Posted - 2009-11-05 : 08:34:35

Hi, Please help. Im trying to use a simple update statement to update the rocrds in my temp table but it keeps returning 0 rows affected?

Code:

SELECT
Main.Year,
Main.Month,
Main.MonthNo,
P.Total_Pol_Started,
P.Set_MP,
Main.Canx_Month,
COUNT(ProductID) as Canx_Policies,
SUM(MonthlyPayment) as Canx_Policies_Value

INTO #TEMP7
FROM(

SELECT Cal.Year,
Cal.Month,
Cal.MonthNo,
COUNT(active.ProductID) as Total_Pol_Started,
SUM(MonthlyPayment) AS Set_MP,
M.Campaign
FROM advsvr25.rgcentral.dbo.vw_PPI_ActivePPICoverDetails active
LEFT JOIN advsvr25.mi.dbo.Resources_Calendar cal
ON CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, active.startdate))) = cal.Date
LEFT JOIN (SELECT ProductID ,
MIN(Calldate)FirstCall,
MIN(DDI)DDI
FROM advsvr25.rgcentral.dbo.productcalllog
GROUP BY Productid) call
ON active.productid = call.productid
LEFT JOIN (SELECT DISTINCT DDI, Campaign
FROM advsvr25.PaymentguardMI.dbo.tbl_MarketingDimension) M
ON Call.DDI = M.DDI
WHERE Startdate is not null
GROUP BY
Cal.Year,
Cal.Month,
Cal.MonthNo,
Campaign) p

JOIN
(SELECT Cal.Year,
Cal.Month,
Cal.MonthNo,
active.ProductID,
MonthlyPayment,
Startdate,
Cancelledon,
Call.DDI,
M.Campaign,
CASE WHEN DATEDIFF(mm,StartDate,CancelledOn)<=1 THEN 1 ELSE DATEDIFF(mm,StartDate,CancelledOn)END AS Canx_Month

FROM advsvr25.rgcentral.dbo.vw_PPI_ActivePPICoverDetails active

JOIN advsvr25.mi.dbo.Resources_Calendar cal
ON CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, active.startdate))) = cal.Date

JOIN (SELECT ProductID ,
MIN(Calldate)FirstCall,
MIN(DDI)DDI
FROM advsvr25.rgcentral.dbo.productcalllog
GROUP BY Productid) call
ON active.productid = call.productid

LEFT JOIN (SELECT DISTINCT DDI, Campaign
FROM advsvr25.PaymentguardMI.dbo.tbl_MarketingDimension) M
ON Call.DDI = M.DDI

GROUP
BY Cal.Year,
Cal.Month,
Cal.MonthNo,
active.ProductID,
MonthlyPayment,
Startdate,
Cancelledon,
Call.DDI,
M.Campaign )main

ON p.Year = main.year and P.Month = Main.Month and p.Campaign = Main.Campaign

WHERE p.Campaign = 'PG LFUK internal Transfer' --and canx_month is not null

GROUP
BY Main.Year,
Main.Month,
Main.MonthNo,
P.Total_Pol_Started,
P.Set_MP,
Main.Canx_Month
ORDER
BY Main.Year,
MonthNO,
CAST (Canx_Month AS INT)

UPDATE #temp7
SET canx_policies = 0
WHERE canx_month = null

Thanks in advance

Help me , im new at this :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-05 : 08:36:02

[code]UPDATE #temp7
SET canx_policies = 0
WHERE canx_month IS null[/code]

KH
[spoiler]Time is always against us[/spoiler]

Goldengame
Starting Member

2 Posts

Posted - 2009-11-05 : 08:37:01

quote:
Originally posted by khtan

UPDATE #temp7
SET canx_policies = 0
WHERE canx_month IS null

KH
[spoiler]Time is always against us[/spoiler]

It was that simple? Thankyou so much!!

Help me , im new at this :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources