Sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sql query

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-07-07 : 20:21:31

I have a table like this

Smkr Gender MinAge MaxAge Rate
y F 30 39 100
N M 40 49 200

Is there any way to split this table and create another table like this

Smkr Gender Agee Rate
y F 30 100
y F 32 100
y F 33 100
y F 34 100
y F 35 100
y F 36 100
y F 37 100
:::

Thank you,
kal30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 21:06:55

Use a number table or master..spt_values or F_TABLE_NUMBER_RANGE



select	s.Smkr, s.Gender, Age = n.NUMBER, s.Rate
from	table s
	inner join F_TABLE_NUMBER_RANGE(1, 99) n	
	on	s.MinAge	<= n.NUMBER
	and	s.MaxAge	>= n.NUMBER

KH
[spoiler]Time is always against us[/spoiler]

rkalyani
Starting Member

30 Posts

Posted - 2009-07-08 : 12:42:06

Thank you so much. I created a table called age and then did something like this
SELECT s.SMKR, s.Gender, n.num AS Age, s.Rate, 11 AS prdID
FROM table s INNER JOIN
Age n ON s.MinAge <= n.num AND s.MaxAge >= n.num
WHERE (s.EffDate = '02/01/2009')

I get Smkr Gender Age Rate and prdId with this method. Is there a way to multiply 1.1 to rate and create another set of record with same values but different rate. Should I use cursor for this.

Thank you,
kal30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 13:10:31

do you mean this?


SELECT s.SMKR, s.Gender, n.num AS Age, s.Rate * t.Val, 11 AS prdID
FROM table s INNER JOIN
Age n ON s.MinAge <= n.num AND s.MaxAge >= n.num
CROSS JOIN (SELECT 1 AS Val UNION ALL SELECT 1.1) t
WHERE (s.EffDate = '02/01/2009')

rkalyani
Starting Member

30 Posts

Posted - 2009-07-08 : 15:50:20

Thank you. I got it to work. I have one small thing that I would like to fix.

In my original table I have
Smkr Gender MinAge MaxAge Rate OneChild TwoChild
y F 30 39 100 25 45
N M 40 49 200 30 35

I need to select the child rate with gender as null and age as 0 for onechild and 1 for twochild. So I did this

SELECT
n.num AS Age, s.SMKR,
(case n.num
when 0 then null
when 1 then null
else
s.Gender
end)
as gender,
(case n.num
when 0 then cast(s.onechild as money(5,2))* p1.val
when 1 then cast(s.twoChild as money(5,2))* p1.val
else
cast(s.Rate as money(5,2))* p1.val
end)
as rate
FROM table s INNER JOIN
age n ON s.MinAge <= n.num AND s.MaxAge >= n.num
CROSS JOIN (SELECT 1.0 AS Val UNION ALL SELECT 1.34
Union all select 1.356
Union all select 1.446) p1

The problem is it calculates child rate the same number of time it calculates adult rate. I need to prevent duplicates. How do I do that. Also how do I make rates as two digit number. Can I use money with some parameters?

eg of my result

11 0 Y NULL 44.4600000
12 0 Y NULL 46.6830000
13 0 Y NULL 48.0168000
14 0 Y NULL 50.9511600
11 1 Y NULL 88.9500000
12 1 Y NULL 93.3975000
13 1 Y NULL 96.0660000
14 1 Y NULL 101.9367000
11 0 N NULL 37.0600000
12 0 N NULL 38.9130000
13 0 N NULL 40.0248000
14 0 N NULL 42.4707600
11 1 N NULL 74.1100000
12 1 N NULL 77.8155000
13 1 N NULL 80.0388000
14 1 N NULL 84.9300600
11 0 Y NULL 44.4600000
12 0 Y NULL 46.6830000
13 0 Y NULL 48.0168000
14 0 Y NULL 50.9511600
11 1 Y NULL 88.9500000
12 1 Y NULL 93.3975000
13 1 Y NULL 96.0660000
14 1 Y NULL 101.9367000
11 0 N NULL 37.0600000
12 0 N NULL 38.9130000
13 0 N NULL 40.0248000
14 0 N NULL 42.4707600
11 1 N NULL 74.1100000
12 1 N NULL 77.8155000
13 1 N NULL 80.0388000
14 1 N NULL 84.9300600

Thank you,
kal30

rkalyani
Starting Member

30 Posts

Posted - 2009-07-09 : 13:07:07

Is there a way to modify my original table which is like this
Smkr Gender MinAge MaxAge Rate OneChild TwoChild
y F 30 39 100 25 45
N M 40 49 200 30 35

to show something like this

Smkr Gender MinAge MaxAge Rate
y F 30 39 100
N M 40 49 200
Y 0 0 25
Y 1 1 45
N 0 0 30
N 1 1 35

Thank you,
kal30

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-09 : 13:49:31

Here is one way....not sure why you would want to do this...

DECLARE  @t  TABLE( 
                   smkr     CHAR(1), 
                   gender   CHAR(1), 
                   minage   INT, 
                   maxage   INT, 
                   rate     INT, 
                   onechild INT, 
                   twochild INT 
                   ) 

INSERT @t 
SELECT 'Y', 
       'F', 
       30, 
       39, 
       100, 
       25, 
       45 
UNION ALL 
SELECT 'N', 
       'M', 
       40, 
       49, 
       200, 
       30, 
       35 

SELECT smkr, 
       gender, 
       minage, 
       maxage, 
       rate 
FROM   @t 
UNION ALL 
SELECT smkr, 
       gender, 
       0, 
       0, 
       onechild 
FROM   @t 
UNION ALL 
SELECT smkr, 
       gender, 
       1, 
       1, 
       twochild 
FROM   @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources