May 19, 2009 at 2:47 am
HI
i write query for showing the reports in SSRS ,in that report showing Month in rows and year in coloum and respective count in detaile view
BUT
i want to show the Month in rows , but since there is no all month data present in table its not showing me all Month in out put result of the query
right now my O/P: is
mode count year Month
----------------------------------
kunal81912009mar
vikram32009April
i want out put in following way:
mode count year Month
----------------------------------
x02009jan
x02009feb
kunal81912009mar
vikram32009April
x02009jun
x02009jul
x02009aug
xo2009sep
xo2009oct
xo2009nov
x02009dec
Thanks
kunal
May 19, 2009 at 3:58 am
Can you post the query/view which you are currently using?
--Ramesh
May 19, 2009 at 4:24 am
Hi thanks for reply,
here below is my query
declare
@year int,
@month int
set @year=2009
set @month=4
SELECT
IncidentMode,
Count(IncidentMode) AS ModeCount,
DATEPART (yy,CreatedDate) as CurrentModeYear,
CASE
WHEN DATEPART (mm,CreatedDate) = 3 THEN 'March'
WHEN DATEPART (mm,CreatedDate) = 4 THEN 'April'
END
AS ModeMonth
FROM
vw_MYVIEW
GROUP BY
IncidentMode,
DATEPART (yy,CreatedDate),
DATEPART (mm,CreatedDate)
HAVING
DATEPART (yy,CreatedDate)=@year
AND
DATEPART (mm,CreatedDate) <= @month
Thansk
Kunal
May 19, 2009 at 5:03 am
Check out the following code.
DECLARE@year INT, @month INT
IF ( OBJECT_ID( 'tempdb..#test' ) IS NOT NULL )
DROP TABLE #test
CREATE TABLE #test
(
IncidentMode VARCHAR(100) NOT NULL,
CreatedDate DATETIME NOT NULL
)
INSERT#test( IncidentMode, CreatedDate )
SELECT'kunal', '01-Mar-2009'
UNION ALL
SELECT'vikram', '01-Apr-2009'
SELECT@year = 2009,
@month = 4
; WITH Months
AS
(
SELECT'January' AS [Month] UNION ALL SELECT 'February' UNION ALL SELECT 'March'
UNION ALL
SELECT'April' UNION ALL SELECT 'May' UNION ALL SELECT 'June'
UNION ALL
SELECT'July' UNION ALL SELECT 'August' UNION ALL SELECT 'September'
UNION ALL
SELECT'October' UNION ALL SELECT 'November' UNION ALL SELECT 'December'
),
TestCTE
AS
(
SELECTIncidentMode, COUNT( IncidentMode ) AS ModeCount,
DATEPART( yy, CreatedDate ) AS CurrentModeYear,
DATENAME( mm, CreatedDate ) AS ModeMonth
FROM#test
GROUP BY IncidentMode, DATEPART( yy, CreatedDate ), DATENAME( mm, CreatedDate ), DATEPART( mm, CreatedDate )
HAVING DATEPART( yy, CreatedDate ) = @year
AND DATEPART( mm, CreatedDate ) <= @month
)
SELECTISNULL( IncidentMode, 'X' ) AS IncidentMode, ISNULL( ModeCount, 0 ) AS ModeCount,
ISNULL( CurrentModeYear, @year )AS CurrentModeYear, m.[Month] AS ModeMonth
FROMMonths m
LEFT JOIN TestCTE t ON m.[Month] = t.ModeMonth
--Ramesh
May 19, 2009 at 5:23 am
HI ramesh ,
Really thanks , it working fine now!
🙂
Thanks
kunal
May 19, 2009 at 8:13 am
HI ramesh,
this Query i try to put in Stored Procedure , but give mr following error
Msg 195, Level 15, State 1, Procedure usp_MonthlyEPCHazardousSpillReport_2, Line 27
'Months' is not a recognized option.
Msg 102, Level 15, State 1, Procedure usp_MonthlyEPCHazardousSpillReport_2, Line 44
Incorrect syntax near ','.
Any problem with stored procedure
Thasnks
Kunal
May 19, 2009 at 8:16 am
How about posting the code of the stored procedure?
--Ramesh
May 19, 2009 at 8:38 am
hi ramesh ,
here below is SP which is am try to create
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE PROCEDURE [HSI].[usp_MonthlyEPCHazardousSpillReport_2]
@month int,
@year int
WITH Months
AS
(
SELECT 'January' AS [Month] UNION ALL SELECT 'February' UNION ALL SELECT 'March'
UNION ALL
SELECT 'April' UNION ALL SELECT 'May' UNION ALL SELECT 'June'
UNION ALL
SELECT 'July' UNION ALL SELECT 'August' UNION ALL SELECT 'September'
UNION ALL
SELECT 'October' UNION ALL SELECT 'November' UNION ALL SELECT 'December'
),
TestCTE
AS
(
SELECT
IncidentMode,
CASE
WHEN DATEPART (yy,CreatedDate)= @Year THEN Count(IncidentMode)
END AS CurrentYear,
CASE
WHEN DATEPART (yy,CreatedDate)= @Year-1 THEN Count(IncidentMode)
END AS YearAgo,
DATEPART (yy,CreatedDate) as CurrentModeYear,
CASE
WHEN DATEPART (mm,CreatedDate) = 3 THEN 'March'
WHEN DATEPART (mm,CreatedDate) = 4 THEN 'April'
END
AS ModeMonth
FROM
vw_MonthlyEPCHazardousSpill
GROUP BY
IncidentMode,
DATEPART (yy,CreatedDate),
DATEPART (mm,CreatedDate)
HAVING
--DATEPART (yy,CreatedDate)=@year
--AND
DATEPART (mm,CreatedDate) <= @month
)
SELECT ISNULL( IncidentMode, 'Othere' ) AS IncidentMode, ISNULL( CurrentYear, 0 ) AS CurrentYear,ISNULL( YearAgo, 0 ) AS YearAgo,
ISNULL( CurrentModeYear, @year )AS CurrentModeYear
--m.[Month] AS ModeMonth
FROM Months m
LEFT JOIN TestCTE t ON m.[Month] = t.ModeMonth
Thanks
Vikram
May 19, 2009 at 8:44 am
Change this part of the procedure
CREATE PROCEDURE [HSI].[usp_MonthlyEPCHazardousSpillReport_2]
@month int,
@year int
To
CREATE PROCEDURE [HSI].[usp_MonthlyEPCHazardousSpillReport_2]
(
@month int,
@year int
)
AS
--Ramesh
May 19, 2009 at 9:01 am
hi Ramesh,
i did changes according u
but still showing me same error
here is below the changes i did in my query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE PROCEDURE [HSI].[usp_MonthlyEPCHazardousSpillReport_2]
(
@month int,
@year int
)
WITH Months
AS
(
SELECT 'January' AS [Month] UNION ALL SELECT 'February' UNION ALL SELECT 'March'
UNION ALL
SELECT 'April' UNION ALL SELECT 'May' UNION ALL SELECT 'June'
UNION ALL
.....
thansks
Vikram
May 19, 2009 at 9:02 am
hi Ramesh,
i did changes according u
but still showing me same error
here is below the changes i did in my query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE PROCEDURE [HSI].[usp_MonthlyEPCHazardousSpillReport_2]
(
@month int,
@year int
)
WITH Months
AS
(
SELECT 'January' AS [Month] UNION ALL SELECT 'February' UNION ALL SELECT 'March'
UNION ALL
SELECT 'April' UNION ALL SELECT 'May' UNION ALL SELECT 'June'
UNION ALL
.....
thansks
Vikram
May 19, 2009 at 9:06 am
uppal.kunal (5/19/2009)
hi Ramesh,i did changes according u
but still showing me same error
here is below the changes i did in my query
Didn't you read my post correctly? Just add "AS" before the WITH expresssion.
--Ramesh
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply