Tricky Query help!

Question

Post reply

Tricky Query help!

khushbu

SSCarpal Tunnel

Points: 4591
More actions
May 3, 2010 at 3:17 am

#222089

I have a requirement of having a column like
LMR200700000001
LMR200700000002
..
..
..
Here LMR is hard coded, 2007 is the year generated on some condition (like take 19 century if this else 20 century). Then there should be padding of 0 and the last digit is generated by dense rank function (pretty comples huh!)
to generate this number using dense rank many tables are joined, etc and year genaration too requires those tables.
Leading zeros are genareted using REPLACE function (this assumes that column is already populated with value. it will only add zeros to that col.)
now if add query for year generation , it is summing up like
2007 + 1= 2008.. hence wrong result
Also, i cannot concatenate LMR + year query + dense_rank query.
This is all very tedious. Do you have any more method which can give me correct result.
My query looks something like
SELECT 'LMR' + (case when opo.der < 90 then '20'+opo.der else '19'+opo.der end)
from my_table opo --- this gives me LMR2007
the replace command
SELECT REPLACE(STR(column, 8), SPACE(1), '0') -- now this column assumes that last digit is already populated with correct values.
Then i'm thinking of joining the results of these two columns
I hope i have not confused too much.

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I'm a bit confused...

Can you post table scripts, some sample data, expected output and what you have coded so far?

Please read the article linked in my signature, it will help you in this task.

-- Gianluca Sartori

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

sorry for confusing..

but there are 6/7 tables involved and query is too complex.

May be i can have my question like this.

Can I use replace command (to add leading zeros) with a dense_rank function.

If yes, how?

below gives me error.

SELECT (replace(str(dense_rank() over (partition by opo.dein, opo.delc order by opo.deop, opo.der)),8),SPACE(1),'0'))

from lbz.dbo.droposl opo

However if I take only dense_rank part it works

select dense_rank() over (partition by opo.dein, opo.delc order by opo.deop, opo.der) from

lbz.dbo.droposl opo

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Can't you apply the replace part later?

You could use a subquery or a CTE to do that.

-- Gianluca Sartori

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 4

well I can do that but then it involves billions of rows.. which will surely affect performance.

In addition, if I am able to accomplish this it will become more easier for me to add remaining data required for that column.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Reformatting a query with a CTE doesn't affect performance, it's up to the optimizer to choose the appropriate query plan and a simple CTE won't hurt.

I'm a bit concerned about the windowed aggregate on billions of rows... That's a big performance hit.

-- Gianluca Sartori