Post reply

Row count based on datetime

  • September 28, 2009 at 5:45 am

    #138349

    Hi Guys,

    I'm new to sql. Please help me on this.

    Table ProjectDetails

    projectid integer,

    startdate datetime,

    enddate datetime,

    status varchar

    I need to count the number of rows for the month january. And also the number of rows for last 11 months from currentdate.

    Any idea?

    Thx,

    Linus

  • September 28, 2009 at 5:53 am

    #1058953

    Also,

    If I need to pupulate a temptable Month, ProjectCount for last 10 months from current month, how can I do.

    For Eg : January 10, February 15, March 1, etc.

    Thx,

    Linus

    Go to Top of Page

  • September 28, 2009 at 6:38 am

    #1058969

    Do you need to count the start date or the end date or both? I'm not entirely sure what you mean by the second part, can you provide sample data and a sample output?

    Thanks,

    Bradley Jacques

  • September 28, 2009 at 6:57 am

    #1058983

    Hi Bradley Jacques,

    Thanks for assisting me.

    I need to take report on project allocation for the last 10 months from the current month. So, for each of the last 10 months, if it falls in between the project starttime and endtime, we have to count that.

    Table ProjectDetails

    -------------------

    projectid , startdate , enddate

    1 01/01/2009 01/25/2009

    2 01/01/2009 01/30/2009

    3 01/01/2009 02/28/2009

    4 02/01/2009 02/28/2009

    5 01/01/2009 03/31/2009

    6 03/01/2009 03/31/2009

    Finally, the report needs to be for the last 10 months from current date to view the projects count.

    Month ProjectCount

    Jan 4

    Feb 3

    March 2

    ::::::::::::

    Thanks,

    Linus

  • September 28, 2009 at 8:39 am

    #1059023

    Hey,

    Give this a shot.

    The temp table #report contains the months that you need i.e. you insert the last 10 months. The query uses a subselect to lookup the relevant information... This should get you in the right direction, if you need any more help, hit me up.

    Thanks

    Bradley Jacques

    /****** Object: Table [dbo].[Clients] Script Date: 09/28/2009 09:26:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ProjectDetails](

    [ProjectID] [int] IDENTITY(1,1) NOT NULL,

    [StartDate] DATETIME NULL,

    [EndDate] DATETIME NULL,

    CONSTRAINT [ProjectIDKEY] PRIMARY KEY CLUSTERED

    (

    [ProjectID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('01/01/2009', '01/25/2009')

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('01/01/2009','01/30/2009')

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('01/01/2009','02/28/2009')

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('02/01/2009','02/28/2009')

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('01/01/2009','03/31/2009')

    INSERT INTO PROJECTDETAILS (StartDate,EndDate) values ('03/01/2009','03/31/2009')

    Create table #Report (

    MonthN datetime,

    Projects int

    )

    insert into #report (MonthN) values ('01/01/2009')

    insert into #report (MonthN) values ('02/01/2009')

    insert into #report (MonthN) values ('03/01/2009')

    insert into #report (MonthN) values ('04/01/2009')

    select Month(MonthN), (select Count(*) from projectdetails b where MONTH(a.MonthN) between MONTH(b.startdate) and MONTH(b.enddate))

    from #Report a

  • September 29, 2009 at 12:14 am

    #1059319

    Hi Bradley Jacques,

    Yes, this works. Thank you.

    Cheers

    Linus

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply