Tally Table

Question

Post reply

Tally Table

Viewing 14 posts - 46 through 58 (of 58 total)

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

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

Take a look at the following URL for additional uses such as

Encoding/Decoding a string

Removing text between delimiters such as in HTML

Doing a "moving" average

Finding/Counting Days of the Week

Finding/Counting Weekdays in a range

http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/

I know I've used a Tally table for other things... they all revolve around finding strings, generating dates/times, and generating ranges of numbers. There's a lot to that.

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

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

karthikeyan (5/15/2008)
Jeff,
vow...Plenty of uses...
Could you please give me examples for all those uses ? I am eagerly waiting for your reply.

Sure... do a search on "Tally Table" or "Number Table"... like Matt says, there had to be a least a half dozen places where someone posted a solution using a Tally Table.

Maybe I'll write an article like Phil and Robyn did for their workbench... if I get the time.

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

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

If ya wanna have some fun, make as much performance improvement as you can to the first query found at the following URL... result must be functionally identical to the original, of course...

http://www.builderau.com.au/program/sqlserver/soa/Finding-dependencies-in-SQL-Server-2005/0,339028455,339288594,00.htm

This is dedicated to all those that think Indexes are the only thing you need to improve performance of code. :hehe:

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

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Can you explain me about Hidden RBAR or Triangular Join ? I have studied your article 'Triangular Join'. After that lot of doubts arises. Its like putting stone in a calm water.:) Really it opened my eyes,then only i started to think about my earlier queries as whether it is really a set based code or not.

I already posted it ,and somebody replied for that,but for your reference, i would like to bring the following query to your notice. Please take a look and let me know your feedback.

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

UPDATE #PfManager

SET monthEnd = ( select MAX(dt)

FROM PMag A

WHERE #PfManager.SP = A.SP

AND A.sno IN ( SELECT MID FROM Mgr WHERE SP = #PfManager.SP)

AND A.Type <>'T' Group BY SP)

where #PfManager.Flag ='P'

whether it comes under Set Based or RBAR or Hidden RBAR. I hope it comes under Hidden RBAR.

if yes, How to overcome Hidden RBAR in this kind of situation ?

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

when I read about (Traingular Join)Hidden RBAR, I got confused.

= or "Equality" Triangular Join

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

SELECT MID,Status,SP,DateStart ,DateEnd ,CheckDate = DATEADD(dd,-(DATEPART(dd, DATEADD(mm,N,DateStart))), DATEADD(mm,N,DateStart))

INTO #FinalResult

FROM #Result,Tally

WHERE N <= DATEDIFF(MONTH,DateStart,DATEADD(MM,1,DateEnd))

whether it comes under Set Based or RBAR or Hidden RBAR.I hope it also comes under Hidden RBAR.

if yes, How to overcome Hidden RBAR in this kind of situation ?

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

karthik

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

First one is definitely "hidden RBAR"... it doesn't have an explicit loop, but it does have a correlated subquery and executes that correlated sub-query once for each row updated. Impossible to fix unless I can see the Create Table statements for each table involved... and there needs to be some primary keys on those, as well.

Second one is definitely a triangular join... you are trying to return more than one row for each row in #Result, aren't you? If so, how else would you do it? The hidden RBAR of a triangular join is necessary there.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Second one is definitely a triangular join... you are trying to return more than one row for each row in #Result,

aren't you?

yes.

If so, how else would you do it? The hidden RBAR of a triangular join is necessary there.

will it hurt the performance or not ?

karthik

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

Not compared to the alternative's for generating multiple rows from a single row.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 8

for #1,

Create Table #PMgr

(

sP varchar(25) NULL,

MID varchar(10) NULL,

mm int Null,

yyyy int Null,

DateStart datetime Null,

monthEndDate datetime Null,

Flag char(1)

)

Insert into #PMgr (sP,mm,yyyy,DateStart,monthEndDate,Flag)

SELECT sP,

MIN(YYYY*100 +MM) % 100,

MIN(YYYY*100 +MM)/100,

CONVERT(DATETIME,CONVERT(VARCHAR,MIN(YYYY*100 +MM)%100) + '/'+'01'+'/'+ CONVERT(VARCHAR,MIN(YYYY*100 +MM) / 100)),

CONVERT(VARCHAR,NULL),

'P'

FROM Recal

WHERE PStarted IS NULL

AND MID IS NULL

GROUP BY sP

Insert into #PMgr (MID,mm,yyyy,DateStart,monthEndDate,Flag)

SELECT MID,

MIN(YYYY*100 +MM) % 100,

MIN(YYYY*100 +MM)/100,

CONVERT(DATETIME,CONVERT(VARCHAR,MIN(YYYY*100 +MM)%100) + '/'+'01'+'/'+ CONVERT(VARCHAR,MIN(YYYY*100 +MM) / 100)),

CONVERT(VARCHAR,NULL),

'M'

FROM Recal

WHERE PStarted IS NULL

AND MID IS NOT NULL

GROUP BY MID

Now i am updating the MonthEndDate by using the mentioned UPDATE statement.

karthik

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Karthik, you really don't pay attention to earlier suggestions made to you, do you?

Here is your code

SELECTMID,

MIN(YYYY * 100 + MM) % 100 AS mm,

MIN(YYYY * 100 + MM) / 100 AS yyyy,

CONVERT(DATETIME, CONVERT(VARCHAR, MIN(YYYY * 100 + MM) % 100) + '/' + '01' + '/' + CONVERT(VARCHAR, MIN(YYYY * 100 + MM) / 100)) AS DateStart,

CONVERT(VARCHAR,NULL) AS monthEndDate,

'M' AS Flag

FROMRecal

WHEREPStarted IS NULL

AND MID IS NOT NULL

GROUP BYMID

What is the purpose of the MIN statements?

1) Multiplying by 100 just to take modula 100?

2) Multiplying by 100 just to take integer part divided by 100?

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Run this query instead

SELECT DISTINCTMID,

MM AS mm,

YYYY AS yyyy,

DATEADD(MONTH, 12 * YYYY - 22801 + mm, '19000101') AS DateStart,

DATEADD(MONTH, 12 * YYYY - 22800 + mm, '19000101') AS monthEndDate,

'M' AS Flag

FROMRecal

WHEREPStarted IS NULL

AND MID IS NOT NULL

N 56°04'39.16"
E 12°55'05.25"

karthik M One Orange Chip Points: 29139 More actions · Answer 11

What is the purpose of the MIN statements?
1) Multiplying by 100 just to take modula 100?
2) Multiplying by 100 just to take integer part divided by 100?

Ok.I will explain it.

Assume i have a table like

MID mm YYYY

20 5 2006

20 1 2000

20 1 2008

20 12 2000

10 4 2008

10 2 2007

10 1 2006

10 12 1999

I want to retrieve the min year and its corresponding month.

thats why i used the above query ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 12

why i have used ? for your reference,

http://www.sqlservercentral.com/Forums/Topic492922-8-1.aspx#bm492981

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Jeff,

I have posted the complete query. Please add your suggestion.

karthik

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 14

Not a problem... but you really have to get in the habit of making things easy on me... post the Create Table statement and the Insert/Select/Union All's that are requried to build the example table you posted. See the URL in my signature for how to do that.

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