• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

sql server question

Ahh, thanks for the info. i have thought about getting one at home to experiment with but always thought it would be too expensive to bother. Do I need to set up anything special, hardware wise, to use it?

I agree, Sir Edward, it's not the best place to be pivoting etc. and it should be done in cubes etc etc. In the past I have also used Access via passthru queries to do this sort of analysis and reports generation
 
Ahh, thanks for the info. i have thought about getting one at home to experiment with but always thought it would be too expensive to bother. Do I need to set up anything special, hardware wise, to use it?
You will need XP pro or a windows server 2000 or higher installation.
I've got XP Pro and it works fine, even have a report server publishing reports.

I agree, Sir Edward, it's not the best place to be pivoting etc. and it should be done in cubes etc etc. In the past I have also used Access via passthru queries to do this sort of analysis and reports generation

I agree with that too, but sometimes it is unavoidable or simply, due to the system, it is the logical place and knowing what tools are available to you is invaluble
 
my solution

my solution

USE [FRB_NWCO]
GO
/****** Object: StoredProcedure [dbo].[CPUpdatePivot] Script Date: 03/13/2008 13:56:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Eternal Optimist>
-- Create date: <11 March 2008>
-- Description: <Updates the CP temp table using a pivot>
-- =============================================
ALTER PROCEDURE [dbo].[CPUpdatePivot]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--drop table #TblCriticalP

select c.level1,
c.level2,
c.level3,
c.designref,
c.Colour,
c.Supplier,
c.Fabricref,
c.STEP,
c.StatusBits,
Coalesce(c.[ActualDate],c.[ProposedDate]) as myDate
into #TblCriticalP
FROM tblCriticalPath c
join tempTblCriticalPathView as t on
c.level1 = t.level1 and
c.level2 = t.level2 and
c.[level3] = t.[level3] and
c.[designref] = t.[designref] and
c.[Colour] = t.[Colour] and
c.[Supplier] = t.[Supplier] and
c.[Fabricref] = t.[Fabricref]

--update status

update tempTblCriticalPathView
set tempTblCriticalPathView.f01 = p.[1],
tempTblCriticalPathView.f02 = p.[2],
tempTblCriticalPathView.f03 = p.[3],
tempTblCriticalPathView.f04 = p.[4],
tempTblCriticalPathView.f05 = p.[5],
tempTblCriticalPathView.f06 = p.[6],
tempTblCriticalPathView.f07 = p.[7],
tempTblCriticalPathView.f08 = p.[8],
tempTblCriticalPathView.f09 = p.[9],
tempTblCriticalPathView.f10 = p.[10],
tempTblCriticalPathView.f11 = p.[11],
tempTblCriticalPathView.f12 = p.[12],
tempTblCriticalPathView.f13 = p.[13],
tempTblCriticalPathView.f14 = p.[14],
tempTblCriticalPathView.f15 = p.[15],
tempTblCriticalPathView.f16 = p.[16],
tempTblCriticalPathView.f17 = p.[17],
tempTblCriticalPathView.f18 = p.[18],
tempTblCriticalPathView.f19 = p.[19],
tempTblCriticalPathView.f20 = p.[20],
tempTblCriticalPathView.f21 = p.[21],
tempTblCriticalPathView.f22 = p.[22],
tempTblCriticalPathView.f23 = p.[23],
tempTblCriticalPathView.f24 = p.[24],
tempTblCriticalPathView.f25 = p.[25],
tempTblCriticalPathView.f26 = p.[26],
tempTblCriticalPathView.f27 = p.[27],
tempTblCriticalPathView.f28 = p.[28],
tempTblCriticalPathView.f29 = p.[29],
tempTblCriticalPathView.f30 = p.[30],
tempTblCriticalPathView.f31 = p.[31],
tempTblCriticalPathView.f32 = p.[32],
tempTblCriticalPathView.f33 = p.[33],
tempTblCriticalPathView.f34 = p.[34],
tempTblCriticalPathView.f35 = p.[35],
tempTblCriticalPathView.f36 = p.[36],
tempTblCriticalPathView.f37 = p.[37],
tempTblCriticalPathView.f38 = p.[38],
tempTblCriticalPathView.f39 = p.[39],
tempTblCriticalPathView.f40 = p.[40],
tempTblCriticalPathView.f41 = p.[41],
tempTblCriticalPathView.f42 = p.[42],
tempTblCriticalPathView.f43 = p.[43],
tempTblCriticalPathView.f44 = p.[44],
tempTblCriticalPathView.f45 = p.[45],
tempTblCriticalPathView.f46 = p.[46],
tempTblCriticalPathView.f47 = p.[47],
tempTblCriticalPathView.f48 = p.[48],
tempTblCriticalPathView.f49 = p.[49],
tempTblCriticalPathView.f50 = p.[50],
tempTblCriticalPathView.f51 = p.[51],
tempTblCriticalPathView.f52 = p.[52],
tempTblCriticalPathView.f53 = p.[53],
tempTblCriticalPathView.f54 = p.[54],
tempTblCriticalPathView.f55 = p.[55],
tempTblCriticalPathView.f56 = p.[56],
tempTblCriticalPathView.f57 = p.[57],
tempTblCriticalPathView.f58 = p.[58],
tempTblCriticalPathView.f59 = p.[59],
tempTblCriticalPathView.f60 = p.[60],
tempTblCriticalPathView.f61 = p.[61],
tempTblCriticalPathView.f62 = p.[62],
tempTblCriticalPathView.f63 = p.[63],
tempTblCriticalPathView.f64 = p.[64],
tempTblCriticalPathView.f65 = p.[65],
tempTblCriticalPathView.f66 = p.[66],
tempTblCriticalPathView.f67 = p.[67],
tempTblCriticalPathView.f68 = p.[68],
tempTblCriticalPathView.f69 = p.[69],
tempTblCriticalPathView.f70 = p.[70],
tempTblCriticalPathView.f71 = p.[71],
tempTblCriticalPathView.f72 = p.[72],
tempTblCriticalPathView.f73 = p.[73],
tempTblCriticalPathView.f74 = p.[74],
tempTblCriticalPathView.f75 = p.[75]

FROM #TblCriticalP
PIVOT (max([#TblCriticalP].[statusbits]) FOR [#TblCriticalP].[step] IN (
[0],[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],[32],[33],[34],[35],[36],[37],[38],[39],
[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],
[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],
[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],
[70],[71],[72],[73],[74],[75]
)) p
join tempTblCriticalPathView z on
p.level1 = z.level1 and
p.level2 = z.level2 and
p.[level3] = z.[level3] and
p.[designref] = z.[designref] and
p.[Colour] = z.[Colour] and
p.[Supplier] = z.[Supplier] and
p.[Fabricref] = z.[Fabricref]




END
 

Advertisers

Back
Top