SQL Server 2005 Common Table Expression(CTE) Implement Reg.

Aug 30, 2006

Hi,

We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.

Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.

In ASP Application, We call this query €œ081Stats€? from Record set.

This €˜081Stats€™ query call below sub query itself

Master Query: 081Stats

SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;

Sub Query 1: 08Accepted

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 2: 08Applicants

SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 3: 08Dinged

SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 4: 08Interviewed

SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

Sub Query 5: 08Matriculating

SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;

So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.

How can I call that CTE from ASP.NET 2.0 using C#?

CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.

How can I implement the CTE, where can I write the CTE and where can I store the CTE.

And how can I call the CTE from ASP.NET 2.0 using C#.

It€™s Very Urgent Requirements.

We need your timely help.

With Thanks & Regards,
Sundar

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

SQL Server 2012 :: Filtering Common Table Expression Within View

Sep 1, 2015

I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...

ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId

[code]....

View 4 Replies View Related

Common Table Expression (CTE):How To Delete A Wrong CTE That Is In SQL Server Management Studio Express (SSMSE)?

Feb 25, 2008

Hi all,

I ran the following CTE sql code:


Use ChemDatabase

GO

WITH PivotedTestResults AS

(

SELECT TR.AnalyteName, TR.Unit,

Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),

Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),

QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)

FROM TestResults TR

JOIN Samples S ON TR.SampleID = S.SampleID

GROUP BY TR.AnalyteName, TR.Unit

)

SELECT AnalyteName, UnitForConc,

avg1 = abs(Prim + Dupl) / 2,

avg2 = abs(Prim + QA) / 2,

avg3 = abs(Dupl + QA) / 2,

RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,

RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,

RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2

FROM PivotedTestResults

GO

//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:

Msg 207, Level 16, State 1, Line 9

Invalid column name 'Unit'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'Unit'.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?

Please help and advise.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Common Table Expression

May 28, 2008

Hi
 
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
 
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan 
 

View 3 Replies View Related

Common Table Expression?

Jul 20, 2005

What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston

View 2 Replies View Related

Recursive Common Table Expression Data?

Jan 10, 2014

With my new data, need result for below scenario ( use CTE )

ยด`````````````
CREATE TABLE
ยดยดยดยดยดยดยดยดยดยดยดยดยดยดยดยด
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,

[Code] ....

===============================
Insert DATA TABLE 1
===============================

INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')

===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,

2.HasNodes = Yes, Display Parent and 1st Node

3.HasNodes = No, Display Parent Only.

================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6

===============
My Query
=============

learning CTE To apply for HasNodes Rules.

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (

[code]....

View 4 Replies View Related

Multiple Operations On A Common Table Expression

Jan 28, 2008

Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.

What is wrong with my syntax?


;With OrderedTable

AS

(

select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee

)

delete from OrderedTable where RowId != 1

SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc

View 1 Replies View Related

Conditional Processing From A Common Table Expression (CTE)

Sep 5, 2007

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:


WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

View 3 Replies View Related

Is WITH / Common Table Expression A Syntactic Sugar?

Oct 4, 2006

I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.

However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.

Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?

View 5 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!

View 11 Replies View Related

Encountered Problems With Multiple Common Table Expression

Feb 20, 2006

Hi guys,

I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.

The following are the errors

Code:


Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.



I'm using SQL Server Express

View 4 Replies View Related

Common Table Expression Calling Stored Procedure

Nov 19, 2007

Hi,

I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.

So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.

This is what I want to change:

Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))

Insert into @TempTable
Exec Procedure @Paramter

select @Col = col2 from @TempTable

INTO

With CTE(Col2, Col3, Col4) AS
(

Exec Procedure @Paramter)

select @Col = col2 from @TempTable

Thanks
Punu

View 6 Replies View Related

Grouping By Month In Common Table Expression Counts Wrong

Sep 26, 2012

I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date

[code]...

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.

View 3 Replies View Related

Preferred Method To Incorporate Common Table Expression (CTE) Functionality

Feb 26, 2007

What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset?  The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and  then limiting the persisted results based on the CTE function. 

It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
 
 

 

View 1 Replies View Related

Speed/efficiency Of View Vs. Common/nested Table Expression In A Join

Mar 2, 2008



i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.

i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.

the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?

View 2 Replies View Related

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

Common Table Expression (CTE) T-SQL Errors:Invalid Object Name 'ProductItemPrices'.The Variablename '@TopEmp' Has Been Declared

Jan 4, 2008

Hi all,
I copied the following code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CET.sql--

USE AdventureWorks

GO

--Use column value from a table pointed at by a foreign key

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT p.Name, pp.AvgPrice

FROM ProductItemPrices pp

JOIN

Production.Product p

ON

pp.ProductID = p.ProductID

ORDER BY p.Name

SELECT * FROM ProductItemPrices

GO

--Display rows from SalesOrderDetail table with a LineTotal

--value greater than the average for all Linetotal values with

--the same ProductID value

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice

FROM Sales.SalesOrderDetail sd

JOIN

ProductItemPrices pp

ON pp.ProductID = sd.ProductID

WHERE sd.LineTotal > pp.AvgPrice

ORDER BY sd.SalesOrderID, sd.ProductID



--Return EmployeeID along with first and last name of employees

--not reporting to any other employee

SELECT e.EmployeeID, c.FirstName, c.LastName
JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID)

JOIN Empcte a

ON e.ManagerID = a.empid)

--Order and display result set from CTE

SELECT * Hi all,

I copied the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):

--CTE.sql--

USE AdventureWorks

GO

--Use column value from a table pointed at by a foreign key

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT p.Name, pp.AvgPrice

FROM ProductItemPrices pp

JOIN

Production.Product p

ON

pp.ProductID = p.ProductID

ORDER BY p.Name

SELECT * FROM ProductItemPrices

GO

--Display rows from SalesOrderDetail table with a LineTotal

--value greater than the average for all Linetotal values with

--the same ProductID value

WITH ProductItemPrices AS

(

SELECT ProductID, AVG(LineTotal) 'AvgPrice'

FROM Sales.SalesOrderDetail

GROUP BY ProductID

)

SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice

FROM Sales.SalesOrderDetail sd

JOIN

ProductItemPrices pp

ON pp.ProductID = sd.ProductID

WHERE sd.LineTotal > pp.AvgPrice

ORDER BY sd.SalesOrderID, sd.ProductID



--Return EmployeeID along with first and last name of employees

--not reporting to any other employee

SELECT e.EmployeeID, c.FirstName, c.LastName

FROM HumanResources.Employee e

JOIN Person.Contact c

ON e.ContactID = c.ContactID

where ManagerID IS NULL

--Specify top level EmployeeID for direct reports

DECLARE @TopEmp as int

SET @TopEmp = 109;

--Names and departments for direct reports to

--EmployeeID = @TopEmp; calculate employee name

WITH Empcte(empid, empname, mgrid, dName, lvl)

AS

(

-- Anchor row

SELECT e.EmployeeID,

REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +

' ' + c.LastName, ' ', ' ') 'Employee name',

e.ManagerID, dn.Name, 0

FROM Person.Contact c

JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID

WHERE e.EmployeeID = @TopEmp

UNION ALL

-- Recursive rows

SELECT e.EmployeeID,

REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +

' ' + c.LastName, ' ', ' ') 'Employee name',

e.ManagerID, dn.Name, a.lvl+1

FROM (Person.Contact c

JOIN HumanResources.Employee e

ON e.ContactID = c.ContactID

JOIN HumanResources.EmployeeDepartmentHistory d

ON d.EmployeeID = e.EmployeeID

JOIN HumanResources.Department dn

ON dn.DepartmentID = d.DepartmentID)

JOIN Empcte a

ON e.ManagerID = a.empid)

--Order and display result set from CTE

SELECT *

FROM Empcte

WHERE lvl <= 1

ORDER BY lvl, mgrid, empid

--Alternate statement using MAXRECURSION;

--must position immediately after Empcte to work

SELECT *

FROM Empcte

OPTION (MAXRECURSION 1)
====================================
This is Part 1 (due to the length of input > 50000 characters).
Scott Chang

View 5 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Is It Possible To Execute The T-Sql Code Of Common Table Expressions In VB 2005 Express?

Jan 2, 2008

Hi all,
I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):

--CTE.sql--

USE ChemAveRpd

GO

WITH PivotedLabTests AS

(

SELECT LT.AnalyteName, LT.Unit,

Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),

Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),

QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)

FROM LabTests LT

JOIN Samples S ON LT.SampleID = S.SampleID

GROUP BY LT.AnalyteName, LT.Unit

)

SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,

avg2 = (abs(Prim + QA)) / 2,

avg3 = (abs(Dupl + QA)) / 2,

RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,

RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,

RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2

FROM PivotedLabTests

GO

===========================================
How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?

Please help and respond.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Can I Implement A Replication Between Sql Server 2005 And IBM AS400 DB?

Jun 7, 2007

Hello,



I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.

Can I do it ? If yes, how to do it?



Thanks

View 1 Replies View Related

Implement Manual Replication Between Two SQL Server 2005 Express?

Mar 2, 2007

Hi,

I need to synchronize the data between two SQL Server 2005 Express.

The database is really simple, just few tables (but data are inserted very often) without any triggers.

Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.

Does it sound a good idea?
Has anyone done this before o there are better solution?

Thanks in advance,
Stefano

View 6 Replies View Related

How Do You Implement Change Data Capture In SSIS Or SQL Server 2005?

Dec 3, 2007



Hi All,
I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:

Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively.
Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is
1. Am i using the right components?
2. what consideration should i have to see to make it true (some Logics on the conditional split)?
3. Any script which helps in this strategy?
4. If you have a better idea please try to help me, i need you help badly.

Thank you,

SamiDC

View 11 Replies View Related

How To Implement Alter Database Implement Restrictions On SQL2K ?

Dec 28, 2007

Hi Guyz

it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2

"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:

Explicitly denying the ALTER DATABASE statement.


A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.


If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."

thanks in advance.

View 4 Replies View Related

How To Implement A DropDownList W/o A Table

Jun 9, 2007

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

View 2 Replies View Related

Constraint Expression In SQL Server 2005 IDE

Mar 20, 2007

I was trying to enter a constraint expression into the dialog box. I have three fields of the data type "bit". I want to make sure that of the three, only one equals "1". Using the documentation I found in Books Online, I attempted to use the following expression, but it would not validate.

(expert == 1 && supplier == 0 && employee == 0) || (expert == 0 && supplier == 1 && employee == 0) || (expert == 0 && supplier == 0 && employee == 1)

Can someone please assist me in getting this corrected?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://www.servicerank.com/

View 4 Replies View Related

How To Implement SCD Type1 And Type2 At The Same Table

Apr 7, 2008

Hi All,
I want to implement SCD Type1 and Type2 in one table, i.e. my columns has got type1 and type2. I have the following table made from different tables; the columns are Column1, Column 2, Column3, Column4, Column5, and Column6. Here I have two type1 (Column2 and Column3 ) I want these to be Typ1, the rest will be Type2. My concern is I don€™t want use SCD component of the SSIS (b/se of performance issue, I hope) but I am using Lookup and conditional Split instead. The General concept is i have type1 and type2 dimensions in one table (dimension table). The question is:
1. Can I meet my goal using these components I mean without SCD transformation component and again i am thinking to avoid OLE DB Command transformation Component?
2. If yes then how do I use the conditional split, I mean how to write the condition for type1 and type2.
3. Is there any other option to use instead of SCD transformation component having type1 and type2 at the same table?
Thank you in advance for everything, I hope to get one solution from all of you guys. Have a great day.
Thank you,

View 3 Replies View Related

Common Table Expressions With Table Creation

May 20, 2008

I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.

I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?

like:


create table #temp (row_no int identity (1,1),time datetime, action varchar(5))

insert into #temp select * from cte5

View 4 Replies View Related

Delete The Common Row Of A Table

Jan 7, 2006

hi i am using MS SQL Server 2000, i want to delete the common row from a table

table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300

no primary key defile in to the table ....

i want to delete the row of
1 Jim 1000

please anybody can can help me ...

samarjit

View 1 Replies View Related

Common Need: History Table

Jan 22, 2008

Hi there!

I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.

When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.

First, what do you think of that implementation?

We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...

What is the common solution for this need?

Thanks
Frantz

View 4 Replies View Related

Common Table Expressions

Aug 31, 2006

Hi guys,

I know its not a good idea to give you a big query...but/...

I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...

 

----------------------------------------------

DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)

SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS

(SELECT
 dbo.tmpValidServices_ALL.SiteID,
 dbo.tmpValidServices_ALL.Programme,
 dbo.tmpValidServices_ALL.Complete AS Complete,
 dbo.tmpValidServices_ALL.RID AS PID,
 dbo.tmpValidServices_ALL.COC# AS COC,
 (dbo.lkpService.Notional$) AS NotionalSum,
 (TVMins) AS TVMinsSUM,
 dbo.tmpDemographics_ALL.Postcode,
 dbo.tmpdemographics_ALL.Suburb,
                count(*) as Qty

FROM  lkpService

INNER JOIN dbo.tmpValidServices_ALL vs  ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '

IF @COCGroup IS NOT NULL
SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '

SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '      

SELECT     @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '

 IF @StartDate IS NOT NULL
     SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete  >= @StartDate ) '

 IF @EndDate IS NOT NULL
      SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '


 IF @SiteID IS NOT NULL
       SELECT     @sql = @sql + 'AND (ag.EXACT# = @SiteID) '

 IF @COCGroup IS NOT NULL
 SELECT     @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '

 IF @Provider IS NOT NULL
 SELECT     @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '

 IF @Postcode IS NOT NULL
SELECT     @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '

SELECT     @sql = @sql  + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
                                                              dbo.tmpValidServices_ALL.Programme,
                                                              dbo.tmpValidServices_ALL.Complete ,
                                                              dbo.tmpValidServices_ALL.RID , 
                                                              dbo.tmpValidServices_ALL.COC# ,
                                                              dbo.tmpDemographics_ALL.postcode, 
                                                              dbo.tmpdemographics_ALL.Suburb,
                                                              (dbo.lkpService.Notional$),
                                                              (dbo.lkpService.TVMins)  ) 

SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20))  + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
 COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients, 
 COUNT(DISTINCT  CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
 SUM(NotionalSum) AS NotionalSum,
 SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
 
FROM Q1

Group by Postcode,Suburb

Order by Postcode,Suburb  '

SET              @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
                     
 EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider

View 2 Replies View Related

Edit Expression Using SQL Server 2005 Report Wizard

May 28, 2007

Hi



Bear with me I am creating my first report using sql server 2005 report wizard.



I have a query that s runs OK and returns name/addresses of companies for me to write a statement report.



My question is , how can only the 'FILLED' address column get displayed onto report and empty address fields are rejected i.e.



Address 1: 12 WoodStock Road

Address 2:

Address 3: Oxford

Address 4:

Address 5: OX10 0AB



therefore the report (page) should only display Address 1, Address 3 and Address 5



I tried to solve by trying to write an expression in 'Edit Experssion' without any joy.



Thanks in advance

View 5 Replies View Related

SQL Server Reporting Services 2005 Group Expression

Mar 8, 2007

I have an expression in a group that calculates a percentage of sales:

=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100

If there is a 0 balance for both the book dollar and the net dollar the expression returns #Error.

I have made sure that if the value is null it should return a 0 in the initial query.

,SUM(ISNULL(R.BOOKD,0)) BOOKD
,SUM(ISNULL(R.NET,0)) NET



Any suggestions would be greatly appreciated.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved