I need to add some ROWS to a table during my nightly DTS table copy process.

Your best bet is to create a procedure that UNIONS these records into the select. IT is best not to add unecessary records to a table.

Depending on what you want the outcome to be, you should be able to do this in multiple ways.

Something like this might lead you into the right direction...

SELECT FILENUMBER,ASSIGNMENTDATE, DEPT, ADMINISTRATOR
FROM YourTable
UNION
SELECT DISTINCT FILENUMBER, DATEPART(mm,ASSIGNMENTDATE) + '/01/' + DATEPART(yy,ASSIGNMENTDATE), DEPT, 'AUTOASSIGN'
FROM YourTableName

Hi Mike,

First - I'm guessing that your dates are displayed mm/dd/yy. Can you confirm this?

Second, yes, more data is needed. There is a gap between the first and second tables. Can you show the first table with the missing fields and entries prior to creating them for the second table. Its not clear what or how you "guessed" to fill in the blanks. Why was PCM the default department?

More information on the table structures is needed. If the filenumber is constant (100 in your examples), then what is it doing?

Is that all you need? - if there is nothing for the month, then insert a dummy line for the first of the month wiht PCM and Auto-Assign?

On that basis, the easiest is to do a cursor or loop (Yes, I'm thinking out-loud so-to-speak) going from the start date to the finish date
and check for an entry for that month

declare @StartDate datetime
select @StartDate = min( AssignmentDate )
from dbo.TableName

declare @FinishDate datetime
select @FinishDate = max( AssignmentDate )
from dbo.TableName

declare @MonthDate datetime
set @MonthDate = dateadd( month, datediff( month, 0, @StartDate ), 0 )

while @MonthDate < @FinishDate begin
  if
    (
    select count( 1 )
    from dbo.TableName
    where dateadd( month, datediff( month, 0, AssignmentDate ), 0 )  = @MonthDate
    ) = 0 -- nothing for the month
    insert dbo.TableName values ( 100, @MonthDate, 'PCM', 'AutoAssign' )

  set @MonthDate = dateadd( month, 1 @MonthDate )
end

Other Assumptions -
1. @AssignmentDate is a datetime column
2. TableName is the _destination_ table, not the source table. That is, run this after the event.

Regards
  David

The NEW table location will be separate FROM the original...and will be in a "data warehouse" type of environment. I'm not sure if that makes a difference, but I'm hoping to get a NEW table for REPORTING ONLY...that contains these new rows.

First - I'm guessing that your dates are displayed mm/dd/yy. Can you confirm this?
      Answer: NO...I'm just simplifying..they are DATETIME   01-01-2006 00:00:00

Second, yes, more data is needed. There is a gap between the first and second tables. Can you show the first table with the missing fields and entries prior to creating them for the second table. Its not clear what or how you "guessed" to fill in the blanks. Why was PCM the default department?

      Answer: I thought I included the first original table in my original question?? The department is soley based on the previous DEPT assignment. IF the department changes, then I want to be able to assign a DATE for the MONTH that it appears in.

More information on the table structures is needed. If the filenumber is constant (100 in your examples), then what is it doing?

     Answer: Sorry don't understand this question... that is just ONE file number out of thousands that will appear in this table. I just gave ONE sample.

Is that all you need? - if there is nothing for the month, then insert a dummy line for the first of the month wiht PCM and Auto-Assign?

    Answer: YESSSSSS!... I know this sounds simpler than it is...right/???

thanks

David:

I'm confused about the TableName NOT being the original table? How are you pulling data from the original table...where does that table info go into this logic?

Thanks

Atlanta MIKE:

I have this:

SELECT [FILE NUMBER],[ASSIGNMENT DATE],[assigned to],[ADMINISTRATOR NAME]
FROM dbo.assignh
UNION
SELECT DISTINCT [FILE NUMBER], DATEPART(mm,[ASSIGNMENT DATE]) + '/01/' + DATEPART(yy,[ASSIGNMENT DATE]), [assigned to], 'AUTOASSIGN'
FROM dbo.Assignh

And I get this error: (sorry just learning SQL.. :)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '/01/' to a column of data type int.

DAVID:

also, the DEPT is not always PCM.... as you have below....

=insert dbo.TableName values ( 100, @MonthDate, 'PCM', 'AutoAssign' )

But rather should be the previous DEPT before the CHANGE of the DEPT.

THANKS

Hi,

The instead of the previous insert try this
insert dbo.TableName
select top 1 100, @MonthDate, Dept, 'AutoAssign'
from dbo.TableName
where AssignmentDate < @MonthDate
order by AssignmentDate desc

I can't tell at this distance if that will give the 'correct' department, as I don't have enough infomation on the table and its primary keys and data distribution.

What that does is to select the last (hopefully) department from before the gap we are trying to fill.

Can you post the select you are using from Crystal? There might be a better way to handle this issue - the 'missing' data - than inserting dummy records.

Regards
  David

Crap... sorry... I forgot to convert the numerics to character.

SELECT [FILE NUMBER],[ASSIGNMENT DATE],[assigned to],[ADMINISTRATOR NAME]
FROM dbo.assignh
UNION
SELECT DISTINCT [FILE NUMBER], convert(varchar(2),DATEPART(mm,[ASSIGNMENT DATE])) + '/01/' + convert(varchar(4),DATEPART(yy,[ASSIGNMENT DATE])), [assigned to], 'AUTOASSIGN'
FROM dbo.Assignh

Well I just realized what you wanted... if no one has it tomorrow, I'll give another stab at it.

Atlanta Mike:

I'm using this logic:

SELECT [FILE NUMBER],[ASSIGNMENT DATE],[assigned to],[ADMINISTRATOR NAME]
FROM dbo.assignh
where [file number] = '7700-N-0008'
UNION
SELECT DISTINCT [FILE NUMBER], convert(varchar(2),
DATEPART(mm,[ASSIGNMENT DATE])) + '/01/' + convert(varchar(4),DATEPART(yy,[ASSIGNMENT DATE])), [assigned to], 'AUTOASSIGN'
FROM dbo.Assignh
where [file number] = '7700-N-0008'
order by [file number],[assignment date]

And getting these results:

7700-N-0008      4/1/2003 12:00:00 AM      ORA      AUTOASSIGN  
7700-N-0008      4/1/2003 12:00:00 AM      WOS      AUTOASSIGN
7700-N-0008      4/14/2003 12:00:00 AM      ORA      File Awaiting Assignment
7700-N-0008      4/16/2003 12:00:00 AM      WOS      Erika Maclin
7700-N-0008      5/1/2003 12:00:00 AM      WOS      AUTOASSIGN
7700-N-0008      5/13/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/1/2003 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      7/1/2003 12:00:00 AM      WOS      AUTOASSIGN
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Alicia Ponce
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/7/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      7/11/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      9/1/2003 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      9/9/2003 12:00:00 AM      RLS      Kristi Hedgcoxe
7700-N-0008      10/1/2003 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      10/28/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      10/31/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      12/1/2003 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      12/24/2003 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      6/1/2004 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      6/29/2004 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      9/1/2004 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      9/2/2004 12:00:00 AM      PCM      Claudette Grimes
7700-N-0008      1/1/2005 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      1/31/2005 12:00:00 AM      PCM      Sheketha Cole
7700-N-0008      9/1/2005 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      9/9/2005 12:00:00 AM      PCM      Jason Miller
7700-N-0008      11/1/2005 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      11/29/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      11/30/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      2/1/2006 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      2/1/2006 12:00:00 AM      PCM      Tierney Eichman
7700-N-0008      3/1/2006 12:00:00 AM      PCM      AUTOASSIGN
7700-N-0008      3/15/2006 12:00:00 AM      PCM      Azeb Alemu
7700-N-0008      4/1/2006 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      4/18/2006 12:00:00 AM      RLS      Mary Elizabeth Hampton

I only need to have a data ADDED IF...the DATE is MISSING for a given MONTH within the RANGE of time that the FILE is assigned to a DEPARTMENT.

So if a file is assigned to RLS dept on 01/01/05 and next is assigned to PCM on 06/05/05, then I need to assign a ROW..with the ASSIGNMENT DATE for EACH MONTH "in between" those 2 ASSIGNMENT DATES between the departments.

Hoping this makes sense.. if not ask..

thanks
MIke

Hi Folks,

Please do have a look at this line I gave above

dateadd( month, datediff( month, 0, AssignmentDate ), 0 )

For any current datetime, it will give the datetime as of midnight 0:00:00 on the first of the month. This eliminates the character and datepart manipulation Mike is doing, and for something like this where hundreds or thousands of rows are being manipulated, significantly faster.

What it does
0 is a date - first of january somewhere around 1900.

It takes the number of whole months since date zero, then adds that number of months from 0.

This works for years, quarters, months, weeks can be a problem depending on first day of the week settings, days, hours, minutes, but overflows on seconds. For seconds need to specify a more recent date than 0.

The key to this working is that dates are stored as a number, and so the conversion from 0 integer to a date is quicker than string manipulation ...

Regards
  David

Yes, that's what I realized last night. I'll see if I can look at it again. This is not a simple puzzle... :-)

Thanks for the added info...and yes AMike..I know..this is the last great mystery of our data table.

If this can be figured out...ALL of the workload for ALL of our departments can be monitored more accurately...and employee headcount..and salaries..etc..can be fine tuned.

It really is a very import project for me...and I KNOW for sure that you experts there can solve it.

THANK and I look forward to hearing any and all ideas

MIKE

Awesome..I'll give it whirl and see what happens. I'll be in touch shortly..

Appreciate the expertise!

Atlanta Mike:

What is the significance of this line?

SET @FILENUMBER = 100

Just so I understand what this line is doing.

Thanks
M

Also, just fyi... there are more data elements in this table than what I gave you in the example. I just listed the data elements that I thought were important. Will the Final Select * command have a problem with OTHER data elements in the table..that are NOT listed in the logic, but that DO in fact reside in the table?

Again just trying to understand it.

Thanks

I have this, but not working. Just pulls the NORMAL table info for the file...no rows are updated,added etc.????? If possible, can you REVIEW my original TABLE layout...AND..also the sample of what I need to achieve? May something got overlooked.
THANKS

SET NOCOUNT ON
--DROP TABLE #TempAutoAssign
CREATE TABLE #TempAutoAssign ([FILE NUMBER] varchar(50),[ASSIGNMENT DATE] datetime,[assigned to] char(3),[ADMINISTRATOR NAME] varchar(50))

DECLARE @ASSIGNMENTDATE datetime, @FileNumber varchar
DECLARE @MinAssignmentDate datetime, @MaxAssignmentDate datetime, @Month tinyint, @AssignmentDateFirst datetime, @AssignmentDateLast datetime, @Dept varchar(3)
SET @FILENUMBER ='7700-N-0008'

SELECT  @MinAssignmentDate = MIN([ASSIGNMENT DATE]),
        @MaxAssignmentDate = MAX([ASSIGNMENT DATE])
FROM dbo.Assignh
WHERE [FILE NUMBER] = @FileNumber


SET @Month = 1
SET @AssignmentDateFirst = convert(varchar(2),DATEPART(mm,@MinAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MinAssignmentDate))
SET @AssignmentDateLast =  convert(varchar(2),DATEPART(mm,@MaxAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MaxAssignmentDate))
PRINT @AssignmentDateFirst
PRINT @AssignmentDateLast
WHILE @AssignmentDateFirst < @AssignmentDateLast
BEGIN
    WHILE @Month <= 12
    BEGIN
    print @AssignmentDateFirst
            IF NOT EXISTS(SELECT * FROM dbo.Assignh WHERE [ASSIGNMENT DATE] = @AssignmentDateFirst)
            BEGIN  
                SELECT TOP 1 @Dept = [ASSIGNED TO] FROM dbo.Assignh WHERE [ASSIGNMENT DATE] < @AssignmentDateFirst ORDER BY [ASSIGNMENT DATE] DESC
                INSERT INTO #TempAutoAssign VALUES(@FileNumber,@AssignmentDateFirst, @Dept, 'AUTOASSIGN')        
            END
        SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDateFirst)
        SET @Month = @Month + 1

    END
        SET @Month = 1
END


SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM dbo.Assignh
WHERE [FILE NUMBER]='7700-N-0008'
UNION
SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM #TempAutoAssign
WHERE [FILE NUMBER]='7700-N-0008'

DROP TABLE #TempAutoAssign

Seems that #tempautoassign is empty??? on each run?

What are the values of your dates... you may have to convert ... convert(varchar(15), [ASSIGNMENT DATE],101) = convert(varchar(15),@AssignmentDateFirst,101)

I'll bet your datetime values have time values intead of zeros as displayed in your example

SET @FILENUMBER = 100 is left over from my test code... I built a temp table and set a variable.

Yes TIME values...instead of zeros..you are correct... so I'll try the adjustment above.

Thanks
M

Sorry Atlanta Mike, I'm just getting feet wet with SQL

Where does this:

 convert(varchar(15), [ASSIGNMENT DATE],101) = convert(varchar(15),@AssignmentDateFirst,101)

get inserted into???

M

Everywhere you have a date value you need to convert it to a "short date"

convert(varchar(15),'01/05/2006 01:06:10.241',101) will convert to '01/05/2005'

So you need to "remove" the time value and then compare the dates... and you should be ok I'm guessing.

This:

SET NOCOUNT ON
CREATE TABLE #TempAutoAssign ([FILE NUMBER] varchar(50),[ASSIGNMENT DATE] datetime,[assigned to] char(3),[ADMINISTRATOR NAME] varchar(50))

DECLARE @ASSIGNMENTDATE datetime, @FileNumber varchar
DECLARE @MinAssignmentDate datetime, @MaxAssignmentDate datetime, @Month tinyint, @AssignmentDateFirst datetime, @AssignmentDateLast datetime, @Dept varchar(3)
SET @FILENUMBER ='7700-N-0008'

SELECT  @MinAssignmentDate = MIN( convert(varchar(15), [ASSIGNMENT DATE],101)),
        @MaxAssignmentDate = MAX( convert(varchar(15), [ASSIGNMENT DATE],101))
FROM dbo.Assignh
WHERE [FILE NUMBER] = @FileNumber

SET @Month = 1

SET @AssignmentDateFirst = convert(varchar(2),DATEPART(mm,@MinAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MinAssignmentDate))
SET @AssignmentDateLast =  convert(varchar(2),DATEPART(mm,@MaxAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MaxAssignmentDate))
PRINT @AssignmentDateFirst
PRINT @AssignmentDateLast
WHILE @AssignmentDateFirst < @AssignmentDateLast
BEGIN
    WHILE @Month <= 12
    BEGIN
    print @AssignmentDateFirst
            IF NOT EXISTS(SELECT * FROM dbo.Assignh WHERE   [ASSIGNMENT DATE] = @AssignmentDateFirst)
            BEGIN  
                SELECT TOP 1 @Dept = [ASSIGNED TO] FROM dbo.Assignh WHERE [ASSIGNMENT DATE] < @AssignmentDateFirst ORDER BY [ASSIGNMENT DATE] DESC
                INSERT INTO #TempAutoAssign VALUES(@FileNumber,@AssignmentDateFirst, @Dept, 'AUTOASSIGN')        
            END
        SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDateFirst)
        SET @Month = @Month + 1

    END
        SET @Month = 1
END

SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM dbo.Assignh
WHERE [FILE NUMBER]='7700-N-0008'
UNION
SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM #TempAutoAssign
WHERE [FILE NUMBER]='7700-N-0008'

DROP TABLE #TempAutoAssign

Gives me this, which is the exact way that it appears in the ORIGINAL TABLE. No rows added. Any ideas?

I do appreciate the help.
Mike


7700-N-0008      4/14/2003 12:00:00 AM      ORA      File Awaiting Assignment
7700-N-0008      4/16/2003 12:00:00 AM      WOS      Erika Maclin
7700-N-0008      5/13/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Alicia Ponce
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/7/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      7/11/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      9/9/2003 12:00:00 AM      RLS      Kristi Hedgcoxe
7700-N-0008      10/28/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      10/31/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      12/24/2003 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      6/29/2004 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      9/2/2004 12:00:00 AM      PCM      Claudette Grimes
7700-N-0008      1/31/2005 12:00:00 AM      PCM      Sheketha Cole
7700-N-0008      9/9/2005 12:00:00 AM      PCM      Jason Miller
7700-N-0008      11/29/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      11/30/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      2/1/2006 12:00:00 AM      PCM      Tierney Eichman
7700-N-0008      3/15/2006 12:00:00 AM      PCM      Azeb Alemu
7700-N-0008      4/18/2006 12:00:00 AM      RLS      Mary Elizabeth Hampton

The trick here is we need to be comparing dates in the same format... are all of your dates in the table 4/18/2006 12:00:00 AM?

The above posting of data is directly from my table in the format of the table.

Thanks

I'm trying to achieve THIS:

7700-N-0008     4/14/2003 12:00:00 AM     ORA     File Awaiting Assignment
7700-N-0008     4/16/2003 12:00:00 AM     WOS     Erika Maclin
7700-N-0008     5/13/2003 12:00:00 AM     WOS     Deborah Allen
7700-N-0008     6/01/2003 12:00:00 AM     WOS     AutoAssigned *****
7700-N-0008     7/3/2003 12:00:00 AM     WOS     Alicia Ponce
7700-N-0008     7/3/2003 12:00:00 AM     WOS     Deborah Allen
7700-N-0008     7/7/2003 12:00:00 AM     RLS     Andrea L. Walborn
7700-N-0008     7/11/2003 12:00:00 AM     RLS     Andrea L. Walborn
7700-N-0008     8/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     9/9/2003 12:00:00 AM     RLS     Kristi Hedgcoxe
7700-N-0008     10/28/2003 12:00:00 AM     RLS     Don Blanco
7700-N-0008     10/31/2003 12:00:00 AM     RLS     Don Blanco
7700-N-0008     11/30/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     12/24/2003 12:00:00 AM     RLS     Lawrence Suldon
7700-N-0008     01/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     02/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     03/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     04/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     05/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     6/29/2004 12:00:00 AM     RLS     Lawrence Suldon
7700-N-0008     9/2/2004 12:00:00 AM     PCM     Claudette Grimes
7700-N-0008     10/01/2004 12:00:00 AM     PCM    AutoAssigned *****
7700-N-0008     11/01/2004 12:00:00 AM     PCM    AutoAssigned *****
7700-N-0008     12/01/2004 12:00:00 AM     PCM    AutoAssigned *****
7700-N-0008     1/31/2005 12:00:00 AM     PCM     Sheketha Cole
7700-N-0008     02/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     03/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     04/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     05/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     06/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     07/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     08/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     9/9/2005 12:00:00 AM     PCM     Jason Miller
7700-N-0008     10/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     11/29/2005 12:00:00 AM     PCM     Joyce Hudson
7700-N-0008     11/30/2005 12:00:00 AM     PCM     Joyce Hudson
7700-N-0008     12/01/2005 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     01/01/2006 12:00:00 AM     PCM     AutoAssigned *****
7700-N-0008     2/1/2006 12:00:00 AM     PCM     Tierney Eichman
7700-N-0008     3/15/2006 12:00:00 AM     PCM     Azeb Alemu
7700-N-0008     4/18/2006 12:00:00 AM     RLS     Mary Elizabeth Hampton

Oops I skipped a big gap between these lines above....

7700-N-0008     05/01/2003 12:00:00 AM     RLS     AutoAssigned *****
7700-N-0008     6/29/2004 12:00:00 AM     RLS     Lawrence Suldon


But you get the picture right?

Yes, just wanted to make sure... let me look at it a bit.

I understood that, I just need to make sure the dates are being compared correctly... I can't compare '04/18/2006 01:20:34' = '04/18/2006' it won't work... I think that's where our problem is.

What are the datatypes for  [file number],[assignment date],[assigned to],[administrator name]?

[FILE NUMBER] = STRING (15)
[ASSIGNMENT DATE]= DATETIME
[ASSIGNED TO]= STRING (3)
[ADMINISTRATOR NAME] = STRING (25)

I think I found the problem... somehow we've lost the definition of  @FileNumber varchar

It should be whatever the datatype of [FILE NUMBER] is.

Arghhhhh....

One other thing... if there isn't a valid assigned to record before the first record assigned to will be null. If you want a default value you'll have to do an ISNULL([assigned to],'ORA') for example.

Not sure what you are saying. This is all based on FILE NUMBER and so the FIRST Assigned TO...should be the beginning of this process..and the LAST Assigned To should end it for the FILENUMBER...then it should take a look at the next FILENUMBER..and so on..and so on.

What are you meaning, regarding NULLS?

Ok, then you might have to add one more constraint to the where clause to prevent a 04/01/2003 record from being created before this record in your example.

7700-N-0008     4/14/2003 12:00:00 AM     ORA     File Awaiting Assignment

Have you run it? Did it work?

when I run the script, it returns whats in the original table...NO rows added.

It looks like is simple pulling data from my original table UNTOUCHED???

shouldnt' you have somekind of logic to handle the DIFFERENT FILE NUMBERS? How does this logic know when ONE file number ends and another begins?

All of your examples showed the same FILE NUMBER as if you were providing a parameter to restrict by FILE NUMBER...

If you're saying it could go over multiple FILE NUMBERS that would have been helpful to know up front. So is the recordset supposed to be sorted by FILE NUMBER then DATE?

Well, I try to keep things simple. I'll try to be MORE detailed, but I thought the assumption would have been that we have more than ONE file in our business.

The table consists of 100,000s of different files.

So, I think a group by FILENUMBER, ASSIGNDATE would help out.

Thanks

This Logic....

SET NOCOUNT ON
--DROP TABLE #TempAutoAssign
CREATE TABLE #TempAutoAssign ([FILE NUMBER] varchar(15),[ASSIGNMENT DATE] datetime,[assigned to] char(3),[ADMINISTRATOR NAME] varchar(50))

DECLARE @ASSIGNMENTDATE datetime, @FileNumber varchar (15)
DECLARE @MinAssignmentDate datetime, @MaxAssignmentDate datetime
, @Month tinyint, @AssignmentDateFirst datetime, @AssignmentDateLast datetime, @Dept varchar(3)
SET @FILENUMBER ='7700-N-0008'

SELECT  @MinAssignmentDate = MIN([ASSIGNMENT DATE]),
        @MaxAssignmentDate = MAX([ASSIGNMENT DATE])
FROM dbo.Assignh
WHERE [FILE NUMBER] = @FileNumber

SET @Month = 1
SET @AssignmentDateFirst = convert(varchar(2),DATEPART(mm,@MinAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MinAssignmentDate))
SET @AssignmentDateLast =  convert(varchar(2),DATEPART(mm,@MaxAssignmentDate)) + '/01/' + convert(varchar(4),DATEPART(yy,@MaxAssignmentDate))
PRINT @AssignmentDateFirst
PRINT @AssignmentDateLast
WHILE @AssignmentDateFirst < @AssignmentDateLast
BEGIN
    WHILE @Month <= 12
    BEGIN
    print @AssignmentDateFirst
            IF NOT EXISTS(SELECT * FROM dbo.Assignh WHERE [ASSIGNMENT DATE] = @AssignmentDateFirst)
            BEGIN  
                SELECT TOP 1 @Dept = [ASSIGNED TO] FROM dbo.Assignh WHERE [ASSIGNMENT DATE] > @AssignmentDateFirst ORDER BY [ASSIGNMENT DATE] DESC
                INSERT INTO #TempAutoAssign VALUES(@FileNumber,@AssignmentDateFirst, @Dept, 'AUTOASSIGN')        
            END
        SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDateFirst)
        SET @Month = @Month + 1

    END
        SET @Month = 1
END


SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM dbo.Assignh
WHERE [FILE NUMBER]='7700-N-0008'
UNION
SELECT [file number],[assignment date],[assigned to],[administrator name]
FROM #TempAutoAssign
WHERE [FILE NUMBER]='7700-N-0008'

DROP TABLE #TempAutoAssign
=------------------------------------------------------------------------

Is giving me this result:.....

7700-N-0008      4/14/2003 12:00:00 AM      ORA      File Awaiting Assignment
7700-N-0008      4/16/2003 12:00:00 AM      WOS      Erika Maclin
7700-N-0008      5/13/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Alicia Ponce
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/7/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      7/11/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      9/9/2003 12:00:00 AM      RLS      Kristi Hedgcoxe
7700-N-0008      10/28/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      10/31/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      12/24/2003 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      1/1/2004 12:00:00 AM      RLS      AUTOASSIGN
7700-N-0008      6/29/2004 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      9/2/2004 12:00:00 AM      PCM      Claudette Grimes
7700-N-0008      1/31/2005 12:00:00 AM      PCM      Sheketha Cole
7700-N-0008      9/9/2005 12:00:00 AM      PCM      Jason Miller
7700-N-0008      11/29/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      11/30/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      2/1/2006 12:00:00 AM      PCM      Tierney Eichman
7700-N-0008      3/15/2006 12:00:00 AM      PCM      Azeb Alemu
7700-N-0008      4/18/2006 12:00:00 AM      RLS      Mary Elizabeth Hampton


I need this:

7700-N-0008      4/14/2003 12:00:00 AM      ORA      File Awaiting Assignment
7700-N-0008      4/16/2003 12:00:00 AM      WOS      Erika Maclin
7700-N-0008      5/13/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      6/1/2003 12:00:00 AM      WOS      AUTOASSIGN    ***
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Alicia Ponce
7700-N-0008      7/3/2003 12:00:00 AM      WOS      Deborah Allen
7700-N-0008      7/7/2003 12:00:00 AM      RLS      Andrea L. Walborn
7700-N-0008      8/1/2003 12:00:00 AM      RLS      AUTOASSIGN   ***
7700-N-0008      9/9/2003 12:00:00 AM      RLS      Kristi Hedgcoxe
7700-N-0008      10/28/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      10/31/2003 12:00:00 AM      RLS      Don Blanco
7700-N-0008      11/1/2003 12:00:00 AM      RLS      AUTOASSIGN   ***
7700-N-0008      12/24/2003 12:00:00 AM      RLS      Lawrence Suldon
7700-N-0008      1/1/2004 12:00:00 AM      RLS      AUTOASSIGN  ***
7700-N-0008      2/1/2004 12:00:00 AM      RLS      AUTOASSIGN   ***
7700-N-0008      3/1/2004 12:00:00 AM      RLS      AUTOASSIGN   ***
7700-N-0008      4/1/2004 12:00:00 AM      RLS      AUTOASSIGN ***
7700-N-0008      5/1/2004 12:00:00 AM      RLS      AUTOASSIGN ***
7700-N-0008      6/1/2004 12:00:00 AM      RLS      AUTOASSIGN ***
7700-N-0008      7/1/2004 12:00:00 AM      RLS      AUTOASSIGN ***
7700-N-0008      8/1/2004 12:00:00 AM      RLS      AUTOASSIGN ***
7700-N-0008      9/2/2004 12:00:00 AM      PCM      Claudette Grimes
7700-N-0008      10/1/2004 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      11/1/2004 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      12/1/2004 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      1/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      2/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      3/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      4/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      5/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      6/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      7/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      8/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      9/9/2005 12:00:00 AM      PCM      Jason Miller
7700-N-0008      10/1/2005 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      11/29/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      11/30/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      12/1/2005 12:00:00 AM      PCM      Joyce Hudson
7700-N-0008      1/1/2006 12:00:00 AM      PCM      AUTOASSIGN ***
7700-N-0008      2/1/2006 12:00:00 AM      PCM      Tierney Eichman
7700-N-0008      3/15/2006 12:00:00 AM      PCM      Azeb Alemu
7700-N-0008      4/18/2006 12:00:00 AM      RLS      Mary Elizabeth Hampton

Right now I'm using this for a SINGLE FILE only...... after I see how this works I'll try and open it up to more than one file.. but for now..I just want to work with a SINGLE FILE NUMBER.

Can  you help me correct the logic to achieve the above?????

I basically need a data line for EACH MONTH for the life of the file... that takes into consideration the UNIT that the file is in.
thanks
m