Getting data for same month last year

Question

Hi i am writing a report which will be doing monthly comparisons based on the current date and  i am trying to get data for the same point in time for this month last year in SQL 2008 using dateadd or datediff (for example today one column will show data from january 1st to 16th 2013 and the other column will display info from january 1st to 16th from 2012) 

thanks

Answer 1

What is your question? Use dateadd(year,-1, ReportDate) to subtract a year from date column.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

what i mean is i will need to find, for example, products sold since the first to the current day of the month last year

Answer 3

select count(*) as Sold, ProductId 

from Sales

where SaleDate between convert(date, cast(datepart(year,CURRENT_TIMESTAMP)-1 as char(4)) + '0101')
and convert(date, cast(datepart(year,CURRENT_TIMESTAMP)-1 as char(4)) + 
right(convert(varchar(8),CURRENT_TIMESTAMP,112),4))
GROUP BY ProductId

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

--This year

WHERE SaleDate>=dateadd(MONTH, datediff(MONTH,0, getDate()), 0) AND SaleDate < GETDATE()


--Last Year
WHERE SaleDate>=DATEADD(YEAR,-1,dateadd(MONTH, datediff(MONTH,0, getDate()), 0)) AND SaleDate < DATEADD(YEAR,-1, getDate())

Answer 5

Hi, I did MTD and YTD calculations in TSQL. Maybe this might help. Please refer to URL below.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/94f8905e-7005-490e-a903-c36f322f9db4

Answer 6

where <DateField> BETWEEN
 dateadd(month, datediff(month, 0, getutcdate())-1,0)
and 
 dateadd(year, -1, getutcdate())

---

PAC

Answer 7

amazing, this worked just fine.

Answer 8

Hi

See the below example ,hope I think this example is relevant to your requirement :

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):
CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)
INSERT INTO Sales VALUES (‘January’, 100)
INSERT INTO Sales VALUES (‘February’, 200)
INSERT INTO Sales VALUES (‘March’, 300)
SELECT * FROM SALES
 
Month             SaleAmount
—————-  ———–
January           100
February          200
March             300 
Suppose we wanted to convert the above into this: 
 
January     February    March
———– ———-  ———-
100         200         300
We can do this using the PIVOT operator, as follows: 
SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt
However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:
In the first table I have the column names I want to use:
CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, ‘Country’)
INSERT INTO Table1 VALUES(2, ‘Month’)
INSERT INTO Table1 VALUES(3, ‘Day’)
In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:
CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))
INSERT INTO Table2 VALUES (1,1, ‘US’)
INSERT INTO Table2 VALUES (1,2, ‘July’)
INSERT INTO Table2 VALUES (1,3, ’4′)
INSERT INTO Table2 VALUES (2,1, ‘US’)
INSERT INTO Table2 VALUES (2,2, ‘Sep’)
INSERT INTO Table2 VALUES (2,3, ’11′)
INSERT INTO Table2 VALUES (3,1, ‘US’)
INSERT INTO Table2 VALUES (3,2, ‘Dec’)
INSERT INTO Table2 VALUES (3,3, ’25′)
Now I would like to retrieve data from these two tables, in the following format: 
tID         Country    Day        Month
———– ———- ———- ———-
1           US         4          July
2           US         11         Sep
3           US         25         Dec 
In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like: 
SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;
However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1. 
In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].
Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like: 
DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ‘,[' + colName + ']‘,
                         ‘[' + colName + ']‘)
FROM    Table1
ORDER BY colName
This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH. 
DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                ‘],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']‘
This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries. 
Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like: 
DECLARE @query NVARCHAR(4000)
SET @query = N’SELECT tID, ’+
@cols +‘
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( ’+
@cols +‘ )
) AS pvt
ORDER BY tID;’
Executing this with 
EXECUTE(@query)
will give us the expected result: a table that is pivoted and shows columns that were specified in a table:
tID         Country    Day        Month
———– ———- ———- ———-
1           US         4          July
2           US         11         Sep
3           US         25         Dec

Source :http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/