query giving unexpected results

• plivingstone

  Old Hand

  Points: 397

  More actions

  January 8, 2014 at 8:51 am

  #302416

  I am working on a procedure that will check directories using `xp_DirTree` to confirm that the correct number of files have been generated by an earlier process.

  Unfortunately my query (the last part) is giving incorrect results

  declare @DateOffset int

  set @DateOffset=6

  -- Create a table variable to store user data

  DECLARE @myTable TABLE

  (

  docID INT IDENTITY(1,1),

  docRef VARCHAR(50),

  YPTMPID varchar(3),

  saveDir VARCHAR(4000),

  totalLettersExpected int,

  actualLetters int

  );

  insert @myTable SELECT docRef, YPTMPID,

  saveDir=max(Save_Directory) + cast(YEAR(GETDATE()-@DateOffset) as varchar(4)) + '\' + datename(month, GETDATE()-@DateOffset) + '\'+SUBSTRING(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101),4, 2) + '.' + LEFT(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101), 2)

  + '.' + SUBSTRING(CONVERT(CHAR(20), GETDATE()-@DateOffset, 101),7, 4),

  COUNT(*) as 'Total Letters', null

  FROM [alpsMaster].[dbo].[uExtractMonitor]

  group by docRef, YPTMPID

  order by 1,2

  select * from @myTable

  -- Get the number of rows in the looping table

  DECLARE @RowCount INT, @SQL nvarchar(4000), @LoopSQL nvarchar(4000), @Date varchar(20)

  set @Date=rtrim(CONVERT( CHAR(12), getDate()-@DateOffset, 106)) --'29 Oct 2013'

  SET @RowCount = (SELECT COUNT(docID) FROM @myTable)

  -- Declare an iterator

  DECLARE @I INT

  -- Initialize the iterator

  SET @I = 1

  -- Loop through the rows of table @myTable, and for each docRef, check the file directory for the correct number of files

  WHILE (@I <= @RowCount)

  BEGIN

  DECLARE @docRef VARCHAR(10), @saveDir VARCHAR(500), @letterCount int

  -- Get the data from table and set to variables

  SELECT @docRef = docref FROM @myTable WHERE docID = @I

  SELECT @saveDir = saveDir FROM @myTable WHERE docID = @I

  update @myTable set actualLetters = 0 where docRef=@docRef

  create table #files (subdirectory varchar(100), depth int, [file] int)

  insert into #files EXEC master.sys.xp_dirtree @saveDir,0,1;

  select @letterCount = COUNT(*) from #files

  drop table #files

  update @myTable set actualLetters = @letterCount where docRef=@docRef

  -- Increment the iterator

  SET @I = @I + 1

  END

  select * from @myTable

  Unfortunately in my last WHILE loop something is going wrong. The numbers put in the actualLetters column should match the column totalLettersExpected

  On the network the number of files does match the column *totalLettersExpected* so where have I gone wrong, and is there a better, more robust way of doing this?

  thanks

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  January 8, 2014 at 12:15 pm

  #1678543

  Do you have any DDL/Sample data for [alpsMaster].[dbo].[uExtractMonitor]? ... and some sample data for what will go into files? If you can provide that I am sure we can help you.

  From what I can see thusfar, this can be done without all the t-sql date gymnastics and without a loop (which is certainly the way to go;-) )

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Jeff Moden

  SSC Guru

  Points: 1004311

  More actions

  January 8, 2014 at 1:26 pm

  #1678573

  xp_DirTree returns the names of files AND directories. If you want to count just the files, then you have to include the "secret" 3rd operand for xp_DirTree and then count only the "files" in the temp table.

  insert into #files EXEC master.sys.xp_dirtree @saveDir,0,1[font="Arial Black"],1[/font];

  Yeah... you're going to need to add an extra column to the temp table to do this.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)