Query Needed For Path Navigation Through A Web Site
Dec 13, 2006
hi experts,
i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
is this possible and if not, what are the other approches that can i make to get the report i want.
also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
thanks
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!
Hello, We have some queries that are long and intensive. We have thought about running the queries and storing the data in a text file for lookup from our website.
Example: Our online store only displays items that are in stock so when a user selects a category a query runs and grabs only items that are in stock and then displays them. There could be thousands of items the query needs to sort through before displaying the items that are in stock. What if we ran this query once every hours an stored the results in a txt file? The asp page would then go to the text file to grab the results instead of having to run the query every time a user selects a category. Will this speed up the site by not having to query every time? Would this be a correct way to eliminate queries that run thousands of times a day?
I am new to XML in SQL Server and X-Path Query Recently I have done the following virtual lab in techNet site SQL Server 2005 XML Capabilities The following query is worked fine in the lab SELECT TOP 10 Demographics.query('declare default element namespace= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" /IndividualSurvey/YearlyIncome') FROM Sales.Individual But later I installed SQL Server Express in my system and also the Express Manager CTP but i am getting the following error if I execute the above query in my system Msg 9317, Level 16, State 1, Line 1 XQuery [Sales.Individual.Demographics.query()]: Syntax error near '=', expected string literal.
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..
Who can I send the Log File to for analysis and the fix feedback?
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
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 need someone that knows something about SQL queries.
I have a client that is running a Database known as ProLaw. It is in part a document management system for Law Offices.
They have an SQL 2005 database that tracks per client all the documents they create.
We had to replace there server with new server. The new server is running sbs2003 and had to have a different Netbios name then the old sbs2000 server. (Small Bus. Server has some weird quirks that make simply using the same netbios name impossible. Google search it if you don't believe me.)
The database holds in a single column the full network share path to each document.
Different documents may have different names and more subdirectories but the root path of "\lawwillsbs2000ProlawDocuments" is shared by all.
The new server is named \sbs2003 I need to change the first part of almost 3000 path statements to the new server. The rest of the path is unchanged.
I have had several people running prolaw tell me that I should run this query:
UPDATE Events
SET DocDir=REPLACE(DocDir, '\\lawwillsbs2000', '\\sbs2003')
WHERE EventKind='O'
This doesn't work. Nothing is changed. I'm guessing it is because this query assumes the value will be ONLY \lawwillsbs2000 I see nothing in here that tells the query that this is only part of the string. No wild card or other marker.
I need some kind of string function here do I not? Anyone know enough to help me craft a proper query?
I wrote a simple sql query to get the shortest path length from node 1 to all the other nodes. Since there's a loop in the graph, I want to prevent it from going back to some nodes it has expanded before.
I got the following error:
Msg 253, Level 16, State 1, Line 2 Recursive member of a common table expression 'CTE_Sample' has multiple recursive references.
It is referring to "and Table1.t NOT IN (select fr from CTE_Sample)"
Can somebody help me to solve it?
btw. How to use the UNION, EXCEPT or INTERSECT operators when doing the recursive join? It seems I must use UNION ALL.
WITH CTE_Sample (fr, t, level) AS ( SELECT Table1.fr, Table1.t, 1 AS level FROM Table1 WHERE fr=1 UNION ALL SELECT Table1.fr, Table1.t, level+1 FROM Table1 INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t and Table1.t NOT IN (select fr from CTE_Sample) ) SELECT CTE_Sample.t, min(CTE_Sample.level) FROM CTE_Sample group by CTE_Sample.t
Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
Hi I have a function which returns the periodId from the period table and the function is as follows: ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS int AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 1 AND 3 THEN 5 WHEN @month BETWEEN 4 AND 6 THEN 6 WHEN @month BETWEEN 7 AND 9 THEN 7 WHEN @month BETWEEN 10 AND 12 THEN 8 END END Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS. How do i check if all the three parameters have the same value.. Any help will be appreciated.. Regards, Karen
Hi, I have this query SELECT o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join User c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840 16534/25/2008 10:15:42 AM378550 16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045 16624/25/2008 2:30:43 PM4174387130 16634/25/2008 2:47:14 PM154825 16644/25/2008 2:49:10 PM265085 it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
I have some data that looks like this: field1(pk) field2 field3 563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468 Field2 can have the same value. What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'. So, I can't simply say: SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468' because it will still return record 5. Can anyone help me with this? Thank you!
I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks
Query1: SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone FROM Orders INNER JOIN ((NewWarehouse INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1 GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone ORDER BY Min(Shipment.OrderNum) DESC)
Query 2: (This is working) SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName FROM ((Orders INNER JOIN (NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd) WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1)) GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName ORDER BY Min(Shipment.OrderNum) DESC
I am sorry I am not that good in sql as some of you may be. Thanks.
I have 2 tables: Table1 has a field [Description](varchar) 100 Table2 has a field [IDDescription] int and field [Description] varchar(100)
In table 2 I have IDDescription store but some time also description wich is not store in table2 I need to pull all description (if it is from table2 or table1). How do I do this?
I have a query that need to do something like this.
About the data: --------------- I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.
select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id
Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.
Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
consider i have these tables: tbl_requests id description owner
tbl_owners id name
tbl_RequestStatus id description
am looking for output look like this: OwnerName New Completed x 2 5 y 4 3
table requests holds number of requests where each request has an owner, table owners holds the owners names, table status holds values for a request status for example : new, in progress and completed, the query am looking for should show each owner with the number of request he/she is handling grouped by the status, so we can say that owner X has 2 new requests and 5 completed requests.
hi i have a table with below structure id no.of visit no of sales no of delivery.
I want all records to be selected with the following data also average( no.of visit),average( no of sales ), average( no of delivery) in a single query ?
hi all i have table which has a userid,email and other fields. And i have a text file called email.txt,this text file has userid and email values corresponding to those in the table.And this file has only this userid and email fields separated by commas..
hi all I know a constraint's name,say cst_name .And i know which database it is existing,say db.then can i know which table belongs to this constraint in tat db with help of a query?
