Find month totals for items open usingdate columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find month totals for items open usingdate columns

Author

Topic

bbt2d
Starting Member

13 Posts

Posted - 2013-03-11 : 13:26:53

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 each month January, February, etc. ?


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

[code]
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');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 14:11:12

do you've a calendar table? you need to use one here and apply correlated query wit your table to get Month wise count

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bbt2d
Starting Member

13 Posts

Posted - 2013-03-11 : 14:30:46

No I do not have a calendar table, but I am going to make one!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 15:13:31

ok...you can use below logic

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources