March 11, 2013 at 10:29 am
I have a sample view with some dates. How would you find the numbers of items open per month. Say between OpenDate and CloseDate I want to find how many were open for January, February,?
Here is a sample table with the data
CREATE TABLE [dbo].[TestDate](
[ItemTitle] [nvarchar](50) NULL,
[ItemAttachAssignDate] [date] NULL,
[ItemDetachConcludeDate] [date] NULL,
[Status] [nvarchar](50) NULL,
[FullName] [nvarchar](100) NULL,
[OpenDate] [date] NULL,
[CloseDate] [date] NULL
) ON [PRIMARY]
GO
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Done', 'Jeff Hunter ', '2013-02-18 00:00:00', '2013-02-19 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Tommy Johnson', '2013-01-22 00:00:00', '2013-01-28 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Jeff Haynes', '2012-10-17 00:00:00', '2013-02-01 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Nancy Belkin', '2012-10-28 00:00:00', '2012-12-14 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Rudolph Porche', '2013-01-16 00:00:00', '2013-02-02 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-20 00:00:00', '2013-02-07 00:00:00', 'Done', 'Pat Franks', '2013-01-20 00:00:00', '2013-01-25 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-20 00:00:00', '2013-02-07 00:00:00', 'Done', 'Leslie Jordan', '2012-11-25 00:00:00', '2012-12-04 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-20 00:00:00', '2013-02-07 00:00:00', 'Done', 'Fred Haney', '2012-10-20 00:00:00', '2013-02-04 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-20 00:00:00', '2013-02-07 00:00:00', 'Done', 'Henry Hanks', '2012-10-31 00:00:00', '2012-11-15 00:00:00');
March 11, 2013 at 12:38 pm
Excellent job posting ddl and sample data. There are a couple ways this could be done and it somewhat depends on what you want as output. Do you want to see all months regardless of if there are rows for that month? Also, do you want all Januarys grouped together or are you looking to have January 2012 and January 2013 as separate rows? Do you only want to see the could for any given month?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2013 at 12:51 pm
Thanks for the help!
I Want to see all months even if there are no rows, January 2012 and January 2013 should be seperate rows
March 11, 2013 at 3:54 pm
Not totally sure what you want for output but this is probably a decent place to at least get you started.
I first added 1 more row to your table so we have some rows that would be in the same month across years.
insert testdate
select null, '2012-01-01', '2012-02-15', 'Done', 'Test Name', '2012-01-13', '2012-02-04'
Now since you want to see all months even if there is no match for that month you need to use a tally table. You can read it here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
Then I just use a cte to figure the date range.
;with MinDates as
(
select Min(datediff(month, 0, OpenDate)) as MinOpenDate,
Max(datediff(month, 0, CloseDate)) as MaxCloseDate
from TestDate
)
select dateadd(month, t.N, 0) as ReportingMonth, count(td.CloseDate) as OpenItems
from tally t
join MinDates x on t.N >= x.MinOpenDate and t.N <= x.MaxCloseDate
left join [TestDate] td on dateadd(month, t.N, 0) >= dateadd(mm, datediff(mm, 0, td.OpenDate ), 0)
and dateadd(month, t.N, 0) <= dateadd(mm, datediff(mm, 0, td.CloseDate), 0)
group by dateadd(month, t.N, 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2013 at 4:54 pm
Without using a tally table and using a loop. However not sure why want to show Dates from Jan 2012 when there is no data?
If you do just change the Start month and Start Year below to 01 and 2012 as shown.
DECLARE @Months Table(m int, y int)
DECLARE @startMonth int
DECLARE @startYear int
DECLARE @endMonth int
DECLARE @endYear int
--SET @startMonth = (select month(min(OpenDate)) from dbo.TestDate)
--SET @startYear = (select year(min(OpenDate)) from dbo.TestDate)
SET @startMonth = 1
SET @startYear = 2012
SET @endMonth = (select month(max(OpenDate)) from dbo.TestDate)
SET @endYear = (select year(max(OpenDate)) from dbo.TestDate)
WHILE @startYear <= @endYear
BEGIN
WHILE (@startYear <> @endYear) OR (@startMonth <> @endMonth)
BEGIN
INSERT INTO @Months VALUES (@startMonth, @startYear)
IF( @startMonth = 12)
BEGIN
SET @startMonth = 1; BREAK
END
ELSE SET @startMonth = @startMonth + 1
END
SET @startYear = @startYear + 1
END
select
RIGHT('0' + CAST(m.m as varchar(2)),2) + '/' +CAST(m.y as varchar(4)) as MonthYear
,isnull(n.noItems,0) as [No of Items Opened]
from @Months m
left join
(
select count(FullName)as noItems ,MONTH(OpenDate) as m ,YEAR(OpenDate) as y
from Testdate
group by YEAR(OpenDate), MONTH(OpenDate)
)n on m.m = n.m and m.y = n.y
Order by m.y,m.m asc
March 11, 2013 at 9:30 pm
/*Initial variables defining the range of dates to return*/
DECLARE @ReportedMonths int, @StartMonth datetime
SET @StartMonth = '20120101'
SET @ReportedMonths = 15
SELECT CalendarMonth, ISNULL(Report.OpenedItems, 0)
/*LEFT JOIN allows to return every month in the selected range.
Zero is presented where there are no corresponding records in Report */
FROM (/* Tally part - selecting all the month in the selected range*/
SELECT DATEADD(mm, N-1, @StartMonth) CalendarMonth
FROM dbo.Tally AS T
WHERE N > 0 AND N <= @ReportedMonths
/* Some people use Tally table started with 1, others with 0, this version of the script will work with both options */
) T
LEFT JOIN (/* grouping and counting "open" events by month */
SELECT DATEADD(MM, DATEDIFF(MM, 0, Opendate), 0) ReportingMonth, COUNT(opendate) OpenedItems
FROM testdate
GROUP BY DATEADD(MM, DATEDIFF(MM, 0, Opendate), 0)
) Report ON Report.ReportingMonth = T.CalendarMonth
ORDER BY CalendarMonth
_____________
Code for TallyGenerator
March 12, 2013 at 7:16 am
bugg (3/11/2013)
Without using a tally table and a loop. However not sure why want to show Dates from Jan 2012 when there is no data?If you do just change the Start month and Start Year below to 01 and 2012 as shown.
DECLARE @Months Table(m int, y int)
DECLARE @startMonth int
DECLARE @startYear int
DECLARE @endMonth int
DECLARE @endYear int
--SET @startMonth = (select month(min(OpenDate)) from dbo.TestDate)
--SET @startYear = (select year(min(OpenDate)) from dbo.TestDate)
SET @startMonth = 1
SET @startYear = 2012
SET @endMonth = (select month(max(OpenDate)) from dbo.TestDate)
SET @endYear = (select year(max(OpenDate)) from dbo.TestDate)
WHILE @startYear <= @endYear
BEGIN
WHILE (@startYear <> @endYear) OR (@startMonth <> @endMonth)
BEGIN
INSERT INTO @Months VALUES (@startMonth, @startYear)
IF( @startMonth = 12)
BEGIN
SET @startMonth = 1; BREAK
END
ELSE SET @startMonth = @startMonth + 1
END
SET @startYear = @startYear + 1
END
select
RIGHT('0' + CAST(m.m as varchar(2)),2) + '/' +CAST(m.y as varchar(4)) as MonthYear
,isnull(n.noItems,0) as [No of Items Opened]
from @Months m
left join
(
select count(FullName)as noItems ,MONTH(OpenDate) as m ,YEAR(OpenDate) as y
from Testdate
group by YEAR(OpenDate), MONTH(OpenDate)
)n on m.m = n.m and m.y = n.y
Order by m.y,m.m asc
How can you say that is without using a loop?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2013 at 7:20 am
Sergiy I don't think yours is returning the correct information. The OP wants the count of rows that are open during the month. For example look at November 2012. Yours returns 1, but there are a total of 5 that open during the month because they were opened earlier and do not close until later.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2013 at 9:47 pm
Sean Lange (3/12/2013)
Sergiy I don't think yours is returning the correct information. The OP wants the count of rows that are open during the month. For example look at November 2012. Yours returns 1, but there are a total of 5 that open during the month because they were opened earlier and do not close until later.
Yeah, I thought about it as well.
That's about how you read it.
"Are open" means "opening event appened within a month" or "remain open during the month".
Then "remain open during the month" - is it "being open for whole month" or "been open for most of the month" or "been open for at least a moment within the month"?
Since it's not clear from OP I posted the simplest option of the query (naturally ;-)).
_____________
Code for TallyGenerator
March 12, 2013 at 10:03 pm
This version returns 5 for Nivember 2012:
/*Initial variables defining the range of dates to return*/
DECLARE @ReportedMonths int, @StartMonth datetime
SET @StartMonth = '20120101'
SET @ReportedMonths = 15
SELECT CalendarMonth, COUNT(TD.OpenDate) OpenIems
/*LEFT JOIN allows to return every month in the selected range.
Zero is presented where there are no corresponding records in Report */
FROM (/* Tally part - selecting all the month in the selected range*/
SELECT DATEADD(mm, N-1, @StartMonth) CalendarMonth
FROM Service.dbo.Tally AS T
WHERE N > 0 AND N <= @ReportedMonths
/* Some people use Tally table started with 1, others with 0, this version of the script will work with both options */
) T
/* insluding all items which have been open within the month or remained open fior at least 1 day within the month */
LEFT JOIN dbo.testdate TD ON TD.CloseDate > T.CalendarMonth AND TD.Opendate < DATEADD(mm, 1, T.CalendarMonth)
GROUP BY CalendarMonth
ORDER BY CalendarMonth
Once again - if this is what's required.
_____________
Code for TallyGenerator
March 13, 2013 at 2:33 am
Sean Lange (3/12/2013)
bugg (3/11/2013)
Without using a tally table and a loop. However not sure why want to show Dates from Jan 2012 when there is no data?If you do just change the Start month and Start Year below to 01 and 2012 as shown.
DECLARE @Months Table(m int, y int)
DECLARE @startMonth int
DECLARE @startYear int
DECLARE @endMonth int
DECLARE @endYear int
--SET @startMonth = (select month(min(OpenDate)) from dbo.TestDate)
--SET @startYear = (select year(min(OpenDate)) from dbo.TestDate)
SET @startMonth = 1
SET @startYear = 2012
SET @endMonth = (select month(max(OpenDate)) from dbo.TestDate)
SET @endYear = (select year(max(OpenDate)) from dbo.TestDate)
WHILE @startYear <= @endYear
BEGIN
WHILE (@startYear <> @endYear) OR (@startMonth <> @endMonth)
BEGIN
INSERT INTO @Months VALUES (@startMonth, @startYear)
IF( @startMonth = 12)
BEGIN
SET @startMonth = 1; BREAK
END
ELSE SET @startMonth = @startMonth + 1
END
SET @startYear = @startYear + 1
END
select
RIGHT('0' + CAST(m.m as varchar(2)),2) + '/' +CAST(m.y as varchar(4)) as MonthYear
,isnull(n.noItems,0) as [No of Items Opened]
from @Months m
left join
(
select count(FullName)as noItems ,MONTH(OpenDate) as m ,YEAR(OpenDate) as y
from Testdate
group by YEAR(OpenDate), MONTH(OpenDate)
)n on m.m = n.m and m.y = n.y
Order by m.y,m.m asc
How can you say that is without using a loop?
I meant to say without using a tally table and using loop instead.
March 13, 2013 at 7:14 am
bugg (3/13/2013)
I meant to say without using a tally table and using loop instead.
:w00t: A set based query will out perform a loop any day. We spend a lot of time and effort around here finding solutions that don't use loops.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 7:54 am
I know it will, I was just giving an alternative approach.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply