|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Cursor working in this stored Procedure??I need some help with my cursor in a stored procedure in SQL Query Analyzer, please. It takes so long to finish, that I break out of it every time and I can not see if it actually works. What it needs to do is the following: I have a file with say 150 000 items on it and for each item, there is (I think) 62 months of sales history. That info needs to go into a temp table of some sort and create a entry for each item and the sales qty with a sales date to it, so if I have Item A and 62 months of sales history it will be something like this: Item Date Qty A 31-10-2005 5 A 30-09-2005 2 A 31-08-2005 3 etc. Where it would be like this in the file: A, 5, 2, 3.....(entries until 62 times) B, ......... for 62 times etc. Could Anyone please help me with it? Here is the code for the cursor: /*******************CODE HERE**************/ /***********************************************************************************/ /* cusp_Import_Isuzu_SalesHistory */ /***********************************************************************************/ /* This procedure imports Isuzu's data into SQL. Then Cleans it and exports .txt */ /* files for JustEnough to import and run a daily. */ /***********************************************************************************/ /* This procedure gets called from: cuspt_Import_Isuzu */ /* Update History: */ /* */ /* 01-11-2005 Marlene Erasmus Created the procedure */ /***********************************************************************************/ --EXEC cusp_Import_Data @strFTPDestPath, @strToday, @strImport IF EXISTS (SELECT * FROM sysobjects WHERE name = 'cusp_Import_Isuzu_SalesHistory' AND TYPE = 'P') DROP PROCEDURE cusp_Import_Isuzu_SalesHistory GO CREATE PROCEDURE cusp_Import_Isuzu_SalesHistory ( @strFTPDestPath VARCHAR(220) , @strToday VARCHAR(10) , @strImport VARCHAR(2000) , @strFTPFile VARCHAR(20) , @strBackupImport VARCHAR(220) , @strArchivePath VARCHAR(200) , @strText VARCHAR(2000) OUTPUT ) /* @strFTPDestPath = 'E:\GMSA\Isuzu\Isuzu_Import_Files\FileName' @strToday = '01-11-2005' @strImport = for import into SQL @strFTPFile = 'FileName' @strBackupImport = 'E:\GMSA\Isuzu\Isuzu_Import_Files\' + @strToday + '\' @strArchivePath = 'E:\GMSA\Isuzu\Isuzu_Archived_Data\Archived_Isuzu_Files\' + @strToday */ AS BEGIN SET NOCOUNT ON --This should allow your recordset to be populated. DECLARE @intResult INT DECLARE @intTrigger INT DECLARE @intCopy INT DECLARE @intArchive INT DECLARE @strCopy VARCHAR(200) DECLARE @strFile VARCHAR(50) DECLARE @strSpace VARCHAR(1) DECLARE @strDelete VARCHAR(200) --DECLARE @strText VARCHAR(1000) -- Variables for Cursor - Duplicates DECLARE @intTotal INT DECLARE @strLoc VARCHAR(50) DECLARE @strItem VARCHAR(50) DECLARE @strOrder VARCHAR(50) DECLARE @strTable VARCHAR(100) DECLARE @strSite VARCHAR(30) DECLARE @strOrderNo VARCHAR(50) DECLARE @strItemNo VARCHAR(50) DECLARE @strSQL VARCHAR(2000) SET @strText = NULL SET @strSpace = ' ' SET @strFile = @strFTPFile + ' /Y' SET @strCopy = 'copy ' + @strFTPDestPath + @strSpace + @strBackupImport + @strFile SET @strDelete = 'del ' + @strFTPDestPath + ' /Q' SET @intTrigger = 2 SET @intCopy = 2 SET @intArchive = 2 SET @intResult = 2 EXEC @intTrigger = cusp_CheckTriggerFile @strFTPDestPath, 0 IF @intTrigger = 0 BEGIN DELETE FROM FGHIST DELETE FROM tbl_SalesHistory --Importing the data EXEC master.dbo.xp_cmdshell 'cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn' EXEC @intResult = master.dbo.xp_cmdshell @strImport, no_output --EXEC master.dbo.xp_cmdshell 'DTSrun.exe -F "E:\GMSA\Isuzu\Isuzu_Import_Files\ImportDirectories\Isuzu_Import_DTS\Import_FG_MAST_DTS.dts" -Uimportuser -Pimport -A"ServerName"="JE" -A"FileName"="E:\GMSA\Isuzu\Isuzu_Import_Files\FGMAST.TXT" -A"DatabaseName"="IsuzuCleanUp"', no_output IF @intResult = 0 BEGIN --Table for Duplicates CREATE TABLE #tbl_Unique (location VARCHAR(50) ,item VARCHAR(50) ,OrderNo VARCHAR(50) ,Qty INT ) UPDATE FGHIST SET material_number = LTRIM(material_number) UPDATE FGHIST SET material_number = RTRIM(material_number) INSERT tbl_SalesHistory (SiteCode, ItemCode, SalesHistoryDate, SalesQty) SELECT Location_Code, material_number, '2005-10-31', Current_Month_Sales FROM FGMAST -- Append ItemMaster and Inventory Items from Sales INSERT tbl_ItemMaster (ItemCode) SELECT tbl_SalesHistory.ItemCode FROM tbl_SalesHistory LEFT JOIN tbl_ItemMaster ON tbl_SalesHistory.ItemCode = tbl_ItemMaster.ItemCode WHERE tbl_ItemMaster.ItemCode Is NULL UPDATE tbl_ItemMaster SET ProductDescription = 'From SalesHistory' WHERE ProductDescription IS NULL INSERT tbl_Inventory (ItemCode, SiteCode, UnitOfIssue, MinimumSafetyStockUnits, HostSystemLeadTimeDays, StockingIndicator) SELECT tbl_SalesHistory.ItemCode, tbl_SalesHistory.SiteCode, 'EA', 0, 0, 'Y' FROM tbl_SalesHistory LEFT JOIN tbl_Inventory ON tbl_SalesHistory.ItemCode = tbl_Inventory.ItemCode AND tbl_Inventory.SiteCode = tbl_SalesHistory.SiteCode WHERE tbl_Inventory.ItemCode IS NULL UPDATE tbl_Inventory SET UnitOfIssue = 'EA' , MinimumSafetyStockUnits = 0 , HostSystemLeadTimeDays = 0 , StockingIndicator = 'Y' WHERE UnitOfIssue IS NULL -- Update Sales from FGHIST ---------------------------- /*Current-month-sales = October 2005 current-1 = September 2005 current-2 = Augustus 2005 */ DECLARE @intCounter INT DECLARE @strColumn VARCHAR(12) DECLARE @strDate VARCHAR(12) DECLARE @strTheDate VARCHAR(12) DECLARE @dCurrentDate VARCHAR (12) DECLARE @SQLString VARCHAR(8000) DECLARE @cSelect VARCHAR(8000) DECLARE @cFrom VARCHAR(8000) DECLARE @cWhere VARCHAR(8000) DECLARE @strDay VARCHAR(3) CREATE TABLE #tbl_SalesPeriod ( SequenceNumber INT ,vMonth VARCHAR(2) ,iYear INT ) CREATE INDEX #tbl_SalesPeriod_Clustered_IDX ON #tbl_SalesPeriod(SequenceNumber) SET @intCounter = 0 SET @dCurrentDate = '2005-10-31' -- GetDate() SET @SQLString = '' SET @cSelect = '' SET @cFrom = '' SET @cWhere = '' SET @strDay = '' BEGIN INSERT INTO #tbl_SalesPeriod SELECT SequenceNumber, vMonth, iYear FROM udf_GetSalesPeriod(@dCurrentDate) -- user defined function -- Reading through that data DECLARE @iNum INT DECLARE @vM VARCHAR(2) DECLARE @iYear INT DECLARE Cursor_SalesPeriod CURSOR FOR SELECT SequenceNumber, vMonth, iYear FROM #tbl_SalesPeriod ORDER BY SequenceNumber ASC OPEN Cursor_SalesPeriod -- Perform the first fetch. FETCH NEXT FROM Cursor_SalesPeriod INTO @iNum, @vM, @iYear -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString = '' SET @cSelect = '' SET @cFrom = '' SET @cWhere = '' SET @strDay = '' --SELECT @iNum, @vM, @iYear IF @iNum <> 0 BEGIN SET @strColumn = '[Current-' + CAST(@iNum AS VARCHAR(3)) + ']' -- Leap year IF @iYear % 4 = 0 BEGIN IF @vM % 2 = 0 BEGIN IF @vM = 2 SET @strDay = '-29' ELSE IF @vM = 8 OR @vM = 10 OR @vM = 12 SET @strDay = '-31' ELSE SET @strDay = '-30' END ELSE IF @vM = 9 OR @vM = 11 SET @strDay = '-30' ELSE SET @strDay = '-31' END ELSE -- Not Leap Year BEGIN IF @vM % 2 = 0 BEGIN IF @vM = 2 SET @strDay = '-28' ELSE IF @vM = 8 OR @vM = 10 OR @vM = 12 SET @strDay = '-31' ELSE SET @strDay = '-30' END ELSE IF @vM = 9 OR @vM = 11 SET @strDay = '-30' ELSE SET @strDay = '-31' END SET @strDate = CAST(@iYear AS VARCHAR(5)) + '-' + CAST(@vM AS VARCHAR(5)) + @strDay SET @cSelect = ' INSERT INTO tbl_SalesHistory (SiteCode, ItemCode, SalesQty) SELECT Location_Code, material_number, ' + @strColumn SET @cFrom = ' FROM FGHIST ' SET @cWhere = ' WHERE ' + @strColumn + ' <> 0' --BEGIN TRANSACTION EXEC (@cSelect+@cFrom+@cWhere) UPDATE tbl_SalesHistory SET SalesHistoryDate = @strDate WHERE SalesHistoryDate IS NULL IF @@ERROR = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION FETCH NEXT FROM Cursor_SalesPeriod INTO @iNum, @vM, @iYear END -- IF @iNum <> 0 END -- WHILE @@FETCH_STATUS = 0 CLOSE Cursor_SalesPeriod DEALLOCATE Cursor_SalesPeriod END DROP TABLE #tbl_SalesPeriod -- BACKUPS EXEC @intCopy = master..xp_cmdshell @strCopy, no_output -- Archive Dir SET @strCopy = 'copy ' + @strFTPDestPath + @strSpace + @strArchivePath + '\' + @strFile EXEC @intArchive = master..xp_cmdshell @strCopy, no_output IF @intCopy = 0 AND @intArchive = 0 EXEC master..xp_cmdshell @strDelete, no_output END END --File Exists ELSE BEGIN IF @strText IS NULL SET @strText = @strFTPFile ELSE SET @strText = @strText + ', ' + @strFTPFile END END GO GRANT EXECUTE ON cusp_Import_Isuzu_SalesHistory TO PUBLIC GO /**********************CODE ENDS****************************/ Thanks in advance for any help! Is using a cursor the only option in this case?
Please, show us more details of the source file - I believe there's a set-based solution in here somewhere. ML It doesn't seem like you need the cursor here and your code will almost
certainly be more efficient without out it. If you want more help, the following article describes the best way to specify a problem such as this. http://www.aspfaq.com/etiquette.asp?id=5006 -- David Portas SQL Server MVP -- Hi there,
There is no need to use cursors. After having a look at it for a while, I guess this might help you. What I assume is that you transfer a row into the History table and then update the newly added row's date we can do that in the insert statement. INSERT INTO tbl_SalesHistory (SiteCode, ItemCode, SalesQty,SalesHistoryDate ) SELECT Location_Code, material_number, '[Current-' + CAST(iNum AS VARCHAR(3)) + ']' , Convert(varchar,AA1.y) + Right('0'+Convert(varchar,AA1.m) , 2) + Case When AA1.y % 4 = 0 Then '29' Else AA.days End From ( Select 1 m , '31' days Union All Select 2 , '28' Union All Select 3 , '31' Union All Select 4 , '30' Union All Select 5 , '31' Union All Select 6 , '30' Union All Select 7 , '31' Union All Select 8 , '31' Union All Select 9 , '30' Union All Select 10 , '31' Union All Select 11 , '30' Union All Select 12 , '31' ) AA Inner Join ( --Select 4 m , 2005 y SELECT SequenceNumber iNum, vMonth as M, iYear As Y FROM #tbl_SalesPeriod ) AA1 On AA.m = AA1.m With Warm regards Jatinder Singh Dear Jatinder
Thanks for your help! I am going to try it now and let you know. Just one thing, from the point of: -- Update Sales from FGHIST ---------------------------- you replace all that with your insert statement? Thanks Marlene What am I doing wrong here in this procedure, first it did not want to
convert the varchar to float, then i changed it to BIGINT and then it just kept on running and then nothing was in the tbl_SalesHistory? Please help! CODE: DECLARE @intResult INT DECLARE @intTrigger INT DECLARE @intCopy INT DECLARE @intArchive INT DECLARE @strCopy VARCHAR(200) DECLARE @strFile VARCHAR(50) DECLARE @strSpace VARCHAR(1) DECLARE @strDelete VARCHAR(200) DECLARE @intTotal INT DECLARE @strLoc VARCHAR(50) DECLARE @strItem VARCHAR(50) DECLARE @strOrder VARCHAR(50) DECLARE @strTable VARCHAR(100) DECLARE @strSite VARCHAR(30) DECLARE @strOrderNo VARCHAR(50) DECLARE @strItemNo VARCHAR(50) DECLARE @strSQL VARCHAR(2000) DECLARE @strText VARCHAR(2000) Declare @strFTPFile VARCHAR(2000) declare @strFTPDestPath VARCHAR(2000) declare @strBackupImport VARCHAR(2000) declare @strArchivePath VARCHAR(2000) SET @strText = NULL SET @strSpace = ' ' SET @strFile = @strFTPFile + ' /Y' SET @strCopy = 'copy ' + @strFTPDestPath + @strSpace + @strBackupImport + @strFile SET @strDelete = 'del ' + @strFTPDestPath + ' /Q' SET @intTrigger = 2 SET @intCopy = 2 SET @intArchive = 2 SET @intResult = 2 /* UPDATE FGHIST SET material_number = LTRIM(material_number) UPDATE FGHIST SET material_number = RTRIM(material_number) -- Update the minus after the qty UPDATE FGHIST SET [Current-1] = '-' + REPLACE([Current-1],'-','') WHERE [Current-1] LIKE '%-' UPDATE FGHIST SET [Current-2] = '-' + REPLACE([Current-2],'-','') WHERE [Current-2] LIKE '%-' UPDATE FGHIST SET [Current-3] = '-' + REPLACE([Current-3],'-','') WHERE [Current-3] LIKE '%-' UPDATE FGHIST SET [Current-4] = '-' + REPLACE([Current-4],'-','') WHERE [Current-4] LIKE '%-' UPDATE FGHIST SET [Current-4] = '-' + REPLACE([Current-1],'-','') WHERE [Current-1] LIKE '%-' UPDATE FGHIST SET [Current-5] = '-' + REPLACE([Current-5],'-','') WHERE [Current-5] LIKE '%-' UPDATE FGHIST SET [Current-6] = '-' + REPLACE([Current-6],'-','') WHERE [Current-6] LIKE '%-' UPDATE FGHIST SET [Current-7] = '-' + REPLACE([Current-7],'-','') WHERE [Current-7] LIKE '%-' UPDATE FGHIST SET [Current-8] = '-' + REPLACE([Current-8],'-','') WHERE [Current-8] LIKE '%-' UPDATE FGHIST SET [Current-9] = '-' + REPLACE([Current-9],'-','') WHERE [Current-9] LIKE '%-' UPDATE FGHIST SET [Current-10] = '-' + REPLACE([Current-10],'-','') WHERE [Current-10] LIKE '%-' UPDATE FGHIST SET [Current-11] = '-' + REPLACE([Current-11],'-','') WHERE [Current-11] LIKE '%-' UPDATE FGHIST SET [Current-12] = '-' + REPLACE([Current-12],'-','') WHERE [Current-12] LIKE '%-' UPDATE FGHIST SET [Current-13] = '-' + REPLACE([Current-13],'-','') WHERE [Current-13] LIKE '%-' UPDATE FGHIST SET [Current-14] = '-' + REPLACE([Current-14],'-','') WHERE [Current-14] LIKE '%-' UPDATE FGHIST SET [Current-15] = '-' + REPLACE([Current-15],'-','') WHERE [Current-15] LIKE '%-' UPDATE FGHIST SET [Current-16] = '-' + REPLACE([Current-16],'-','') WHERE [Current-16] LIKE '%-' UPDATE FGHIST SET [Current-17] = '-' + REPLACE([Current-17],'-','') WHERE [Current-17] LIKE '%-' UPDATE FGHIST SET [Current-18] = '-' + REPLACE([Current-18],'-','') WHERE [Current-18] LIKE '%-' UPDATE FGHIST SET [Current-19] = '-' + REPLACE([Current-19],'-','') WHERE [Current-19] LIKE '%-' UPDATE FGHIST SET [Current-20] = '-' + REPLACE([Current-20],'-','') WHERE [Current-20] LIKE '%-' UPDATE FGHIST SET [Current-21] = '-' + REPLACE([Current-21],'-','') WHERE [Current-21] LIKE '%-' UPDATE FGHIST SET [Current-22] = '-' + REPLACE([Current-22],'-','') WHERE [Current-22] LIKE '%-' UPDATE FGHIST SET [Current-23] = '-' + REPLACE([Current-23],'-','') WHERE [Current-23] LIKE '%-' UPDATE FGHIST SET [Current-24] = '-' + REPLACE([Current-24],'-','') WHERE [Current-24] LIKE '%-' UPDATE FGHIST SET [Current-25] = '-' + REPLACE([Current-25],'-','') WHERE [Current-25] LIKE '%-' UPDATE FGHIST SET [Current-26] = '-' + REPLACE([Current-26],'-','') WHERE [Current-26] LIKE '%-' UPDATE FGHIST SET [Current-27] = '-' + REPLACE([Current-27],'-','') WHERE [Current-27] LIKE '%-' UPDATE FGHIST SET [Current-28] = '-' + REPLACE([Current-28],'-','') WHERE [Current-28] LIKE '%-' UPDATE FGHIST SET [Current-29] = '-' + REPLACE([Current-29],'-','') WHERE [Current-29] LIKE '%-' UPDATE FGHIST SET [Current-30] = '-' + REPLACE([Current-30],'-','') WHERE [Current-30] LIKE '%-' UPDATE FGHIST SET [Current-31] = '-' + REPLACE([Current-31],'-','') WHERE [Current-31] LIKE '%-' UPDATE FGHIST SET [Current-32] = '-' + REPLACE([Current-32],'-','') WHERE [Current-32] LIKE '%-' UPDATE FGHIST SET [Current-33] = '-' + REPLACE([Current-33],'-','') WHERE [Current-33] LIKE '%-' UPDATE FGHIST SET [Current-34] = '-' + REPLACE([Current-34],'-','') WHERE [Current-34] LIKE '%-' UPDATE FGHIST SET [Current-35] = '-' + REPLACE([Current-35],'-','') WHERE [Current-35] LIKE '%-' UPDATE FGHIST SET [Current-36] = '-' + REPLACE([Current-36],'-','') WHERE [Current-36] LIKE '%-' UPDATE FGHIST SET [Current-37] = '-' + REPLACE([Current-37],'-','') WHERE [Current-37] LIKE '%-' UPDATE FGHIST SET [Current-38] = '-' + REPLACE([Current-38],'-','') WHERE [Current-38] LIKE '%-' UPDATE FGHIST SET [Current-39] = '-' + REPLACE([Current-39],'-','') WHERE [Current-39] LIKE '%-' UPDATE FGHIST SET [Current-40] = '-' + REPLACE([Current-40],'-','') WHERE [Current-40] LIKE '%-' UPDATE FGHIST SET [Current-41] = '-' + REPLACE([Current-41],'-','') WHERE [Current-41] LIKE '%-' UPDATE FGHIST SET [Current-42] = '-' + REPLACE([Current-43],'-','') WHERE [Current-43] LIKE '%-' UPDATE FGHIST SET [Current-44] = '-' + REPLACE([Current-44],'-','') WHERE [Current-44] LIKE '%-' UPDATE FGHIST SET [Current-45] = '-' + REPLACE([Current-45],'-','') WHERE [Current-45] LIKE '%-' UPDATE FGHIST SET [Current-46] = '-' + REPLACE([Current-46],'-','') WHERE [Current-46] LIKE '%-' UPDATE FGHIST SET [Current-47] = '-' + REPLACE([Current-47],'-','') WHERE [Current-47] LIKE '%-' UPDATE FGHIST SET [Current-48] = '-' + REPLACE([Current-48],'-','') WHERE [Current-48] LIKE '%-' UPDATE FGHIST SET [Current-49] = '-' + REPLACE([Current-49],'-','') WHERE [Current-49] LIKE '%-' UPDATE FGHIST SET [Current-50] = '-' + REPLACE([Current-50],'-','') WHERE [Current-50] LIKE '%-' UPDATE FGHIST SET [Current-51] = '-' + REPLACE([Current-51],'-','') WHERE [Current-51] LIKE '%-' UPDATE FGHIST SET [Current-52] = '-' + REPLACE([Current-52],'-','') WHERE [Current-52] LIKE '%-' UPDATE FGHIST SET [Current-53] = '-' + REPLACE([Current-53],'-','') WHERE [Current-53] LIKE '%-' UPDATE FGHIST SET [Current-54] = '-' + REPLACE([Current-54],'-','') WHERE [Current-54] LIKE '%-' UPDATE FGHIST SET [Current-55] = '-' + REPLACE([Current-55],'-','') WHERE [Current-55] LIKE '%-' UPDATE FGHIST SET [Current-56] = '-' + REPLACE([Current-56],'-','') WHERE [Current-56] LIKE '%-' UPDATE FGHIST SET [Current-57] = '-' + REPLACE([Current-57],'-','') WHERE [Current-57] LIKE '%-' UPDATE FGHIST SET [Current-58] = '-' + REPLACE([Current-58],'-','') WHERE [Current-58] LIKE '%-' UPDATE FGHIST SET [Current-59] = '-' + REPLACE([Current-59],'-','') WHERE [Current-59] LIKE '%-' UPDATE FGHIST SET [Current-60] = '-' + REPLACE([Current-60],'-','') WHERE [Current-60] LIKE '%-' UPDATE FGHIST SET [Current-61] = '-' + REPLACE([Current-61],'-','') WHERE [Current-61] LIKE '%-' INSERT tbl_SalesHistory (SiteCode, ItemCode, SalesHistoryDate, SalesQty) SELECT Location_Code, material_number, '2005-10-31', Current_Month_Sales FROM FGMAST -- Append ItemMaster and Inventory Items from Sales INSERT tbl_ItemMaster (ItemCode) SELECT tbl_SalesHistory.ItemCode FROM tbl_SalesHistory LEFT JOIN tbl_ItemMaster ON tbl_SalesHistory.ItemCode = tbl_ItemMaster.ItemCode WHERE tbl_ItemMaster.ItemCode Is NULL UPDATE tbl_ItemMaster SET ProductDescription = 'From SalesHistory' WHERE ProductDescription IS NULL INSERT tbl_Inventory (ItemCode, SiteCode, UnitOfIssue, MinimumSafetyStockUnits, HostSystemLeadTimeDays, StockingIndicator) SELECT tbl_SalesHistory.ItemCode, tbl_SalesHistory.SiteCode, 'EA', 0, 0, 'Y' FROM tbl_SalesHistory LEFT JOIN tbl_Inventory ON tbl_SalesHistory.ItemCode = tbl_Inventory.ItemCode AND tbl_Inventory.SiteCode = tbl_SalesHistory.SiteCode WHERE tbl_Inventory.ItemCode IS NULL UPDATE tbl_Inventory SET UnitOfIssue = 'EA' , MinimumSafetyStockUnits = 0 , HostSystemLeadTimeDays = 0 , StockingIndicator = 'Y' WHERE UnitOfIssue IS NULL */ -- Update Sales from FGHIST ---------------------------- /*Current-month-sales = October 2005 current-1 = September 2005 current-2 = Augustus 2005 */ DECLARE @intCounter INT DECLARE @strColumn VARCHAR(12) DECLARE @strDate VARCHAR(12) DECLARE @strTheDate VARCHAR(12) DECLARE @dCurrentDate VARCHAR (12) DECLARE @SQLString VARCHAR(8000) DECLARE @cSelect VARCHAR(8000) DECLARE @cFrom VARCHAR(8000) DECLARE @cWhere VARCHAR(8000) DECLARE @strDay VARCHAR(3) CREATE TABLE #tbl_SalesPeriod ( SequenceNumber INT ,vMonth VARCHAR(2) ,iYear INT ) CREATE UNIQUE INDEX #tbl_SalesPeriod_IDX ON #tbl_SalesPeriod(SequenceNumber) SET @intCounter = 0 SET @dCurrentDate = '2005-10-31' -- GetDate() SET @SQLString = '' SET @cSelect = '' SET @cFrom = '' SET @cWhere = '' SET @strDay = '' BEGIN INSERT INTO #tbl_SalesPeriod SELECT SequenceNumber, vMonth, iYear FROM udf_GetSalesPeriod(@dCurrentDate) -- user defined function -- Reading through that data DECLARE @iNum INT DECLARE @vM VARCHAR(2) DECLARE @iYear INT DECLARE Cursor_SalesPeriod CURSOR FOR SELECT SequenceNumber, vMonth, iYear FROM #tbl_SalesPeriod ORDER BY SequenceNumber ASC OPEN Cursor_SalesPeriod -- Perform the first fetch. FETCH NEXT FROM Cursor_SalesPeriod INTO @iNum, @vM, @iYear Declare @Update VARCHAR(200) DECLARE @set VARCHAR(2000) DECLARE @Where VARCHAR(2000) -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 INSERT INTO tbl_SalesHistory (SiteCode, ItemCode, SalesQty, SalesHistoryDate) SELECT Location_Code, material_number, CAST('[Current-' + CAST(iNum AS VARCHAR(3)) + ']' AS BIGINT), Convert(varchar,AA1.y) + Right('0'+ Convert(varchar,AA1.m) , 2) + Case When AA1.y % 4 = 0 Then '29' Else AA.days End From FGHIST, ( Select 1 m , '31' days Union All Select 2 , '28' Union All Select 3 , '31' Union All Select 4 , '30' Union All Select 5 , '31' Union All Select 6 , '30' Union All Select 7 , '31' Union All Select 8 , '31' Union All Select 9 , '30' Union All Select 10 , '31' Union All Select 11 , '30' Union All Select 12 , '31' ) AA Inner Join ( --Select 4 m , 2005 y SELECT SequenceNumber iNum, vMonth as M, iYear As Y FROM #tbl_SalesPeriod ) AA1 On AA.m = AA1.m FETCH NEXT FROM Cursor_SalesPeriod INTO @iNum, @vM, @iYear END -- WHILE @@FETCH_STATUS = 0 CLOSE Cursor_SalesPeriod DEALLOCATE Cursor_SalesPeriod /*END*/ --Begin Sorry for bugging you so many times! I took the insert into statement
out and only selected what should be inserted, i got the following output: location_Code Material_Number SalesQty Date 503 1090420300 [Current-0] 20051031 Now that is basically correct, because the SalesQty would be that column, but not the column name but the contents of column 'current-0' and the date would be fine. That is why it says cannot convert varchar to float or bigint or whatever the type of data is that is inside the SalesQty. Could you please assist?? Thanks so much for all the efforts! Marlene Hi David
This is the DDL for the FGHIST table which will have the data that needs to be converted into the tbl_SalesHistory format: CREATE TABLE [FGHIST] ( [Location_Code] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [material_number] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-1] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-3] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-4] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-5] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-6] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-7] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-8] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-9] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-10] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-11] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-12] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-13] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-14] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-15] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-16] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-17] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-18] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-19] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-20] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-21] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-22] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-23] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-24] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-25] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-26] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-27] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-28] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-29] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-30] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-31] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-32] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-33] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-34] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-35] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-36] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-37] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-38] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-39] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-40] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-41] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-42] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-43] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-44] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-45] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-46] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-47] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-48] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-49] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-50] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-51] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-52] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-53] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-54] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-55] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-56] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-57] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-58] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-59] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-60] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [Current-61] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO Now the table: tbl_SalesHistory format: CREATE TABLE [tbl_SalesHistory] ( [SiteCode] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL , [ItemCode] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [SalesHistoryDate] [datetime] NULL , [SalesQty] [float] NULL , [StockOnHand] [bigint] NULL ) ON [PRIMARY] GO Now for some sample data that needs to populate the FGHIST Table (the sample data is first a Location_Code, ItemCode and then about 62 month's back sales history data): 503,1090420300,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,8,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0 503,9991165160,0,1,0,0,2,0,0,0,5,0,0,2,0,0,0,4,2,2,2,1,0,0,4,0,9,2,0,6,3,1,5,2,0,2,0,5,0,0,0,2,0,0,0,0,0,0,0,0,2,0,0,1,0,0,2,4,2,3,12,3,2 503,1095032410,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,8,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,24,0 503,1090003830,12,8,0,19,24,1,0,10,11,27,0,4,10,0,31,26,18,10,2,0,8,6,26,18,10,10,0,26,10,27,0,4,0,5,4,12,0,2,15,30,1,6,10,13,4,0,8,12,10,0,12,13,21,0,61,0,32,14,32,50,0 503,1371241690,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1373190090,3,2,1,2,0,4,0,4,10,5,11,0,0,0,0,4,12,8,0,0,6,4,0,0,4,4,12,0,4,0,0,0,4,4,0,9,0,0,0,5,8,0,0,8,0,0,0,0,0,0,7,0,0,0,0,4,4,0,0,0,0 503,1090880060,8,10,12,16,0,8,1,15,18,4,28,0,6,6,0,34,24,16,0,0,11,4,0,42,8,16,24,0,8,0,0,0,4,12,0,4,0,0,0,18,20,0,8,20,8,0,0,1,0,8,16,0,0,0,8,8,8,0,0,0,0 503,1513003520,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,2,1,3,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1513109520,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,108064800,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1096607400,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,9091607100,1,2,6,0,1,3,1,0,0,0,0,6,1,0,0,2,1,0,1,0,0,0,0,1,0,0,0,0,0,1,0,4,0,0,2,2,0,0,0,0,0,0,1,0,0,1,0,1,0,0,0,3,1,0,0,0,2,1,0,1,1 503,1517711130,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1517730840,0,0,0,0,1,0,0,0,0,0,2,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,1,0,0,0,0,0,0,0 503,1517790840,8,18,1,10,8,2,4,4,0,10,4,0,8,8,0,4,4,0,4,0,0,4,0,1,0,3,4,0,6,0,0,0,4,0,8,4,0,4,0,0,4,2,0,0,0,0,0,0,0,0,14,2,6,0,4,0,1,2,0,0,0 503,208012500,0,0,0,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0 503,208012300,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0 503,1516817240,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0 503,9991105200,171,196,167,260,229,135,81,242,294,200,205,147,217,286,131,113,158,116,145,114,103,167,221,51,75,86,256,111,71,55,95,70,104,37,82,136,93,4,59,87,134,23,31,38,110,16,28,91,206,66,108,39,15,69,74,42,58,71,51,109,15 503,1420296870,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1420296880,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1098930240,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1511304740,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1516306440,1,13,1,4,1,1,0,0,2,12,2,4,7,6,4,5,4,2,4,7,4,2,0,3,1,0,0,4,6,0,2,0,5,0,3,2,0,8,6,2,0,0,0,0,2,1,3,2,0,3,4,2,2,8,2,4,4,0,1,0,0 503,1516817220,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1516817230,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1532713360,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,9091508140,0,20,20,30,10,30,20,40,30,40,20,10,30,0,60,0,20,10,20,10,10,10,10,0,10,42,0,0,0,10,10,0,20,20,0,10,10,22,20,0,0,30,0,10,10,12,18,0,10,42,34,24,35,30,47,18,0,0,84,10,20 503,1531683410,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1531682760,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1531682870,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1516816160,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1533560570,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,4,0,0,0,0,0,0,0 503,93599159,5,7,13,7,13,2,7,5,7,2,6,4,9,7,8,3,3,0,1,2,6,4,5,2,7,10,5,7,3,6,8,4,3,1,8,6,6,4,5,3,8,3,5,2,9,1,5,5,5,1,6,1,5,4,0,5,0,0,2,1,1 503,1724970490,0,3,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,2,2,1,2,1,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,1,0,2,0,0,0,0,1 503,1534532670,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 503,1534586340,0,2,2,3,4,3,6,0,2,0,0,0,7,0,4,0,5,0,0,4,0,4,6,0,0,0,3,2,2,1,0,2,2,0,0,2,4,11,0,0,0,2,2,2,2,0,4,3,0,0,4,0,0,0,0,3,0,0,0,6,0 503,1534532680,0,0,1,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0 503,9920614500,0,12,0,0,0,0,0,40,0,0,0,14,0,0,0,16,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,1,0,0,2,1,0,0,0,12,2,0,0,0,0,0,0,0,0,16,0,0,13,0,0,0,0,2,0,0 503,1534586350,2,1,0,0,0,6,2,0,1,0,0,0,0,0,0,0,1,0,0,2,0,0,2,0,0,0,0,0,2,1,0,0,2,0,0,0,2,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,2,2,0,0,0,0,2,0,0 503,1534546020,1,1,0,0,1,0,1,0,1,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0 503,1221390430,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,6,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,2,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0 Now as I said before I need to transfor this horizontal data into vertical data. Creating a date for each of those columns of saleshistory and populating the tbl_SalesHistory then. Hope this is sufficient info. Thanks for your help! Please let me know if you need more info! Kind Regards Marlene Hi There,
INSERT INTO tbl_SalesHistory (SiteCode, ItemCode, SalesQty, SalesHistoryDate) SELECT Location_Code, material_number, --------- A Minor Change NO NEED to put it in cursor (As I Assume you are trying to convert vertical data to horizontal ) Case iNum When 1 Then [Current-1] When 2 Then [Current-2] and so....on End ----------- , Convert(varchar,AA1.y) + Right('0'+ Convert(varchar,AA1.m) , 2) + Case When AA1.y % 4 = 0 Then '29' Else AA.days End >From FGHIST, (Select 1 m , '31' days Union All Select 2 , '28' Union All Select 3 , '31' Union All Select 4 , '30' Union All Select 5 , '31' Union All Select 6 , '30' Union All Select 7 , '31' Union All Select 8 , '31' Union All Select 9 , '30' Union All Select 10 , '31' Union All Select 11 , '30' Union All Select 12 , '31' ) AA Inner Join ( --Select 4 m , 2005 y SELECT SequenceNumber iNum, vMonth as M, iYear As Y FROM #tbl_SalesPeriod ) AA1 On AA.m = AA1.m With Warm regards Jatinder Singh Hi Jatinder
I have maid the changes, and it takes forever to run. Have you got any idea how long it should run on about 140 000 line items? Thanks Marlene Hi There,
If you have removed the cursor and use Permanent Table in place of #tbl_SalesPeriod With Warm regards Jatinder Singh Sorry for incomplete post.
You should not use Cursor and use Permanent Table. With Warm regards Jatinder Singh  |
|||||||||||||||||||||||
Other interesting topics