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
 Transact-SQL (2005)
 Why this SP wont insert

Author  Topic 

robayb
Starting Member

7 Posts

Posted - 2015-02-26 : 13:21:10
I would greatly appreciate help - I have been pulling my hair out on this. The loop work fine but no insert happens. I'm sure it's something basic.



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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 13:49:01
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 13:54:32
This is probably close for the one query:


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]


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

robayb
Starting Member

7 Posts

Posted - 2015-02-26 : 14:07:13
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/



Thanks!
I had PRINT statements in there before and all the data looks good... but I was wondering if I have to append single quotes around anything? What do you think? I have not worked with the CTE stuff but will try to implement that too.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 14:09:00
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/
Go to Top of Page

robayb
Starting Member

7 Posts

Posted - 2015-02-26 : 16:40:41
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/



The CTE method worked like a charm - I will be using that alot from here on out!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 16:45:26


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -