Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Unanswered: Find How long items open using date columns

    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


    Code:
    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');

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Try this:

    Code:
    select DATEPART(mm, OpenDate) MonthNum, Count(*) Cnt
      from TestDate
     where OpenDate >= '2013-01-01' and CloseDate < DATEADD(DD, 1, '2013-02-28')
     group by DATEPART(mm, OpenDate)

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LinksUp - you need to include the year in your grouping too. I know that the question asked doesn't cover 12 months but what if it did?

    I have opted to truncate each date to the first of the month and group by that instead. This covers the years issue as well as keeping the data typing.

    Code:
    DECLARE @lbound date = '20130101'
          , @ubound date = '20130301'
    
    SELECT DateAdd(mm, DateDiff(mm, 0, opendate), 0) As first_of_month
         , Count(*) As number_of_items
    FROM   dbo.testdate
    --WHERE  opendate >= @lbound -- Optional filter to show only the two months
    --AND    opendate <  @ubound
    GROUP
        BY DateAdd(mm, DateDiff(mm, 0, opendate), 0)
    ORDER
        BY first_of_month
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •