Solved

Aging of data in Sql

Posted on 2011-02-11
34
1,049 Views
Last Modified: 2012-06-21
Hello
I have a logic of ageing to implement. I need to count the distinct records (EmployeeID as PK) which have been present in the database for the past consecutive dates. I am jotting down the scenario below

Week1: data uploaded with UploadDate

Week2: Data Uploaded with Upload Date
Here first level ageing to be checked If the same EmployeeID is present in the last week the count should come as 2

Week3: Data Uploaded with Upload Date
Here second level ageing to be checked If the same EmployeeID is present in the last 2 weeks the count should come as 3

Week4: Data Uploaded with Upload Date
Here Third level ageing to be checked If the same EmployeeID is present in the last 3 weeks the count should come as 4

But here there is a condition

If in any of the week the EmployeeID is not present the count should reset. Like

EmployeeID was in  Week1 & Week2 : Count 2
EmployeeID was in  Week1 & Week2 and not in Week3 : Count reset to 0
EmployeeID was in  Week4 : Count 1

Please Help

TIA
0
Comment
Question by:ExpertHelp79
  • 20
  • 8
  • 4
  • +1
34 Comments
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34871038
I am attaching the query on which this logic needs to be implemented. I need to display the column of the ageing count.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34871041
Select HAR.PersonNo as PersonNo,HAR.EmployeeName as EmployeeName,HAR.Period as Period,
HAR.ProfitCenter as ProfitCenter,HAR.CostCenter as CostCenter,HAR.ProjectTime as ProjectTime,
YTC.Text as Text,Null as Status,Null as Remark,HAR.Account as Account, HAR.ProjectNumber as ProjectNumber,
CONVERT(VARCHAR(10),HAR.Date, 120) as Date,PC.ProjectTLE as ProjectTLE,EMD.BaseLocation as BaseLocation,
ETES.ManagerName as ManagerName,EMD.ORGManager1 as ORGManager1,HAR.Vertical as Vertical,
ICE.Manual_Auto as Manual_Auto,ICE.GDS_NonGDS as GDS_NonGDS,
convert(varchar(10),GPS.ResourceStartDate, 120) as ResourceStartDate,convert(varchar(10),GPS.ResourceEndDate, 120) as ResourceEndDate,
GPS.Status as Status_Act, GPS.BillingKey as BK,PC.SoldToPt as SoldToPt,AM.AccountDescription as AccountDescription,
ETES.EmployeeEmailID as EmployeeEmail,ETES.ManagerEmailID as EtesManagerEmail,
CONVERT(VARCHAR(10),HAR.UploadDate, 120) As UploadDate

From WeeklyHARReport HAR 

left outer Join (select * from (select x.*
             ,row_number() over (partition by (cast(PersonNo as varchar)+cast(cast(ObjectID as datetime) as varchar))
                                     order by objectid ) as rn 
                  from YTCError as x) as y where rn=1
      ) as YTC
on (cast(HAR.PersonNo as varchar) + cast(cast(HAR.Date as datetime) as varchar)) = 
(cast(YTC.PersonNo as varchar) + cast(cast(YTC.ObjectID as datetime) as varchar))

left outer Join (select * from (select a.*
             ,row_number() over (partition by ShortIdentification order by ShortIdentification) as rn1	 
                  from ProjectConsolidated as a) as b where rn1=1
      ) as PC
on HAR.ProjectNumber = PC.ShortIdentification 

left outer Join (select * from (select a.*
             ,row_number() over (partition by EmpID order by EmpID) as rn1	 
                  from ETesMgrEmp as a) as b where rn1=1
      ) as ETES
on HAR.PersonNo = ETES.EmpID 

left outer Join (select * from (select a.*
             ,row_number() over (partition by EmployeeID order by EmployeeID) as rn1	 
                  from EmanageDump as a) as b where rn1=1
      ) as EMD
on HAR.PersonNo = EMD.EmployeeID

left outer Join (select * from (select a.*
             ,row_number() over (partition by SoldToParty order by SoldToParty) as rn1	 
                  from ICEntities as a) as b where rn1=1
      ) as ICE
on PC.SoldToPt = ICE.SoldToParty

left outer Join (select * from (select a.*
             ,row_number() over (partition by (WBSCode + EmployeeID) order by EmployeeID) as rn1	 
                  from GPSF as a) as b where rn1=1
      ) as GPS
on (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID)

left outer Join (select * from (select a.*
             ,row_number() over (partition by Account order by Account) as rn1	 
                  from AccountMaster as a) as b where rn1=1
      ) as AM
on HAR.Account = AM.Account

Where 

HAR.UploadDate =  @uploaddate   and HAR.PersonNo is not null

order by HAR.EmployeeName

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34872449
so which table (and what columns) are to be tested for the data....

and please confirm what you mean by a Week...
  what you mean by an upload/load....

is it a single record is present or are multiple records present?

can muliple users be attached to the same record?

SORRY please confirm at what level the ageing is to apply at ... the whole data , or individual records?
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34872686
Week is the weekly data upload we are doing in the database. Upload date is the Date of upload of data.
For each week there are multiple records in HAR but only one YTC.TEXT
The aging need to be implemented with the whole data.

Table HAR and YTC and related columns in the above query need to be tested.

Thanks for helping.. hope i tried to answer to the queries

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34872753
The count will be based on the combination of

(cast(HAR.PersonNo as varchar) + cast(cast(HAR.Date as datetime) as varchar))

ie if the combination of data is found in the past consecutive weeks then count will increase
based on this the 4 week logic need to be implemented
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34872760
This logic is very complicated for me .. hope i can get a help. Thanks in advance
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34876972
Please any threads....
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34881003
Is there any way you can script out this DB with sanitized data (i.e. John Smith becomes Abe Lincoln, Jane becomes Betsy Ross, etc.

The first pass on reading the query is giving me a headache. ;-) I'm thinking this should be done with a stored procedure and a cursor and temp tables.

And this DB is either very normalized or not at all.

I'm seeing the Casts on the date fields. You may want to automatically change fields so that everything is set to a YYYY-MM-DD 00:00:00.000 as part of the upload.
 
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34881448
The DB is normalized to some extent....
the date is always in the same format yyyy-mm-dd

The introduction of stored procedure or cursor is always welcome
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34882656
I'm looking just for some sample data from the multiple tables involved. This is going to be a programming exercise. It sounds like you have gotten the rules hammered out -- it is a matter of how to implemement them programatically.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34889374
Hello Sorry for the late reply
I attaching the sample file where i need to implement the recurrence count
TestData.xlsx
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34893793
I'm swamped for the day. I'll see what I can do with it tomorrow.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34893814
Sure ... in the meantime i will try myself too....
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34899134
Please any thread
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34903790
Here is the table I'm working off of from your SS EE-Q26814647-PermTbl.sql

Here is the  EE-Q26814647-RecurTbl.sql  the results table.

And below is my total aircode for the SP -- no edit checks. I think the logic is sound -- but will at least give you a place to start.
CREATE PROCEDURE ChkRecur
AS
BEGIN

DECLARE	@CurrPersonNum	as	varchar(25)
DECLARE	@PersonNum		as	varchar(25)
DECLARE	@EmpName		as	varchar(50)
DECLARE	@Period			as	varchar(6)
DECLARE	@RecrCnt		as	integer
DECLARE	@PeriodBeg		as	varchar(6)
DECLARE @RecCnt			as	smallint
DECLARE	@PriPeriod		as	smallint

set	@PeriodBeg = cast(year(getdate()) as varchar) + right('00'+(cast(DATEPART(week,getdate()) as varchar)  ),2)

-- SELECT right('00'+(cast(DATEPART(week,getdate()) as varchar)  ),2)

DECLARE @PermTbl CURSOR 
		SELECT	Person_Number,	Employee_Name,	Period
		FROM	PermTbl
		WHERE	Period = @PeriodBeg
		ORDER BY Person_Number, Period

FETCH NEXT FROM @PermTbl
INTO  @PersonNum,	@EmpName, @Period

set	@PriPeriod = @Period

WHILE @@FETCH_STATUS = 0 
BEGIN  
IF @CurrPersonNum <> @PersonNum 
	BEGIN
		UPDATE	RecurTbl
		SET		Person_Number	=	@CurrPersonNum	,
				Employee_Name	=	@EmpName	,
				AsOfDate		=	cast(convert(varchar,getdate(),101) as datetime)	,
				Recurrence		=	@RecCnt;
		SELECT	@CurrPersonNum = @PersonNum,
			@RecCnt = 0,
			@PriPeriod = @Period;
	END
IF @CurrPersonNum = @PersonNum 
	BEGIN
		IF cast(@Period as smallint) < cast(@PriPeriod as smallint)-1
			BEGIN
				set @RecCnt = @RecCnt + 1
			END
		ELSE
			BEGIN
				set @RecCnt = 1
			END

		FETCH NEXT FROM @PermTbl
		INTO  @PersonNum,	@EmpName, @Period

	END
END

UPDATE	RecurTbl
SET		Person_Number	=	@CurrPersonNum	,
		Employee_Name	=	@EmpName	,
		AsOfDate		=	cast(convert(varchar,getdate(),101) as datetime)	,
		Recurrence		=	@RecCnt;


DEALLOCATE @PermTbl
END

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34903877
please tell me how to configure the sql to run this sp

Do i need to create the tables you suggested.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 38

Expert Comment

by:Jim P.
ID: 34904030
The first table is the data from the spreadsheet. The RecurTbl (second script) is where I'm writing the output to.

I'm assuming the Person_Number,  Employee_Name, and Period are the pertinent columns. So if JDoe (empnum 1) has a record in week 1 and week 2 he gets credit and increments.

If not then it resets to 1. I'm doing a 4 week look back with the @PeriodBeg

Then from there if the person changes it writes to the RecurTbl. If not it goes to check the change.

I did forget to add on the first fetch to beat the @CurrPersonNum so the first fetch works correctly.

I have all this code done in VBA. Trying to translate is a bear (or some other creature that starts with b). ;-)
FETCH NEXT FROM @PermTbl
INTO  @PersonNum,       @EmpName, @Period

set     @CurrPersonNum = @PersonNum
set     @PriPeriod = @Period

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34905504
Ok .. thanks a lot... but yet i am not able to runit ... do i need to create recurtable in my database and then the SP .. please help me
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34909361
The PermTbl name and column names table will need to be changed to reflect the table where you store the data in the database.

The RecurTbl will need to be created. You can use the script I provided.

The SP will then select from the PermTbl (or whatever you call it.) It will then write the results to the RecurTbl.

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34909414
ok i will try it in the morning... and respond you accordingly
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34925053
I am getting this error

(0 row(s) affected)
Msg 16950, Level 16, State 2, Procedure ChkRecur, Line 19
The variable '@PermTbl' does not currently have a cursor allocated to it.

(0 row(s) affected)
Msg 16950, Level 16, State 2, Procedure ChkRecur, Line 66
The variable '@PermTbl' does not currently have a cursor allocated to it.

(1 row(s) affected)
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34966654
I messed in my first code -- I'm trying to rebuild the code. I've asked for some other experts to take a glance at what I missed.
DECLARE	@CurrPersonNum	as	varchar(25)
DECLARE	@PersonNum		as	varchar(25)
DECLARE	@EmpName		as	varchar(50)
DECLARE	@Period			as	varchar(6)
DECLARE	@RecrCnt		as	integer
DECLARE	@PeriodBeg		as	varchar(6)
DECLARE @RecCnt			as	smallint
DECLARE	@PriPeriod		as	int
DECLARE @FirstPass		as bit

set	@FirstPass = 1
--	SELECT	*
--	FROM	PermTbl
set	@PeriodBeg = cast(year(getdate()) as varchar) + right('00'+(cast(DATEPART(week,getdate()) as varchar)  ),2)

-- SELECT right('00'+(cast(DATEPART(week,getdate()) as varchar)  ),2)

DECLARE PermTbl CURSOR FAST_FORWARD FOR 
	SELECT	Person_Number,	Employee_Name,	Period
		FROM	PermTbl
		--WHERE	Period = @PeriodBeg
		ORDER BY Person_Number, Period

OPEN PermTbl
		
FETCH NEXT FROM PermTbl
INTO  @PersonNum,	@EmpName, @Period

set	@PriPeriod = @Period
print @PersonNum+	@EmpName+ @Period 
set  @CurrPersonNum=0
WHILE @@FETCH_STATUS = 0 
BEGIN  
IF @CurrPersonNum <> @PersonNum or @FirstPass = 1
	set	@FirstPass = 0
 
print @CurrPersonNum 
	BEGIN
		INSERT INTO	RecurTbl
			(Person_Number	,
			 Employee_Name	,
			 AsOfDate		,
			 Recurrence		)
		VALUES	(@CurrPersonNum	,
				@EmpName	,
				cast(convert(varchar,getdate(),101) as datetime)	,
				@RecCnt)

		FETCH NEXT FROM	PermTbl	
		INTO  @PersonNum,	@EmpName, @Period;

		SELECT	@CurrPersonNum = @PersonNum,
			@RecCnt = 0,
			@PriPeriod = @Period;

		print @PersonNum + ' ' + cast(@RecCnt as varchar) + ' first'

	END
IF @CurrPersonNum = @PersonNum 
	BEGIN
	print @PersonNum + ' ' + cast(@RecCnt as varchar)  + ' second'

		IF cast(@Period as integer) < cast(@PriPeriod as integer)-1
			BEGIN
				set @RecCnt = @RecCnt + 1
			END
		ELSE
			BEGIN
				set @RecCnt = 1
			END


	END
	FETCH NEXT FROM	PermTbl	
	INTO  @PersonNum,	@EmpName, @Period

END

UPDATE	RecurTbl
SET		Person_Number	=	@CurrPersonNum	,
		Employee_Name	=	@EmpName	,
		AsOfDate		=	cast(convert(varchar,getdate(),101) as datetime)	,
		Recurrence		=	@RecCnt;


DEALLOCATE PermTbl

select * from RecurTbl
-- truncate table RecurTbl

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34967161
jimpen: thanks a lot for the help.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34967237
The output is not correct
the same record came 9000 times but there are more than 100 distinct PersonNumber
Person_Number         EmployeeName            As of Date   Recurrence
1000001	              JK	              2-24-2011	   1
1000001	              JK	              2-24-2011	   1
1000001	              JK	              2-24-2011    1
1000001	              JK	              2-24-2011	   1

Open in new window

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34967245
Also we have to count recurrence based on PersonNumber and UploadDate
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 34967456
I'm aware of the problems -- there is an issue with my code. That is why I asked other experts to chime in.

While a large number of our Expert volunteers are US based, I think EE has an expert in something in every single time zone. None of us are ignoring you -- it just takes time to get everyone to see and respond.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34967516
jimpen:    Thanks a lot for the help. I am also trying to implement thru C# and i hope i am quite near
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34967962
Hi,

By seeing the data in sample excel my assumptions are below:
1. On a single data_upload_date, there is a possibility of having same "person number" and "Employee Name" multiple times.
2. The data is uploaded on daily basis (without any holiday or missing).
3. If a "person number" exists for just previous data_upload_date, then the reoccurence is increased otherwise set to zero.

Please confirm. Accordingly I will proceed.

rnm
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34968066
Hello rajeevnandanmishra:Please find my comments inline

1. On a single data_upload_date, there is a possibility of having same "person number" and "Employee Name" multiple times. YES
2. The data is uploaded on daily basis (without any holiday or missing).: Uploaded Every Week Monday
3. If a "person number" exists for just previous data_upload_date, then the reoccurence is increased otherwise set to zero. YES
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34968820
Hi
Please try to execute below code. I am not very good at cursors. So have written few statements that is giving me the output. I have considered the excel data as base data (Thought I need to change the upload dates, because they were not in a week difference).
Let me know its result on your data:

 
select identity(int,1,1) row_id, [Data Upload Date], [Person Number], [Employee Name], cast (0 as int) reoccur 
into #weekdata_proc_1 from weekdata 
group by [Data Upload Date], [Person Number], [Employee Name] 
order by [Person Number], [Employee Name], [Data Upload Date] 

declare @dt datetime, @person_number varchar(50), @reoccur int
select @dt = '1 Jan 1900', @person_number = '', @reoccur = 0

update #weekdata_proc_1 
set 	
	@reoccur = case when @person_number = [person number] then 
			case when dateadd(wk,1,@dt) = [Data Upload Date] then @reoccur + 1 else 1 end 
		   else 1 end,
	@person_number = [person number], 
	@dt = [Data Upload Date] , 
	reoccur = @reoccur 
	

select a.[Person Number], a.[Employee Name], a.[Data Upload Date] asOnDate, a.reOccur  
from #weekdata_proc_1 a 
join (select [Person Number], Max(row_id) Row_Id from #weekdata_proc_1 group by [Person Number]) b 
on a.row_id = b.row_id

Open in new window


rnm
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34968839
Hi,
The output will provide you the reoccurence level as per the last time that Person's details are uploaded.

I am not sure, that what you want, in case suppose 'Person A' has not been uploaded in the last upload.

0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34968876
>>I am not sure, that what you want, in case suppose 'Person A' has not been uploaded in the last upload.

Then Count will be 1
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 34968989
Ok,

So have a little change in the code and find it below:
 
select identity(int,1,1) row_id, [Data Upload Date], [Person Number], [Employee Name], cast (0 as int) reoccur 
into #weekdata_proc_1 from weekdata 
group by [Data Upload Date], [Person Number], [Employee Name] 
order by [Person Number], [Employee Name], [Data Upload Date] 

declare @dt datetime, @person_number varchar(50), @reoccur int
select @dt = '1 Jan 1900', @person_number = '', @reoccur = 0

update #weekdata_proc_1 
set 	
	@reoccur = case when @person_number = [person number] then 
			case when dateadd(wk,1,@dt) = [Data Upload Date] then @reoccur + 1 else 1 end 
		   else 1 end,
	@person_number = [person number], 
	@dt = [Data Upload Date] , 
	reoccur = @reoccur 
	

select x.[Person Number], x.[Employee Name], convert(char(10),getdate(),111) asOnDate, isnull(y.reOccur,1) reOccur 
from #weekdata_proc_1 x left outer join  
(select a.[Person Number], a.[Employee Name], a.[Data Upload Date] asOnDate, a.reOccur  
from #weekdata_proc_1 a 
join (select [Person Number], Max(row_id) Row_Id from #weekdata_proc_1 group by [Person Number]) b 
on a.row_id = b.row_id) y 
on x.[Person Number] = y.[Person Number] and x.[Employee Name] = y.[Employee Name] 
and y.asOnDate = (SELECT max([Data Upload Date]) from weekdata)
group by x.[Person Number], x.[Employee Name], y.reOccur

Open in new window


0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 20
Complex SQL 10 33
sql query to reportserver  table error 3 23
Pivot not using aggregate yield error 3 18
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now