CODEWITHSUNDEEP
sqlsql-serverdateunion
user3486773
user3486773

Reputation: 1246

Manually adding a date (month) column to a query in SQL

So essentially I have a table that looks like this:

SalesPerson    Target 
John           50000.00
Bill           75000.00
Jake           40000.00

I want to add a new column that will make my query look like this:

SalesPerson    Target      Month
John           50000.00    01/01/14
Bill           75000.00    01/01/14
Jake           40000.00    01/01/14 
John           50000.00    02/01/14
Bill           75000.00    02/01/14
Jake           40000.00    02/01/14

And so on.... Obviously the target is a monthly value. The purpose is to be used in a pivot chart in Tableau.

The month field needs to be datetime which should be easy. The only thing coming to mind is to manually do something like:

Convert(datetime, '2014-01-01 00:00:000') as 'MONTH'

and then do that 11 more times and use UNION all each time. This just seems like a lot of text and time. I'm hoping there is a much easier way.

Thanks in advance for all the help!

Upvotes: 0

Views: 2851

Answers (1)

Lamak
Lamak

Reputation: 70678

This is a somehow bizarre scenario, why would you need to repeat every value of your table for every month?. That said, this is one way to do it:

SELECT  A.*,
        DATEADD(MONTH,B.number,'20140101') AS [Month]
FROM YourTable A
CROSS JOIN (SELECT *
            FROM master.dbo.spt_values
            WHERE type = 'P'
            AND number BETWEEN 0 AND 11) B

Do be aware that this is multiplying the number of rows of your table by 12.

Upvotes: 4

Related Questions