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: 246993 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246993 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246993 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel