?
Solved

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

Posted on 2006-06-06
42
Medium Priority
?
313 Views
Last Modified: 2008-02-01
Here is a sample of my table:

FILENUMBER   ASSIGNMENTDATE     DEPT      ADMINISTRATOR
100                   01/01/05                ORA       JOHN DOE
100                   01/02/05                RLS        JILL SMITH
100                   01/05/05                RLS        JACK JONES
100                   02/05/05                PCM       JACKIE CONE
100                   06/30/05                CNC       JOHN ORR


I need to somehow ADD an ASSIGNMENT DATE to the MONTHS whereby the file was assigned to that month, but where there is NOT an ASSIGNMENT DATE showing in the table. I need this:

FILENUMBER   ASSIGNMENTDATE     DEPT      ADMINISTRATOR
100                   01/01/05                ORA       JOHN DOE
100                   01/02/05                RLS        JILL SMITH
100                   01/05/05                RLS        JACK JONES
100                   02/01/05                RLS        AUTOASSIGN
100                   02/05/05                PCM       JACKIE CONE
100                   03/01/05                PCM       AUTOASSIGN
100                   04/01/05                PCM       AUTOASSIGN
100                   05/01/05                PCM       AUTOASSIGN
100                   06/30/05                CNC       JOHN ORR

This is needed so that I can create a TREND type of report in Crystal Reports XI. I need a DATE to key off of in order to create my Crosstabs in Crystal.

So can DTS handle something like this.....again this would be only for MONTH date "gaps" that are missing for a DEPT so that the file can be AUTOASSIGNED to a particular calender month.

Please let me know if you need more data.....and just fyi...the changing the original TABLE is NOT an option.

Thanks
Mike




0
Comment
Question by:MIKE
  • 23
  • 16
  • 3
42 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16847154
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 16847184
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16847200
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.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Author Comment

by:MIKE
ID: 16847252
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16847299
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16847399
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.
0
 
LVL 17

Author Comment

by:MIKE
ID: 16847595
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 16847954
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16849019
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16849198
Well I just realized what you wanted... if no one has it tomorrow, I'll give another stab at it.
0
 
LVL 17

Author Comment

by:MIKE
ID: 16852417
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 16856510
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
0
 
LVL 13

Expert Comment

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

Author Comment

by:MIKE
ID: 16856795
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
0
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 2000 total points
ID: 16857132
Ok, something like this... sorry we couldn't do it in one select, but this should work. Make sure you check the datatypes on the temp table and the columnnames. This would work great as a table variable, but I used a temp table for testing purposes.


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 int
DECLARE @MinAssignmentDate datetime, @MaxAssignmentDate datetime, @Month tinyint, @AssignmentDateFirst datetime, @AssignmentDateLast datetime, @Dept varchar(3)
SET @FILENUMBER = 100

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 = DEPT 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 *
FROM dbo.Assignh
UNION
SELECT *
FROM #TempAutoAssign

DROP TABLE #TempAutoAssign
0
 
LVL 17

Author Comment

by:MIKE
ID: 16861047
Awesome..I'll give it whirl and see what happens. I'll be in touch shortly..

Appreciate the expertise!
0
 
LVL 17

Author Comment

by:MIKE
ID: 16861073
Atlanta Mike:

What is the significance of this line?

SET @FILENUMBER = 100

Just so I understand what this line is doing.

Thanks
M
0
 
LVL 17

Author Comment

by:MIKE
ID: 16861110
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16862415
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16862615
Seems that #tempautoassign is empty??? on each run?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16863878
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16863894
SET @FILENUMBER = 100 is left over from my test code... I built a temp table and set a variable.
0
 
LVL 17

Author Comment

by:MIKE
ID: 16863921
Yes TIME values...instead of zeros..you are correct... so I'll try the adjustment above.

Thanks
M
0
 
LVL 17

Author Comment

by:MIKE
ID: 16864010
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16864204
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.
0
 
LVL 17

Author Comment

by:MIKE
ID: 16864373
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16864437
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?
0
 
LVL 17

Author Comment

by:MIKE
ID: 16864457
The above posting of data is directly from my table in the format of the table.

Thanks
0
 
LVL 17

Author Comment

by:MIKE
ID: 16864526
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16864534
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?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16864821
Yes, just wanted to make sure... let me look at it a bit.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16864836
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.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16865038
What are the datatypes for  [file number],[assignment date],[assigned to],[administrator name]?
0
 
LVL 17

Author Comment

by:MIKE
ID: 16865072
[FILE NUMBER] = STRING (15)
[ASSIGNMENT DATE]= DATETIME
[ASSIGNED TO]= STRING (3)
[ADMINISTRATOR NAME] = STRING (25)
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16865103
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....
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16865122
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.
0
 
LVL 17

Author Comment

by:MIKE
ID: 16865845
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?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16869847
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?
0
 
LVL 17

Author Comment

by:MIKE
ID: 16870680
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?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16872906
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?
0
 
LVL 17

Author Comment

by:MIKE
ID: 16873548
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 16985376
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

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question