Help needed in the calculated column on the bases

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 2005 Forums

Transact-SQL (2005)

Help needed in the calculated column on the bases

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-03-31 : 03:00:53

select myname,mydate,myduedate,mymonthCal,mydaysleftCal,mycategoryCal,mydateyear_monthCal from tbltest

myname and mydate are in the database wh ich are value who are not allowed null.

mymonthCal,mydaysleftCal,mycategoryCal and mydateyear_monthCal are calculated column on the basis of myname and mydate

myname mydate myduedate
aa 3/31/2005 12:00:00 am 5/31/2005 12:00:00 am
12/1/2005 12:00:00 am 12/30/2006 12:00:00 am
cc 12/12/2006 12:00:00 am

expected resultset
myname mydate mymonthCal, mydaysleftCal, mycategoryCal, mydateyear_monthCal
aa 3/31/2005
bb 12/1/2005
cc

1,mymonthCal

if mydate is 12-31-2005 12:00:00 am THEN mymonth should be Dec-05

2,mydaysleftCal

if myduedate is empty it will return 39,126
other wise it will first check if myname is empty [remeber not null just ''] it will return empty string else
return the difference of current date with mydate

3,mycategoryCal

it is calculated by the excel formula on the bases mydaysleft

=IF(mydaysleftCal="","",IF(mydaysleftCal<=0,0,IF(mydaysleftCal<=10,"0 - 10",IF(mydaysleftCal<=30,"11 - 30",IF(mydaysleftCal<=60,"31 - 60",IF(mydaysleftCal<=90,"61 - 90",IF(mydaysleftCal>90,"90+","")))))))

4,mydateyear_monthCal

if mydate is 3/31/2005 12:00:00 am and month is less then 10 then mydateyear_monthCal should be like 2005_3 where 2005 is the year and 3 is the month
if month is greater then or equal to 10 then mydateyear_monthCal should be like 2005_x3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 05:47:20

1 Where do you want to show formatted data? If you use front end application, use format function there
2 Post table structure, sample data and expected result
3

Madhivanan

Failing to plan is Planning to fail

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-04-05 : 06:12:43

use case statement to replace excel IF statement

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources