January 23, 2014 at 11:06 am
Hi guys
I need to get last 6 months of data grouped by month end dates....
I need to automate this query so that it always looks for date for last 6 months
How do I achieve this?
The reason to write the automated query is that this sql script will go to business objects for reporting.
I use pl/sql
here is ddl
create table #Something
(
base_date datetime, connections int
)
insert #Something
select '01 jul 2013', 21 union all
select '02 jul 2013', 22 union all
select '03 jul 2013', 210 union all
....
...
select '31 jul 2013', 498 unionall
select '01 aug 2013', 44 union all
select '05 aug 2013', 66 union all
...
....
select '03 dec 2013' , 456 union all
.
.
'select '31 dec 2013', 788
..............................................................................
Desired output
Base Date Connection
31 jul 500 (not accurate, just random)
31 Aug 600
30 Sep 356
31 Oct 676
30 Nov 544
31 Dec 456
..............................................................................
Hope this helps
January 23, 2014 at 11:13 am
hi,
As you may notice, this forum is basically for SQL Server users and most of us would come with a solution using T-SQL instead of PL/SQL. would that be fine with you?
January 23, 2014 at 11:21 am
Yes that should be fine. Thanks
January 23, 2014 at 11:35 am
Here's an option. I added comments trying to explain what it's doing. If you have any questions, feel free to ask.
SELECT --This formula adds the difference of months between base_date and a start date (0 is generally equal to 1900-01-01) plus one
-- that would give us the first date of the month following the base date, then it just goes back one day
DATEADD( DD, -1, DATEADD( MONTH, DATEDIFF( MONTH, 0, base_date) + 1, 0)) base_date,
SUM(connections) connections
FROM #Something
--This formula is similar to the first but it just gets the beginning of the month minus 6 months
WHERE base_date >= DATEADD( MONTH, DATEDIFF( MONTH, 0, GETDATE()) - 6, 0)
GROUP BY DATEADD( DD, -1, DATEADD( MONTH, DATEDIFF( MONTH, 0, base_date) + 1, 0))
This will only return months with connections. If you don't have connections in a month it won't show that month. To do that, you need a calendar table to do a left join on it.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply