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

    Unanswered: Query to run against Date Columns

    What are some good querys to run against a sample table of dates and items where the results can be used for charts?

    Here is the sample table.


    Code:
    CREATE TABLE [dbo].[TestDates](
    	[ItemId] [int] IDENTITY(1,1) NOT NULL,
    	[AssignmentName] [nvarchar](max) NULL,
    	[AssignedDate] [date] NULL,
    	[DueDate] [date] NULL,
    	[PId] [int] NULL,
    	[FullName] [nvarchar](max) NULL,
    	[OpenDate] [date] NULL,
    	[CloseDate] [date] NULL,
    	[Status] [nvarchar](max) NULL,
     CONSTRAINT [PK_TestDates] PRIMARY KEY CLUSTERED 
    (
    	[ItemId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO



    Code:
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Finish Reports', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 1, 'Jeff Hunter      ', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Closed');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Error Reports', '2013-02-15 00:00:00', '2013-03-29 00:00:00', 2, 'Henry Hanks', '2013-02-19 00:00:00', NULL, 'InProgress');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Check Dates', '2013-02-15 00:00:00', '2013-02-28 00:00:00', 3, 'Pat Franks', '2013-02-25 00:00:00', NULL, 'Done');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Read Drafts', '2013-03-08 00:00:00', '2013-03-30 00:00:00', 4, 'Nancy Belkin', '2013-03-09 00:00:00', NULL, 'Open');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Finish Reports', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 2, 'Henry Hanks', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Closed');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Error Reports', '2013-02-15 00:00:00', '2013-03-29 00:00:00', 3, 'Pat Franks', '2013-03-02 00:00:00', NULL, 'InProgress');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Check Dates', '2013-02-15 00:00:00', '2013-02-28 00:00:00', 4, 'Nancy Belkin', '2013-02-15 00:00:00', NULL, 'Done');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Read Drafts', '2013-03-08 00:00:00', '2013-03-30 00:00:00', 1, 'Jeff Hunter      ', '2013-03-12 00:00:00', NULL, 'Open');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Check Dates', '2013-02-15 00:00:00', '2013-02-28 00:00:00', 1, 'Jeff Hunter      ', '2013-02-15 00:00:00', NULL, 'Done');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Read Drafts', '2013-03-08 00:00:00', '2013-03-30 00:00:00', 2, 'Henry Hanks', '2013-03-08 00:00:00', NULL, 'Open');
    
    INSERT INTO [TestDates]([AssignmentName], [AssignedDate], [DueDate], [PId], [FullName], [OpenDate], [CloseDate], [Status]) 
    	VALUES('Finish Reports', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 3, 'Pat Franks', '2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Closed');
    I have some results where I am using the datediff

    like

    Code:
    select 
    DATEDIFF(DD,[AssignedDate],[OpenDate]) As Days,
    DATEDIFF(wk,[AssignedDate],[OpenDate]) As Weeks,
    DATEDIFF(MM,[AssignedDate],[OpenDate]) As Months
    
    ,[ItemId]
    ,[AssignmentName]
    ,[PId]
    ,[FullName]
    ,[Status]
    
    
    From TestDates

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by bbt2d View Post
    What are some good querys to run against a sample table of dates and items where the results can be used for charts?
    Uhm....."SELECT" queries?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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