Get Missing Data with Tally Table

Question

Get Missing Data with Tally Table

Viewing 12 posts - 16 through 26 (of 26 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

sognibene (10/23/2008)
... I also had to change r1.[Date] to D.[Date] in the final select.

Heh, you are correct. That was a logical error on my part.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Sognibene,

Well, this one certainly slipped through the cracks... are you all set?

--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)

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Jeff, I lost quite a few brain cells on this one, as it was just a bit over my head, and would be interested to see your solution, using your tally table no doubt. If not interested, have a happy holiday.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Greg Snidow (12/24/2008)
Jeff, I lost quite a few brain cells on this one, as it was just a bit over my head, and would be interested to see your solution, using your tally table no doubt. If not interested, have a happy holiday.

If he is interested, can he still have a happy holiday? :hehe:

Happy Holidays Greg. 😛

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

What caught my eye was the following...

sognibene (10/23/2008)
I will post what I am able to get working.

That seemed to leave the problem up in the air. I wanted to know if sognibene was actually able to get something working and, if so, what it was. The reason is because I believe it's a mistake to do this as a view for performance reasons... why recalculate the same thing over and over and over? I think Seth's suggestion would be the best route to go...

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Yes, but any View solution can be converted to a pre-aggregated solution just by doing: SELECT * INTO PreAggTable FROM MyViewSolution

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Sure it can... but then why spend all those brain cells trying to do it in a single query when a proc can do the same thing and maybe a bit faster?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Brain Cells? You have Brain Cells?!? How do I get some of those??

😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

You're supposed to ask for them BEFORE Christmas! 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Brain Cells? You have Brain Cells?!? How do I get some of those??

😛 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Heh... only 365 brain cell shopping days left...

--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)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

I'll admit to not reading all the posted solutions, so maybe mine is redundant. It just continually propagates the last defined rate data forward until it is changed. The only thing you need to know about my setup is that my Tally table starts at 0 instead of 1.

declare @Rates table(

FromCurr char(3),

ToCurr char(3),

RateDate datetime,

Rate numeric(38,16),

Factor int

);

declare @FromDate datetime,

@ToDate datetime;

select @FromDate = '2008-10-15',

@ToDate = '2008-10-21';

-- INSERT SAMPLE DATA

Insert @Rates

(FromCurr, ToCurr, RateDate, Rate, Factor)

select 'USD', 'IDR', '10/16/2008', 9818.0, -1 union all

select 'USD', 'IDR', '10/20/2008', 9807.5, -1 union all

select 'USD', 'AUD', '10/16/2008', 0.6617, 1 union all

select 'USD', 'AUD', '10/20/2008', 0.68895, 1 union all

select 'USD', 'XYZ', '10/18/2008', 98.0, -1;

select x.CurrentDate, r.RateDate, r.FromCurr, r.ToCurr, r.Rate, r.Factor

from(

select DateAdd( dd, ZN, @FromDate )

from dbo.Tally

where DateAdd( dd, ZN, @FromDate ) between @FromDate and @ToDate

) x (CurrentDate)

join @Rates r

on r.RateDate = (

select Max( r1.RateDate )

from @Rates r1

where r1.RateDate <= x.CurrentDate

and r1.FromCurr = r.FromCurr

and r1.ToCurr = r.ToCurr

)

order by r.ToCurr, x.CurrentDate;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms