Count rows based on datetime - SQL Server Forums

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

Count rows based on datetime

Author

Topic

sunilsi
Starting Member

21 Posts

Posted - 2009-09-28 : 07:43:55

Hi Guys,

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

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

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-28 : 09:20:59

WHERE StartTime BETWEEN FromDate and ToDate OR EndTime BETWEEN FromDate and ToDate

Should work nice and juicy for you my friend (If you need brackets in your WHERE clause put them in big boy)

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-28 : 09:21:50

Or do you want me to write the whole report for you? xD

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-28 : 09:33:03

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT DatePart(mm, StartTime),
COUNT(*)

FROM ProjectDetails

WHERE StartTime BETWEEN GetDate() and DateAdd(mm, -10, GetDate()) OR EndTime BETWEEN GetDate() and DateAdd(mm, -10, GetDate())

GROUP BY DatePart(mm, StartDate)

Should work, get someone else to check that GROUP BY, I probably newbed it up.

[ /fail at query]

sunilsi
Starting Member

21 Posts

Posted - 2009-09-28 : 14:59:58

Thanks for the help. I tried this, but seems like i'm missing something.

My Table is :
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

SELECT DatePart(month, startdate),
COUNT(*)
FROM ProjectDetails
WHERE startdate BETWEEN GetDate() and DateAdd(mm, -10, GetDate()) OR enddate BETWEEN GetDate() and DateAdd(mm, -10, GetDate())
GROUP BY DatePart(mm, startdate)

No output is there on executing. Please guide me on this. Also, instead of numeric month, if it's displayed in string it would be helpful.

Thanks,
Linus

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-28 : 19:59:20

For the BETWEEN the lower value needs to be first. Since you are using a negative value in your DATEADD function, the two values are out of order.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

sunilsi
Starting Member

21 Posts

Posted - 2009-09-29 : 02:10:41

The output of the query : SELECT DatePart(month, startdate),
COUNT(*)
FROM ProjectDetails
WHERE startdate BETWEEN DateAdd(mm, -10, GetDate()) and GetDate() OR enddate BETWEEN DateAdd(mm, -10, GetDate()) and GetDate()
GROUP BY DatePart(mm, startdate)

1 4
2 1
3 1

This is not correct, it should be like :
Month ProjectCount
Jan 4
Feb 3
March 2

Kabila
Starting Member

33 Posts

Posted - 2009-09-29 : 02:38:15

Try this:
SELECT datename(mm,startdate) as MonthName,
COUNT(*)
FROM ProjectDetails
WHERE startdate BETWEEN DateAdd(mm, -10, GetDate()) and GetDate() OR enddate BETWEEN DateAdd(mm, -10, GetDate()) and GetDate()
GROUP BY datename(mm,startdate) order by MonthName

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 05:30:27

Declare @FromDate as DateTime
Set @FromDate = DateAdd(mm, -10, GetDate())

SELECT SubStr(Convert(VarChar(8), startDate, 106), 3, 6),
COUNT(*)

FROM ProjectDetails

WHERE StartTime BETWEEN @FromDate and GetDate() OR EndTime BETWEEN @FromDate and GetDate()

GROUP BY SubStr(Convert(VarChar(8), startDate, 106), 3, 6)

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 05:31:43

Whoops, Change SubStr for SUBSTRING in sql Server whoop sorry people. Sorry. :P

[ /fail at query]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources