Row count based on datetime

Question

Post reply

Row count based on datetime

sunil.si

SSC Enthusiast

Points: 151
More actions
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

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

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

sunil.si SSC Enthusiast Points: 151 More actions · Answer 1

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

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 2

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

sunil.si SSC Enthusiast Points: 151 More actions · Answer 3

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

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 4

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

sunil.si SSC Enthusiast Points: 151 More actions · Answer 5

Hi Bradley Jacques,

Yes, this works. Thank you.

Cheers

Linus