June 22, 2009 at 6:16 am
Hi all,
i have a table named tblAdmission(AdmissionDate DateTime, NoOfStudents int)
rows available
AdmissionDate NoOfStudents
1/Jan/2008 2
5/Jan/2008 1
10/Feb/2008 4
27/Feb/2008 3
15/Dec/2008 10
I want the result in format
AdmissionMonth NoOfStudents
-------------------------------
Jan/2008 3
Feb/2008 7
Mar/2008 0
Apr/2008 0
May/2008 0
Jun/2008 0
Jul/2008 0
Aug/2008 0
Sep/2008 0
Oct/2008 0
Nov/2008 0
Dec/2008 10
i.e. I want the month total admission and those month's records whose value is 0 or not available so that i can easily display the value in my report. I want to query statement. Thanks in advance...
-Min Chaudhary
June 22, 2009 at 6:50 am
select datepart(month,@date),sum(Noofstudents)
from temp1
group by datepart(month,@date)
June 22, 2009 at 7:46 am
or Change DATEPART to DATEADD(MM, DATEDIFF(MM, 0, AdmissionDate ), 0) to get the First of the month in datetime format (if you need to show the year) and group by it too...
Dave
June 22, 2009 at 11:52 pm
Thanks for reply but the above query will return only those months records in which records are available.
Jan/2008 3
Feb/2008 7
But i want all months in given range. Suppose i provide date between 1/Jan/2009 and 30/Jun/2009 then it should return the below result set.
Jan/2008 3
Feb/2008 7
Mar/2008 0
Apr/2008 0
May/2008 0
Jun/2008 0
If it is possible then plz suggest me. thanks...
June 23, 2009 at 7:39 am
Read Jeff Moden's article on Tally tables here: http://qa.sqlservercentral.com/articles/T-SQL/62867/
Create one, then try this:
declare @t table(date datetime,e int)
insert @t(date,e)
select '20080101',2 union
select '20080105', 3 union
select '20080210', 4 union
select '20080801', 4 union
select '20090401', 8
declare @start datetime, @end datetime
select @start = '20080101', @end = '20081231'
SELECT
dateadd(mm,N-1,@start),
coalesce(t.e,0)
FROM
tally
LEFT OUTER JOIN
@t t on MONTH(t.date) = MONTH(dateadd(mm,N-1,@start))
WHERE
N <= 1+datediff(mm, @start,@end)
You'll then need to SUM and GROUP the results of this appropriately.
Disclaimer: this ain't production code.
Nigel
How to post questions
June 24, 2009 at 4:25 am
Create new function
CREATE FUNCTION uf_GenerateDateRange
(
@StartDate DateTime,
@EndDate DateTime
)
RETURNS @TblDates TABLE
(
tmpDate DateTime
)
BEGIN
DECLARE @Inc_Date DateTime
DECLARE @End_Period DateTime
-- Convert input parameters: trunc time
SET @StartDate = CAST(CONVERT(VARCHAR(10), @StartDate, 112) AS DATETIME)
SET @EndDate = CAST(CONVERT(VARCHAR(10), @EndDate, 112) AS DATETIME)
SET @Inc_Date = @StartDate
SET @End_Period = DATEADD(day, 1, @EndDate)
WHILE (DATEDIFF(day, @Inc_Date, @End_Period)0)
BEGIN
INSERT INTO @TblDates (tmpDate) values (@Inc_Date)
SET @Inc_Date = DATEADD(day, 1, @Inc_Date)
END
RETURN
END
And use it to get needed recordset
select datepart(month,tmpDate),sum(IsNull(Noofstudents, 0))
from tblAdmission
right join dbo.uf_GenerateDateRange ('19900101', '20200101') on tmpDate = AdmissionDate
group by datepart(month,tmpDate)
June 24, 2009 at 4:33 am
I would advise strongly against using a UDF with a WHILE loop in it.
Please see Jeff's article[/url] on using a Tally table to avoid loops.
Nigel
How to post questions
June 27, 2009 at 2:23 am
Thank you very much it solved my problem...
June 28, 2009 at 10:50 pm
just for curiosity, which approach you followed, Tally table or UDF and why you followed it?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply