Get Daily weekly monthly and yearly running total sales

 We have a requirement get design the report to show the Daily Weekly monthly and yearly running Total sales.

 Read more about: Running Total

https://bageshkumarbagi-msbi.blogspot.com/2016/11/calculating-running-total-in-sql-server.html

 For the Demo we are using the AdventureWorksDW Database.  

Below is the SQL Script.

SELECT report_type,        year,        month,        week,        date,        number_of_sales_order,        dailysales,        wtdsales,        mtdsales,        ytdsales FROM   (SELECT 'Daily_Sales'                       AS Report_Type,                1                                   AS Report_Sort_Order,                d.calendaryear                      YEAR,                d.weeknumberofyear                  WEEK,                d.englishmonthname                  MONTH,                d.fulldatealternatekey              DATE,                Count(f.salesordernumber)           AS Number_Of_Sales_Order,                Sum(f.salesamount)                  DailySales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear, weeknumberofyear                    ORDER BY fulldatealternatekey ) WTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear, englishmonthname                    ORDER BY fulldatealternatekey ) MTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear                    ORDER BY fulldatealternatekey ) YTDSales         FROM   factinternetsales f                JOIN dimdate d                  ON f.orderdatekey = d.datekey         GROUP  BY d.calendaryear,                   d.weeknumberofyear,                   d.englishmonthname,                   d.fulldatealternatekey         UNION ALL         SELECT 'Weekly_Sales'                  AS Report_Type,                2                               AS Report_Sort_Order,                d.calendaryear                  YEAR,                d.weeknumberofyear              WEEK,                d.englishmonthname              MONTH,                NULL                            DATE,                Count(f.salesordernumber)       AS Number_Of_Sales_Order,                NULL                            DailySales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear, weeknumberofyear                    ORDER BY weeknumberofyear ) WTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear, englishmonthname                    ORDER BY englishmonthname ) MTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear                    ORDER BY d.calendaryear )   YTDSales         FROM   factinternetsales f                JOIN dimdate d                  ON f.orderdatekey = d.datekey         GROUP  BY d.calendaryear,                   d.weeknumberofyear,                   d.englishmonthname         UNION ALL         SELECT 'Monthly_Sales'                 AS Report_Type,                3                               AS Report_Sort_Order,                d.calendaryear                  YEAR,                NULL                            WEEK,                d.englishmonthname              MONTH,                NULL                            DATE,                Count(f.salesordernumber)       AS Number_Of_Sales_Order,                NULL                            DailySales,                NULL                            WTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear, englishmonthname                    ORDER BY englishmonthname ) MTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear                    ORDER BY d.calendaryear )   YTDSales         FROM   factinternetsales f                JOIN dimdate d                  ON f.orderdatekey = d.datekey         GROUP  BY d.calendaryear,                   d.englishmonthname         UNION ALL         SELECT 'Yearly_Sales'                AS Report_Type,                4                             AS Report_Sort_Order,                d.calendaryear                YEAR,                NULL                          WEEK,                NULL                          MONTH,                NULL                          DATE,                Count(f.salesordernumber)     AS Number_Of_Sales_Order,                NULL                          DailySales,                NULL                          WTDSales,                NULL                          MTDSales,                Sum(Sum(f.salesamount))                  OVER (                    partition BY d.calendaryear                    ORDER BY d.calendaryear ) YTDSales         FROM   factinternetsales f                JOIN dimdate d                  ON f.orderdatekey = d.datekey         GROUP  BY d.calendaryear) sales_Report ORDER  BY report_sort_order,           year,           week,           month,           date

Running this script.

Daily report  

Weekly Report  

Monthly Report   

Yearly Report