Cumulative Update 5 Build 9.00.3215 KB - Problem
Dec 28, 2007
Cumulative Update 5 Build 9.00.3215 KB - Problem
http://support.microsoft.com/kb/943656/
We contacted Microsoft and installed the update, but when I do a SELECT @@Version I still get build 3042.
I'm not sure if they sent us the wrong file or if the update didn't apply correctly. Under Add/Remove Programs SQL Native Client shows the new build 3215 though.
Microsoft sent us
335091_intl_i386_zip.exe
This extracted to..
hotfix.txt
sqlncli.msi
Any ideas?
Thanks in advance!
View 3 Replies
ADVERTISEMENT
Aug 10, 2006
Hello,
I do experiment a problem when trying to install the mentionned patch on my Windows XP SP2 machine (fully patched).
Interestingly, the patch works fine for the named instance, which is a "clean" install of SQL Server 2005 SP1, while it fails on the default instance, which is a SQL 2005 SP1 upgraded in place from SQL Server 2000 SP4. Both instances are Developer editions...
The error is along the following lines:
[snip]
08/10/2006 09:22:06.728 Unable to install MSP file: \DHDHY0Jc$1c7c01591e524f9a092159a233hotfixsqlFilessqlrun_sql.msp
08/10/2006 09:22:06.818 The following exception occurred: Unable to install Windows Installer MSP file Date: 08/10/2006 09:22:06.818 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 856
08/10/2006 09:22:06.948
08/10/2006 09:22:06.998 Product Status Summary:
08/10/2006 09:22:07.048 Product: SQL Server Database Services 2005
08/10/2006 09:22:07.088 MSSQLSERVER - Failure
08/10/2006 09:22:07.138 Details: Unable to install Windows Installer MSP file
08/10/2006 09:22:07.228 SQL2K5 - Not Selected
08/10/2006 09:22:07.338
08/10/2006 09:22:42.409 Hotfix package closed
Any thoughts somebody?
Thanks in advance,
Emmanuel
View 3 Replies
View Related
Mar 8, 2007
I've read about this hotfix at http://www.kbalertz.com/Feedback_933097.aspx but I can't find anything on microsoft.com about it. It sounds like a worthwhile hotfix, but I'm wondering about the absence of an announcement on microsoft.com.
Anyone else know about this hotfix?
Thanks!
View 3 Replies
View Related
Jun 30, 2007
I tried to install "Cumulative update package 2 for SQL Server 2005 Service Pack 2"
Everything went well except for the "SQL Server Database Services" update.
It errored out as it was trying to "Finalize" the update.
Kicker to this whole thing is the database file "temp_MS_AgentSigningCertificate_database.mdf" does not even exist.
I could not see any references to it in the master database.
I checked the registry and I can find a couple of search references for it.
It apparently may have been a database that existed in the server at one time.
I am not sure if I should remove the registry references to the database.
Below is a part of the install summary where it has failed:
===========================================================
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3050
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB936305_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]A file activation error occurred. The physical file name 'F:Data emp_MS_AgentSigningCertificate_database.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.. To continue, correct the problem, and then run SQL Server Setup again.
If someone can tell me what I need to do to resolve this issue, I would greatly
appreciate it.
Thanks,
Larry :-(
Larry :-)
View 4 Replies
View Related
Jun 20, 2007
Hi
I have found two cumulative update packages available for sql 2005 sp2 - build 3161 (http://support.microsoft.com/kb/935356) and build 3175 (http://support.microsoft.com/kb/936305).
The fixes are supposed to be cumulative and include all fixes since the last service pack, but the list of hotfixes in each package is different.
Does build 3175 contain the fixes in build 3161? What about fixes mentioned in build 3175 that have version lowere than 3161 - are they included in build 3161?
Anthony
View 1 Replies
View Related
Dec 18, 2013
How can I update a table to have a running cumulative sum?
For Example:
Update Table1 Set Cumulative_Sum = Row_Sum + Previous_Row_Sum
It should look something like this:
Row_Sum Cumulative_Sum
1 1
2 3
3 6
4 10
View 6 Replies
View Related
Mar 4, 2014
What's the most straight forward to determine what the last cumulative update was that was applied to SSMS?
I know that CU8 is out for SQL Server 2012, I just need to confirm which (if any) update was last applied to SSMS I'm currently looking at.
View 4 Replies
View Related
Nov 15, 2007
Re: KB 940287 - Will it be in a Cumulative Update?
SYMPTOMS:
You use Service Broker in SQL Server 2005. When the initiating service starts a conversation and then sends a message, the initiating service ends the conversation immediately. However, after the target service processes the message, the endpoint on the target server is not deleted when the target service ends the conversation. Therefore, when the initiating service starts a new conversation, the number of the conversation endpoints on the target server keeps increasing.
Additionally, you receive the following error message on the initiator server:
An error occurred while receiving data: '64(The specified network name is no longer available.)'
This message could not be delivered because the conversation endpoint has already been closed.
It has several workarounds. I've not been affected by this yet, but KB says it has a hotfix that has been out since July 2007, so it is curious that it has not been put into a Cumulative Update yet, at least as far as documentation in Knowledge Base is concerned. Does anyone know any more about this?
Paul
View 1 Replies
View Related
Jun 1, 2015
I have a question about Service Packs and Cumulative Updates for SQL 2008 R2.
Do you need to apply them in the order that they are released? Or can you just apply the latest Service Pack and Cumulative Update?
View 3 Replies
View Related
Apr 22, 2008
I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.
I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:
This gets all of the organization, their contactId and their primary Address (as long as they have one).
SELECT ContactID, FullName, PrimaryAddressType
FROM tblContactInformation
WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'
This gets a list of all of the individuals that have an organization assigned.
SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType
FROM tblContactInformation
WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL
Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).
UPDATE tblContactInformation
SET PrimaryAddressType =
WHERE PerOrg = '1'
At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?
View 1 Replies
View Related
Jul 16, 2007
My SSIS solution has about hundred packages and time to time I have to edit a package. I understand I could use 'Build' command to compile only updated package, as opposed to Rebuild which recomplies all of the packages.
Nevertheless, in both cases SSIS opens all of the packages in design environment before compilation. My packages are saved in SourceSafe and that process takes quite long and I was wondering if there was any other way to compile only updated package where none of the other packages are opened during Build/Rebuild process? For example we could use dtutil to deploy only updated packages without running Package Installation Wizard.
View 3 Replies
View Related
May 12, 2008
Hi all
I have Re-Builld index Job,it is runnining evry week.now a days my queries are runnign very slow.eben after running re-build indexs.Please guide me how to check where was the problem.
I am suspecting Stastitics how check the stastitics updation.
any help appreciated.
View 1 Replies
View Related
Mar 22, 2006
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
View 5 Replies
View Related
Sep 7, 2007
Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.
thoughts?
thanks
View 6 Replies
View Related
Apr 26, 2000
I'm trying to get the final result using an aggregate function.
Table looks like below;
ID ACRE Probability
1 3 0.3
2 1 0.6
3 6 0.2
4 5 0.5
5 2 0.1
First, I want to descend by probability then select all records that cumulative acre <= 8
So, final result will be:
ID ACRE Probability
2 1 0.6
4 5 0.5
Any idea how to write a script?
Thanks in advance,
Mike Jun
hyunhyo@hotmail.com
GIS reserch Group
University of Colorado
303-492-8781
View 1 Replies
View Related
Oct 4, 2005
I have to group the no. of employees based on month. i.e
Jan 10 employees
Feb 20 employees
Mar 30 employess
The result is to be
Jan 10
Feb 20
Mar 60.
Kindly provide the sql for getting the above result.
Thanks,
Lakshmi
View 6 Replies
View Related
Jul 20, 2005
SQL Server 2000 SP3Hi,How can I get the cumulative weeks from a givedate to the currentdate. I know I can get the weeknumber by using datepart(wk,getdate())but this will giveme the week number for this year. What if I want to know the number ofweeksthat have passed since june 1 2001. If I use datepart(wk,'20010106') Iwillget the week number for 2001 but I would like the number of weeksexpired between then now.Thanks,Reg
View 1 Replies
View Related
Aug 23, 2006
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
Any help/ideas would be appreciated! Thank you.
View 13 Replies
View Related
Dec 29, 2007
In the emp table :
>
> EMPNO ENAME SAL
> ==================
> 7369 SMITH 1000
> 7499 ALLEN 2000
> 7521 WARD 3000
> 7566 JONES 4000
> 7654 MARTIN 5000
>
> there is a requirement to have a calculated col.
> called cummulative sal
>
> EMPNO ENAME SAL CUMMULATIVE_SAL
> 7369 SMITH 1000 1000
> 7499 ALLEN 2000 3000
> 7521 WARD 3000 6000
> 7566 JONES 4000 10000
> 7654 MARTIN 5000 15000
>
>
How to show this calculated col. by using one select statement?
I could get the result using Empno. in my query as --
select ename,sal,(select sum(sal) from emp k where k.empno <= e.empno) as cum from emp e order by ename
but I was asked to get the same result if empno. col is not there?
SUCKS........... the below code is giving the result but I don't know how you could use operator (<) on two strings to compare......????
select ename,sal,(select sum(sal) from emp k where k.ename <= e.ename) as cum from emp e order by ename
Someone tell me if this is the right approach or is there a better way of getting the thing done.
View 4 Replies
View Related
May 4, 2007
Hi,There are 3 tablesTable,TableDetails,TableDaily.With structureTABLE:TableID UserID Money---------- ---------- ----------(int) (int) (money)TABLEDETAILS:TableDetailsID TableID ItemID PaidForItem DayID---------- ---------- ---------- ---------- ----------(int) (int) (int) (money) (int)TABLEDAILY:TableDailyID TableID PaidForItem Money Total Change---------- ---------- ---------- ---------- ---------- ----------(int) (int) (money) (money) (PaidForItem + Money) (money)"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day."TableDaily" holds history. I do not know how to update this table.I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.Problem is with Change column. This column sould hold difference between today's Total and previous one etc.Current procedure looks like this:INSERT INTO TableDaily (TableID, PaidForItem, Money, DayID)SELECT TableDetails.TableID, SUM(PaidForItem) AS PaidForItem, Table.Money, (SELECT DayID FROM Days WHERE (Aktive = 1)) AS DayIDFROM TableDetails INNER JOIN Table ON TableDetails.TableID = Table.TableID GROUP BY TableDetails.TableID, Table.Money
View 3 Replies
View Related
Jun 6, 2014
I have the following:
SELECT '201305' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 2)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID
[Code] ...
How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.
View 4 Replies
View Related
Jul 5, 2014
Is there any way to calculate Cumulative result.
Manpower|Count|Cum_Count|Formula
Apr|70|70|Sum(APR)
May|40|110|Sum(Apr+May)
Jun|110|220|Sum(Apr+May+Jun)
View 2 Replies
View Related
Aug 2, 2014
How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
View 1 Replies
View Related
Jul 30, 2007
Hello,
I want to display subtotals for a column only for that page. Like;
Index Value
-----------------------------
1 4
2 5
Subtotal 9
----------------------------
3 1
4 2
Subtotal 3
Total 12
RunningValue gives cumulative totals. I need subtotals for each visible page only. Is there a way to do it ?
Constraints:
I'm using a table. And I shouldn't use page breaks on my report.
Thanks in advance
View 3 Replies
View Related
Apr 28, 2015
How would I iterate through this table and do a cumulative sum on the value column :
I'm trying to get the following result:
View 5 Replies
View Related
Aug 29, 2007
For Eg.
I have a table like gias given below:
Name Amount
------------------------------
aaa 10
bbb 20
and I want an output like one given below on running SQL query or stored procedure
Name Amount cumulative amount
---------------------------------------------------------------------
aaa 10 10
bbb 20 30
can anyone plz help me on this
View 28 Replies
View Related
Aug 18, 2015
I have table like below, its period wise ,here the value get cumulative period wise.
amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203
View 3 Replies
View Related
Oct 9, 2015
I have a table with the following creation sql script
CREATETABLE [dbo].[TEST_ENROLLMENT](
[ENROLLMENT_ID] [int]
NOTNULL,
[COURSE_OFFERING_ID] [int]
NOTNULL,
[STUDENT_ID] [int]
[Code] ....
Below is the sql insert for the above table’s data
INSERTINTO TEST_ENROLLMENT(ENROLLMENT_ID,COURSE_OFFERING_ID,STUDENT_ID,DEPT_ID,COURSE_CODE,CREDIT,SECTION_ID,
SEMESTER,ACAD_LEVEL,ACAD_YEAR,DATE_ENROLLED,[STATUS],TOTAL_MARK,GRADE_LETTER,QPE,Honor,INSTRUCTOR_ID,USERNAME
)VALUES(877,356,104,15,'IRD
[Code] ...
I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.
How to get the correct CGPA shown in the below desired result set table.
select x.STUDENT_ID,AVG(x.gpa)as
GPA,AVG(y.gpa)AS'COMMULATIVE GPA'
from
(
select STUDENT_ID,SUM(Honor)/SUM(CREDIT)
gpa,ACAD_YEAR,SEMESTER
[Code] ....
Current result set (Incorrect CUMULATIVE GPA)
STUDENT_ID GPA CUMULATIVE GPA
104 2.9 2.9
104 2.25 2.25
104 3 3
Desired result set
STUDENT_ID GPA COMMUTATIVE GPA
104 2.9 2.9
104 2.25 2.575
104 3 2.716
View 5 Replies
View Related
Aug 23, 2006
Hi,
I'm planning to install the cumulative hotfix (build 2187) on my sql 2000 clustering server (SP4, 2040). And I would like to know if the cumulative hotfix is able to roll back. If possible, please provide me any information about that. Thanks in advance.
View 2 Replies
View Related
May 30, 2008
Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6
View 14 Replies
View Related
Sep 2, 2014
I need to calculate cum amount from the following table.
CREATE TABLE #TotalRevenue_Investments
( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))
INSERT INTO #TotalRevenue_Investments
( Month ,
Year ,
TotalRevenue ,
[Code] ....
AND so ON ..
I need the OUTPUT AS FOR example
SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company
View 3 Replies
View Related
Feb 6, 2015
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)
Result:
[Week] [Count]
1 15
2 20
3 26
4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
View 8 Replies
View Related
May 29, 2008
Oh hai
This is closely related to this problem where Ryan & Peter helped me out loads (thanks again guys):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989
I now have a slightly different problem. Instead of returning the cumulative counts of people related to a business unit in the hierarchy I now need to return the actual people records. I had some code, derived from the above, that worked fine until I loaded some new data. It took a while but eventually I figured out the (now obvious) reason for the error. I have tried several changes to the query and not got anywhere. Please could someone have a look and see if you can suggest the required alteration(s)?
USE tempdb
go
---------------------------------------------------------------
--Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
(
unit_codeVARCHAR(10)NOT NULL
, parent_codeVARCHAR(10)NULL
, CONSTRAINT pk_orgPRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
)
GO
CREATE TABLE dbo.org_peeps
(
unit_codeVARCHAR(10)NOT NULL
, person_codeINTNOT NULL
, CONSTRAINT pk_org_peepsPRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_org_peeps_other_org_peepsFOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
)
GO
CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
SELECT org.unit_code
, org.parent_code
, org_peeps.person_code
FROMdbo.org
LEFT OUTER JOIN
dbo.org_peeps
ONorg_peeps.unit_code= org.unit_code
GO
INSERT INTO dbo.org (unit_code, parent_code)
SELECT 'a', 'y'UNION ALL
SELECT 'b', 'y'UNION ALL
SELECT 'c', 'z'UNION ALL
SELECT 'y', 'z'UNION ALL
SELECT 'z', NULL
--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'a', 1UNION ALL
SELECT 'c', 3
---------------------------------------------------------------
SELECT'This works great....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Add data to INTERMEDIATE levels of organisation
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'y', 4UNION ALL
SELECT 'y', 5UNION ALL
SELECT 'y', 6
---------------------------------------------------------------
SELECT'Since there is now data in the intermediate tables there are ' +
'duplicate outputs....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
---------------------------------------------------------------
SELECT'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTDISTINCT
parents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
DROP VIEW dbo.org_peeps_parents
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
DROP TABLE dbo.org
ENDJust paste and run the code and hopefully it should be reasonably clear. If you need any further info please let me know
View 5 Replies
View Related
