June 22, 2010 at 11:28 pm
Hello ! Guys
here I have a SP which is performing well, but I need to modify something in it.
I am giving the SP for your understanding :-
---------->>
IF OBJECT_ID('Proc_PerfectJourneyReport_Business') IS NOT NULL
DROP PROC Proc_PerfectJourneyReport_Business;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Subhro Mukherjee
-- Create date: 15/06/10
-- Description: Proc_PerfectJourneyReport_Business
-- =============================================
CREATE PROC Proc_PerfectJourneyReport_Business
@Enterprise VARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN
SELECT COUNT(c.Container_Key) AS CountContainer,
100*COUNT(c.Container_Key)/(SELECT COUNT(Container_Key) FROM dbo.Container) AS RATE
FROM
dbo.Container c
INNER JOIN dbo.Partner_Master p
ON c.Enterprise_Partner_Key = p.Partner_Key
WHERE Perfect_Journey_Biz_Exception_Flag IS NULL
--OR Perfect_Journey_Biz_Exception_Flag = 0
AND @Enterprise = p.Partner_Name
GROUP BY p.Partner_Name
UNION ALL
select COUNT(c.Container_Key) AS CountContainer,
100*COUNT(c.Container_Key)/(SELECT COUNT(Container_Key) FROM dbo.Container) AS RATE
FROM
dbo.Container c
INNER JOIN dbo.Partner_Master p
ON c.Enterprise_Partner_Key = p.Partner_Key
where DATEPART(YEAR,Begin_Journey_Date)=YEAR(DATEADD(YYYY, DATEDIFF(YYYY,1900,getdate()), 1900))
AND Perfect_Journey_Biz_Exception_Flag IS NULL
--OR Perfect_Journey_Biz_Exception_Flag = 0
AND @Enterprise = p.Partner_Name
GROUP BY p.Partner_Name
UNION ALL
select COUNT(c.Container_Key) AS CountContainer,
100*COUNT(c.Container_Key)/(SELECT COUNT(Container_Key) FROM dbo.Container) AS RATE
FROM
dbo.Container c
INNER JOIN dbo.Partner_Master p
ON c.Enterprise_Partner_Key = p.Partner_Key
where DATEPART(quarter,Begin_Journey_Date)=DATEPART(quarter,getdate())
AND DATEPART(year,Begin_Journey_Date)=DATEPART(year,getdate())
AND Perfect_Journey_Biz_Exception_Flag IS NULL
--OR Perfect_Journey_Biz_Exception_Flag = 0
AND @Enterprise = p.Partner_Name
GROUP BY p.Partner_Name
UNION ALL
select COUNT(c.Container_Key) AS CountContainer,
100*COUNT(c.Container_Key)/(SELECT COUNT(Container_Key) FROM dbo.Container) AS RATE
FROM
dbo.Container c
INNER JOIN dbo.Partner_Master p
ON c.Enterprise_Partner_Key = p.Partner_Key
where DATEPART(YEAR,Begin_Journey_Date) = DATEPART(YEAR,getdate())
AND DATEPART(month,Begin_Journey_Date)=DATEPART(month,getdate())
AND Perfect_Journey_Biz_Exception_Flag IS NULL
AND @Enterprise = p.Partner_Name
GROUP BY p.Partner_Name
END
If I execute this :
EXEC Proc_PerfectJourneyReport_Business @Enterprise = 'AEGIS-MANUFACTURING AANKHEN ABDS BV Corporate';
Then the O/P is like :
--------------------
CountContainer Rate
1258 52
586 24
31 1
But I want to add something that will make the O/P looks like :
Time CountContainer Rate
Lifetime 1258 52
YTD 586 24
Q22010 31 1
June2010 0 0
For the Lifetime and YTD it is being hard coded , but how to generate Q22020 and June2010 dynamically that I want to know .
Please help me in this regards
Thanks
Subhro
June 22, 2010 at 11:33 pm
You want to generate the values dynamically based on what? Current date?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 11:56 pm
If you want to generate the names based on the value of Begin_Journey_Date then you could include the following lines in your union statement:
SELECT 'Q'+ cast(datepart(quarter,Begin_Journey_Date) as char(1))+cast(datepart(year,Begin_Journey_Date) as char(4))
SELECT datename(month,Begin_Journey_Date)+cast(datepart(year,Begin_Journey_Date) as char(4))
June 23, 2010 at 1:15 am
Thanks Luts !
It worked.
Subhro
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply