Marcus Aurelius
asked on
I need to add some ROWS to a table during my nightly DTS table copy process.
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
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
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
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
ASKER
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.
ASKER
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
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
ASKER
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
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
ASKER
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.
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.
ASKER
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
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
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),DATEPAR T(mm,[ASSI GNMENT DATE])) + '/01/' + convert(varchar(4),DATEPAR T(yy,[ASSI GNMENT DATE])), [assigned to], 'AUTOASSIGN'
FROM dbo.Assignh
SELECT [FILE NUMBER],[ASSIGNMENT DATE],[assigned to],[ADMINISTRATOR NAME]
FROM dbo.assignh
UNION
SELECT DISTINCT [FILE NUMBER], convert(varchar(2),DATEPAR
FROM dbo.Assignh
Well I just realized what you wanted... if no one has it tomorrow, I'll give another stab at it.
ASKER
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),DATEPAR T(yy,[ASSI GNMENT 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
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),DATEPAR
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
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... :-)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome..I'll give it whirl and see what happens. I'll be in touch shortly..
Appreciate the expertise!
Appreciate the expertise!
ASKER
Atlanta Mike:
What is the significance of this line?
SET @FILENUMBER = 100
Just so I understand what this line is doing.
Thanks
M
What is the significance of this line?
SET @FILENUMBER = 100
Just so I understand what this line is doing.
Thanks
M
ASKER
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
Again just trying to understand it.
Thanks
ASKER
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),DATEPAR T(mm,@MinA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MinA ssignmentD ate))
SET @AssignmentDateLast = convert(varchar(2),DATEPAR T(mm,@MaxA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MaxA ssignmentD ate))
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,@Assign mentDateFi rst, @Dept, 'AUTOASSIGN')
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa teFirst)
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
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),DATEPAR
SET @AssignmentDateLast = convert(varchar(2),DATEPAR
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,@Assign
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa
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
ASKER
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),@Assig nmentDateF irst,101)
I'll bet your datetime values have time values intead of zeros as displayed in your example
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.
ASKER
Yes TIME values...instead of zeros..you are correct... so I'll try the adjustment above.
Thanks
M
Thanks
M
ASKER
Sorry Atlanta Mike, I'm just getting feet wet with SQL
Where does this:
convert(varchar(15), [ASSIGNMENT DATE],101) = convert(varchar(15),@Assig nmentDateF irst,101)
get inserted into???
M
Where does this:
convert(varchar(15), [ASSIGNMENT DATE],101) = convert(varchar(15),@Assig
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.
convert(varchar(15),'01/05
So you need to "remove" the time value and then compare the dates... and you should be ok I'm guessing.
ASKER
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),DATEPAR T(mm,@MinA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MinA ssignmentD ate))
SET @AssignmentDateLast = convert(varchar(2),DATEPAR T(mm,@MaxA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MaxA ssignmentD ate))
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,@Assign mentDateFi rst, @Dept, 'AUTOASSIGN')
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa teFirst)
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
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),DATEPAR
SET @AssignmentDateLast = convert(varchar(2),DATEPAR
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,@Assign
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa
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?
ASKER
The above posting of data is directly from my table in the format of the table.
Thanks
Thanks
ASKER
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
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
ASKER
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?
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]?
ASKER
[FILE NUMBER] = STRING (15)
[ASSIGNMENT DATE]= DATETIME
[ASSIGNED TO]= STRING (3)
[ADMINISTRATOR NAME] = STRING (25)
[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....
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.
ASKER
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?
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?
7700-N-0008 4/14/2003 12:00:00 AM ORA File Awaiting Assignment
Have you run it? Did it work?
ASKER
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?
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?
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?
ASKER
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
The table consists of 100,000s of different files.
So, I think a group by FILENUMBER, ASSIGNDATE would help out.
Thanks
ASKER
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),DATEPAR T(mm,@MinA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MinA ssignmentD ate))
SET @AssignmentDateLast = convert(varchar(2),DATEPAR T(mm,@MaxA ssignmentD ate)) + '/01/' + convert(varchar(4),DATEPAR T(yy,@MaxA ssignmentD ate))
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,@Assign mentDateFi rst, @Dept, 'AUTOASSIGN')
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa teFirst)
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
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),DATEPAR
SET @AssignmentDateLast = convert(varchar(2),DATEPAR
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,@Assign
END
SET @AssignmentDateFirst = DATEADD(mm,1,@AssignmentDa
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
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,
FROM YourTable
UNION
SELECT DISTINCT FILENUMBER, DATEPART(mm,ASSIGNMENTDATE
FROM YourTableName