i need sql query for report from table item per day(fix day1-day31 as column) of the month when i input month and year.
This is my table (item)
ID | NAME | DATE
---------------------------------------------------
1 | ITEM A | 2015-2-25 13:37:49
2 | ITEM A | 2015-2-25 14:37:49
3 | ITEM A | 2015-2-26 13:30:55
4 | ITEM B | 2015-2-26 15:37:49
5 | ITEM B | 2015-2-26 17:57:49
6 | ITEM C | 2015-2-27 13:00:33
(input month=02 and year=2015)
What I need to achieve with a view is the following:
NAME | 1| 2| 3|…|25|26|27|28|29|30|31|Total
------------------------------------------------------
ITEM A| 0| 0| 0|…| 2 | 1 | 0 | 0 | 0 | 0 | 0 | 3
ITEM B| 0| 0| 0|…| 0 | 2 | 0 | 0 | 0 | 0 | 0 | 2
ITEM C| 0| 0| 0|…| 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1
Any ideas would be very much appreciated.
Thanks in advance.
Sorry this is my first post.
You can do this using a PIVOT in your query
SELECT name,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31],
([1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] + [13] + [14] + [15] + [16] + [17] + [18] + [19] + [20] + [21] + [22] + [23] + [24] + [25] + [26] + [27] + [28] + [29] + [30] + [31]) as total
FROM
(
SELECT Name,
id,
Datepart(day, [date]) day
FROM item
WHERE MONTH([date]) = 2 AND YEAR([date]) = 2015
) x
PIVOT
(
count(id)
FOR day IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) p
This will do it for you. First test data:
CREATE TABLE data ([ID] int, [Name] varchar(30), [Date] datetime)
INSERT INTO data ([ID], [Name], [Date])
SELECT 1,'ITEM A','2015-2-25 13:37:49'
UNION ALL SELECT 2,'ITEM A','2015-2-25 14:37:49'
UNION ALL SELECT 3,'ITEM A','2015-2-26 13:30:55'
UNION ALL SELECT 4,'ITEM B','2015-2-26 15:37:49'
UNION ALL SELECT 5,'ITEM B','2015-2-26 17:57:49'
UNION ALL SELECT 6,'ITEM C','2015-2-27 13:00:33'
Then the query. Note you can use any data range, so if you want a full month just calculate that and put it in the @startDate and @endDate
DECLARE @startDate DATETIME='25-Feb-2015'
DECLARE @endDate DATETIME='28-Feb-2015'
DECLARE @numberOfDays INT = DATEDIFF(DAY, @startDate, @endDate)
declare @dayColumns TABLE (delta int, colName varchar(12))
-- Produce 1 row for each day in the report. Note that this is limited by the
-- number of objects in sysobjects (which is about 2000 so its a high limit)
-- Each row contains a delta date offset, @startDate+delta gives each date to report
-- which is converted to a valid SQL column name in the format colYYYYMMDD
INSERT INTO @dayColumns (delta, colName)
SELECT delta, 'col'+CONVERT(varchar(12),DATEADD(day,delta,@startDate),112) as colName from (
select (ROW_NUMBER() OVER (ORDER BY sysobjects.id))-1 as delta FROM sysobjects
) daysAhead
WHERE delta<=@numberOfDays
-- Create a comma seperated list of columns to report
DECLARE @cols AS NVARCHAR(MAX)= ''
SELECT @cols=CASE WHEN @cols='' THEN @cols ELSE @cols+',' END + colName FROM @dayColumns ORDER BY delta
DECLARE @totalCount AS NVARCHAR(MAX)= ''
SELECT @totalCount=CASE WHEN @totalCount='' THEN '' ELSE @totalCount+' + ' END + 'ISNULL(' + colName +',0)' FROM @dayColumns ORDER BY delta
-- Produce a SQL statement which outputs a variable number of pivoted columns
DECLARE @query AS NVARCHAR(MAX)
SELECT @query=
'declare @days TABLE (reportDay date, colName varchar(12))
INSERT INTO @days (reportDay, colName)
SELECT DATEADD(day,Delta,'''+CONVERT(varchar(22),@startDate,121)+'''), ''col''+CONVERT(varchar(12),DATEADD(day,delta,'''+CONVERT(varchar(22),@startDate,121)+'''),112) as colName from (
select (ROW_NUMBER() OVER (ORDER BY sysobjects.id))-1 as Delta FROM sysobjects
) daysAhead
WHERE Delta<='+CAST(@numberOfDays as varchar(10))+'
SELECT pivoted.*,'+@totalCount+' as total FROM (
SELECT * FROM (
select data.Name, d.colName, 1 as numRows
from @days d
LEFT OUTER JOIN data ON CAST(data.[Date] as DATE)=d.reportDay
) as s
PIVOT (
SUM(numRows) FOR colName in ('+@cols+')
) as pa
) as pivoted
WHERE Name is not null'
-- Run the query
EXEC (@query)
Output is:
Name col20150225 col20150226 col20150227 col20150228 total
------------------------------ ----------- ----------- ----------- ----------- -----------
ITEM A 2 1 NULL NULL 3
ITEM B NULL 2 NULL NULL 2
ITEM C NULL NULL 1 NULL 1
You can determine the date of each column by parsing the column header in your presentation code (it's format is colYYYYMMDD).
select convert(varchar,PaymentDate,103) AS date,
DatePart(MONTH,PaymentDate) as month,
CAST(SUM(Total_Amount) as INT) as Revenue
from Tbl_Name
where YEAR(PaymentDate) = YEAR(CURRENT_TIMESTAMP)
AND MONTH(PaymentDate) = MONTH(CURRENT_TIMESTAMP)
GROUP BY convert(varchar,PaymentDate,103),
DatePart(MONTH,PaymentDate)
order by date;
