0

I want to add the last 12 months to a SQL Server table, something like the following:

FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15

Please can someone help with with this?

Thanks

EDIT: Table schema is as follows:

Department   Month12   Month11   Month10   Month9   Month8   Month7   Month6   Month5   Month4   Month3   Month2   Month1
----------- --------- --------- --------- -------- -------- -------- -------- -------- -------- -------- -------- -------
6
  • 3
    Please give the table definition and example "before" and "after" states. Jan 2, 2015 at 21:41
  • Hi, I have added the table schema and the first row of data that I need to add is a fixed text for department and then the last 12 months names.
    – Anupam
    Jan 2, 2015 at 21:58
  • 1
    next question, why do you need to store all 12 months? Presumably you just need to store one as the others can be calculated by just doing dateadd(month? Also what is this table actually representing anyway? Jan 2, 2015 at 22:01
  • 1
    So, you just want to insert a single row into your table? Have you looked at the INSERT INTO statement? msdn.microsoft.com/en-us/library/ms174335.aspx
    – Hannah Vernon
    Jan 2, 2015 at 22:05
  • 1
    Hi Martin and Max, I know it sounds really strange to add the data as I asked since it would make more sense to have it as a header instead of data, right? To be honest, this fixed data and the months - they were supposed to be headers instead of data. But I need to create a view with dynamic headers and feed it as an input to a third party reporting tool. Since View with Dynamic headers is not possible, it had to go with the approach of adding the header values as data. I hope you get my point!
    – Anupam
    Jan 2, 2015 at 22:38

2 Answers 2

1

Perhaps you want something like this?

INSERT INTO dbo.Test (
    Department
    , Month12
    , Month11
    , Month10
    , Month9
    , Month8
    , Month7
    , Month6
    , Month5
    , Month4
    , Month3
    , Month2
    , Month1
    )
VALUES ('Test'
    , DATEADD(MONTH, -12, GETDATE())
    , DATEADD(MONTH, -11, GETDATE())
    , DATEADD(MONTH, -10, GETDATE())
    , DATEADD(MONTH, -9, GETDATE())
    , DATEADD(MONTH, -8, GETDATE())
    , DATEADD(MONTH, -7, GETDATE())
    , DATEADD(MONTH, -6, GETDATE())
    , DATEADD(MONTH, -5, GETDATE())
    , DATEADD(MONTH, -4, GETDATE())
    , DATEADD(MONTH, -3, GETDATE())
    , DATEADD(MONTH, -2, GETDATE())
    , DATEADD(MONTH, -1, GETDATE())
);

This assumes several things.

  1. You have DATE (or perhaps DATETIME, etc) as the data type for the 12 columns for each month.
  2. You want each month to reflect the current day. For instance, since it is currently Jan 2nd, this code would insert 2014-01-02, 2014-02-02, ... 2014-12-02 into the table.

If you are using SQL Server 2012 and above, you can modify this:

DATEADD(MONTH, -1, GETDATE())

to this:

FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MMM-yyyy')

To get the date as Jan-2014. There are also a lot of other ways that are probably more efficient.

1
  • Hi max, this is very close to what I want, except my fields are all varchar(max) instead of datetime and I need month-year combo like Jan-14 instead of the full date. But thanks anyways, I guess with slight modification I can do that.
    – Anupam
    Jan 2, 2015 at 22:43
0

A possible way is to have a table with twelve numbers in it

CREATE TABLE TwelveNumbers (
    number INT PRIMARY KEY
);

Add the numbers 1 ... 12 to this table. Then you can do

INSERT INTO mytable (columnname)
SELECT DATEADD(month, -TwelveNumbers.number, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM TwelveNumbers;

Where the expression DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) is used to get the first day of the current month without any time part (found here https://stackoverflow.com/a/1520803/880990).


An advanced way looks like this:

WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<12
)
INSERT INTO mytable (columnname)
SELECT DATEADD(month, -Number, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM AllNumbers

UPDATE

As Martin points out, you want to insert all these dates into a single row. I would use a parametrized query for this, where the department and the current date (first month) are used as parameter

INSERT INTO mytable (Department, Month1, Month2, Month3, ..., Month12)
VALUES (
    @dep,
    DATEADD(month, -12, @dat),
    DATEADD(month, -11, @dat),
    DATEADD(month, -10, @dat),
    ...,
    DATEADD(month, -2, @dat),
    DATEADD(month, -1, @dat)
)

Or use the expression DATEADD(month, -N, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) with N = -12 ... -1 instead of a parameter. Note that GETDATE() alone return the current day and includes the current time.

5
  • Hi Oliver, I can't really put in static values! This is because I am generating the last 12 months based on current date (which means when the query is executed). Is there a way to achieve this without using any extra table?
    – Anupam
    Jan 2, 2015 at 21:54
  • The extra table has always the same numbers in it, but those are only used to add months to a (variable) starting date. Jan 2, 2015 at 22:02
  • 2
    This inserts a single value to 12 rows. I think they want the other way round (12 values to a single row) Jan 2, 2015 at 22:05
  • Your update would need @dat to be DATEADD(YEAR, -1, GETDATE()) for that to work.
    – Hannah Vernon
    Jan 2, 2015 at 22:54
  • 1
    @MaxVernon: Ok, I am subtracting the numbers now. Jan 2, 2015 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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