February 26, 2008 at 3:32 pm
Hello I have the following table where I would like to set the value of PersonCount field to 1 if the record is equal the minimum date for that particular AssocaiteName(will Have multiple entries just want to mark first record with 1) else set the value to 0
Table name = #test
Fields = ID,
Site,
ManagerName,
AssociateName,
PesronNumber,
Startdate,
Enddate,
EntryDate,
PersonCount
Here is what I have but is not working. Any help would be greatly appreciated.
Update #test
Set PersonCount = 1
From #test
Join #test B ON #test.PesronNumber = B.PesronNumber
and b.EntryDate = (SELECT PesronNumber,MIN(EntryDate)
FROM #test
where #test.EntryDate = EntryDate
Group By PesronNumber)
February 26, 2008 at 3:57 pm
Try this code. It is untested as you did not provide the DDL or any test data.
with TestDate (
PersonNumber,
EntryDate
) as (
select
PersonNumber,
min(EntryDate)
from
#test
group by
PersonNumber
)
update #test set
PersonCount = 1
from
#test t
inner join TestDate td
on (t.PersonNumber = td.PersonNumber
and t.EntryDate = td.EntryDate);
😎
February 26, 2008 at 5:20 pm
I am sorry i do not understand with TestDate ( PersonNumber, EntryDate) as piece.
Mayby I can clarify what I need. I want to update The PersonCount field to = 1 onlyt for 1 record per person. I would like to set the value to 1 for the first record related to an associate. I also attached some sample data.
Thank you for your help.
ID Site ManagerName AssociateName PesronNumber StartDate Enddate EntryDate PersonCount
1 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 12/19/07 0
2 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 12/28/07 0
3 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/07/08 0
4 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/08/08 0
5 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/09/08 0
6 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/11/08 0
7 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/14/08 0
8 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/15/08 0
9 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/16/08 0
10 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/17/08 0
11 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/18/08 0
12 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/22/08 0
13 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/23/08 0
14 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/24/08 0
15 ATLANTA Doe, John Brown, Charlie 12345678 12/17/07 05/15/08 01/25/08 0
16 ATLANTA Doe, John Fully, Dave 87654321 12/17/07 05/15/08 01/07/08 0
17 ATLANTA Doe, John Fully, Dave 87654321 12/17/07 05/15/08 01/08/08 0
February 26, 2008 at 6:01 pm
David -
Lynn is using what is called a CTE (common table expression). BOL will give you lots of examples of how it can be used. It's pretty nice once you can wrap your head around how its syntax works.
I've reformatted your test data so that it can be used, and compaensated for the typo in the table field name.
create table #test(ID int, [Site] varchar(20),
ManagerName varchar(20),
AssociateName varchar(20),
PersonNumber int,
StartDate datetime,
Enddate datetime,
EntryDate datetime,
PersonCount int)
Insert #test
SELECT 1,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','12/19/07', 0 UNION ALL
SELECT 2,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','12/28/07', 0 UNION ALL
SELECT 3,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/07/08', 0 UNION ALL
SELECT 4,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/08/08', 0 UNION ALL
SELECT 5,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/09/08', 0 UNION ALL
SELECT 6,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/11/08', 0 UNION ALL
SELECT 7,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/14/08', 0 UNION ALL
SELECT 8,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/15/08', 0 UNION ALL
SELECT 9,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/16/08', 0 UNION ALL
SELECT 10,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/17/08', 0 UNION ALL
SELECT 11,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/18/08', 0 UNION ALL
SELECT 12,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/22/08', 0 UNION ALL
SELECT 13,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/23/08', 0 UNION ALL
SELECT 14,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/24/08', 0 UNION ALL
SELECT 15,'ATLANTA','Doe, John','Brown, Charlie','12345678','12/17/07','05/15/08','01/25/08', 0 UNION ALL
SELECT 16,'ATLANTA','Doe, John','Fully, Dave','87654321','12/17/07','05/15/08','01/07/08', 0 UNION ALL
SELECT 17,'ATLANTA','Doe, John','Fully, Dave','87654321','12/17/07','05/15/08','01/08/08', 0
;with TestDate (
PersonNumber ,
EntryDate) as
(
select
PersonNumber ,
min(EntryDate)
from #test
group by PersonNumber
)
update #test
set PersonCount = 1
from #test t
inner join TestDate td
on (t.PersonNumber = td.PersonNumber
and t.EntryDate = td.EntryDate);
select * from #test where personcount=1
Really - give it a whirl - you'll see it's right.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 6:31 pm
Thank you so much SSCrazy and Lynn I really don't yet understand how it works but it does. I am going to try and wrap my brain around this for a while and see what I can learn!
Thank You for the help!
PS - Sorry for the typo's.
February 26, 2008 at 7:00 pm
Nicely done folks...
--Jeff Moden
February 27, 2008 at 8:45 pm
Hello I am a newbie as indicated next to my name. I spent the last 24 hours wrapping my brain around CTE's and feel I learned quite a bit. I have everything working as intended now I would like to open my methodology up to constuctive criticism in order to learn perhaps a better way of doing things. Please read the posts above if want to know what I am attempting to and or send me any questions you may have.
The stored procedure below runs a little slow but it does work. Is there a better way to go about solving this problem.
I am not limited as to how I can do this I just want to learn the best way to go about it.
ALTER procedure [dbo].[Card_OnBoardingDaily_History_sp] AS
Begin
Create table #CardDaily(
ID int constraint pkid Primary Key,
Site nvarchar(100),
ManagerName nvarchar(100),
AssociateName nvarchar(100),
PersonNumber nvarchar(8),
Startdate datetime,
Enddate datetime,
EntryDate datetime,
[30_60_90] nvarchar(100),
PersonCount int,
PersonCount306090 int,
DaysSinceStart int,
PercentOnbording float,
AverageHandleTime Float,
AverageHandleTimeSeconds Float,
AveragehandleTimeCalls int,
TotalBTSales nvarchar(50),
TotalBTDollars money,
TotalProducts int,
IncomingRetentionCalls int,
CB int,
ICR float)
Insert #CardDaily
Select
ID,
Site,
ManagerName,
AssociateName,
PesronNumber As PersonNumber,
Startdate,
Enddate,
EntryDate,
Case
WHEN DateDiff(Day,StartDate,EntryDate) <= 42 THEN '30'
WHEN DateDiff(Day,StartDate,EntryDate) between 43 and 72 THEN '60'
WHEN DateDiff(Day,StartDate,EntryDate) between 73 and 102 THEN '90'
WHEN DateDiff(Day,StartDate,EntryDate) > 102 THEN '90+'
End AS [30_60_90],
PersonCount = 0,
PersonCount306090 = 0,
DateDiff(Day,StartDate,EntryDate) DaysSinceStart,
Case
WHEN DateDiff(Day,StartDate,EntryDate) <= 102 THEN Cast(DateDiff(Day,StartDate,EntryDate) As Float)/102 * 100
else 100
end AS PercentOnbording,
AverageHandleTime,
AverageHandleTimeSeconds,
AveragehandleTimeCalls,
TotalBTSales,
TotalBTDollars,
TotalProducts,
IncomingRetentionCalls,
CB,
ICR
From dbo.Card_OnBoardingDaily_History ;
with TestDate ( PersonNumber , EntryDate) as
(select PersonNumber ,
min(EntryDate)
from #CardDaily
group by PersonNumber)
update #CardDaily set PersonCount = 1
from #CardDaily t
inner join TestDate td
on (t.PersonNumber = td.PersonNumber
and t.EntryDate = td.EntryDate);
with TestDate ( PersonNumber ,[30_60_90], EntryDate) as
(select PersonNumber ,
[30_60_90],
min(EntryDate)
from #CardDaily
group by PersonNumber,[30_60_90] )
update #CardDaily set PersonCount306090 = 1
from #CardDaily t
inner join TestDate td
on (t.PersonNumber = td.PersonNumber
and t.EntryDate = td.EntryDate
and t.[30_60_90] = td.[30_60_90] );
Create index pn_idx on #CardDaily(PersonNumber);
select
ID,
Site,
ManagerName,
AssociateName,
PersonNumber,
Startdate,
Enddate,
EntryDate,
[30_60_90],
PersonCount,
PersonCount306090,
DaysSinceStart,
PercentOnbording,
AverageHandleTime,
AverageHandleTimeSeconds,
AveragehandleTimeCalls,
TotalBTSales,
TotalBTDollars,
TotalProducts,
IncomingRetentionCalls,
CB,
ICR
from #CardDaily
End
Also thank you all for helping me get to this point!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply