Daily Report by Date With Mssql for Mutiple Column

Day by day weekly report in sql server

If you know every date is going to have at least one entry something along the lines of the following will work:

SELECT 
    Day = DATEPART(DAYOFWEEK,dateColumnName)
    , Date = dateColumnName
    , Sales = COUNT(*)
FROM
    salesTable
GROUP BY 
    dateColumnName

If it is possible that there are dates with zero sales then you should consider a temporary table or adding a dates table to your database and joining with this.

Edit:

SELECT 
    Day = DATENAME(DW,dateColumnName) -- Wrong function previously used this will get the name
    , Date = CONVERT(VARCHAR, dateColumnName,103) -- this returns in format dd/mm/yyyy other codes are available
    , Sales = COUNT(*)
FROM
    salesTable
GROUP BY 
    DATENAME(DW,dateColumnName)
    ,CONVERT(VARCHAR, dateColumnName,103)

Other codes are nicely described in this article.

Adding the following code to a stored procedure with the required parameters (I've called them startDate and endDate) means you could call this each week by executing the stored procedure providing the parameters. (Here's some Microsoft documentation on how procedures are used)

CREATE PROC procedureName (
   @startDate DATETIME
   ,@endDate DATETIME
) AS 
SELECT 
    Day = DATENAME(DW,dateColumnName) -- Wrong function previously used this will get the name
    , Date = CONVERT(VARCHAR, dateColumnName,103) -- this returns in format dd/mm/yyyy other codes are available
    , Sales = COUNT(*)
FROM
    salesTable
WHERE
    dateColumnName BETWEEN @startDate AND @endDate
GROUP BY 
    DATENAME(DW,dateColumnName)
    ,CONVERT(VARCHAR, dateColumnName,103)

There are potential disadvantages of having 2 parameters as you could actually show more than 1 week. You could create it with a single parameter and use the DATEADD function to add or remove 7 days to determine the start/end date from the single date provided.

SQL Query-Multiple date ranges

To get the sum of all values between two dates you would do it like this:

SELECT SUM(Column1)
FROM Table1
WHERE Date1 BETWEEN '2/1/2016' AND Date1 <'2/7/2016'

If you want to make it more flexible and have the query get the last week's sum you can use the DATEADD function to lag by one week:

SELECT SUM(Column1)
FROM Table1
WHERE Date1 BETWEEN DATEADD(week, -1, GETDATE()) AND Date1 < GETDATE()

If you want the result set to include a row for each week, you can use UNION to merge the queries.

Find MIN value from multiple columns in a SQL Server table - Return sys.columns ColumnName

You might also consider using the VALUES close instead of UNPIVOT. Seems cleaner and "more standard" for me. Check it out.

Table variable (Thanks, Andrew):

DECLARE @dates TABLE (
id INT,
date1 DATE,
date2 DATE,
date3 DATE,
date4 DATE,
date5 DATE,
date6 DATE)

INSERT @dates VALUES
(1,'2014-12-31',NULL,'2014-03-12','2014-04-20','2014-02-10','2014-01-06'),
(2,'2015-11-08','2014-03-18','2014-07-14',NULL,'2014-04-15','2014-01-17'),
(3,'2015-12-13','2014-11-11','2014-09-18','2014-09-01','2014-06-24','2014-01-28'),
(4,'2016-04-24','2014-12-20','2015-04-14','2015-12-27','2015-05-14',NULL),
(5,'2016-08-22','2015-11-16','2016-03-26','2016-08-31','2015-09-25','2015-02-20'),
(6,NULL,'2016-01-13','2016-08-02','2016-10-08',NULL,'2016-05-28'),
(7,'2016-09-22','2016-01-25','2017-03-06','2016-10-19','2017-02-03','2016-06-14'),
(8,'2017-05-21','2017-01-14','2017-11-07','2017-01-22','2017-02-15','2017-10-30'),
(9,'2017-12-15','2017-05-06',NULL,'2017-12-26','2017-11-07','2017-11-01');

The query:

WITH Unpivoted as (
SELECT [rowId], [date], [colId]
  FROM @dates
CROSS APPLY ( VALUES
              (id, date1, 1)
             ,(id, date2, 2)
             ,(id, date3, 3)
             ,(id, date4, 4)
             ,(id, date5, 5)
             ,(id, date6, 6)
             ) 
          AS T([rowId], [date], [colId])
)
, MinDate as (
SELECT [rowId], MIN([date]) as [date]
  FROM Unpivoted
  GROUP BY [rowId]
  )

  SELECT M.[rowId]
        ,M.[date]
        ,U.[colId]
  FROM [MinDate] as M
  JOIN Unpivoted as U
    ON M.[rowId] = u.[rowId]
   AND M.[date]  = u.[date]

generate days from date range

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

SQL grouping by month and year

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada 
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY 
  CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2) 

---

Related Topics