To increment count based on a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To increment count based on a field

Author

Topic

deepak23
Starting Member

8 Posts

Posted - 2010-10-18 : 18:07:02

Hi,

I am trying to generate a random number based on a field.
Below is a table I am trying to populate:

FutureSegment CurrentSegment random snapshotdt today aging
11 11 1 10/4/2010 10/13/2010 8
11 11 1 10/5/2010 10/13/2010 7
11 11 1 10/6/2010 10/13/2010 6
11b1a 11 1 10/7/2010 10/13/2010 5
11b1a 11b1a 2 10/8/2010 10/13/2010 4
11b1a 11b1a 2 10/9/2010 10/13/2010 3
11 11b1a 2 10/10/2010 10/13/2010 3
11 11 3 10/11/2010 10/13/2010 3
x 11 3 10/12/2010 10/13/2010 2

So if currentsegment = futuresegment then the random number should be the same as previous else it should incremented

Any suggestions on how I should go about??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:02:29

what column determines the order in your table? ie. on what basis you determine previous record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepak23
Starting Member

8 Posts

Posted - 2010-10-20 : 00:02:32

Its based on CurrentSegment, SnapshotDt.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:44:59

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepak23
Starting Member

8 Posts

Posted - 2010-10-20 : 12:47:48

Yes I am using SQL Server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:52:03

quote:
Originally posted by deepak23

Yes I am using SQL Server

I was asking on version. what does

SELECT @@VERSION

return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

weisseb
Starting Member

8 Posts

Posted - 2010-10-22 : 11:21:52

I assume that snapshotdt is the primary key and that it alone determines the order in your table (it is unique in the sample data). The solution below assumes you're using 2005 or later, and is based on Itzik Ben-Gan's work, see [url]http://www.sqlmag.com/article/sql-server/solution-to-the-t-sql-puzzle-grouping-consecutive-rows-with-a-common-element.aspx[/url]. I use a table @table that contains the data in your post. A proposed solution is as below:

declare @table table
(
    FutureSegment varchar(50),
    CurrentSegment varchar(50),
    random int,
    snapshotdt datetime,
    today datetime,
    aging int,
    primary key (snapshotdt)
)

insert into @table values ('11', '11', 1, '10/4/2010', '10/13/2010', 8)
insert into @table values ('11', '11', 1, '10/5/2010', '10/13/2010', 7)
insert into @table values ('11', '11', 1, '10/6/2010', '10/13/2010', 6)
insert into @table values ('11b1a', '11', 1, '10/7/2010', '10/13/2010', 5)
insert into @table values ('11b1a', '11b1a', 2, '10/8/2010', '10/13/2010', 4)
insert into @table values ('11b1a', '11b1a', 2, '10/9/2010', '10/13/2010', 3)
insert into @table values ('11', '11b1a', 2, '10/10/2010', '10/13/2010', 3)
insert into @table values ('11', '11', 3, '10/11/2010', '10/13/2010', 3)
insert into @table values ('x', '11', 3, '10/12/2010', '10/13/2010', 2)

select
    *,
    dense_rank() over (order by minsnapshotdtInSection) as Result
from
(
    select
        *,
        rn - rnWithinCurrentSegment as WeakSectionNumber,
        min(snapshotdt) over (
            partition by
                CurrentSegment,
                rn - rnWithinCurrentSegment
        ) as minsnapshotdtInSection
    from
    (
        select
            *,
            row_number() over (order by snapshotdt) as rn,
            row_number() over (
                partition by CurrentSegment
                order by snapshotdt
            ) as rnWithinCurrentSegment
        from @table
    )
    as t1
)
as t2
order by snapshotdt

Working from the inside out, t1 adds two columns to @table, one is rn that numbers the rows sorted by snapshotdt, the other, rnWithinCurrentSegment, does the same, but starts all over from 1 within each unique value of CurrentSegment. Subtracting these two guarentees a unique constant value within consecutive rows with the same CurrentSegment. I've named this column WeakSectionNumber, because two sections with different CurrentSegment values may be assigned the same section number, hence 'weak'. The column WeakSectionNumber is for debugging purposes only, I use the subtraction literally instead.

Partitioning by the pair (CurrentSegment, WeakSectionNumber) we get unique (strong) segments, which allow us to find the minimum snapshotdt within each. Ultimately the dense_rank() used on the minimum snapshotdt in each section produces the requested ranks.

deepak23
Starting Member

8 Posts

Posted - 2010-10-22 : 11:56:00

Thanks for your response. I can kind of understand what you are telling.
Will be looking into it more detail.

That was just a snapshot of a single loan number. Basically I have 1.3 million such loans and each loan has been in our database for 50 days, moving in and out of segments. So trying to find the latest entry date into the segment. So i thought by grouping, I will be able to get that data.
So the primary key is the loannum and SnapshotDt together.

Hope this helps tp understand my problem better.

Thanks again for your response.

weisseb
Starting Member

8 Posts

Posted - 2010-10-23 : 06:05:01

That would change the over clauses above considerably.

Would it be possible for you to post an example where you atleast use two different loannums with several snapshotdts within each loannum (perhaps with overlapping snapshotdts in different loannums). And if you can provide the desired output/result value as you did last time. I don't quite get the 50 days and loans moving in and out of segments, so an example would be of great help.

deepak23
Starting Member

8 Posts

Posted - 2010-10-23 : 15:36:44

This is the table

DimSetId SnapshotDt LoanNum CurrentSegment
155 6/16/2010 1123776 11b
157 6/17/2010 1123776 11b
159 6/20/2010 1123776 11b
161 6/21/2010 1123776 11b
163 6/22/2010 1123776 11b
165 6/23/2010 1123776 11b
167 6/24/2010 1123776 11b
169 6/27/2010 1123776 11b
171 6/28/2010 1123776 11b
173 6/29/2010 1123776 11b
175 6/30/2010 1123776 11b
177 7/1/2010 1123776 11b
179 7/4/2010 1123776 11b
181 7/6/2010 1123776 11b
183 7/7/2010 1123776 11b
185 7/8/2010 1123776 11b
187 7/11/2010 1123776 11b
189 7/12/2010 1123776 11b
191 7/13/2010 1123776 11b
193 7/14/2010 1123776 11b
195 7/15/2010 1123776 11b
197 7/18/2010 1123776 11b
199 7/19/2010 1123776 11b
201 7/20/2010 1123776 11b
203 7/21/2010 1123776 11b
205 7/22/2010 1123776 11b
207 7/25/2010 1123776 11b
209 7/26/2010 1123776 11b
211 7/27/2010 1123776 11b
213 7/28/2010 1123776 11b
215 7/29/2010 1123776 11b
217 7/31/2010 1123776 11b
219 8/2/2010 1123776 11b
221 8/3/2010 1123776 11b
223 8/4/2010 1123776 11b
225 8/5/2010 1123776 11b
227 8/8/2010 1123776 11b
229 8/9/2010 1123776 11b
231 8/10/2010 1123776 11b
233 8/11/2010 1123776 11b
235 8/12/2010 1123776 11b
237 8/15/2010 1123776 11b
239 8/16/2010 1123776 11b
241 8/17/2010 1123776 11b2.1
243 8/18/2010 1123776 11b2.1
245 8/19/2010 1123776 11b2.1
247 8/22/2010 1123776 11b2.1
249 8/23/2010 1123776 11b2.1
251 8/24/2010 1123776 11b2.1
253 8/25/2010 1123776 11b2.1
255 8/26/2010 1123776 11b2.1
257 8/29/2010 1123776 11b2.1
259 8/30/2010 1123776 11b2.1
261 8/31/2010 1123776 11b2.1
263 9/1/2010 1123776 11b2.1
265 9/2/2010 1123776 11b2.1
267 9/5/2010 1123776 11b2.1
269 9/7/2010 1123776 11b2.1
271 9/8/2010 1123776 11b2.1
273 9/9/2010 1123776 11b2.1
275 9/12/2010 1123776 11b2.1
277 9/13/2010 1123776 11b2.1
279 9/14/2010 1123776 11b2.1
281 9/15/2010 1123776 11b2.1
283 9/16/2010 1123776 11b2.1
285 9/19/2010 1123776 11b2.1
287 9/20/2010 1123776 11b2.1
289 9/21/2010 1123776 11b2.1
291 9/22/2010 1123776 11b2.1
293 9/23/2010 1123776 11b2.1
295 9/26/2010 1123776 11b2.1
297 9/27/2010 1123776 11b2.1
299 9/28/2010 1123776 11b2.1
301 9/29/2010 1123776 11b2.1
303 9/30/2010 1123776 11b
305 10/3/2010 1123776 11b
307 10/4/2010 1123776 11b
309 10/5/2010 1123776 11b
311 10/6/2010 1123776 11b
313 10/7/2010 1123776 11b
315 10/10/2010 1123776 11b
317 10/12/2010 1123776 11b
319 10/13/2010 1123776 11b
321 10/14/2010 1123776 11b
323 10/17/2010 1123776 11b
325 10/18/2010 1123776 11b
327 10/19/2010 1123776 11b
329 10/20/2010 1123776 11b
331 10/21/2010 1123776 11b
155 6/16/2010 1123830 9c
157 6/17/2010 1123830 9c
159 6/20/2010 1123830 9c
161 6/21/2010 1123830 9c
163 6/22/2010 1123830 9c
165 6/23/2010 1123830 9c
167 6/24/2010 1123830 9c
169 6/27/2010 1123830 9c
171 6/28/2010 1123830 9c
173 6/29/2010 1123830 9c
175 6/30/2010 1123830 9c
177 7/1/2010 1123830 9c
179 7/4/2010 1123830 9c
181 7/6/2010 1123830 9c
183 7/7/2010 1123830 9c
185 7/8/2010 1123830 9c
187 7/11/2010 1123830 9c
189 7/12/2010 1123830 9c
191 7/13/2010 1123830 9c
193 7/14/2010 1123830 9c
195 7/15/2010 1123830 9c
197 7/18/2010 1123830 9c
199 7/19/2010 1123830 9c
201 7/20/2010 1123830 9c
203 7/21/2010 1123830 9c
205 7/22/2010 1123830 9c
207 7/25/2010 1123830 9c
209 7/26/2010 1123830 9c
211 7/27/2010 1123830 9c
213 7/28/2010 1123830 9c
215 7/29/2010 1123830 9c
217 7/31/2010 1123830 9c
219 8/2/2010 1123830 9c
221 8/3/2010 1123830 9c
223 8/4/2010 1123830 9c
225 8/5/2010 1123830 9c
227 8/8/2010 1123830 9c
229 8/9/2010 1123830 9c
231 8/10/2010 1123830 9c
233 8/11/2010 1123830 9c
235 8/12/2010 1123830 9c
237 8/15/2010 1123830 9c
239 8/16/2010 1123830 9c
241 8/17/2010 1123830 9c
243 8/18/2010 1123830 9c
245 8/19/2010 1123830 9c
247 8/22/2010 1123830 9c
249 8/23/2010 1123830 9c
251 8/24/2010 1123830 9c
253 8/25/2010 1123830 9c1c
255 8/26/2010 1123830 9c1c
257 8/29/2010 1123830 9c1c
259 8/30/2010 1123830 9c1c
261 8/31/2010 1123830 9c1c
263 9/1/2010 1123830 9c1c
305 10/3/2010 1123830 9c
307 10/4/2010 1123830 9c
309 10/5/2010 1123830 9c
311 10/6/2010 1123830 9c
313 10/7/2010 1123830 9c
315 10/10/2010 1123830 9c
317 10/12/2010 1123830 9c
319 10/13/2010 1123830 9c
321 10/14/2010 1123830 9c
323 10/17/2010 1123830 9c
325 10/18/2010 1123830 9c
327 10/19/2010 1123830 9c
329 10/20/2010 1123830 9c
331 10/21/2010 1123830 9c

I am trying to calculate the aging of a loan from the date it entered the current segment. I was initially doing it by calculating Mindate and Maxdate. But I get an error for Mindate when a Loan Comes back into the same segment after few days.
So for Loan Num 1123830 the aging in 9c as of 10/21/2010 should be - 14(10/21 -10/3). IS this clear?

weisseb
Starting Member

8 Posts

Posted - 2010-10-24 : 07:08:11

Okay, I thought the value you requested was the random column in your original post, I misunderstood. Here's what you want (I think):


select
    *,
    rn - rnWithinCurrentSegment as WeakSectionNumber,
    row_number() over (
        partition by LoanNum, CurrentSegment, rn - rnWithinCurrentSegment
        order by SnapshotDt
    ) as aging
from
(
    select
        *,
        row_number() over (partition by LoanNum order by SnapshotDt) as rn,
        row_number() over (
            partition by LoanNum, CurrentSegment
            order by SnapshotDt
        ) as rnWithinCurrentSegment
    from @TableWithLoanNum
) as t1
order by LoanNum, SnapshotDt

As before rn - rnWithinCurrentSegment gives a unique value within each "run"/section of CurrentSegment (with constant CurrentSegment). But we need to include LoanNum in the partitions, so that row_number() starts from 1 for each new LoanNum.

We now partition this result for each LoanNum, CurrentSegment and rn - rnWithinCurrentSegment. These partitions are your "runs". Within each such partition we use row_number() and order it by SnapshotDt. So the first row in a section is numbered 1, the second 2 and so forth. This is exactly the aging (in number of "row"-days, 14 days in your example).

If you want the actual number of days (i.e. 18 days in your example), instead of the aging column, use


    min(SnapshotDt) over (
        partition by LoanNum, CurrentSegment, rn - rnWithinCurrentSegment
    ) as minSnapshotDtInSection

This will give you the minimum SnapshotDt within each section, and from an "outer" query use datediff(day, minSnapshotDtInSection, SnapshotDt) and you'll have the actual number of days.

Hope that it helps you and that I understood you correctly this time.

deepak23
Starting Member

8 Posts

Posted - 2010-10-25 : 10:14:11

Thanks a lot for that script. it takes a lot of time to run the query when I am looking at a population over 11million. Anyway we could make the performance better?

weisseb
Starting Member

8 Posts

Posted - 2010-10-26 : 04:53:33

11 million is not a lot, yet quite a bit. It's one of those in between numbers. Make sure you have "proper" indexes on the table. Especially an index on LoanNum and CurrentSegment if you haven't that already.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources