Re: a view based on stored procedure

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/18/04

Next message: anonymous_at_discussions.microsoft.com: "Re: Select without tab..."
Previous message: Vishal Parkar: "Re: Select without tab..."
In reply to: sqlgirl: "Re: a view based on stored procedure"
Next in thread: Baisong Wei[MSFT]: "Re: a view based on stored procedure"
Messages sorted by: [ date ] [ thread ]

Date: Wed, 17 Mar 2004 19:02:21 -0500

OK, one more thing - change your provider to MSDASQL:

SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=gorilla;Database=Corp;UID=fin;PWD=acct',
'EXEC rp_ResAvailPlanInfo')

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"sqlgirl" <anonymous@devdex.com> wrote in message
news:%23BT3z%23GDEHA.3796@TK2MSFTNGP10.phx.gbl...
Hi Tom,
Here is the code:
CREATE PROCEDURE rp_ResAvailPlanInfo
AS
SET NOCOUNT ON
IF (1=2)
SELECT ResUniqueID, Resgroup, ResName,
SumPlanWork_2 , SumAvailWork_2,
SumPlanWork_1 , SumAvailWork_1,
SumPlanWork0 , SumAvailWork0 ,
SumPlanWork1 , SumAvailWork1 ,
SumPlanWork2 , SumAvailWork2 ,
SumPlanWork3 , SumAvailWork3 ,
SumPlanWork4 , SumAvailWork4 ,
SumPlanWork5 , SumAvailWork5 ,
SumPlanWork6 , SumAvailWork6 ,
SumPlanWork7 , SumAvailWork7 ,
SumPlanWork8 , SumAvailWork8 ,
SumPlanWork9 , SumAvailWork9 ,
SumPlanWork10, SumAvailWork10 ,
SumPlanWork11, SumAvailWork11 ,
SumPlanWork12, SumAvailWork12 ,
SumPlanWork13, SumAvailWork13 ,
SumPlanWork14, SumAvailWork14 ,
SumPlanWork15, SumAvailWork15 ,
SumPlanWork16, SumAvailWork16 ,
SumPlanWork17, SumAvailWork17 ,
SumPlanWork18, SumAvailWork18 ,
SumPlanWork19, SumAvailWork19 ,
SumPlanWork20, SumAvailWork20 ,
SumPlanWork21, SumAvailWork21 ,
SumPlanWork22, SumAvailWork22 ,
SumPlanWork23, SumAvailWork23 ,
SumPlanWork24, SumAvailWork24 ,
SumPlanWork25, SumAvailWork25 ,
SumPlanWork26, SumAvailWork26
FROM WrkAvailPlanInfo
DELETE FROM WrkAvailPlanInfo
DECLARE @Week0 DATETIME
DECLARE @Week_1 DATETIME
DECLARE @Week_2 DATETIME
DECLARE @Week1 DATETIME
DECLARE @Week2 DATETIME
DECLARE @Week3 DATETIME
DECLARE @Week4 DATETIME
DECLARE @Week5 DATETIME
DECLARE @Week6 DATETIME
DECLARE @Week7 DATETIME
DECLARE @Week8 DATETIME
DECLARE @Week9 DATETIME
DECLARE @Week10 DATETIME
DECLARE @Week11 DATETIME
DECLARE @Week12 DATETIME
DECLARE @Week13 DATETIME
DECLARE @Week14 DATETIME
DECLARE @Week15 DATETIME
DECLARE @Week16 DATETIME
DECLARE @Week17 DATETIME
DECLARE @Week18 DATETIME
DECLARE @Week19 DATETIME
DECLARE @Week20 DATETIME
DECLARE @Week21 DATETIME
DECLARE @Week22 DATETIME
DECLARE @Week23 DATETIME
DECLARE @Week24 DATETIME
DECLARE @Week25 DATETIME
DECLARE @Week26 DATETIME
DECLARE @CubeTime DATETIME
DECLARE @Day INT
DECLARE @DataDate DATETIME
DECLARE @WE_Date DATETIME
DECLARE @SumPlanWork FLOAT
DECLARE @SumAvailWork FLOAT
DECLARE @ResUniqueID INT
DECLARE @ResGroup VARCHAR(64)
DECLARE @ResName VARCHAR(510)
SELECT @CubeTime = wadmin_cube_log_status_timestamp FROM
ProjectServer0000..msp_web_admin_cube
SELECT @Day = DatePart(dw, @CubeTime)
SELECT @Week0 =
CASE @Day
WHEN 1 THEN DATEADD(d, 5, @CubeTime)
WHEN 2 THEN DATEADD(d, 4, @CubeTime)
WHEN 3 THEN DATEADD(d, 3, @CubeTime)
WHEN 4 THEN DATEADD(d, 2, @CubeTime)
WHEN 5 THEN DATEADD(d, 1, @CubeTime)
WHEN 7 THEN DATEADD(d, 6, @CubeTime)
ELSE
@CubeTime
END
SET @Week0 = CONVERT(VARCHAR(10), @Week0, 121)
SET @Week_1 = DATEADD(wk, -1, @Week0)
SET @Week_2 = DATEADD(wk, -2, @Week0)
SET @Week1 = DATEADD(wk, 1, @Week0)
SET @Week2 = DATEADD(wk, 2, @Week0)
SET @Week3 = DATEADD(wk, 3, @Week0)
SET @Week4 = DATEADD(wk, 4, @Week0)
SET @Week5 = DATEADD(wk, 5, @Week0)
SET @Week6 = DATEADD(wk, 6, @Week0)
SET @Week7 = DATEADD(wk, 7, @Week0)
SET @Week8 = DATEADD(wk, 8, @Week0)
SET @Week9 = DATEADD(wk, 9, @Week0)
SET @Week10 = DATEADD(wk, 10, @Week0)
SET @Week11 = DATEADD(wk, 11, @Week0)
SET @Week12 = DATEADD(wk, 12, @Week0)
SET @Week13 = DATEADD(wk, 13, @Week0)
SET @Week14 = DATEADD(wk, 14, @Week0)
SET @Week15 = DATEADD(wk, 15, @Week0)
SET @Week16 = DATEADD(wk, 16, @Week0)
SET @Week17 = DATEADD(wk, 17, @Week0)
SET @Week18 = DATEADD(wk, 18, @Week0)
SET @Week19 = DATEADD(wk, 19, @Week0)
SET @Week20 = DATEADD(wk, 20, @Week0)
SET @Week21 = DATEADD(wk, 21, @Week0)
SET @Week22 = DATEADD(wk, 22, @Week0)
SET @Week23 = DATEADD(wk, 23, @Week0)
SET @Week24 = DATEADD(wk, 24, @Week0)
SET @Week25 = DATEADD(wk, 25, @Week0)
SET @Week26 = DATEADD(wk, 26, @Week0)
DECLARE c_AvailPlan CURSOR FOR
SELECT
    ProjectServer_MSP_VIEW_RES_STD.ResourceUniqueID, WE_Date =
CONVERT(VARCHAR(10), ProjectServer_Sum_Planned_Avail.WE_Date, 121),
    ProjectServer_MSP_VIEW_RES_STD.ResourceGroup,
ProjectServer_MSP_VIEW_RES_STD.ResourceName,
    SumPlanWork =
isnull(sum(ProjectServer_Sum_Planned_Avail.PlannedWork), 0),
    SumAvailWork =
isnull(sum(ProjectServer_Sum_Planned_Avail.Available), 0)
FROM
    Corporate.dbo.ProjectServer_Sum_Planned_Avail1
ProjectServer_Sum_Planned_Avail,
    Corporate.dbo.ProjectServer_MSP_VIEW_RES_STD
ProjectServer_MSP_VIEW_RES_STD
WHERE
    ProjectServer_Sum_Planned_Avail.Resource =
ProjectServer_MSP_VIEW_RES_STD.ResourceUniqueID
group by     ProjectServer_MSP_VIEW_RES_STD.ResourceUniqueID,
ProjectServer_Sum_Planned_Avail.WE_Date,
    ProjectServer_MSP_VIEW_RES_STD.ResourceGroup,
ProjectServer_MSP_VIEW_RES_STD.ResourceName
order by     ProjectServer_MSP_VIEW_RES_STD.ResourceUniqueID,
ProjectServer_Sum_Planned_Avail.WE_Date ,
    ProjectServer_MSP_VIEW_RES_STD.ResourceGroup,
ProjectServer_MSP_VIEW_RES_STD.ResourceName
OPEN c_AvailPlan
WHILE (0=0)
BEGIN
FETCH NEXT FROM c_AvailPlan
INTO @ResUniqueID, @WE_Date, @ResGroup, @ResName, @SumPlanWork,
@SumAvailWork
IF (@@fetch_status <> 0)
BREAK
IF @WE_Date = @Week_2
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week_1
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week0
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week1
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week2
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week3
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week4
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week5
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week6
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week7
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week8
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week9
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week10
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week11
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week12
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week13
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week14
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week15
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week16
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week17
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week18
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week19
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week20
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week21
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week22
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork,
0, 0, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week23
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@SumPlanWork, @SumAvailWork, 0, 0, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week24
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, @SumPlanWork, @SumAvailWork, 0, 0, 0, 0)
END
ELSE
IF @WE_Date = @Week25
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, @SumPlanWork, @SumAvailWork, 0, 0)
END
ELSE
IF @WE_Date = @Week26
BEGIN
INSERT INTO WrkAvailPlanHours
VALUES
(@ResUniqueID, @WE_Date, @ResGroup, @ResName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, @SumPlanWork, @SumAvailWork)
END
END
CLOSE c_AvailPlan
DEALLOCATE c_AvailPlan
INSERT INTO WrkAvailPlanInfo
SELECT ResUniqueID, Resgroup, ResName,
SumPlanWork_2 = ROUND(SUM(SumPlanWork_2), 0), SumAvailWork_2 =
ROUND(SUM(SumAvailWork_2), 0),
SumPlanWork_1 = ROUND(SUM(SumPlanWork_1), 0), SumAvailWork_1 =
ROUND(SUM(SumAvailWork_1), 0),
SumPlanWork0 = ROUND(SUM(SumPlanWork0), 0), SumAvailWork0 =
ROUND(SUM(SumAvailWork0), 0),
SumPlanWork1 = ROUND(SUM(SumPlanWork1), 0), SumAvailWork1 =
ROUND(SUM(SumAvailWork1), 0),
SumPlanWork2 = ROUND(SUM(SumPlanWork2), 0), SumAvailWork2 =
ROUND(SUM(SumAvailWork2), 0),
SumPlanWork3 = ROUND(SUM(SumPlanWork3), 0), SumAvailWork3 =
ROUND(SUM(SumAvailWork3), 0),
SumPlanWork4 = ROUND(SUM(SumPlanWork4), 0), SumAvailWork4 =
ROUND(SUM(SumAvailWork4), 0),
SumPlanWork5 = ROUND(SUM(SumPlanWork5), 0), SumAvailWork5 =
ROUND(SUM(SumAvailWork5), 0),
SumPlanWork6 = ROUND(SUM(SumPlanWork6), 0), SumAvailWork6 =
ROUND(SUM(SumAvailWork6), 0),
SumPlanWork7 = ROUND(SUM(SumPlanWork7), 0), SumAvailWork7 =
ROUND(SUM(SumAvailWork7), 0),
SumPlanWork8 = ROUND(SUM(SumPlanWork8), 0), SumAvailWork8 =
ROUND(SUM(SumAvailWork8), 0),
SumPlanWork9 = ROUND(SUM(SumPlanWork9), 0), SumAvailWork9 =
ROUND(SUM(SumAvailWork9), 0),
SumPlanWork10 = ROUND(SUM(SumPlanWork10), 0), SumAvailWork10 =
ROUND(SUM(SumAvailWork10), 0),
SumPlanWork11 = ROUND(SUM(SumPlanWork11), 0), SumAvailWork11 =
ROUND(SUM(SumAvailWork11), 0),
SumPlanWork12 = ROUND(SUM(SumPlanWork12), 0), SumAvailWork12 =
ROUND(SUM(SumAvailWork12), 0),
SumPlanWork13 = ROUND(SUM(SumPlanWork13), 0), SumAvailWork13 =
ROUND(SUM(SumAvailWork13), 0),
SumPlanWork14 = ROUND(SUM(SumPlanWork14), 0), SumAvailWork14 =
ROUND(SUM(SumAvailWork14), 0),
SumPlanWork15 = ROUND(SUM(SumPlanWork15), 0), SumAvailWork15 =
ROUND(SUM(SumAvailWork15), 0),
SumPlanWork16 = ROUND(SUM(SumPlanWork16), 0), SumAvailWork16 =
ROUND(SUM(SumAvailWork16), 0),
SumPlanWork17 = ROUND(SUM(SumPlanWork17), 0), SumAvailWork17 =
ROUND(SUM(SumAvailWork17), 0),
SumPlanWork18 = ROUND(SUM(SumPlanWork18), 0), SumAvailWork18 =
ROUND(SUM(SumAvailWork18), 0),
SumPlanWork19 = ROUND(SUM(SumPlanWork19), 0), SumAvailWork19 =
ROUND(SUM(SumAvailWork19), 0),
SumPlanWork20 = ROUND(SUM(SumPlanWork20), 0), SumAvailWork20 =
ROUND(SUM(SumAvailWork20), 0),
SumPlanWork21 = ROUND(SUM(SumPlanWork21), 0), SumAvailWork21 =
ROUND(SUM(SumAvailWork21), 0),
SumPlanWork22 = ROUND(SUM(SumPlanWork22), 0), SumAvailWork22 =
ROUND(SUM(SumAvailWork22), 0),
SumPlanWork23 = ROUND(SUM(SumPlanWork23), 0), SumAvailWork23 =
ROUND(SUM(SumAvailWork23), 0),
SumPlanWork24 = ROUND(SUM(SumPlanWork24), 0), SumAvailWork24 =
ROUND(SUM(SumAvailWork24), 0),
SumPlanWork25 = ROUND(SUM(SumPlanWork25), 0), SumAvailWork25 =
ROUND(SUM(SumAvailWork25), 0),
SumPlanWork26 = ROUND(SUM(SumPlanWork26), 0), SumAvailWork26 =
ROUND(SUM(SumAvailWork26), 0)
FROM WrkAvailPlanHours
GROUP BY ResUniqueID, Resgroup, ResName
DELETE FROM WrkAvailPlanHours
SELECT ResUniqueID, Resgroup, ResName,
SumPlanWork_2 , SumAvailWork_2,
SumPlanWork_1 , SumAvailWork_1,
SumPlanWork0 , SumAvailWork0 ,
SumPlanWork1 , SumAvailWork1 ,
SumPlanWork2 , SumAvailWork2 ,
SumPlanWork3 , SumAvailWork3 ,
SumPlanWork4 , SumAvailWork4 ,
SumPlanWork5 , SumAvailWork5 ,
SumPlanWork6 , SumAvailWork6 ,
SumPlanWork7 , SumAvailWork7 ,
SumPlanWork8 , SumAvailWork8 ,
SumPlanWork9 , SumAvailWork9 ,
SumPlanWork10, SumAvailWork10 ,
SumPlanWork11, SumAvailWork11 ,
SumPlanWork12, SumAvailWork12 ,
SumPlanWork13, SumAvailWork13 ,
SumPlanWork14, SumAvailWork14 ,
SumPlanWork15, SumAvailWork15 ,
SumPlanWork16, SumAvailWork16 ,
SumPlanWork17, SumAvailWork17 ,
SumPlanWork18, SumAvailWork18 ,
SumPlanWork19, SumAvailWork19 ,
SumPlanWork20, SumAvailWork20 ,
SumPlanWork21, SumAvailWork21 ,
SumPlanWork22, SumAvailWork22 ,
SumPlanWork23, SumAvailWork23 ,
SumPlanWork24, SumAvailWork24 ,
SumPlanWork25, SumAvailWork25 ,
SumPlanWork26, SumAvailWork26
FROM WrkAvailPlanInfo
Thank You,
sqlgirl
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Next message: anonymous_at_discussions.microsoft.com: "Re: Select without tab..."
Previous message: Vishal Parkar: "Re: Select without tab..."
In reply to: sqlgirl: "Re: a view based on stored procedure"
Next in thread: Baisong Wei[MSFT]: "Re: a view based on stored procedure"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Spalten alternativ ansprechen
... DECLARE @Monat INT ... '01' AS DATETIME) ... case when DATUM between '...' ... then UMSATZ as ...
(microsoft.public.de.access.clientserver)
Re: Language problem
... @datum datetime ... > Then in application I have settings of my connection: ... > everything else doesn't because all other functions are set to slovenian. ... > DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK ...
(microsoft.public.sqlserver.programming)
RE: User Defined Function Help
... @startDateTime datetime = null, ... DECLARE @selectFromList varchar ... SET @subservice = RTRIM ... EXEC ...
(microsoft.public.sqlserver.programming)
Re: Recordset or Object is closed HELP PLEASE!
... DECLARE @IntYearBeforeLast INT ... DECLARE @DateSepFirstYearBeforeLast DATETIME ...
(microsoft.public.excel.programming)
Re: get time in SQL server
... >conversion to datetime data type but could not do it. ... >declare @date2 as datetime ... declare @date1 varchar ...
(microsoft.public.sqlserver.server)