[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Monthly growth percentage increase/decrease

Posted on 2014-08-26
8
Medium Priority
?
1,416 Views
Last Modified: 2014-08-26
Hi Guys,

I have stumbled across the below query from the internet and would like to know how I can apply it to my SQL query. I am currently using SQL Server 2012.

select
    month, total,
    (total::float / lag(total) over (order by month) - 1) * 100 growth
from (
    select to_char(created_at, 'yyyy-mm') as month, count(user_id) total
    from users
    group by month
) s
order by month;

Open in new window


The link to this code is http://stackoverflow.com/questions/16357876/sql-query-growth-of-users-per-month-in-percentage however it is for Postgres.

Could someone please help me translate this for MS SQL. Please note that I would need to SUM my totals for a month as an account could have multiple transactions within a single month.

Many thanks
0
Comment
Question by:databarracks
  • 4
  • 3
8 Comments
 

Author Comment

by:databarracks
ID: 40285250
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40285264
LEAD() & LAG() functions exist in SQL Server 2012

to_char(created_at, 'yyyy-mm') is a method of getting month and year from date information, this isn't hard in TSQL basically get the 1st of a month, it gives you the same effect.
SELECT
      format(yr_month,'yyyy-MM-dd')
    , total
    , (total * 1.0 / LAG(total) OVER (ORDER BY yrZ_month) - 1) * 100 growth
FROM (
            SELECT
                  DATEADD(dd, -(DAY(created_at) - 1), created_at) AS yr_month
                , COUNT(user_id)                                  AS total
            FROM users
            GROUP BY
                  yr_month
      ) s
ORDER BY
      yr_month;

Open in new window

I'm not certain that minus one belongs here, and it may be necessary to multiply that lag() value by 1.0 to get a fractional result.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40285284
Are you working with SQL Server sysusers view? Because if you do, won't work. That view don't returns deleted users.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:databarracks
ID: 40285303
Hi Guys,

PortletPaul your query seems to be working correctly:) However I am interested to know I bit more about "multiply that lag() value by 1.0" and why you are suggesting that this may be a problem?

Other than that I think we are onto a winner.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40285309
I didn't do any testing I was just "translating" using "the little grey cells"

re:"multiply that lag() value by 1.0"
Normally I multiply the divisor by 1.0  e.g. ([someintvalue] / [someotherinteger] * 1.0) as something
So I wasn't sure if integer math would take over if that wasn't done. If you are getting good results that's fine.

I'm still not sure why that minus one exists though.
0
 

Author Comment

by:databarracks
ID: 40285408
Ok great thanks for your help PortletPaul I am happy with the results
0
 

Author Closing Comment

by:databarracks
ID: 40285410
PortletPaul is always on the ball :)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40285463
thanks, is that a jingle?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question