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
I didn't exactly understand what you were attempting to accomplish in step 4 (MyDateYear_MonthCal) -so I left it up for you to verify. However, I think that I have illustrated several concepts that 'should' help you move forward with this query. (A couple of datetime functions, a couple of string functions, CASE structures for switching, and the use of derived tables in place of an actual table.)
In the calculation of MyDaysLeftCal, since a number is being calculated, zero will display instead of NULL or empty string ''. Also, note that MyDate is NULL or empty string '', the date will appear as '01/01/1900'.
Code Snippet
DECLARE @.MyTable table
( MyName varchar(10),
MyDate datetime,
MyDueDate datetime
)
SET NOCOUNT ON
INSERT INTO @.MyTable VALUES ( 'aa', '3/31/2005', '5/31/2005' )
INSERT INTO @.MyTable VALUES ( 'bb', '', '' )
INSERT INTO @.MyTable VALUES ( 'dd', '04/12/2007', '04/30/2007' )
INSERT INTO @.MyTable VALUES ( 'ee', '1/31/2007', '3/31/2007' )
INSERT INTO @.MyTable VALUES ( '', '12/1/2005', '12/30/2006' )
INSERT INTO @.MyTable VALUES ( 'cc', '12/12/2006', '' )
SELECT
MyName,
MyDate,
MyMonthCal,
MyDaysLeftCal,
MyCategoryCal = CASE
WHEN MyDaysLeftCal = '' THEN ''
WHEN MyDaysLeftCal <= 10 THEN '0-10'
WHEN MyDaysLeftCal <= 30 THEN '11-30'
WHEN MyDaysLeftCal <= 60 THEN '31-60'
WHEN MyDaysLeftCal <= 90 THEN '61-90'
ELSE '90+'
END,
MyDateYear_MonthCal
FROM ( SELECT
MyName,
MyDate = convert( varchar(10), MyDate, 101 ),
MyMonthCal = ( left( datename( month, MyDate ), 3 ) + '-' +
( right( cast( year( MyDate ) as char(4) ), 2 ))),
MyDaysLeftCal = CASE
WHEN MyDueDate = '' THEN 39126
WHEN MyName = '' THEN ''
ELSE datediff( day, MyDate, getdate() )
END,
MyDateYear_MonthCal = CASE
WHEN month( MyDate ) > 10 THEN
( cast( year( MyDate ) as varchar(4) )) +
'_x' +
cast( month( MyDate ) as varchar(2) )
ELSE ( cast( year( MyDate ) as varchar(4) )) +
'_' + cast( month( MyDate ) as varchar(2) )
END
FROM @.MyTable
) dt
MyName MyDate MyMonthCal MyDaysLeftCal MyCategoryCal MyDateYear_MonthCal
- - - - - -
aa 03/31/2005 Mar-05 737 90+ 2005_3
bb 01/01/1900 Jan-00 39126 90+ 1900_1
dd 04/12/2007 Apr-07 -5 0-10 2007_4
ee 01/31/2007 Jan-07 66 61-90 2007_1
12/01/2005 Dec-05 0 2005_x12
cc 12/12/2006 Dec-06 39126 90+ 2006_x12
