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


ADVERTISEMENT

Preferred Method Of Updating Tables?

Jul 31, 2015

Right now I am leaning towards joining a temp table that pulls my aggregates in and then joins them on metric id and year month. But I noticed my bosses boss who knows this stuff a lot more than I, seemed to do direct inserts dropping that whole range.

Same criteria but different approach. I like updating the new results and not dropping or deleting the contents. I like using temp tables too it makes it easy to just select into them and join off of them on the destination table that will be updated.

View 3 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

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 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

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

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

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

Grouping Common Functionality In Multiple Stored Procedures

Oct 22, 2007

Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:
SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn);
However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents.  The problem now though is that is that i wish to do queries against the data returned but i can't simply say:
SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn);
The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters.  This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures.  Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).
Appreciate if someone could help,

View 2 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

Most Common Connection Method To SQL 2005

Aug 8, 2007

I am currently using ADO from an application to connect to SQL 2005. Is thisthe preferred way, or is that the most common method? I was told I need touse an API. Which API? Thanks.Charlie

View 1 Replies View Related

How To Incorporate A Table Field Into The Email Message Body Nto As An Attachment?

Oct 5, 2005

Hello everyone,

Please i need your help...

I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though....Just write it in the mail as part of message body...

I use this SQL select statement to retrieve the strTitle and datBorrowed fields

strSQL += @"Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";


Now, I have the following code to write the email

static void SendTest()
{

int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}


And the body of the email is this......


Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***" +
"" +
"Books Borrowed Yesterday are:" +

"" +
"" +
"Thank you," +
"" +
"eLibrarian" +
"" +
"================================================== ===============" +
"" +
"This e-mail is automatically generated by the Library system." +
"Please do not reply.");



i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:" +

So how should i do this?

View 3 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

SQL 2012 :: Incorporate Several Queries In 1

Dec 18, 2014

I am wondering if it is possible to make 1 query that gives several, different resultsets.

I am working on a database for speedskating times, in particular I am working on seasonlists

The query I use for this i (in simplified form)

SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.

I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.

In the current setup I need to make 6 queries.

Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?

View 9 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

SQL Server 2008 :: Table(s) Design For Messaging Functionality?

Jul 21, 2015

I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.

I started to build the table which looked like this:

Id (PK) (Increments by 1)

ToUserId (FK) -- User who they're getting in contact with

FromUserId (FK) -- User who sent the message

Content (nvarchar(3000)) -- Message being send

Status (int) -- read / new / deleted / sent

EmailDate (datetime)

EmailDeleted (datetime)

But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.

View 9 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

Preferred SQL Tools For Sql*server?

Jul 20, 2005

Hi All,I am new this week to sql*server / query analyzer, coming from yearsof an oracle / TOAD background. Does anyone have any tools theyprefer for writing and executing sql code other than query analyzer?Thanks!Mike

View 1 Replies View Related

SQL Server 2012 :: Incorporate RANK Or NTILE With LEAD Function?

Jan 9, 2014

We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:

if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go

-- Create temp tables for sample data

create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)

-- Insert some sample data

insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')

-- Use lead function to determine how many months are between

-- consecutive dates per account

; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval

where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.

-- Show the results

select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis

Results (this is not getting me what I'm looking for):

RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122

[code]....

This is what I'm trying to achieve:

RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114

[code]....

The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.

I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.

View 5 Replies View Related

Distinct In Common Table Expressions CTE

Feb 20, 2007

I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!
My CTE:WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS
(
--Base Case
SELECT
SystemID,
GroupID,
AccessType,
AccessGroupID,
StudentID,
1 as HierarchyLevel
FROM UserGroup a

UNION ALL

--Recursive step
SELECT
u.SystemID,
u.GroupID,
u.AccessType,
u.AccessGroupID,
u.StudentID,
uh.HierarchyLevel + 1 as HierarchyLevel
FROM UserGroup u
INNER JOIN StudentsHierarchy uh ON
u.GroupID = uh.AccessGroupID

)
Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
 
and I would like to have a distinct on the StudentID like:Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
 
 
How should I do?
 

View 1 Replies View Related







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