April 27, 2010 at 5:44 am
I have a function in my db...
CREATE TABLE [dbo].[tblPeriod](
[PeriodID] [int] IDENTITY(1,1) NOT NULL,
[Year] [smallint] NOT NULL,
[PeriodName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PeriodNumber] [int] NOT NULL,
[WorkDays] [int] NOT NULL
) ON [PRIMARY]
CREATE FUNCTION [dbo].[xyz]
(
@intPeriodID AS INT
)
RETURNS @tblMonth TABLE
(
PeriodID INT,
Month1 VARCHAR(30),
Month2 VARCHAR(30),
Month3 VARCHAR(30),
Month4 VARCHAR(30),
Month5 VARCHAR(30),
Month6 VARCHAR(30),
Month7 VARCHAR(30),
Month8 VARCHAR(30),
Month9 VARCHAR(30),
Month10 VARCHAR(30),
Month11 VARCHAR(30),
Month12 VARCHAR(30)
)
AS
BEGIN
DECLARE @Month1 VARCHAR(30)
DECLARE @Month2 VARCHAR(30)
DECLARE @Month3 VARCHAR(30)
DECLARE @Month4 VARCHAR(30)
DECLARE @Month5 VARCHAR(30)
DECLARE @Month6 VARCHAR(30)
DECLARE @Month7 VARCHAR(30)
DECLARE @Month8 VARCHAR(30)
DECLARE @Month9 VARCHAR(30)
DECLARE @Month10 VARCHAR(30)
DECLARE @Month11 VARCHAR(30)
DECLARE @Month12 VARCHAR(30)
SELECT @Month1=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID
SELECT @Month2=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+1
SELECT @Month3=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+2
SELECT @Month4=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+3
SELECT @Month5=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+4
SELECT @Month6=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+5
SELECT @Month7=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+6
SELECT @Month8=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+7
SELECT @Month9=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+8
SELECT @Month10=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+9
SELECT @Month11=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+10
SELECT @Month12=PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')'
From tblPeriod
Where PeriodId=@intPeriodID+11
INSERT INTO @tblMonth(PeriodID,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
VALUES(@intPeriodID,@Month1,@Month2,@Month3,@Month4,@Month5,@Month6,@Month7,@Month8,@Month9,@Month10,@Month11,@Month12)
RETURN
END
This function has 12 select statements in it, can it be rewritten to have one single select statement to serve the need..
April 27, 2010 at 5:53 am
Hey Jus, can you please post sample data for tblPeriod (in tthe form of INSERT INTO TABLE <> SELECT statments) ?? This will be very helpful to revamp the query...
April 27, 2010 at 12:09 pm
Maybe something like :
SELECT @intPeriodID,
MAX(CASE WHEN PeriodId = @intPeriodID THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month1,
MAX(CASE WHEN PeriodId = @intPeriodID + 1 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month2,
MAX(CASE WHEN PeriodId = @intPeriodID + 2 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month3,
MAX(CASE WHEN PeriodId = @intPeriodID + 3 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month4,
MAX(CASE WHEN PeriodId = @intPeriodID + 4 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month5,
MAX(CASE WHEN PeriodId = @intPeriodID + 5 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month6,
MAX(CASE WHEN PeriodId = @intPeriodID + 6 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month7,
MAX(CASE WHEN PeriodId = @intPeriodID + 7 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month8,
MAX(CASE WHEN PeriodId = @intPeriodID + 8 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month9,
MAX(CASE WHEN PeriodId = @intPeriodID + 9 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month10,
MAX(CASE WHEN PeriodId = @intPeriodID + 10 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month11,
MAX(CASE WHEN PeriodId = @intPeriodID + 11 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month12
From tblPeriod
Where PeriodId between @intPeriodID and @intPeriodID + 11
Group By @intPeriodID
* Noel
April 27, 2010 at 10:50 pm
Thanks for the responses..
sample data..
insert into [dbo].[tblPeriod]
select 2008,'March',17,21
union all
select 2008,'Apr',18,24
union all
select 2008,'May',19,25
union all
select 2008,'Jun',20,22
union all
select 2008,'July',19,28
union all
select 2008,'Aug',17,21
union all
select 2008,'Sep',18,24
union all
select 2008,'Oct',19,25
union all
select 2008,'Nov',20,17
union all
select 2008,'Dec',19,15
union all
select 2008,'Jan',20,16
union all
select 2008,'feb',14,21
Noeld...
Your query works fine, but it returns 12 rows each time...first row will have month1 value and remaining columns will be null, second row will have month2 value and remaining columns null etc....
can all values will be clubbed into one row and display the result set?
with above sample data...run the following query to understand my issue..
declare @intPeriodID int
set @intPeriodID = 1
SELECT @intPeriodID,
MAX(CASE WHEN PeriodId = @intPeriodID THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month1,
MAX(CASE WHEN PeriodId = @intPeriodID + 1 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month2,
MAX(CASE WHEN PeriodId = @intPeriodID + 2 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month3,
MAX(CASE WHEN PeriodId = @intPeriodID + 3 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month4,
MAX(CASE WHEN PeriodId = @intPeriodID + 4 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month5,
MAX(CASE WHEN PeriodId = @intPeriodID + 5 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month6,
MAX(CASE WHEN PeriodId = @intPeriodID + 6 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month7,
MAX(CASE WHEN PeriodId = @intPeriodID + 7 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month8,
MAX(CASE WHEN PeriodId = @intPeriodID + 8 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month9,
MAX(CASE WHEN PeriodId = @intPeriodID + 9 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month10,
MAX(CASE WHEN PeriodId = @intPeriodID + 10 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month11,
MAX(CASE WHEN PeriodId = @intPeriodID + 11 THEN PeriodName + ' (' + CAST(WorkDays AS VARCHAR(50))+ ')' END ) AS Month12
From tblPeriod
Where PeriodId between @intPeriodID and @intPeriodID + 11
Group By PeriodID
Thanks in advance..
April 27, 2010 at 11:39 pm
Hey Jus, thanks for posting the DDLs..
Here is a gift for you. This does not perform those 12 SELECT statements, but it still does what u wanted..
Here is the CODE..
ALTER FUNCTION [dbo].[xyz]
(
@intPeriodID AS INT
)
RETURNS @tblMonth TABLE
(
PeriodID INT,
Month1 VARCHAR(30),
Month2 VARCHAR(30),
Month3 VARCHAR(30),
Month4 VARCHAR(30),
Month5 VARCHAR(30),
Month6 VARCHAR(30),
Month7 VARCHAR(30),
Month8 VARCHAR(30),
Month9 VARCHAR(30),
Month10 VARCHAR(30),
Month11 VARCHAR(30),
Month12 VARCHAR(30)
)
AS
BEGIN
;WITH DATA_CTE(PeriodID ,N,RESULT) AS
(
SELECT @intPeriodID PeriodID, CTE.N,A.RESULT FROM
(
SELECT 1 N UNION ALL
SELECT 2 N UNION ALL
SELECT 3 N UNION ALL
SELECT 4 N UNION ALL
SELECT 5 N UNION ALL
SELECT 6 N UNION ALL
SELECT 7 N UNION ALL
SELECT 8 N UNION ALL
SELECT 9 N UNION ALL
SELECT 10 N UNION ALL
SELECT 11 N UNION ALL
SELECT 12
)CTE
LEFT JOIN
( SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ROW_NUM
,PeriodName + ' ('+ CAST(WorkDays AS VARCHAR) +')' RESULT
FROM
[tblPeriod]
WHERE
PERIODID >= @intPeriodID AND
[YEAR] = (SELECT DISTINCT [YEAR] FROM [tblPeriod] WHERE PERIODID = @intPeriodID)
) A
ON A.ROW_NUM = CTE.N
)
INSERT INTO @tblMonth(PeriodID,Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
SELECT PeriodID, [1] Month1 , [2] Month2, [3] Month3 , [4] Month4 , [5] Month5 , [6] Month6 ,
[7] Month7 , [8] Month8, [9] Month9 , [10] Month10 ,[11] Month11 , [12] Month12 FROM
(SELECT PeriodID,N,RESULT FROM DATA_CTE ) PIVOT_TABLE
PIVOT
(MAX(RESULT) FOR N IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) PIVOT_HANDLE
RETURN
END
Hope this helps you buddy!
Cheers!!
April 27, 2010 at 11:46 pm
A year will constitute 12 months. And thats the reason i have done a little tweak to my function. If my code should function as exactly the same as you then comment out the following chunk from my code
AND [YEAR] = (SELECT DISTINCT [YEAR] FROM [tblPeriod] WHERE PERIODID = @intPeriodID)
Cheers!
April 28, 2010 at 12:47 am
ColdCoffee...You rock..........thats what i was exactly looking for...your code works great after removing the line
AND [YEAR] = (SELECT DISTINCT [YEAR] FROM [tblPeriod] WHERE PERIODID = @intPeriodID)
April 28, 2010 at 3:05 am
Wow, nice to hear that my code helped you..Pleasure!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply