Trying to answer a question to come up with a testing plan. It has to do with using a website and/or webservice that queries a sql server to get data and display to user. * Solution must be able to handle an estimated 2000 users, approximately 700 concurrent users, 10,000 + website hits a month. Database calls should handle ...
I have a query that returns one row. However, I want to invert the rows and columns, meaning show the rows as columns and columns as rows. I think the best way to do this is to use a pivot table, which I am no expert in. Here is my simple query: SELECT Period1, Period2, Period3 FROM GL.Actuals WHERE Year = 2009 AND Account = '001-4000-...
Is there a straight forward way to view the SQL command text actually executed against the underlying database by a DbCommand object (i.e. after the parameters have been processed into a statement)? Here the detail: I'm using VB.Net 3.5 and have a factory object (DbProviderFactory) and a connection (System.Data.IDbConnection). I am usin...
Hi all, I have a system whereby a user can view categories that they've subscribed to individually, and also those that are available in the region they belong in by default. So, the tables are as follows: Categories UsersCategories RegionsCategories I'm querying the db for all the categories within their region, and also all the indi...
I was just wondering if a storage engine type existed that allowed you to do version control on row level contents. For instance, if I have a simple table with ID, name, value, and ID is the PK, I could see that row 354 started as (354, "zak", "test")v1 then was updated to be (354, "zak", "this is version 2 of the value")v2 , and could s...
I have some oracle calls that I am porting. Today I came across this code which looks like "procedural" language, but is not declared in a function or anything... My question is: Can postgres handle this in this form? What form does this need to be in? DECLARE BEGIN IF :start_time IS NULL OR :start_date IS NULL OR :end...
I need to store three items as an array in a single column and be able to quickly/easily modify that data in later functions. [---YOU CAN SKIP THIS PART IF YOU TRUST ME--] To be clear, I love and use x_ref tables all the time but an x_ref doesn't work here because this is not a one-to-many relationship. I am making a project managem...
Hi! I'm looking to translate this SQL statement to a well working & performant LINQ command. I've managed to have the first count working using the grouping count and key members, but don't know how to get the second count. select main.title, count(details.id) as details, count(messages.id) as messages from main left outer join detail...
I am trying to come up with the most efficient database as possible. My bus routes all have about 10 stops. The bus starts at number one until it reaches the 10th stop, then it comes back again. This cycle happens 3 times a day. I am really stuck as to how I can efficiently generate the times for the buses and where I should store the s...
hi, i've got 1 row with many columns: col1|col2|col3|... and i want to have 1 column with many rows, like that: col1 col2 col3 .. ...
I have two tables --> Variables (id, name) and Variable_Entries (id, var_id, value). I want each variable to have a unique set of entries. If I make the value entry unique then a different variable won't be able to have that same value which is not right. Is there some way to make the value column unique for identical var_id's? ...
I'm trying to execute a SELECT statement that includes a column of a static string value. I've done this in Access, but never with raw SQL. Is this possible? Example: Name | Status ------+-------- John | Unpaid Terry | Unpaid Joe | Unpaid In the above example, the "Status" column doesn't exist in the database. ...
I am attempting to return a rowcount from a subquery as part of a result set. Here is a sample that I've tried that didn't work: SELECT recordID , GroupIdentifier , count() AS total , (SELECT COUNT() FROM table WHERE intActingAsBoolean = 1) AS Approved FROM table WHERE date_format(Datevalue, '%Y%m%d') BETWEEN 'startDat...
Suppose I have a table (tblEmp) whose structure is like as under Dept Emp ----- ------ d1 e1 d1 e2 d1 e3 d2 e4 d2 e5 d3 e6 If I need to bring the output as Dept DepartmentSpecificEmployees ------ ---------------------------- d1 e1,e2,e3 d2 e4,e5 d3 ...
I am writing a sql query which involves finding if timestamp falls in particular range of days. I have written that in the postgres but it doesn't works in Oracle and SQL Server: AND creation_date < (CURRENT_TIMESTAMP - interval '5 days') AND creation_date >= (CURRENT_TIMESTAMP - interval '15 days') Is there are common way to compar...
We are planning to capture a user's height and weight and am looking for ideas on representing them in sql. I have the following questions in mind weight can be expressed in kilograms and grams and height in meters and centimeters, so should I capture them as a BigDecimal with an appropriate precision and scale or capture them as vanil...
hi, can we create user define function with in stored procedure then end of the store procedure we need to delete that custom user define function. ...
I have the following tables and fields: +------------------+ +-------------------+ +---------------+ | Request | | RequestItem | | Item | +------------------+ +-------------------+ +---------------+ | + Requester_Name | | + Request_No | | + Item | +------------------+ +-------------------+ ...
I have a query that is ExcelQuery = "Update [Sheet1$] " +"set CITIZEN_ID = #" + value + " where CITIZEN_ID = " + value; As you can see, I'm essentially just prepending a "#" to the CITIZEN_ID field. value is a int/numeric value. So if I had "256" in the CITIZEN_ID column it would be converted to "#256"...
How could I adapt this query to show the previous, say, 61 weeks? select to_char(order_date,'IYYY') as iso_year, to_char(order_date,'IW') as iso_week, sum(sale_amount) from orders where to_char(order_date,'IW') <> to_char(SYSDATE) --exclude this week in progress and to_char(order_date,'IYYY') = 2010 group...