All Forums Database
indrajit_td 50 posts Joined 10/09
13 Jun 2012
SQL Query - Help

Hi,

 I need a help in writing a query.

TABLEA

Tbl_Key

Position

Date_Key

5148005

1

20101214

5148005

2

20101214

5148005

3

20101221

5148005

4

20101221

5148005

5

20101221

5148005

6

20101214

5148005

7

20101214

5148005

8

20101221

5148005

9

20101221

5148005

10

20101213

5148005

11

20101215

 The output I need is as below:

 

Tbl_Key

Position

prin_date_key

init_date_key

Indicator

5148005

1

20101214

NULL

P

5148005

10

NULL

20101213

I

Logic:

If the Position of a record is 1 (there can be multiple records with POS=1) then get me the minimum of datekey in column prin_date_key with an indicator of 'P' and init_date_key left as blank/null

In the init_date_key i need the min of date for all records no matter what position is. So in this case min date key is for POS=10 and that value should be in init_date_key 

 

Currently I m using UNION ALL to achieve this but was curious if we can achieve this by doing a single pass on TABLEA as UNION and SELF join would require 2 passes on this table. Any thought???

 

 

Thanks,

 

 

 

INDRAJIT GAIKWAD
ulrich 816 posts Joined 09/09
13 Jun 2012

In case the min date is on position 1 would you expect 1 or 2 result rows?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

indrajit_td 50 posts Joined 10/09
13 Jun 2012

Two rows:

Tbl_Key

Position

prin_date_key

init_date_key

Indicator

5148005

1

20101213

NULL

P

5148005

1

NULL

20101213

I

Its ok if I dont have Position in my output. I can use the indicator.

 

 

 

 

INDRAJIT GAIKWAD

ulrich 816 posts Joined 09/09
14 Jun 2012

In this case the union might be best. Otherwise you would need a small product join to duplicate at least the Position 1 rows into two rows.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

indrajit_td 50 posts Joined 10/09
14 Jun 2012

Many Thanks for looking into it.. that helps.

INDRAJIT GAIKWAD

You must sign in to leave a comment.