Insert two custom fields in sql server script - "MonthNbr","Facto 1

[BxWill]

Extracting data from sql server 2005 and sql script include the following lines;

,Convert (varchar,DD.DATE_Origin_Dt,101) DateOriginDt

,[PeriodNm] =
CASE
When Date.PeriodNm between 201001 and 201003 THEN 'Current'
When Date.PeriodNm between 200901 and 200903 THEN 'Prior'
END

Is it possible to include a column titled "MonthNbr" that will display "1","2",or "3" so that a comparison can be made for the same month across the two years - 2009 and 2010?

For example, if the PeriodNm is either 200901 (Jan2009) or 201001 (Jan2010), the value in the column titled "MonthNbr" should be 1. If the PeriodNm is either 200902 or 201002, then the value in the column titled "MonthNbr" should be 2 and so on.

The end result is a data set that I can pivot on with PeriodNm on the row labels and then have MonthNbr - 1, 2 or 3 across the columns to enable me to readily see the trends Year over Year and compare the months - Month 1 in 2009 with Month 1 in 2010,etc.

Also, would like to create a column titled "Factor" that includes 12000 on each row of data.

If this is possible/feasible, please modify the sql script above to provide a example as to how it can be accomplished.

Thanks in advance.

 

[gmmastros]

I assume your PeriodNm column is an integer. If it is, you can easily get the month number by using the mod operator. In SQL, the mod operator is represented by the % symbol.

,Convert (varchar,DD.DATE_Origin_Dt,101) DateOriginDt

,[PeriodNm] = 
  CASE
    When Date.PeriodNm between 201001 and 201003 THEN 'Current'
    When Date.PeriodNm between 200901 and 200903 THEN 'Prior'
  END
[!], [MonthNm] = PeriodNm % 100
, [Factor] = 12000[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom