I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.
In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.
How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and in the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of treeview, but I am having a problem building a query in sql.
The query below shows all Parent, then child then grand child(all well and working), but what is desired is at times child takes place of a father.
declare @x as xml
set @x =
(
SELECT distinct
Table1.AssetSysID, Table1.Asset_ID , Table1.FromLR, Table1.Asset_ID + ', ' + Table1.[Desc2] as GarndFather,
Table2.ACISysID ,Table2.PAssetSysID, Table2.FeatureName + ', ' + Table2.[DESC] AS Father,
Table3.ITMSysID ,Table3.Item_ID + ',' + Table3.[DESC] as Child
I need to query a sql database with sql I need to return the data in a nested form ID, ParentID, Title I also need to know which level each item is with the query, making the result something like, ID, Title, Depth Any help?
Hi Everyone,I'm drawing a blank here and I am hoping someone can point me in the right direction. I have a table with the following columns (some omitted)IDGUIDPageNameParentPageID I want to build a hierarchical navigation system (2-tier). The conceptual problem that I am running into was getting this information from the same table. Initially I was going to use a nested repeater but I am thinking a treeview would be better. Anyway, the problem is the query. How would I start with something like this? Let's use the following as an exampleRows(ID '1', GUID '888....', PageName 'Page A', ParentPageID '-1')(ID '2', GUID '111....', PageName 'Page B', ParentPageID '-1')(ID '3', GUID '222....', PageName 'Page C', ParentPageID '-1')(ID '4', GUID '375....', PageName 'Page 1', ParentPageID '1')(ID '5', GUID '562....', PageName 'Page 2', ParentPageID '1')(ID '6', GUID '874....', PageName 'Page 3', ParentPageID '2') (ID '7', GUID '388....', PageName 'Page 4', ParentPageID '3') So, I want to be able to build a query so that I can do the followingPage A Page 1 Page 2Page B Page 3Page C Page 4 Any help would be greatly appreciated. Thanks!
userfeatureuserName featureIda 1a 5b 1b 5b 9c 5 c 9 menuid Pid Name1 Administrator2 1 Create User3 1 Delete USer4 1 View log5 WSR6 5 X7 5 X8 5 X9 Manager10 9 Y11 9 Y Using the above table i want to create a treeview ie based on the user login. Please let me know if there is any previous sample to this situation.
Hi guys, i need some help here regrading my project
i would like to create a tree view diagram by importing my data from database and display it in the tree view.. however im not sure how to implement it
ok i have a design question and since I am not a db designer I hope somebody can give me some insight into this...
I have an app that uses a treeview control to display a hierarchy of a machine assembly. Currently it only goes two levels deep (top level and a single subcomponent. WHat I would like to do is enable my users to add n-deep levels to the top level machine. The problem with that is that I can't think of a way to store this in a DB and how the table(s) structure would look like.
It seems like this would be a classic problem in DB design, but that is where I lack knowledge so any help will be greatly appreciated
I have an ASP.NET web application that uses a Treeview control to display what can potentially be a very large data set. In the past, I would just run a recursive stored procedure in my database that would output the XML which I would save to a file. The Treeview used the XML file as its data source. I did this because it can take so long for the stored procedure to run (10 seconds or more) that, it isn't practical to have the treeview point directly to the stored procedure. This worked well enough because the data didn't change very often.
Now, it looks as if the application will be used in a production environment, and I really need to find a way to supply up-to-date data to the treeview in a dynamic way. I have tried creating a view that would provide XML and that would be updated any time the target table is updated but, that has not worked. I have also tried creating a trigger that would output to an XML file any time an edit was made (using the xp_cmdshell functionality) but, that has proven difficult as well.
Is there a simpler solution that I am just missing? I just want an up-to-date XML representation of the data that is a result of a recursive function.
I'm trying to insert data into locally stored database (SQL Server). The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset. The treeview contains checkboxes allowing a user to select what to install in the locally stored database.
To sum up:
1. Get data from a webservice' not my problem 2. Present data in a Treview control' not my problem 3. Allow to user to select which data to install' not my problem 4. Insert data that the user has selected into my db' MY PROBLEM!!!!
The Treeview is generated with DataRelations between Group and Rule.
My locally stored database is designed by a third party provider and therefore the database must not be altered. The table I want to store data in is called "Groups" and it looks like this:
GroupID uniqueidentifier ' (newid()) GroupName nvarchar(50) ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value. GroupType tinyint ' 0 = subgroup, 1 = "top"group
The third party also created a stored procedure called pr_AddGroup taking the following parameters:
@GroupName ' can be both the RuleName and the GroupName @GroupType ' can be 0 for subgroup or 1 for "top"group @ParentGroup ' GUID
The problem with this stored procedure is that it does not have return value, which is here my problem actually lies. If it returned @@IDENTITY I could use this as the parameter for @ParentGroup. Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @@IDENTITY to get the newly created record).
My SQL Commands look like this
Dim cmd As SqlCommand Dim Conn As SqlConnection = New SqlConnection Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI" cmd = New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.Connection = Conn cmd.CommandText = "pr_AddGroup"
dim cmd2 as SqlCommand cmd2 = new SqlCommand cmd2.commandtype = commandtype.Text cmd2.commandtext = "SELECT @@IDENTITY as ID FROM Groups" cmd2.connection = Conn
dim ParentGroupGUID as system.guid
To get the data inserted in the Groups table I would something like the following, but the code is very ugly (and it doesn't work either);
For Each Group In TreeView1.Nodes ' Loop through Groups If Group.Checked Then cmd.Parameters("@GroupName").Value = Group.Text.ToString cmd.Parameters("@GroupType").Value = 1
For Each Rule In Group.Nodes ' Loop through Rules. If Rule.Checked Then cmd.Parameters("@GroupName").Value = Group.Text.ToString cmd.Parameters("@GroupType").Value = 1 cmd.Parameters("@ParentGroup").value = ParentGroupGUID cmd.ExecuteNonquery() End If Next Next
I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate, p.name as Project, SUM(s.amount) as TotalSales FROM Sales s JOIN Project p ON p.projectID = s.projectID WHEREs.closing >= DATEADD(mm, -12, GETDATE()) GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 LEFT OUTER JOIN #BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0) As Actual_CurrentMonth,
select col1,count(*) from client1..table1 group by col1 union select col1,count(*) from client2..table1 group by col1 union select col1,count(*) from client3..table1 group by col1
The results yields
33915 3405 3412
I am trying to get the following result but can't figure out how to get the total in the end.
I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.
Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.
For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.
SELECT distinct Ac.activityCode, Ac.ActivityName + isnull(Ac.description,'') AS ActivityName, Ac.activityStartDate, Ac.activityEndDate, LU.auditCode, LU.AuditName, St.studyCode AS StudyCode, St.StudyName AS StudyName
I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.
i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.
I have a table CombinedSales script below (CreateCombSalesTable.sql) which contains the details of a sale.
A row in the table has two flags online/offline SalesAmount is the financial amount, online = 1 (online sales) online = 0 (combined sales off and online).
The table also contains the product and the client etc.,
What I am trying to do is subtract onlinesales from combinedsales within the query or outside I don't mind, to return the result as (below)
select skuid,SUM(SalesAmount),online,ClientName from t_CombinedSalesDetails where ClientName ='Asda' and SkuId = 200437 group by Skuid,Online,ClientName order by skuid
THIS is what I get below the online = 0 is the total sales of this product.
/****** Object: Table [dbo].[t_CombinedSalesDetails] Script Date: 27/08/2014 13:23:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL Starting copy... NULL 0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):
Select DM.DriveID [DriveID], DM.FromDateTime [FromDateTime], DSD.ShiftID [ShiftID], Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic], Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType], Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
[code]...
SQL Server Newbie or here either. I'm a newbie as well.
I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.
My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).
WITH MonthlyVals AS (SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal FROM MyTable WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate AND StatementType IN (8,4,2)
[code]....
To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?
I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.
I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).
Is there any way of stopping these extra rows from being created using T-SQL in my query?
if t-sql query is perfectly run in development and when I execute in production at that time I want to use execution plan which is in development . so how I can do using cache? I know about hint we can use hint USE_PLANE. but I want to do with cache .
I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.
Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.
Here are some things I’ve tried so far…
1.Changed the States table into a subquery that only returns a single state 2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other). 3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.
SELECT s.state_name , COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits , SUM(DistinctPermits) AS DistinctPermits FROM States S CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits
SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact
Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side
For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact
Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table
There are One way, Round trips and journey with connecting flights in the above data, what I am looking for a report is tktamt, deprt city, arrival city and tktnum in a single line for each trip.
SO if it is one way trip or round trip there is no issue, if it is a trip with connecting flights tktnum has more than four records. In the above case for tktnum 34567 there are four records, for which I have to display only one record, where the depart city is 'JFK and Arrival city is 'SFO' as well as amount and tktnum.
I am building a application, which will generates a MASTER query with 15 fields & 5 tables joins.Now, the user selects only some fields from that master fields and generates the CHILD query. This is creating a performance issue.
To minimize this, i would like to remove the not necessary joins in the child query.
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS ( SELECT 170983,'DateToday','6/04/2014' UNION ALL SELECT 170983,'DateToday','6/04/2014' UNION ALL SELECT 170983,'employee','1010' UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT ID ,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON ,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE ,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE ,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE ,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS ,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules.
Basically I have a massive custom search query which I now have filter even further. [URL] for previous discussions on this query, which I'm currently happy with).
so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.
each transaction can be see by
* Everyone If no record found in security table for transaction.
* If even just one transaction found, the below have permission to view it.
- Person who raised transaction
- person currently responsible for it.
- person currently working on it.
- everyone in the security table for this transaction.
- Admins for this Transaction Type.
So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.And I think I have them all EXCEPT someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.
The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.
ALTER PROCEDURE [dbo].[uspJobSearch] @csType as nvarchar(20) = '-1', @status as nvarchar(20) = '-1', @startID as nvarchar(50) = '', @endID as nvarchar(50) = '', @complaintType as nvarchar(50) ='',
