ssis strange problem

Question

ssis strange problem

riajain9

SSC Journeyman

Points: 92
More actions
July 13, 2010 at 12:01 am

#147927

Hi everyone,
I have a strange requirement in SSIS, that I wanted to share with you all, and need some idea on how can I implement it...
I have to associate my source data with a date range and add a SEQUENCE column to the target based on that date range....
Actually I load data everyday(daily load) and it come in with an Account Number primary key(, but the day when I start loading it from from that day to + 5 days I have to maintain a sequence column (initial =1) and if the same account comes in that date range I have to increment the SEQUENCE column by 1....
Now after 5 days I have to restart the counts...and if same account comes again I have to SEQUENCE it as 1 now (now again wait 5 days , if it comes in 5 days increment sequence otherwise start new sequence)
Now my problem is that I have to maintain a date range separately for every account and I don't know how and where to maintain it... as from the src I am just getting one date , that tells me the record in date, I have to check this date against the date range for every account...
INPUT is like
ACcno InDAte
A1 1-Jul 2010
A2 1-Jul 2010
A2 3-Jul 2010
A2 4-Jul 2010
A2 6-Jul 2010
A3 6-Jul 2010
A2 11-Jul 2010
A2 19-Jul 2010
Output should be like
ACcno InDAte Sequence
A1 1-Jul 2010 1
A2 1-Jul 2010 1
A2 3-Jul 2010 2
A2 4-Jul 2010 3
A2 6-Jul 2010 1
A3 6-Jul 2010 1
A2 11-Jul 2010 1
A2 13-Jul 2010 2
You can see as the Indate for A2 changes sequence keeps on incrementing in 5 days range....
Any help on this...
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Hi Ria,

This is simple. You have to use SQL Windowing functions such as ROW_NUMBER,RANK,DENSE_RANK with the OVER CLAUSE using Partition...:cool::cool::cool:

Hope this helps!!!

Raunak J

riajain9 SSC Journeyman Points: 92 More actions · Answer 2

Hi ,

Thanks but I cannot think of this scenario being solved using CTE...

What do you think I should use in Partition by for this scenario as I cannot partition by any column I have to use the insertdate + 5 for every account ..

ROW_NUMBER() OVER (PARTITION BY?? )

Can you explain more...

riajain9 SSC Journeyman Points: 92 More actions · Answer 3

here is some sample data...

CREATE TABLE [abc1](

[I_KEY] [int] NULL,

[A_NO] [nvarchar](14) NULL,

[IN_DATE] [datetime] NULL,

[Sequence] [int] NULL

)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 1, '23', 'Jul 1 2010', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 2, '23', 'Jul 4 2010 ', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 3, '23', 'Jul 5 2010', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 4, '23', 'Jul 6 2010 ', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 5, '23', 'Jul 8 2010 ', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 6, '23', 'Jul 15 2010 ', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 7, '23', 'Jul 16 2010 ', NULL)

INSERT INTO abc1 ( I_KEY,A_NO,IN_DATE,Sequence) VALUES ( 8, '23', 'Jul 21 2010 ', NULL)

Just to make sure that my INDATE will act as the starting point for the date range....

Thanks

Account no 23 is coming in 3 ranges out put will be like this

1232010-07-01 00:00:00.000 1

2232010-07-04 00:00:00.0002

3232010-07-05 00:00:00.0003

4232010-07-06 00:00:00.000 1

5232010-07-08 00:00:00.0002

6232010-07-15 00:00:00.0001

7232010-07-16 00:00:00.0002

8232010-07-21 00:00:00.0001

riajain9 SSC Journeyman Points: 92 More actions · Answer 4

Hi all,

Any help on this,

The only way I can think of doing this is to maintain a separate stage table for every account with 2 dates in 5 date range period and when the 5th day crosses, either update or insert a new row for that account.....

but it looks not very efficient to me...

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

Raunak Jhawar (7/13/2010)
Hi Ria,
This is simple. You have to use SQL Windowing functions such as ROW_NUMBER,RANK,DENSE_RANK with the OVER CLAUSE using Partition...:cool::cool::cool:
Hope this helps!!!

Please present your simple solution.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

This is looking like a challenge. Other than using a chunk of procedural code to read through the data line by line (in order) and add a 'Group Number' field, nothing is coming to mind.

I would be tempted to post this on the T-SQL forum - there are some wizards there that probably don't check this one and they might be able to help.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

ok

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

riajain9 SSC Journeyman Points: 92 More actions · Answer 8

Here is the solution in SSIS...

Pls Let me know , how can I do the same in T-SQL, do you want me to post it there again

Phil Parkin SSC Guru Points: 246965 More actions · Answer 9

riajain9 (7/13/2010)
Here is the solution in SSIS...
Pls Let me know , how can I do the same in T-SQL, do you want me to post it there again

Yes - I would suggest posting this in the T-SQL forum here - you'll get the T-SQL experts looking at it there and they like a challenge. If it is possible in T-SQL, it would be nice to do it as your datasource, making the rest of your dataflow much simpler, I would imagine.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin