T-SQL (SS2K8) :: Finding Tree Structure - Show All Upward And Downward Nodes
Jun 3, 2015
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP SELECT * INTO Emp FROM ( SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b */
I am just wondering how can I retrieve only the leaf node from the table below
Region RegionID RegionPID RegionName 1 Null South 2 Null East 3 Null Central 4 3 Central CBD 5 3 Central - East 6 3 Central - West 7 1 South-Yarra 8 4 China Town 9 4 Korean Town ....
What i would like to do is retrieve all the region name which is the lowest level from the tree e.g only those records below will return 5 3 Central - East 6 3 Central - West 7 1 South-Yarra 8 4 China Town 9 4 Korean TownCould any one help? Thank you
Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy?? Thank you very much for any ideas!! M.
I have the following table Memberid int submemberid int
example MemberId SubMemberId
1 2 2 3 1 4 3 5 2 6 7 8
Each member may supervise more than one member under him (whom I call submember) and then each sub member may also supervise more than one member under him I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so on I do not know how to do this here is my trial:
ALTER Procedure dbo.MemberReports;1
@MemberMaskId nvarchar(8)
As SET NOCOUNT ON Begin Declare @Exists Int -- Return Value
-- Select * from MembersTree where MemberId = @memId or MemberId = subMemberId
SELECT MembersTree.memberid, MembersTree.submemberid, MembersTree1.memberid FROM MembersTree INNER JOIN MembersTree MembersTree1 ON MembersTree.Submemberid = MembersTree1.memberid
WHERE MembersTree.MemberId = @memId
If @@rowcount = 0 -- No Record returned Select @Exists = 0 Else Select @Exists = 1 Return @Exists
hello i have a table with four fields : name_id, name, tree_id, level withthese data:1 name1 001 12 name2 002 13 name3 003 14 name4 001001 25 name5 001002 26 name6 001003 27 name7 001001001 3tree:1-- 4-7-5-62-3-and procedure which add nodes to tree looks tree.add(idparent,idchild,name)how to write select which returns idparent,idchild and name then i can addnodes in loop reading datareader ?
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Hi all! I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options: Example 1 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child Example 2 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table? Thanks in advance,
Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality
If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure
i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes
Country Department
+ America + Computers
---Florida -- HP
+ SothAfrica + Sales
---Capetown __Bikes
How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .
The above table shows a parent child relationship with the hierarchy shown in column level. for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
I have an xml file that I need to read out into columns and I'm having a problem with the section that has the persons address in it, a snippit of the file is shown below:-
DECLARE @xml XML SET @xml = ' <PersonStructure> <PersonName> <GivenName>Mike</GivenName> <FamilyName>Myers</FamilyName>
[code].....
Now the problem I am getting is, it is pulling out all the fields except it repeats the first part of the 'PersonAddress' field 3 times instead of giving me the whole address?
Is there a way of gaining access to all the parts within the 'PersonAddress' node even though all 3 parts within it are named the same (Address)?
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4, (col1 + col2) as addcol1, (col2 + col3) as addcol2, (col3 + col4) as addcol3, Max(addcol1,addcol2,addcol3) as maxvalue from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
INSERT INTO #SessionTest SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone_optimized @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX)) RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
CREATE TABLE #TEMP ( TYPE VARCHAR(10), SEQ INT, SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1 CROSS APPLY (SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE GROUP BY t2.TYPE) t2 INNER JOIN #TEMP t3 ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS SALES Sales Net Sales 444.65 Sales Sales LESS TRD 22.55 SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD) Finance LESS ED LESS ED 40 Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... .. production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime, @todate datetime, @category varchar(200) as begin create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' ) begin select responsibility, dept, category, round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example: Table 1 id Name 1 Coupon1 2 Coupon2 3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is: SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
