looping through a dim table

Question

looping through a dim table

NewbieCoding

Say Hey Kid

Points: 689
More actions
February 22, 2016 at 5:14 pm

#399757

I have a dim table see below where I need to grab the min effective date for every time the data changes
-----------------------------------------------------------------------------------------------------------------------
Here is the original Data set
----------------------------------------------------------------------------------------------------------------------
1000004846-12013-11-11
1000004846-12013-11-14
1000004846-12013-11-18
1000004846-12013-11-21
1000004846-12013-12-02
100000484602013-12-23
100000484602013-12-26
100000484602014-01-01
100000484602014-01-02
100000484602014-01-07
100000484602014-01-08
1000004846-12014-01-21
1000004846-12014-01-21
1000004846-12014-02-03
1000004846-12014-02-05
1000004846-12014-02-09
1000004846-12014-02-10
1000004846-12014-02-10
1000004846-12014-02-11
1000004846-12014-03-03
1000004846-12014-03-05
1000004846-12014-03-17
1000004846-12014-03-19
1000004846-12014-04-01
1000004846-12014-04-02
1000004846-12014-04-05
1000004846-12014-04-08
1000004846-12014-04-16
1000004846-12014-04-19
1000004846-12014-11-10
1000004846-12014-11-19
1000004846-12014-12-01
1000004846-12014-12-06
1000004846-12014-12-09
1000004846-12015-03-30
1000004846-12015-04-01
100000484602015-04-02
100000484602015-04-06
-----------------------------------------------------
What the data should look after looping through
-----------------------------------------------------
1000004846-12013-11-11
100000484602013-12-23
1000004846-12014-01-21
100000484602015-04-02
------------------------------------------------
any help is greatly appreciated. been banging my head against the desk for 2 days now

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

First of all, welcome aboard.

Second, if you want to get test code for an answer, please see the article at the first link in my signature line below. You'll find that if you provide readily consumable data and a place to store it, people will climb over each other to help you. Without it, not so much. I'll do it for you this time. Here's one way to provide readily consumable data...

SELECT ColA,ColB = CAST(ColB AS SMALLINT), ColC = CAST(ColC AS DATE)

INTO #TestTable

FROM (

SELECT 1000004846,-1,'2013-11-11' UNION ALL

SELECT 1000004846,-1,'2013-11-14' UNION ALL

SELECT 1000004846,-1,'2013-11-18' UNION ALL

SELECT 1000004846,-1,'2013-11-21' UNION ALL

SELECT 1000004846,-1,'2013-12-02' UNION ALL

SELECT 1000004846, 0,'2013-12-23' UNION ALL

SELECT 1000004846, 0,'2013-12-26' UNION ALL

SELECT 1000004846, 0,'2014-01-01' UNION ALL

SELECT 1000004846, 0,'2014-01-02' UNION ALL

SELECT 1000004846, 0,'2014-01-07' UNION ALL

SELECT 1000004846, 0,'2014-01-08' UNION ALL

SELECT 1000004846,-1,'2014-01-21' UNION ALL

SELECT 1000004846,-1,'2014-02-03' UNION ALL

SELECT 1000004846,-1,'2014-02-05' UNION ALL

SELECT 1000004846,-1,'2014-02-09' UNION ALL

SELECT 1000004846,-1,'2014-02-10' UNION ALL

SELECT 1000004846,-1,'2014-02-11' UNION ALL

SELECT 1000004846,-1,'2014-03-03' UNION ALL

SELECT 1000004846,-1,'2014-03-05' UNION ALL

SELECT 1000004846,-1,'2014-03-17' UNION ALL

SELECT 1000004846,-1,'2014-03-19' UNION ALL

SELECT 1000004846,-1,'2014-04-01' UNION ALL

SELECT 1000004846,-1,'2014-04-02' UNION ALL

SELECT 1000004846,-1,'2014-04-05' UNION ALL

SELECT 1000004846,-1,'2014-04-08' UNION ALL

SELECT 1000004846,-1,'2014-04-16' UNION ALL

SELECT 1000004846,-1,'2014-04-19' UNION ALL

SELECT 1000004846,-1,'2014-11-10' UNION ALL

SELECT 1000004846,-1,'2014-11-19' UNION ALL

SELECT 1000004846,-1,'2014-12-01' UNION ALL

SELECT 1000004846,-1,'2014-12-06' UNION ALL

SELECT 1000004846,-1,'2014-12-09' UNION ALL

SELECT 1000004846,-1,'2015-03-30' UNION ALL

SELECT 1000004846,-1,'2015-04-01' UNION ALL

SELECT 1000004846, 0,'2015-04-02' UNION ALL

SELECT 1000004846, 0,'2015-04-06'

) d (ColA,ColB,ColC)

;

Once that's done and only because the "date" column provides something to sort by, the difference between two ROW_NUMBER()s makes this problem a piece of cake. Here's the cake...

WITH cteEnumerateGroups AS

(

SELECT Grp = ROW_NUMBER() OVER (ORDER BY ColC)

- ROW_NUMBER() OVER (PARTITION BY ColA,ColB ORDER BY ColC)

,ColA,ColB,ColC

FROM #TestTable

)

SELECT ColA = MIN(ColA)

,ColB = MIN(ColB)

,ColC = MIN(ColC)

FROM cteEnumerateGroups

GROUP BY Grp

ORDER BY ColC

;

The results from that are...

ColA ColB ColC

----------- ------ ----------

1000004846 -1 2013-11-11

1000004846 0 2013-12-23

1000004846 -1 2014-01-21

1000004846 0 2015-04-02

(4 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 2

NewbieCoding

Say Hey Kid

Points: 689

February 23, 2016 at 9:37 am

#1860474

That worked great

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

NewbieCoding (2/23/2016)
That worked great

Great. Thank you for the feedback.

Since you're the one that will need to support it, the next question would be... do you understand how and why it works?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 4

I appreciate this. I did some testing and I see a couple of groping that doesn't make sense on different account numbers

100053607021103092013-03-11

100053607021103092013-03-12

100053607021103092013-03-13

100053607021-12013-03-14

100053607021103092013-03-22

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 5

I appreciate this. I did some testing and I see a couple of groping that doesn't make sense on different account numbers

100053607021103092013-03-11

100053607021103092013-03-12

100053607021103092013-03-13

100053607021-1 2013-03-14

100053607021103092013-03-22

expected results:

grp 1 =

100053607021103092013-03-11

100053607021103092013-03-12

100053607021103092013-03-13

grp 2 =

100053607021-1 2013-03-14

grp 3 =

100053607021103092013-03-22

Actual results:

grp 1 =

100053607021103092013-03-11

100053607021103092013-03-12

100053607021103092013-03-13

grp 2 =

100053607021-1 2013-03-14

100053607021103092013-03-22

i've been tweaking the order by and partition by with every combination and cant get it parse into 3 groups instead of three. This is skewing the data and not returning every time it changes. Any Suggestions?

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 6

NewbieCoding

Say Hey Kid

Points: 689

February 23, 2016 at 4:27 pm

#1860602

correction

trying to get 3 groups instead of 2