Recursive Data Query(e.g Heading- Sub Heading)

Dec 23, 2004

we have a table called evaluation_questions, the table has following fields

queId,Question, level, parentId

the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....


queIdQuestionlevelparentId
1English00
2Reading11
3Writing11
4recognizing words12
5Fluency22

so English is a heading as level is 0 and has no parent as parentId is also 0.
Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
same is the case with writing
where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.

Output:

Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored
procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.

View 3 Replies


ADVERTISEMENT

Variable Heading

Mar 29, 2007

Hello:



I have a situation where our users will key into a parameter field the name for the heading. I callled the parameter heading VarHdg1. How do I go about doing this? I tried printing @VarHdg1 but nothing came out. Please advise.



Thanks

View 1 Replies View Related

Getting Field Value As Column Heading

Aug 13, 2004

I have a table like this

EMPnoSERISSTATUS jOB POINTS
1AAPOC12
1BAQCS5
2CPREP4
2AASOS1
5AATDS4
1APVSS320

and i need a query or Stored prodedure to get data in the below format

EMPNOABC

133250
2104
5400


Note: A, B,C ... are field name in table (SERIS) and i would like that data as the column heading.

I dont want to use below query , as we dont know the value is dyanamic

SELECT empno,
SUM(CASE seris WHEN <B>'A'</B> THEN POINTS END ) AS <B> 'A' </B>
SUM(CASE seris WHEN <B> 'B' </B> THEN POINTS END ) AS <B>'B'</B>
FROM TABLE1
GROUP BY EMPNO

View 2 Replies View Related

Changing Column And Row Heading

Sep 20, 2005

I am trying to change the row and column heading to make it more meaningful in MDX.

For example instead of showing the_actual_cost, i want to show cost. This is done easily in SQL like

SELECT the_actual_cost AS cost......

Any help will be greatly appreciated.

Thanks
Mahesh

View 1 Replies View Related

Dynamic Derivation Of Heading - Is It Possible

Nov 10, 2005

Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently

Extract of Current SP

CREATE PROC dbo.FairValeSummaryPivot
@BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @BatchRunID
GO

what I would like would be along the lines of

MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,

Hope this is clear
Cheers

View 3 Replies View Related

Report Heading With Dates

May 4, 2007

I'm trying to create a heading for my report.

I want

sales figures between 09/11/2006 to 09/04/2007.

The date comes from parameters



What I have done is created 4 text boxes

textbox 1 = sales figures between

textbox 2 = =datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value)

textbox 3 = To

textbox 4 = =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)



Ok the problem is the text boxes when previewed are all over the place.

In crystal reports you can add a formula into a text box after text



Is this sort of thing possible in Reporting services



Thanks for all your help

View 3 Replies View Related

Column Heading Wrap

May 14, 2008



Hello,

I have a simple question, can someone provide syntax to wrap a column heading?

Example: Select sum(netprice) as 'Current YTD Invoice Amount'

I would like to see the output's column heading as:

Current YTD
Invoice Amount
----------------------
####.##



Thank you in advance,
Maria

View 1 Replies View Related

Heading Of The Report With Database.

Jan 31, 2007

Hello :
In the heading of the report, I want to put one text area, or i put a datum of the base.
 
He says to me :
 
Fields cannot be used in headings o feet of page.
 
Have you ideas??
 
Thank you very much

 

View 1 Replies View Related

Column Heading Getdate Function

Jan 7, 2015

I would like to make the column heading to be the current year for the Sales I'm adding below.

SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name,
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name

What I have now gives me incorrect syntax near keyword convert.

View 2 Replies View Related

Column Data To Column Heading By Dynamic Pivot Maybe

Feb 27, 2008



Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41

StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41

The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.

View 9 Replies View Related

Using A Variable In PIVOT Value List As Column Heading

Sep 20, 2007

Hi All,
 I have never used PIVOT before but looks exactly what I want for this scenario:
I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date.....  I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like:
DECLARE @todayDate varchar(255),
DECLARE @todayPlusOne varchar(255),
DECLARE @todayPlusTwo  varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express.
Thanks in advance.
Lee
 

View 5 Replies View Related

How Do You Format A Column Heading To Use Carriage Returns?

Mar 12, 2008



In SSRS/RDL, How do you format a column heading to use carriage returns?

I have a couple of instance where I have a column heading that I want spread over 3 lines. For example, the column heading "= Actual Amount" I would want centered and displayed on 3 lines, as follows:

=
Actual
Amount

View 4 Replies View Related

Reporting Services :: Row Heading Repeat In A Tablix With Column Grouped

Nov 30, 2015

We run 2014 enterprise.  I tried this with both table and matrix controls to no avail.

In the table scenario, I drag the table control over, instruct ssrs that a group name will go into column 1 and a sales figure in column2.  Then I highlight the sales figure cell,  add a column group on month number and generate my report off some june and july data.

ssrs understands that the months now expand horizontally but the rows alternate one with june filled in (blanks in july) and the next with july filled in for the same group name.  I believe I got all my sort by conditions set but am not sure. 

I tried all sorts of combos in the tablix and group properties before giving up.

View 3 Replies View Related

Using Colors In HTML Heading And Also Printing Colors To Failure / DBCC CHECKDB Error?

Apr 14, 2015

I need to print in RED if there's atleast 1 or more consistency error and need a heading color of the table (Caption) how to do it.

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>DB Corruption Report:</H1>' +
N'<table border="1">' +
N'<tr><th>DBName</th><th>MessageText</th>' +

[code]....

View 1 Replies View Related

Reporting Services :: Merging Column Heading For Column Groups

Jun 3, 2015

I am using Column Groups:

And I am getting:

Note how Today is being repeated three times. What I want is Today appearing once and centered (merged cell). Is this possible?

View 9 Replies View Related

Reporting Services :: Fixing Ssrs Report Table Column Heading While Scrolling Is Not Working In Report-viewer?

Dec 14, 2012

i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first  top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....

View 6 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

Need To Store Data From Recursive Query Using CTE

Apr 18, 2008

I have a recursive query, using common table expressions, like this:
 WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel)
AS
(
SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel
FROM Task
WHERE ParentGUID = @GUID

UNION ALL

SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel
FROM Task t
INNER JOIN TaskHierarchy th
ON t.ParentGUID = th.GUID
)
SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy
  The result is a number.  I need access to this number.  Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?

View 1 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

How To Query The Using Recursive?

Jun 10, 2007

 
Hello,
I have the following tables:

Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article: 

1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
 
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:

The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.  
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
 

View 1 Replies View Related

Recursive Query ..

Jan 18, 2008

Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project ..  .



I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.


CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)

 

I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:

 


As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:

·        
requested
ID_Classification

o       
PAL (ID_Classification: 16)

·        
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :

o       
Torrents (ID_Classification:
1) that will show the products values for the “Size�

o       
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.

o       
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)

 




Hmnn i show a graphic that i design for (feel to click over to see at correct resolution) 

 
 
 

I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com 
 

 

View 2 Replies View Related

Recursive SQL Query

Sep 21, 2004

I have a tough issue with a query.

I have the following structure


Table: Users

UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5


I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.

So if Param=1 it would return result set of 2,3,4,5,6

Anyone done this before or have any ideas?

Thanks,
ScAndal

View 1 Replies View Related

Recursive Query Help

Jul 20, 2005

Hi,

Does anyone know how to do an sql recursion queries?

I believe it involves a view with a union.

I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,

Employee_ID Boss_ID
1
2                     1
3                    
4                     3
5                     2

So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.

To do that i'd have to have recursion query.

Thanks,

View 2 Replies View Related

Recursive Sql Query

Sep 29, 2004

Hi,

i have the following table structure:

id | parentID
1 | NULL
2 | 1
3 | 2
4 | 3

im having trouble in creating a recursive query to return all the parents of a particular id. Have anyone ever done this?

thanks in advance,

View 7 Replies View Related

Can A Recursive Query Do This?

Dec 23, 2004

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)


Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT


I can write a query that takes the PRODUCTID, and returns all



PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws



ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138



I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:


PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws

Is it possible to write such a query or stored procedure?

View 4 Replies View Related

Recursive Query

Dec 6, 2007

Can you please help me to write a recursive query in sql server 2000?

I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.

Sample data in the table is as follows

wonum parent
7792 NULL
7793 7792
165305 7793
7794 7792
7795 7792

eg:
7792 is a workorder,which has got children and grand children

7793 is a child of 7792
165305 is a child of 7793
7794 is a child of 7792 and
7795 is a child of 7792

When I give the 7792 in the query,it should fetch all the children and grand children,etc.
output should be :
7793
165305
7794
7795

How can we do that?

View 16 Replies View Related

Recursive Query ??

Jul 23, 2005

Went looking for an answer but not really sure what phrases to lookfor. Just decided to post my question.I have a collection of groups which contain items. I also have acollection of users which can be assigned permissions to both groupsand individual items. If a user has permission to a group then the userhas that permission to each of the items in the group. I need a querywhich will return all the items and permission for a particular user.Here is the code for creating the tables and populating them.CREATE TABLE [Account] ([Name] VARCHAR(10))INSERT INTO [Account] VALUES ('210')INSERT INTO [Account] VALUES ('928')INSERT INTO [Account] VALUES ('ABC')CREATE TABLE [AccountGroup] ([Name] VARCHAR(10))INSERT INTO [AccountGroup] VALUES ('Group1')INSERT INTO [AccountGroup] VALUES ('Group2')CREATE TABLE [AccountGroupMembership] ([GroupName] VARCHAR(10), [AccountName] VARCHAR(10))INSERT INTO [AccountGroupMembership] VALUES ('Group1', '210')INSERT INTO [AccountGroupMembership] VALUES ('Group1', 'ABC')INSERT INTO [AccountGroupMembership] VALUES ('Group2', '928')INSERT INTO [AccountGroupMembership] VALUES ('Group2', 'ABC')CREATE TABLE [Permission] ([User] VARCHAR(10), [Item] VARCHAR(10), [ItemType] VARCHAR(1)-- 'A' for account, 'G' for account group, [ReadPerm] INT, [WritePerm] INT)INSERT INTO [Permission] VALUES ('john', '210', 'A', 1, 0)-- readaccess to 210 accountINSERT INTO [Permission] VALUES ('john', 'Group1', 'G', 1, 1)--read/write access to Group1 groupINSERT INTO [Permission] VALUES ('mary', '928', 'A', 0, 1)-- writeaccess to 928 accountThe simple querySELECT * FROM [Permission] WHERE [User] = 'john'returnsUser Item ItemType ReadPerm WritePerm---------- ---------- -------- ----------- -----------john 210 A 1 0john Group1 G 1 1but what I really want is (notice that Group1 has been replaced withthe two members of Group1)User Item ReadPerm WritePerm---------- ---------- ----------- -----------john 210 1 0john 210 1 1john ABC 1 1(Forget for the moment that 210 is listed twice with differentpermissions. I could take the result and do some sort of union to least(or most) restrictive permissions.)

View 4 Replies View Related

Recursive Query

Jul 20, 2005

Hi there,Need a little help with a certain query that's causing a lot of acidin my stomach...Have a table that stores sales measures for a given client. The salesmeasures are stored per year and there could be multiple salesmeasures every year per client. There is another field called lastupdate date. If there are multiple sales measures then need to selectthe one that's been entered last based on this field. Also, if thereis no sales measure data for current year then I need to return thelast year's data for which sales measure has been entered. Forexample: if client #1 has sales measure value of $200 for 1999 andnothing since, then I need to return $200 for any year following 1999.So the query would look something like this:SELECT client_name, sm_dollars FROM <tables>Based on the DDL at the bottom I would expect to get back: c1, 100;c2, 200The way I am doing it now is with correlated subqueries (3 to beexact) that each do an aggregate and join back to the original table.It works, but it is notoriously slow. SQL Server is scanning theindex and does a merge join which in a large query takes %95 of thetime. Here is the part of the query plan for it:| | | | | | |--MergeJoin(Inner Join, MANY-TO-MANYMERGE:([sales_measure].[client_id])=([sales_measure].[client_id]),RESIDUAL:(([sales_measure].[client_id]=[sales_measure].[client_id]AND [sales_measure].[tax_year]=[sales_measure].[tax_year]) AND[Expr1013]=[sales_measure].[last_update_date]))| | | | | | |--StreamAggregate(GROUP BY:([sales_measure].[client_id],[sales_measure].[tax_year])DEFINE:([Expr1013]=MAX([sales_measure].[last_update_date])))| | | | | | | |--MergeJoin(Inner Join, MERGE:([sales_measure].[client_id],[Expr1010])=([sales_measure].[client_id], [sales_measure].[tax_year]),RESIDUAL:([sales_measure].[client_id]=[sales_measure].[client_id] AND[sales_measure].[tax_year]=[Expr1010]))| | | | | | ||--Stream Aggregate(GROUP BY:([sales_measure].[client_id])DEFINE:([Expr1010]=MAX([sales_measure].[tax_year])))| | | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | |--IndexScan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)There are two indexes on sales measure table:sales_measure_pk - sales_measure_id (primary key) clusteredsales_measure_idx1 - client_id, tax_year, last_update_date, sm_dollarssales_measure table has 800,000 rows in it.Here is the rest of the DDL:IF OBJECT_ID('dbo.client') IS NOT NULLDROP TABLE dbo.clientGOcreate table dbo.client (client_idintidentityprimary key,client_namevarchar(100)NOT NULL)GOIF OBJECT_ID('dbo.sales_measure') IS NOT NULLDROP TABLE dbo.sales_measureGOcreate table dbo.sales_measure(sales_measure_idintidentityprimary key,client_idintNOT NULL,tax_yearsmallintNOT NULL,sm_dollarsmoneyNOT NULL,last_update_datedatetimeNOT NULL)GOCREATE INDEX sales_measure_idx1 ON sales_measure (client_id, tax_year,last_update_date, sm_dollars)GOINSERT dbo.client(client_name)SELECT'c1' UNION SELECT 'c2' UNION SELECT 'c3'GOINSERTdbo.sales_measure(client_id, tax_year, sm_dollars,last_update_date)SELECT1, 2004, 100, '1/4/2004'UNIONSELECT2, 2003, 100, '1/3/2004'UNIONSELECT 2, 2004, 150, '1/4/2004'UNIONSELECT2, 2004, 200, '1/5/2004'The view that I use to calculate sales measures:CREATE VIEW sales_measure_vw ASSELECTsm.*FROM sales_measure smINNER JOIN (SELECT sm2.client_id, sm2.tax_year,MAX(sm2.last_update_date) as last_update_dateFROM sales_measure sm2INNER JOIN (SELECT sm4.client_id, MAX(sm4.tax_year)as tax_yearFROM sales_measure sm4 GROUP BYsm4.client_id) sm3on sm3.client_id = sm2.client_idand sm3.tax_year = sm2.tax_yearGROUP BY sm2.client_id, sm2.tax_year ) sm1ON sm.client_id = sm1.client_id ANDsm.tax_year = sm1.tax_year ANDsm.last_update_date = sm1.last_update_dateAny advice on how to tame this would be appreciated. Also, any adviceon the indexes would help as well.ThanksBob

View 14 Replies View Related

Recursive Query

Dec 11, 2007

I'm trying to get from the first table bellow to the second one using recursive queries. Could you help me on this issue? I want to be able to group by project and employee id and then if the dates overlap then to get the min and max dates in one row

ProjectID EmpId StartDate EndDate
P01 E01 2007-10-01 2007-10-19
P01 E01 2007-11-01 2007-11-20
P01 E01 2007-11-15 2007-11-25
P01 E02 2007-11-30 2007-12-31


ProjectID EmpId StartDate EndDate
P01 E01 2007-10-01 2007-10-19
P01 E01 2007-11-01 2007-11-25
P01 E02 2007-11-30 2007-12-31

Thank you in advance

View 1 Replies View Related

Help With Recursive Query

Nov 7, 2007

I've been struggling with this for while. It doesn't seem that hard, but I can't seem get the results right. I've tried several SELECT queries and also played with CTE but I'm quite a novice in that dept. I have searched this forum and read many similar posts but I can't seem to get it quite right.

Suppose I have a table (Categories) with parent categories and sub categories with the following example data:

Id ParentId Category
1 null Items
2 null Autos
3 1 Books
4 2 Honda
5 1 DVDs
6 1 Furniture
7 2 BMW

Can someone suggest the most efficient way to get an appropriate result set that I could use to display each Parent Category followed by each appropriate Sub Category on a web page?

For example:

Autos
Honda
BMW

Items
Books
DVDs
Furniture

I'd appreciate any advice and thanks in advance.

View 5 Replies View Related

Recursive Query?

Jul 23, 2006

I have a set of data and I need to actually add a column to the result set based on information contained within the query. For example,

SalesmanID  SalesBudget   SalesRegion      Product      Amount
1                      1000                 USA                   Soap          100.50
1                      1000                 USA                   Milk               50.50
5                       2000                OZ                      Bread             2.50
1                       1000                USA                   Bread             2.50
3                        350                 OZ                      Nappies         4.50

Notice that Salesbudget is related to the salesmanid and doesn't change. I need to be able to add a column to this query that sum the total budget for the saleregion so output looks like this

SalesmanID  SalesBudget   SalesRegion      Product      Amount   RegionBudget
1                      1000                 USA                   Soap          100.50    3500
1                      1000                 USA                   Milk               50.50    3500
5                       2000                OZ                      Bread             2.50    2350
2                       1500                USA                   Bread             2.50    3500
3                        350                 OZ                      Nappies         4.50    2350

So I need to do a sum distinct on salesbudget by region and append to my query as a column, is this possible? If so how?

It is important that I do it this way as I will have parameters in my query that might restrict which lines are returned. For instance show all bread sales and that way I only what to sum region budgets for these.

I could do it with 2 queries but needs to be 1 as I am using a matrix report that does'nt contain a sum distinct function and is contrained by scope.

Thanks in advance

Damien

View 9 Replies View Related

Help With A Recursive Query!!!

Dec 20, 2007

i have two tables, one called P_TAXONOMY with these columns:

IDObject TaxID
DB1 259

and one hierarchical table called (TAXONOMY) with this columns:

TaxID TaxIDParent tax_name taxlevel
259 256 P.O 3

256 4 A 2

4 1 B 1

1 0 C 0


i get the TaxID from P_TAXONOMY and i have to look for recursively its father (level 0) in the TAXONOMY table; I'm pretty sure the solution to this involves some type of recursive query but if anyone here can help me out I'd really appreciate it.

i have the next query but only for 3 levels, i need it for n levels (from any leaf)

select stf.tax_name
from TAXONOMY stf,
(select st0.TaxIDParent
from TAXONOMY st0, (select st.TaxIDParent
from P_TAXONOMY spt, TAXONOMY st
where spt.TaxID = st.TaxID ) as u
where st0.TaxID = u.TaxIDParent ) as cons
where stf.TaxID = cons.TaxIDParent
group by tax_name


Thanx
Diego Bayona.


pd:
for example:
the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)

View 1 Replies View Related

Recursive Query --- Very Urgent

May 15, 2007

Hello. I have a question. I have a table that keeps track of
claims being made by clients (like insurance claims). There is a table which
has 2 fields. Field1 and Field2. Field1 has the original claim number. If an
adjustment is made to the claim, a new claim number will be generated. For example
if a person pays $100 and files a claim, he is issued a claim number which goes
into Field1. If that person spends some more money towards the same issue and
then files another claim then he is given a new claim number which is stored in
Field2.

Field1--------------Field2
--------------------------------
100--------------------150
150--------------------200
200--------------------250
250--------------------Null (No more adjustments made)
and so on.

So in the query I will supply a claim number (say 100). I
need to get the history of all the claims attached to it. In this example 100,
150, 200, 250.

Also if the claim number provided is 200 I need to get the
original claim number (which is 100) meaning I need to back track and forward
track too. I need this in SQL Server 2000.

I hope I am clear enough. Thanks.

View 3 Replies View Related







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