March 17, 2011 at 6:23 am
I have the following stored procedure which is doing a perfect job and creating a matrix style output of 41 items and showing data day wise like [1]..........[31]
Now need to add the TOTAL at the end of the day so it can show the total of the whole month also ..output required like this. (Note the TOTAL field at the end)..please help
[1]................................[29]....[30].....[31]......TOTAL
CREATE PROCEDURE [dbo].[VTR_Report_DaysOfMonthWise]
@Month int,
@Year int,
@Branch_ID int
AS
BEGIN
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
SELECT *
FROM
(
SELECT c.CLName, DATEPART(dd,cd.vtrRespDate) as 'Day', ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue'
FROM dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON cd.CLid = c.CLid
AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105)
AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)
) a
PIVOT
(
SUM(VTRValue) FOR Day IN
(
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15],
[16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29],
[30], [31]
)
) b
END
March 17, 2011 at 6:29 am
joshtheflame (3/17/2011)
I have the following stored procedure which is doing a perfect job and creating a matrix style output of 41 items and showing data day wise like [1]..........[31]Now need to add the TOTAL at the end of the day so it can show the total of the whole month also ..output required like this. (Note the TOTAL field at the end)..please help
[1]................................[29]....[30].....[31]......TOTAL
You're gonna kick yourself, it's so easy. 😀
SELECT
*
,[1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] + [13] + [14] + [15] +
[16] + [17] + [18] + [19] + [20] + [21] + [22] + [23] + [24] + [25] + [26] + [27] + [28] + [29] +
[30] + [31] AS Total
FROM
(
SELECT c.CLName, DATEPART(dd,cd.vtrRespDate) as 'Day', ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue'
FROM dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON cd.CLid = c.CLid
AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105)
AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)
) a
PIVOT
(
SUM(VTRValue) FOR Day IN
(
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15],
[16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29],
[30], [31]
)
) b
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 17, 2011 at 9:47 am
Jason awesome...:-) guess what? I just kicked myself...gosh it was so easy ..thank you so much for your guidance 🙂 I have oracle background and learning sqlserver is kinda steep for me at this moment..no complains its a project need..lol.....just added ISNULL([1],0) and it worked like a charm 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply