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#.
Its Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
View 1 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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 dont 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
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
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
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
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
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
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