how to match computed column to another table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how to match computed column to another table?

Author

Topic

nasman
Starting Member

15 Posts

Posted - 2011-12-16 : 21:49:50

SELECT SUM(T1.SUM_REG_AMT) AS SALES_AMT,
COUNT(DISTINCT(T1.LOYALTY_ACCOUNT_ID)) AS MEM_COUNT,
SALES_AMT / MEM_COUNT AS AVER,
case
when t1.SUM_REG_AMT < 1000 then 1
when T1.SUM_REG_AMT >= 1000 and T1.SUM_REG_AMT < 2000 then 2
when T1.SUM_REG_AMT >= 2000 and T1.SUM_REG_AMT < 3000 then 3
when T1.SUM_REG_AMT >= 3000 and T1.SUM_REG_AMT < 5000 then 4
when T1.SUM_REG_AMT >= 5000 and T1.SUM_REG_AMT < 10000 then 5
when T1.SUM_REG_AMT >= 10000 and T1.SUM_REG_AMT < 15000 then 6
when T1.SUM_REG_AMT >= 15000 and T1.SUM_REG_AMT < 20000 then 7
when T1.SUM_REG_AMT >= 20000 and T1.SUM_REG_AMT < 30000 then 8
when T1.SUM_REG_AMT >= 30000 and T1.SUM_REG_AMT < 40000 then 9
when T1.SUM_REG_AMT >= 40000 and T1.SUM_REG_AMT < 50000 then 10
when T1.SUM_REG_AMT >= 50000 and T1.SUM_REG_AMT < 70000 then 11
when T1.SUM_REG_AMT >= 70000 and T1.SUM_REG_AMT < 100000 then 12
when T1.SUM_REG_AMT >= 100000 then 13
end AS TIER_SEQ

FROM SUM_AWARD_BRANCH T1,
BRANCH T2
WHERE T1.BRANCH_ID = T2.BRANCH_ID
AND T2.CORPORATION_ID = 305
AND T1.YEAR_NO = 2011
AND T1.MONTH_NO BETWEEN 1 AND 6

GROUP BY case, T1.LOYALTY_ACCOUNT_ID
ORDER BY TIER_SEQ ASC

i have another table, that contains tier information

tier_id
lower_bound
upper_bound
and tier_seq

can i compare that computed column to another table that doesnt have an related key to the 1st 2 to table?

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 00:57:09

I am not sure how you are going to compare or join the information together.

How is the data from SUM_AWARD_BRANCH related to the other table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 03:12:07

the posted code is not syntactically correct .
for example you cant use aliases directly as in SALES_AMT / MEM_COUNT AS AVER

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources