0

Following is my query.

Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    ,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData

I want to do conditional/processing on the dummy data column to create new dummy column. How can I use a dummy column to create a new dummy column?

1
  • I am creating a dummy column based on the value of C. I want to use the value which is now in dummy column to create new dummy column. I do not want to write above query by using the same 'Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy' in the Cash statement.
    – Mytroy2050
    Oct 16, 2015 at 13:28

2 Answers 2

2

with the help of CTE

;WITH CTE AS (Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    --,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData)

-- here you can apply your CASE with YEARDummy,MONTHDummy,DAYDummy column
SELECT *,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4 
FROM CTE 
2
  • Thanks. above query works fine. Can you tell where this CTE table stored as temporary data.?
    – Mytroy2050
    Oct 16, 2015 at 13:57
  • @PratikRamani glad it help you.-- CTE is like a derived table: it’s not stored as an object and is valid only during the execution of the primary statement.CTE results are not stored anywhere, they don't produce results a CTE is just a definition. simple-talk.com/sql/t-sql-programming/sql-server-cte-basics
    – wiretext
    Oct 16, 2015 at 15:02
1

Besides the mentioned CTE, you could also use a subquery.

SELECT  [A]
  , [C]
  , CASE WHEN MONTHDummy > 0
              AND DAYDummy < 31 THEN '30dayas'
         ELSE 'xyz'
    END AS Dummy4
FROM    (SELECT [A]
          , [C]
          , CASE WHEN [A] > 19999
                      AND [A] < 30000 THEN '12'
                 ELSE '21'
            END AS 'dummy1'
          , SUBSTRING(CAST(C AS varchar(9)), 1, 4) AS YEARDummy
          , SUBSTRING(CAST(C AS varchar(9)), 5, 2) AS MONTHDummy
          , SUBSTRING(CAST(C AS varchar(9)), 7, 2) AS DAYDummy
     FROM   TableYearlyData
    ) AS SUB
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.