sql

How do I make this SQL statement return empty strings instead of NULLS?

LastAccessed=(select max(modifydate) from scormtrackings WHERE bundleid=@bundleid and userid=u.userid), CompletedLessons=(select Value from scormtrackings WHERE bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'), TotalLessons=100, TotalNumAvail=100, TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE bundleid=@bun...

SQL Structure to Store Surveys and Answers - Build Tables and Queries Based on User Data?

I'm a total newbie when it comes to SQL. I'm making a site in ASP.NET for doing surveys. A privileged admin user will be able to build a survey. Then lots of other people will respond to the survey. Once I have the set of questions for a new survey, what is the best way to automatically construct a table and an INSERT query to store t...

What is the best approach to calculate a formula which changes value each day?

I use the following columns stored in a SQL table called tb_player: Date of Birth (Date), Times Played (Integer), Versions (Integer) to calculate a "playvalue" (integer) in the following formula: playvalue = (Today - Date of Birth) * Times Played * Versions I display upto 100 of these records with the associataed playvalue on a webpa...

CTE to traverse back up a hierarchy?

I can find all the children of a given record in a hierarchical data model (see code below) but I'm not sure how to traverse back up the Parent/Child chain with a given Child ID. Can anyone point me in the right direction to figure out how to do this? Is this possible in Linq to SQL as well? WITH TaskHierarchy (TaskID, [Subject], Pare...

Contraint - unique or empty

There is a nvarchar column in a table that allows empty values. Once there is a none-empty value it must be uniqe. Is it possible to define this rule without a trigger? EDIT: http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/ is a nice workaround. The only issue I see is th...

Rollback complete database one hour

We had some problems this morning and need to rollback our database for about one hour. Is this possible and how is it done? It is a Microsoft SQL 2005 database. BR Larre ...

Update several columns at once in Derby

DB2 supports this syntax: UPDATE DEST D SET (AAA,BBB) = ( SELECT MAX(Z.AAA), MAX(Z.BBB) FROM OTHER O WHERE O.ID = D.ID ) i.e. I can run a select which returns more than one column and copy the results into various columns of the destination table (the one to update). Derby only allows the syntax: UPDATE table-Name [[AS] correlat...

Calculating different tariff-periods for a call in SQL Server

For a call-rating system, I'm trying to split a telephone call duration into sub-durations for different tariff-periods. The calls are stored in a SQL Server database and have a starttime and total duration. Rates are different for night (0000 - 0800), peak (0800 - 1900) and offpeak (1900-235959) periods. For example: A call starts at ...

A work around Group By Clause Limitation

I'm working on a social network web application, and i got a situation where i need to resend reminder emails to users who haven't activated their emails. The problem is when i investigated the DB i found that many emails are duplicated (there was no validation on the email uniqueness apparently. So what i need to do know is to retrieve ...

MySQL not using indexes with WHERE IN clause?

I'm trying to optimize some of the database queries in my Rails app and I have several that have got me stumped. They are all using an IN in the WHERE clause and are all doing full table scans even though an appropriate index appears to be in place. For example: SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id...

SQL Group By

If I have a set of records name amount Code Dave 2 1234 Dave 3 1234 Daves 4 1234 I want this to group based on Code & Name, but the last Row has a typo in the name, so this wont group. What would be the best way to group these as: Dave/Daves 9 1234 ...

Database Properties "Mirroring" Page

I am having a problem with one database on my SQL Server 2005 production server. A number of databases are already set up for mirroring, however when I right click and go to properties in SSMS, on one particular database there is no "Mirroring" property page available. I have done the normal tasks, such as setting Full Recovery model, ...

Finding the T-SQL to return these values

I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008. First, the relevant tables, and the fields that matter to the problem. ProductionRuns RunID (key, and RunID is giv...

PostgreSQL - fetch the row which has the Max value for a column

I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent lives_remaining total for each usr_id There are multiple users (distinct usr_id's) time_stamp is not a unique identifier: sometimes user even...

Partial import of Sql server 2005 database for local developement

So at work, my team is using a central SQL server 2005 database server for integration testing and I want to move to testing on my local database. The only problem is that the central database is in excess of 10 Gb. I am interested in importing the data objects and a rows for each table to ensure I can be up and running. Can you guys ad...

SQL: Select TOP N Marks Per User (In a List of Users)

I am having difficulty writing a Stored Procedure that will query a list of students with their associative marks. Retrieving a List of Students - trivial Retrieving the top five marks per student - trivial...SELECT TOP (5) * WHERE StudentID = X Combining these two, I am a bit confused. I would like the Stored Procedure to return two ...

Help! - SQL - IF Else IF logic for returning Containstable selection

Hi guys, I'm trying to use IF else If logic inside an inline table valued function for SQL and returning a containstable based on that logic. but i'm having syntax problems with the IF Else IF block. thanks for the help. since i can't parametrize the columns in the containstable i have to resort to using if else statements. here's the co...

How can I clean up this SELECT query?

I'm running PHP 5 and MySQL 5 on a dedicated server (Ubuntu Server 8.10) with full root access. I'm cleaning up some LAMP code I've inherited and I've a large number of SQL selects with this type of construct: SELECT ... FROM table WHERE LCASE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( strSomeField, ' ', '-'), ',', ''), '/', '-'), ...

Handling Hierarchy Data in Database

I'm curious to know what the best way (best practice) to handle hierarchies are in regards to database design. Here is a small example of how I usually handle them. Node Table NodeId int PRIMARY KEY NodeParentId int NULL DisplaySeq int NOT NULL Title nvarchar(255) Ancestor Table NodeId int AncestorId int Hops int with Indexes on N...

Database Disk Queue too high, what can be done?

I have a problem with a large database I am working with which resides on a single drive - this Database contains around a dozen tables with the two main ones are around 1GB each which cannot be made smaller. My problem is the disk queue for the database drive is around 96% to 100% even when the website that uses the DB is idle. What op...