Tricky Query

Question

Post reply

Tricky Query

kishoremania

Ten Centuries

Points: 1059
More actions
October 5, 2011 at 5:11 pm

#390553

I need a query
Data is in millions. I am just presenting you some data
TableA
Col1 Col2 Col3 Col4
1 121 21/10/2002 Row1
1 231 25/11/2002 Row2
1 341 28/12/2002 Row3
2 121 18/08/2003 Row4
2 231 19/09/2003 Row5
2 341 21/11/2003 Row6
TableB
Col1 Col2 Col3
1 21% 21/10/2002 Row7
1 32% 02/12/2002 Row8
1 45% 01/01/2003 Row9
2 11% 18/08/2003 Row10
2 42% 20/10/2003 Row11
2 85% 01/01/2004 Row12
If you observe the data,
Row1 Date= Row7 date
Row2 Date= between Row7 Date and Row8 Date
Row3 Date= between Row8 Date and Row9 Date
Row4 Date= Row10 date
Row5 Date= between Row10 Date and Row11 Date
Row6 Date= between Row11 Date and Row12 Date
I need to populate Col4 in TableA with Col2 in TableB if above conditions are true with same Col1.
If above conditions satisfied,
O/p looks like:
TableA
Col1 Col2 Col3 Col4
1 121 21/10/2002 21%
1 231 25/11/2002 21%
1 341 28/12/2002 32%
2 121 18/08/2003 11%
2 231 19/09/2003 11%
2 341 21/11/2003 42%

Viewing 15 posts - 1 through 14 (of 14 total)

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

kishoremania Ten Centuries Points: 1059 More actions · Answer 1

My bad!

Structure is not showed properly.

TableA: Col1-> 1..2

Col2 -> 121...341

Col3-> Date's

Col4 -> empty

TableB:

Col1 -> 1..2

Col2-> Percentages

Col3-> Date's

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

If you would post your table definitions, sample data in a readily consumable format, you increase the chance of someone assisting you with a tested answer.

To do as above, please read the article whose link in the first line of my signature block. The article contains easily useable T-SQL code to produce the information in a readily consumable format

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

kishoremania Ten Centuries Points: 1059 More actions · Answer 3

USE [AdventureWorks]

GO

CREATE TABLE [dbo].[tblA](

[ID] [smallint] NULL,

[Code] [varchar](50) NULL,

[Date] [date] NULL

)

CREATE TABLE [dbo].[tblB](

[ID] [smallint] NULL,

[Percentage] [varchar](50) NULL,

[EffectiveDate] [date] NULL

)

GO

INSERT INTO [AdventureWorks].[dbo].[tblA]

([ID]

,[Code]

,[Date])

VALUES

(1,121,'2002-02-10'),

(1,231,'2002-03-15'),

(1,322,'2002-04-20'),

(2,241,'2003-05-12'),

(2,561,'2003-07-27'),

(2,871,'2003-09-22')

GO

INSERT INTO [AdventureWorks].[dbo].[tblB]

([ID]

,[Percentage]

,[EffectiveDate])

VALUES

(1,22,'2002-02-10'),

(1,24,'2002-03-30'),

(1,15,'2002-04-25'),

(2,83,'2003-05-12'),

(2,56,'2003-08-30'),

(2,72,'2003-10-10')

GO

I need to add a extracolumn to my tblA with Percentage column in tblB

Here in tblB,Percentage column for ID 1

22 is effective from '2002-02-10'to '2002-03-30'

24 is effective from '2002-03-30'to '2002-04-25'

15 is effective from '2002-04-25'

For example,

2nd row in tblA Date is '2002-03-15', It will fall in Percentage 22

Output should look like:

1 121 '2002-02-10' 22

1 231 '2002-03-15' 22

1 322 '2002-04-20' 24

2 241 '2003-05-12' 83

2 561 '2003-07-27' 83

2 871 '2003-09-22' 56

mimran18 SSC-Addicted Points: 451 More actions · Answer 4

try this

Select *

,(Select top 1 [Percentage] from [tblB] B Where B.EffectiveDate<=A.Date

Order By .EffectiveDate Desc) as [ColB]

from [tblA] A

MI
http://raresql.com

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

How about this?

SELECT A.* , CrsAppOutput.Percentage

FROM tblA A

CROSS APPLY

(

SELECT TOP 1 B.Percentage

FROM tblB B

WHERE B.ID = A.ID

AND ( B.EffectiveDate <= A.Date )

ORDER BY B.EffectiveDate DESC

) CrsAppOutput

With proper indexing, this code perform very good 🙂 But i sense, Jeff's Quirky Update might dash everything out..let me see if i could code that..

kishoremania Ten Centuries Points: 1059 More actions · Answer 6

Thanks for answering..

Could you please tell me....how query looks if I need to append Effectivedate column(tblB) to the result set...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Like this..

SELECT A.* , CrsAppOutput.Percentage , CrsAppOutput.EffectiveDate

FROM tblA A

CROSS APPLY

(

SELECT TOP 1 B.Percentage , B.EffectiveDate

FROM tblB B

WHERE B.ID = A.ID

AND ( B.EffectiveDate <= A.Date )

ORDER BY B.EffectiveDate DESC

) CrsAppOutput

select * from tblB

select * from tblA

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 8

For that business logics , yoy could use the simple below update query:

update TableA SET COL4=COL2 from ( select col2,col3 from TableB )AS s INNER JOIN TableA on TableA.COL3=S.COL3

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

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

Performace Guard (Shehap) (10/9/2011)
For that business logics , yoy could use the simple below update query:
update TableA SET COL4=COL2 from ( select col2,col3 from TableB )AS s INNER JOIN TableA on TableA.COL3=S.COL3

Shehap... Did you try that code against the given data?

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

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 10

You are right Jeif, i have missed one business case, therefore to match up perfectly with the output results , we could go with the udpate statements in consequence :

update TableA SET COL4= s.COL2 from ( select cal2 , tableB.col1 from tableB inner join (select col1,min(col3) as col3_min from TableB group by col1) as S1 on S1.col1=TableB .col1 )AS S INNER JOIN TableA on TableA.COL1=S.COL1

update TableA SET COL4=case when table1.col3>col3_max then s.COL2 end from ( select cal2 , tableB.col1,col3_max from tableB inner join( select col1,max(col3) as col3_max from TableB group by col1) as S1 on S1.col1=TableB .col1 )AS S INNER JOIN TableA on TableA.COL1=S.COL1

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 11

Performace Guard (Shehap) (10/9/2011)
You are right Jeif, i have missed one business case, therefore to match up perfectly with the output results , we could go with the udpate statements in consequence :
update TableA SET COL4= s.COL2 from ( select cal2 , tableB.col1 from tableB inner join (select col1,min(col3) as col3_min from TableB group by col1) as S1 on S1.col1=TableB .col1 )AS S INNER JOIN TableA on TableA.COL1=S.COL1
update TableA SET COL4=case when table1.col3>col3_max then s.COL2 end from ( select cal2 , tableB.col1,col3_max from tableB inner join( select col1,max(col3) as col3_max from TableB group by col1) as S1 on S1.col1=TableB .col1 )AS S INNER JOIN TableA on TableA.COL1=S.COL1

Really ??? :w00t: Man, that query will be very very slow, as the requirement is to run the query over a million row table.. hmmm. even the query i sent will perform slow..

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 12

Performace Guard (Shehap) (10/9/2011)
You are right Jeif, i have missed one business case, ...

Heh... I have 4 letters in my name and you did me the dishonor of mispelling it. 😉

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

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 13

If huge data entity of records , we have to look for indexing enhancements since that code below could run sufficiently even for millions of record ..

For the needed indexes :

Create nonclustered index TableB_index1 on TableB (col3 asc) include (col2)

Create nonclustered index TableB_index2 on TableB (col3 asc) include (col1)

Create nonclustered index TableA_index1 on TableA (col1 asc) include (col2)

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 14

Also More enhancement

1-You could create those indexes below

Create nonclustered index TableB_index1 on TableB (col3 asc) include (col2)

Create nonclustered index TableB_index2 on TableB (col3 asc) include (col1)

Create nonclustered index TableA_index1 on TableA (col3 asc) include (col4)

2-Then apply page compression for those indexes.

3-Disable any relevant triggers while update

4-If needed for more enhancements, you could apply an appropriate schema partitioning model to get advantage of I/O parallel processing for huge date entity

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]