none
Help needed in the calculated column on the bases of other column RRS feed

  • Question

  •  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
    Saturday, March 31, 2007 7:34 AM

Answers

  •  
    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
            

     

     

    Saturday, April 7, 2007 11:45 PM
    Moderator