Hi,
I have a requirement where I need to display the items category wise
I have 3 tables,
one stores category_id, parent_id and category_name
second table stored item_id,item_name and proce
3rd table stores category_id and item_id
I need to display the values like this
Category name, Item name
with first parent category ans ites items should be displayed and next sub category and its item
By category table with contain 2 levels of sub category
i.e Category1
Category11
Category111
any help with the query would be much appreciated, I wrote the query something like this....
select c.category_name,parent_id,item_name from category c,items i,Category_item ci where
ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2
Thanks
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time ------------------------- ---------------- ------------- --------- ----------- ------------ --------------- ----------- 2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0 2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0 2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation where direction='I' and Is_Internal=0 and continuation=0 and RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' For outgoing data calculation
i want to break 2 by 2 rows in reportceiling(rownumber(nothing)/2).i used this expression in row group. URL....but i want to use this expression in matrix and that matirx is with in list . so i am getting error . how to use rownumber in list.here i used list to break the page wise id
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday. Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query sum(CASE WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31 WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0 ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)) AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2 ((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.
I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.
HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit
I've always bound data to a dataset...how do I display data without a dataSet? This is how i usually display data... SqlConnection myConnection = new SqlConnection(conn); string sqlQuery = "SELECT * FROM questions"; SqlDataAdapter myCommand = new SqlDataAdapter(sqlQuery, myConnection); DataSet ds = new DataSet(); myCommand.Fill(ds, "questions"); MyDataGrid.DataSource = ds.Tables["questions"].DefaultView; MyDataGrid.DataBind(); <%# DataBinder.Eval(Container.DataItem, "answer3")%>
Hi All I am new to VS 2005 and ASP.NET. I used to use Dreamweaver to design but now I am trying VS. What I want to do is to display data retrieved with a SqlDataSource in a web page. I know how to do it by binding it to the various controls (Grid, DataList, DetailsView, FormView, Repeater.) available but how can I display it without using any of the mentioned (Grid, DataList, DetailsView, FormView, Repeater)? Any help apprecited.
Hi, I have stored procedure with cursor I have to use following step 1 fetch RateValidDates into @Id_RateCode, @TheValidDate .... get data
step 2 fetch prior from RateValidDates ... condition
step 3 fetch NEXT FROM RateValidDates into @Id_RateCode, @TheValidDate ... go to next record
Sql query analyzer output result of fetch NEXT FROM on the screen What seeting should i use to avoid output on screen (The reason My cursor takes about 8000 - 10000 rows when it's open)
When I select a table and try to view the data I take the return all rows or return top option. However nothing is displayed. These options worked before and I don't understand why it's not working now. Can anyone help?
I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:
Station Month Day Reading
1 1 1 100
1 1 2 200
1 1 3 300
1 2 1 400
1 2 2 500
And I would like to create a query that returns:
Station Month Day 1 Day 2 Day 3
1 1 100 200 300
1 2 400 500
Any help you can provide or tips to steer me in the right direction are much appreciated.
I am using a filter in a matrix control. I would like to display a "No data" type message if no data is present. The problem I'm running into is the base query is returning data, but the optional filter is removing all rows from display.
So using CountRows in the base dataset doesn't quite cut it. Is there an alternative?
I have a fairly complicated report consisting of numerous datasets and numerous tables.
In one of my tables I want to display the message "No data to display" if nothing comes back from the query. Currrently the table just disappears if there is no data. Is there a way to display the table if there is nothing in the query?
Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?
Public Sub ProductShow()
Dim myReader As SqlCeDataReader Dim mySqlCommand As SqlCeCommand Dim myCommandBehavior As New CommandBehavior Try connLocal.Open() mySqlCommand = New SqlCeCommand mySqlCommand = connLocal.CreateCommand mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id " myCommandBehavior = CommandBehavior.CloseConnection myReader = mySqlCommand.ExecuteReader(myCommandBehavior) While (myReader.Read()) cboProductPurchased.Items.Add(myReader("P_Name").ToString()) End While myReader.Close() Catch ex As Exception MsgBox(ex.ToString) Finally connLocal.Close() End Try
I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.
Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:
1. Backup all dbs [in case defragmentation messes something up] 2. Defragmentation [this will grow the transaction log] 3. Backup transaction log [backs up trans log and truncates inactive portion of it] 4. Shrink log file
Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.
Is it regrowing of the log file that will affect performance?
Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?
I have the following three tables : Account (Id int, AccountName nvarchar(25)) Role (id int, Rights int) AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method. One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights. Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK) JOIN RoleAccount ON RoleAccount.AccountID = Account.Id JOIN Role ON RoleAccount.RoleId = Role.Id GROUP BY Account.Name
Let's say you a 1000 records in the Employees table, who are spread over 40 different cities. How would you get a breakdown of how many employees in each city ?
Do I have to loop with a Count(*) for each CityID, or something ?
