DECLARE @StartDate SMALLDATETIME;
DECLARE @EndDate SMALLDATETIME;

Set @StartDate = DateAdd(Day, Datediff(Day,0, CONVERT(DATETIME, '5/11/2014')), 0)
Set @EndDate =  DateAdd(Day, Datediff(Day,0, CONVERT(DATETIME, '5/12/2014')), 0)

-- Create Temp table
CREATE TABLE #myTemp_tbl  (
RowID int identity(1,1),
FID int,
S_ID int,
SKU varchar(10),
MBCN varchar(10),
BCN varchar(10),
Fyear varchar(4),
Fmonth varchar(2),
Reportdate datetime,
whenperiod varchar(2)
 )

DECLARE @NumberRecords int, @RowCount int
DECLARE @Reportdate smalldatetime, @FID int,@S_ID int,@SKU varchar(10),@MBCN varchar(10),@BCN varchar(10)
,@FYear varchar(4),@FMonth varchar(2),@whenPeriod varchar(2)


Insert into #myTemp_tbl(
   ReportDate,FID,S_ID,SKU,MBCN,BCN,FYear,Fmonth,whenperiod
)
select [period date],F_ID,S_id, SKU, MBCN,BCN,FiscalYear,FiscalMonth,'B'
from CFT.dbo.Magellan_Date  inner join CFT.dbo.forecast f on begindate = [period date]
inner join CFT.dbo.SalesForecast s on s.fkf_id = f.f_id
Where [period date] >= CONVERT(DATETIME, @Startdate)
and [period date] <= CONVERT(DATETIME, @enddate)
and BCN <> MBCN
Order by [period date]


-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN

Select @Reportdate = Reportdate ,@FID = FID, @S_ID = S_ID, @SKU = SKU, @MBCN = MBCN, @BCN = BCN, @Fyear = Fyear, @Fmonth = Fmonth
,@whenPeriod = whenPeriod
from #myTemp_tbl
Where RowID = @RowCount and FID IS NOT NULL


INSERT INTO [CFT].[dbo].[PM_SKUSALES_30Day_US]
([Date] ,[ALC],[Customer Number],[Sku],[QtyShip],[SO Number],ExtActualPrice,ExtRetailprice,MVN,LineNbr,Whenperiod,fkfid,fksid,Fyear,Fmonth,MBCN)
SELECT I.[Invoice Date] AS Date, LEFT(I.[Branch Customer Nbr],2) AS ALC, 
Right(I.[Branch Customer Nbr],len(I.[Branch Customer Nbr])-2) AS [Customer Number], IL.Sku, SUM(IL.[Quantity Shipped]) AS [QtyShip], 
I.[Invoice Nbr] as [SO Number],[Extended Sales],
([Retail-Price]* [Quantity Shipped])as ExtRetailprice,[VENDOR-NBR-MASTER],[Line Nbr],@whenperiod
,@fid,@s_id,@Fyear,@Fmonth,@MBCN
FROM  VMOPS.dbo.IL il INNER JOIN
VMOPS.dbo.I i ON IL.[Invoice Date] = I.[Invoice Date] AND IL.[Invoice Nbr] = I.[Invoice Nbr]
INNER JOIN VMOPS.dbo.ICP852_INRDSS IC on IC.SKU = il.SKU
INNER JOIN VMOPS.dbo.VCP852_VNRDSS VC on VC.[Vendor-Nbr] = IC.[VENDOR-NBR]
left outer join CFT.dbo.salesforecast s on s.SKU = il.SKU
WHERE  IL.Sku = @SKU and IL.[Invoice Date] =  CONVERT( VARCHAR(24), @ReportDate, 110)
and [Quantity Shipped] > 0
and I.[Branch Customer Nbr] IN  (Select [Branch Customer Nbr]
                                FROM VMOPS.dbo.MAGELLAN_Customer 
                                 where   [Master Branch Customer Nbr] = @MBCN)
group by I.[Invoice Date],I.[Branch Customer Nbr],IL.[Quantity Shipped],I.[Invoice Nbr],[Extended Sales],IL.Sku
,[Retail-Price],[Quantity Shipped],[VENDOR-NBR-MASTER],[Line Nbr]


 SET @RowCount = @RowCount + 1
END

DROP TABLE #myTemp_tbl

WITH CTE (ReportDate,FID,S_ID,SKU,MBCN,BCN,FYear,Fmonth,whenperiod)
AS
(
	select [period date],F_ID,S_id, SKU, MBCN,BCN,FiscalYear,FiscalMonth,'B'
	from CFT.dbo.Magellan_Date  inner join CFT.dbo.forecast f on begindate = [period date]
	inner join CFT.dbo.SalesForecast s on s.fkf_id = f.f_id
	Where [period date] >= CONVERT(DATETIME, @Startdate)
	and [period date] <= CONVERT(DATETIME, @enddate)
	and BCN <> MBCN
	Order by [period date]
)
INSERT INTO [CFT].[dbo].[PM_SKUSALES_30Day_US]
([Date] ,[ALC],[Customer Number],[Sku],[QtyShip],[SO Number],ExtActualPrice,ExtRetailprice,MVN,LineNbr,Whenperiod,fkfid,fksid,Fyear,Fmonth,MBCN)
SELECT I.[Invoice Date] AS Date, LEFT(I.[Branch Customer Nbr],2) AS ALC, 
Right(I.[Branch Customer Nbr],len(I.[Branch Customer Nbr])-2) AS [Customer Number], IL.Sku, SUM(IL.[Quantity Shipped]) AS [QtyShip], 
I.[Invoice Nbr] as [SO Number],[Extended Sales],
([Retail-Price]* [Quantity Shipped])as ExtRetailprice,[VENDOR-NBR-MASTER],[Line Nbr],t.whenPeriod
,t.FID,t.S_ID,t.Fyear,t.Fmonth,t.MBCN
FROM  VMOPS.dbo.IL il INNER JOIN
VMOPS.dbo.I i ON IL.[Invoice Date] = I.[Invoice Date] AND IL.[Invoice Nbr] = I.[Invoice Nbr]
INNER JOIN VMOPS.dbo.ICP852_INRDSS IC on IC.SKU = il.SKU
INNER JOIN VMOPS.dbo.VCP852_VNRDSS VC on VC.[Vendor-Nbr] = IC.[VENDOR-NBR]
INNER JOIN CTE t ON IL.Sku = t.SKU AND IL.[Invoice Date] =  CONVERT( VARCHAR(24), t.Reportdate, 110)
INNER JOIN VMOPS.dbo.MAGELLAN_Customer mc ON t.MBCN = mc.[Branch Customer Nbr]
left outer join CFT.dbo.salesforecast s on s.SKU = il.SKU
WHERE  [Quantity Shipped] > 0
group by I.[Invoice Date],I.[Branch Customer Nbr],IL.[Quantity Shipped],I.[Invoice Nbr],[Extended Sales],IL.Sku
,[Retail-Price],[Quantity Shipped],[VENDOR-NBR-MASTER],[Line Nbr]

quote:

---

Originally posted by tkizer

Add PRINT statements to figure out the issue.

For instance, after the two SETs for the rowcounts, PRINT them out to verify they are what you expect. Add PRINTs inside the loop.

I wouldn't bother with a loop though. You can achieve this with one query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

quote:

---

Originally posted by tkizer

If you needed single quotes, it would either throw syntax error or it would insert the wrong data. I suspect that the SELECT just isn't returning any data and something is amiss with it. We can't help you with that really since we don't know the business logic, table design, etc.

The CTE is just to get rid of the temp table. It's the same query. Then we join to it rather than looping.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---