stored procedure to total dates
-
In english?
-
Method #2:
Select DateName(YY, CrDate) as Year, DateName(M, CrDate) as Month, count(*) as Total
from dbo.SysObjects
where CrDate between @startdate and @enddate
group by DateName(YY, CrDate), DateName(M, CrDate), month(CrDate) --extra level
order by DateName(YY, CrDate), month(CrDate)
* Noel -
Nicer than my version... Looks like I still have to agree with you
. -
Method #1
Select Left(Dte,4) Year, Right(Dte,2) Mon, Total
From
(Select convert(char(7), CrDate,20) Dte, count(*) as Total
from dbo.SysObjects
where CrDate between @startdate and @enddate
group by convert(char(7), CrDate,20)
) d
order by Dte
* Noel -
Dam! you keep typing faster than me
* Noel -
I still have to agree
. -
I thaught that the month name was required in the select... but it obviously can be generated in the application. Thanx for the solutions.
-
That is true is just me being lazy not to type a case statement including the Right. The point is that because you are grouping by one column expression instead of two the operation finishes very fast and you leave the complexity to the outer query to do the order by and Presentation stuff.
And Yes I'll have to learn to type faster I promise I will!
* Noel -
"operation finishes very fast and you leave the complexity to the outer query to do the order by and Presentation stuff."
As it should be...
And I'll try to type slower but I can't make any promess
. -
select datepart(mm,date) as Mon,count(datepart(mm,date)) as Nbr
into ##Dates
from TableName
where datepart(yy,date) = 2005
group by date
having count(datepart(mm,date)) > 0
select [Month] = case
when mon = 1 then 'January'
when mon = 2 then 'Feburary'
when mon = 3 then 'March'
when mon = 4 then 'April'
when mon = 5 then 'May'
when mon = 6 then 'June'
when mon = 7 then 'July'
end,
[Number] = sum(nbr)
From ##Dates
group by mon
-
No chance... not gonna risk locking up the temp systables for such a simple query... and do it a 2 steps too when only one is needed.
-
He was Probably trying to demonstrate how to convert month number to characters
* Noel -
Maybe... but I'd still never use code like that in production.
-
Year included, sorted by month.
select
Year(entrydate) Y
, Month(entrydate) M
, datename(m,entrydate) MName
, count(*) C
from tblparticipants
where entrydate between @start and @end
group by
Year(entrydate)
, Month(entrydate)
, datename(m,entrydate)
order by
Year(entrydate)
, Month(entrydate)
-
declare @startdate datetime, @enddate datetime
select @startdate = '1/2/1996'
select @enddate = '1/4/1998'
select datename(m,OrderDate) + ' ' + Convert(char(4),year(OrderDate)) mon, count(*) cnt
from Northwind.dbo.Orders
where OrderDate between @startdate and @enddate
group by year(OrderDate), month(OrderDate), datename(m,OrderDate) + ' ' + Convert(char(4),year(OrderDate))
Order By year(OrderDate), month(OrderDate)
mon cnt
----------------------------------- -----------
July 1996 22
August 1996 25
September 1996 23
October 1996 26
November 1996 25
December 1996 31
January 1997 33
February 1997 29
March 1997 30
April 1997 31
May 1997 32
June 1997 30
July 1997 33
August 1997 33
September 1997 37
October 1997 38
November 1997 34
December 1997 48
January 1998 5
(19 row(s) affected)
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply