February 12, 2016 at 7:11 am
A Friday afternoon noodle scratcher.
So I have the following query:
select
Cast(Cast(Year(post.CREATE_DATE) as nvarchar) + '-' + Cast(Month(post.CREATE_DATE) as nvarchar) + '-01' as DateTime) as THEDATE,
Year(post.CREATE_DATE) as THEYEAR,
Month(post.CREATE_DATE) as THEMONTH,
concat(convert(char(3),post.create_date),'-',year(post.CREATE_DATE)) as [Month-Year],
Count(distinct post.POSTING_ID) as POST_PERF,
from
[POSTING] post with (NOLOCK)
where
post.CREATE_DATE >= '20150101'
and
post.CREATE_DATE < '20160201'
group by
Cast(Cast(Year(post.CREATE_DATE) as nvarchar) + '-' + Cast(Month(post.CREATE_DATE) as nvarchar) + '-01' as DateTime) ,
Year(post.CREATE_DATE) ,
Month(post.CREATE_DATE),
concat(convert(char(3),post.create_date),'-',year(post.CREATE_DATE))
What I need to do is add another column to this to calculate the data from the previous year based on that particular month to get a month YOY comparison.
I can't do what I needed to do in SSRS so am doing this. Have always used getdate using DATEADD so am a little lost this time.
I started doing a case statement based on dates used but I think I lost myself so any help is appreciated.
February 12, 2016 at 7:34 am
Here's some sample data (which you should have posted) and a shorter version of your query.
What should the results look like for this data?
CREATE TABLE POSTING(
POSTING_ID INT IDENTITY(1,1),
CREATE_DATE DATETIME
);
INSERT INTO POSTING
VALUES
('20140101'),
('20140201'),
('20140301'),
('20140401'),
('20140501'),
('20140101'),
('20150201'),
('20150211'),
('20150301'),
('20150311'),
('20150401'),
('20150411'),
('20150501'),
('20150511'),
('20150601'),
('20150711'),
('20160101'),
('20160111'),
('20160201'),
('20160211');
SELECT *
FROM POSTING;
SELECT DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0) AS THEDATE
, YEAR(post.CREATE_DATE) AS THEYEAR
, MONTH(post.CREATE_DATE) AS THEMONTH
, STUFF(CONVERT( VARCHAR(11), post.create_date), 4, 4, '-') AS [Month-Year]
, COUNT(DISTINCT post.POSTING_ID) AS POST_PERF
FROM POSTING AS post
WHERE post.CREATE_DATE >= '20150101'
AND post.CREATE_DATE < '20160201'
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0)
, YEAR(post.CREATE_DATE)
, MONTH(post.CREATE_DATE)
, STUFF(CONVERT( VARCHAR(11), post.create_date), 4, 4, '-');
GO
DROP TABLE POSTING;
Why are you using NOLOCK hints? Are you aware of the risks of bringing incorrect data?
February 12, 2016 at 8:01 am
Apologies, I did think after I should have put up a sample of the data.
Also, I lifted a part of the sp created but am concentrating on getting the query right sorry for confusion.
Data looks like this currently:
THEDATE THEYEARTHEMONTHMonth-Year Post_Perf
01/01/201520151 Jan-15186671
01/02/201520152 Feb-15168709
01/03/201520153 Mar-15280339
01/04/201520154 Apr-15159865
01/05/201520155 May-15172875
01/06/201520156 Jun-15287016
01/07/201520157 Jul-15181495
01/08/201520158 Aug-15175767
01/09/201520159 Sep-15190672
01/10/2015201510 Oct-15194156
01/11/2015201511 Nov-15280153
01/12/2015201512 Dec-15426880
01/01/201620161 Jan-16230070
What I want is an additional column to put in the date from 12 months prior to that date.
Thanks for your help.
February 12, 2016 at 8:09 am
You could do this in PowerPivot... makes it really easy... You can use PowerBI, which is a free download too... if you can get someone to install it for you. (the drilldown capabilities in PBI are better, but I find creating models etc easier in Excel/PowerPivot)...
This is covered in both Rob Collie's book and Ferrari & Russo. If you're new to PowerPivot, definitely start with Rob's book. (and check out his website, PowerPivotPro.com[/url]
The good part about doing this in Excel/PowerBI is that the data analysts can do their thing and they'll leave you alone... except it works better against a star schema than a regular OLTP design.
February 12, 2016 at 8:22 am
Thanks for the link pietlinden, we are automating the report in SSRS so I need to do this via this method this time. However I have wanted to learn more about powerpivot and its capabilities so that's really useful actually.
February 12, 2016 at 8:41 am
I would actually remove THEYEAR, THEMONTH & Month-Year columns and leave them as formatted values in SSRS.
Here's an option:
CREATE TABLE POSTING(
POSTING_ID INT IDENTITY(1,1),
CREATE_DATE DATETIME
);
INSERT INTO POSTING
VALUES
('20140101'),
('20140201'),
('20140301'),
('20140401'),
('20140501'),
('20150101'),
('20150201'),
('20150211'),
('20150301'),
('20150311'),
('20150401'),
('20150411'),
('20150501'),
('20150511'),
('20150601'),
('20150711'),
('20160101'),
('20160111'),
('20160201'),
('20160211');
DECLARE @StartDate datetime = '20150101',
@EndDate datetime = '20160201';
DECLARE @PrevStartDate datetime = DATEADD( YY, -1, @StartDate),
@PrevEndDate datetime = DATEADD( YY, -1, @EndDate);
WITH CurrentYear AS(
SELECT DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0) AS THEDATE
, YEAR(post.CREATE_DATE) AS THEYEAR
, MONTH(post.CREATE_DATE) AS THEMONTH
, STUFF(CONVERT( VARCHAR(11), post.create_date), 4, 4, '-') AS [Month-Year]
, COUNT(DISTINCT post.POSTING_ID) AS POST_PERF
FROM POSTING AS post WITH (NOLOCK)
WHERE post.CREATE_DATE >= @StartDate
AND post.CREATE_DATE < @EndDate
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0)
, YEAR(post.CREATE_DATE)
, MONTH(post.CREATE_DATE)
, STUFF(CONVERT( VARCHAR(11), post.create_date), 4, 4, '-')
)
,PreviousYear AS(
SELECT DATEADD( yy, 1, DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0)) AS THEDATE
, COUNT(DISTINCT post.POSTING_ID) AS POST_PERF
FROM POSTING AS post WITH (NOLOCK)
WHERE post.CREATE_DATE >= @PrevStartDate
AND post.CREATE_DATE < @PrevEndDate
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0)
)
SELECT cy.THEDATE,
cy.THEYEAR,
cy.THEMONTH,
cy.[Month-Year],
cy.POST_PERF,
ISNULL( py.POST_PERF, 0) AS Prev_POST_PERF
FROM CurrentYear cy
LEFT --change to FULL if needed.
JOIN PreviousYear py ON cy.THEDATE = py.THEDATE;
GO
DROP TABLE POSTING;
February 12, 2016 at 9:27 am
Thanks alot Luis, I have just tested it. I have values manually done that I can compare to so I know the data is right.
I literally in the meantime started a CTE to see if that would be better so I really appreciate this to know that was the right thing to do.
February 12, 2016 at 6:39 pm
If you want to learn PowerPivot, get Rob Collie's book. Takes a bit to get your head around, but he's really good at walking you through a lot of stuff. Then you can go apply it to your data. It's like $25 or so. Absolutely worth it - perfect beginner book, but he goes pretty deep.
February 25, 2016 at 2:59 am
Just wanted to know, how this will be working.
DATEADD(mm, DATEDIFF(mm, 0, CREATE_DATE), 0)
Thanks in advance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply