common-table-expression

SQL Error in VBA

Hey guys! I have the following code in SQL (2005) which calculates the avarage user logins on a systm: with user_total as ( select COUNT(distinct ID) as counter FROM [dbo].[LOG] where [LOG].DESCRIPTION='Login success.' AND Convert(datetime,convert(char(10),[LOG].CREATED_ON,101)) BETWEEN '2009-01- 01...

How do you create an index on a subquery factored temporary table?

I've got a query which has a WITH statement for a subquery at the top, and I'm then running a couple of CONNECT BYs on the subquery. The subquery can contain tens of thousands of rows, and there's no limit to the depth of the CONNECT BY hierarchy. Currently, this query takes upwards of 30 seconds; is it possible to specify indexes to put...

Common table expressions, how to avoid infinite recusion when traversing a graph?

I have a simple weighted graph A 1 / \\ 0.5 / \\0.5 B C Suppose this describes a family and A is the father, B is the son and C is the mother. Let's say B is studying in an university and A has bought an apartment for him. A is living with C in a house which is commonly owned, 50-50. I want to transform the graph into a...

Evaluation of CTEs in SQL Server 2005

I have a question about how MS SQL evaluates functions inside CTEs. A couple of searches didn't turn up any results related to this issue, but I apologize if this is common knowledge and I'm just behind the curve. It wouldn't be the first time :-) This query is a simplified (and obviously less dynamic) version of what I'm actually doing...

T-sql Common expression query as subquery

I have the following query: WITH Orders(Id) AS ( SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang ) SELECT Id, ( SELECT CONVERT(VARCHAR(255),anfragetext) + ' | ' FROM MPHotlineAnfrageAnhang WHERE anfrageid = Id ORDER BY anfrageid, erstelltam FOR XML PATH('') ) AS Descriptions FROM Orders Its concatenates varchar values of dif...

SQL: Recursively get parent records using Common Table Expressions

Hi there, Suposse you have to following tables where a sale consists of products and a product can be placed in multiple categories. Whereby categories have a hierachly structure like: Man Shoes Sport Casual Watches Women Shoes Sport Casual Watches Tables: Sale: id name 1 Sale1 Product: id saleidfk name ...

MS SQL server and Trees

Im looking for some way of extrating data form a tree table as defined below. Table Tree Defined as :- TreeID uniqueidentifier TreeParent uniqueidentifier TreeCode varchar(50) TreeDesc varchar(100) Data some (23k rows), Parent Refs back into ID in table The following SQL renders the whole tree (takes arround 2 mins 30) I need to do...

Consolidating values in a junction table

I have the following schema: Parcels Segments SegmentsParcels ========= ========== ================= ParcelID SegmentID ParcelID ... Name SegmentID ... id A user of the data wants to consolidate Segments.Names and gave me a list of current Segment.Names ma...

How to use Common Table Expression and check no duplication in SQL Server

I have a table references to itself. User table: id, username, managerid and managerid links back to id Now, I want to get all the managers including direct manager, manager of direct manager, so on and so forth... The problem is that I do not want to have a unstop recursive sql. So, I want to check if an id alreay in a list, I will ...

Common Table Expression Counters with 2 Unions

If I have a common table expression for a family with mother & father, how can I increment the 'Generation' counter? A family should have the child as generation zero, parents as generation 1, and the four grandparents as generation 2. But the loop is performed twice, one for each set of grandparents. ;WITH FamilyTree AS ( SELECT ...

Ordering recursive result set in SQL Server

I am having extreme difficulty constructing a query which returns an XML style hierarchy. We have a database table which contains a hierarchy of URLs for our website. The table contains the columns: ID, URL, DisplayName, ParentID, ItemOrder The parent ID forms a recursive relationship between the current item and it's parent. The item ...

CTE to build a list of departments and managers (hierarchical)

I need to generate a list of users that are managers, or managers of managers, for company departments. I have two tables; one details the departments and one contains the manager hierarchy (simplified): CREATE TABLE [dbo].[Manager]( [ManagerId] [int], [ParentManagerId] [int]) CREATE TABLE [dbo].[Department]( [DepartmentId] [int], [Ma...

How do I create a Pivot query for this simple case?

I have a very simple query that I need pivoted, but I haven't got a clue how to turn it into a pivot table from what I've read in the literature. Everything I've read involves hard-coded columns or is too complex. It has to be simpler, or I just can't grasp CTE's. The query is a list of user names and clients they have access to. So I...

Updating records with their subordinates via CTE or subquery

Let's say I have a table with the following columns: Employees Table employeeID int employeeName varchar(50) managerID int totalOrganization int managerID is referential to employeeID. totalOrganization is currently 0 for all records. I'd like to update totalOrganization on each row to the total number of employees under them. So ...

Is this possible with Sql 2005 CTE?

I have been working on a query that will return a suggested start date for a manufacturing line based on due date and the number of minutes needed to complete the task. There is a calendar table(LINE_ID, CALENDAR_DATE, SCHEDULED_MINUTES) that displays per manufacturing line, the number of minutes scheduled for that day. Example: (Usu...

T-SQL - how to get around the order by restriction in CTEs

Hi all I have the following CTE. Its purpose is to provide unique Month/Year pairs. Later code will use the CTE to produce a concatenated string list of the Month/Year pairs. ;WITH tblStoredWillsInPeriod AS ( SELECT DISTINCT Kctc.GetMonthAndYearString(DateWillReceived) Month FROM Kctc.StoredWills WHERE DateWillReceived BETW...

Conditional Common Table Expression (CTE) in SQL

Hi I'm trying to select the hierarchy of a product category tree in SQL. My code looks as follows. I'm trying to achieve a dynamic sort order, using IF or Case When on the SortOrder parameter. The commented line should be active if @SortOrder is equal to 'sortorder'. I tried to add If Else statement around it, but I failed... Can yo...

SQL Server CTE referred in self joins slow

Hello, I have written a table-valued UDF that starts by a CTE to return a subset of the rows from a large table. There are several joins in the CTE. A couple of inner and one left join to other tables, which don't contain a lot of rows. The CTE has a where clause that returns the rows within a date range, in order to return only the row...

SQL Server Multi-statement UDF - way to store data temporarily required

Hello, I have a relatively complex query, with several self joins, which works on a rather large table. For that query to perform faster, I thus need to only work with a subset of the data. Said subset of data can range between 12 000 and 120 000 rows depending on the parameters passed. More details can be found here: http://stackoverf...

Possible to concatinate column values into a string?

Say I have the following table: id|myId|Name ------------- 1 | 3 |Bob 2 | 3 |Chet 3 | 3 |Dave 4 | 4 |Jim 5 | 4 |Jose ------------- Is it possible to use a recursive CTE to generate the following output: 3 | Bob, Chet, Date 4 | Jim, Jose I've played around with it a bit but haven't been able to get it working. Would I be bett...