Thanks, SQLBlue. :blush: Very happy it does what you want and I sure do appreciate the compliment... you made my weekend. 🙂

Never too late to say thanks... you showed me that cool trick a couple of years ago. Thanks...

Heh... yeah... and some generic data with a generic table structure along with your generic code and a generic sample of what the output should look like would be a...

p.s.

Longer code is not always slower code. Longer code does the ol' "Divide and Conquer". The code I posted is what they call "Set Based" programming and that...

As you're finding out, the hard part is to "smear" the existing data into the new rows for the missing years. This type of smearing is VERY procedural and...

Matt is correct about the Tally table... but a Tally table isn't really worth a hoot when it comes to performance unless you also build the necessary Clustered Primary Key......

JD (4/12/2008)

---

Do you read or reference one or two tech books evry other month?, do online training? answer posts online? etc etc..its those kinda questions i ask...to get direction...i definetly...

Heh... I've made many such a mistake... thanks for the feedback.

Sometimes, "old school" works better...

SET NOCOUNT ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT IDENTITY(INT,1,1) AS RowNum,

ID,ST,Date

INTO #MyHead

...

So, what was the problem and how did you fix it?

Like practicing the piano, either you invest the time to get really good... or you don't. There are no shortcuts. For example, I recently answered a post where...

DateDiff and DateAdd have the tendency to round to whatever the "date part" is and, despite what many folks think, shouldn't be used to calculate what the difference between full...

Ok... thanks Tim.

You made the mistake of using ISNUMERIC to check for digits. ISNUMERIC does not equal ISALLDIGITS... try this and see what I mean...

SELECT dbo.check_digit('072---326')

Rather than just have it return...

You can shrink this down quite a bit...

[font="Courier New"] CREATE FUNCTION CreateABACheckDigit

--===== Created by Jeff Moden

        (@RoutingNumber CHAR(8))

RETURNS INT

     AS

  BEGIN

 RETURN (SELECT NULLIF(10-( SUBSTRING(@RoutingNumber,1,1)*3

                          + SUBSTRING(@RoutingNumber,2,1)*7

                          + SUBSTRING(@RoutingNumber,3,1)*1

                          + SUBSTRING(@RoutingNumber,4,1)*3

                          + SUBSTRING(@RoutingNumber,5,1)*7

                          + SUBSTRING(@RoutingNumber,6,1)*1

                          + SUBSTRING(@RoutingNumber,7,1)*3

                          + SUBSTRING(@RoutingNumber,8,1)*7)%10

                      ,10))

    END

[/font]