sql

Sum and average over null values SQL Server 2008 Analysis Services

I have a simple problem, I think, but I have googled and can't find the solution. I have a cube that has MeasureA, MeasureB and MeasureC. Not all three measures have values for each record, sometimes they can be null, it's depending if it was applicable. Now for my totals, I need to average but the average must not take nulls into acc...

Doctrine - Multiple models referencing same id field in another model

I have a Files Model, and Multiple (currently 3) different other Models (Article, Job, Event) that can all have files, that are stored in the Files Model. The problem is that when i generate the tables via the CLI-Tool (./doctrine build-all-reload), i get this error message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot ...

SQL Split function that handles string with delimeter appearing between text qualifiers?

There are several SQL split functions, from loop driven, to using xml commands, and even using a numbers table. I haven't found one that supports text qualifiers. Using the example string below, I would like to split on ",", but not when it appears between double or single quotes. Example data: [email protected], "Sally \"Heat\" Jo...

T-SQL Query to Get SUM(COUNT())

Hi, I am planning to get a report for a table with following table structure: ID RequestDate ----------------------------- 1 2010/01/01 2 2010/02/14 3 2010/03/20 4 2010/01/07 5 2009/03/31 I want the results as: I D_Count RequestDate S...

SQL select row-wise increase in amount of running total column

Suppose I have a table with columns (DayId, RunningTotal): DayId RunningTotal --------------------- 1 25 3 50 6 100 9 200 10 250 How can I select the DayId and the amount the RunningTotal has increased from the previous day? i.e. how can I select: DayId DayTotal --------------------- 1 2...

What is wrong with this SQL statement

The error is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RA---SIN', 'DEC--SIN', 0.0,-90.0)' at line 1 INSERT INTO files_table (filename, folder, survey, telescope, author, observer, equinox, ctype1, ctype2, crval1, crval2) VALUES('H001_abcde_lut...

SSMS Ctrl+Tab Page Thumbnail Preview Add-on?

Hey Now, Is there a SQL Server Management studio add-on/plug-in that will enhance the tab switching dialog box when Ctrl+Tab is used to switch tabs to provide a thumbnail image of the tab? When we use Ctrl+Tab to switch tabs we see the list of tabs open. When we switch tabs in Visual Studio we get a small thumb nail preview screen of t...

Keeping SQL Server Dev Environment up to date

Hi All, I'm trying to figure out the best way to keep my Development servers synced up to the Production servers. Initially, I was just going to restore the backups from the production environment to the dev environment every week or so, however I realized that this would wipe out any actual development work that had taken place during ...

Passing delimited string to stored procedure to search database

How can i pass a string delimited by space or comma to stored procedure and filter result? I'm trying to do something like - Parameter Value -------------------------- @keywords key1 key2 key3 Then is stored procedure i want to first find all records with first or last name like key1 filter step 1 with first or last name...

Data access strategy for a site like SO - sorted SQL queries and simultaneous updates that affect the sort?

I'm working on a Grails web app that would be similar in access patterns to StackOverflow or MyLifeIsAverage - users can vote on entries, and their votes are used to sort a list of entries based on the number of votes. Votes can be placed while the sorted select queries are being performed. Since the selects would lock a large portion ...

How to count number of occurences for all different values in database column?

I have a Postgre database that has say 10 columns. The fifth column is called column5. There are 100 rows in the database and possible values of column5 are c5value1, c5value2, c5value3...c5value29, c5value30. I would like to print out a table that shows how many times each value occurs. So the table would look like this: Value(of col...

SQL: without a cursor, how to select records making a unique integer id (identity like) for dups?

If you have the select statement below where the PK is the primary key: select distinct dbo.DateAsInt( dateEntered) * 100 as PK, recordDescription as Data from MyTable and the output is something like this (the first numbers are spaced for clarity): PK Data 2010 01 01 00 New Years Day 2010 0...

SQL Duplicates Issue SQL SERVER 2000

I have two tables : Product and ProductRateDetail. The parent table is Product. I have duplicate records in the product table which need to be unique. There are entries in the ProductRateDetail table which correspond to duplicate records in the product table. Somehow I need to update the ProductRateDetail table to match the original (...

SQL: join within same table with different 'where' clause

Ok, so the problem I'm facing is this, I have a table with 3 columns : ID, Key and Value. ID | Key | Value ================ 1 | 1 | ab 1 | 2 | cd 1 | 3 | ef 2 | 1 | gh 2 | 2 | ij 2 | 3 | kl Now I want to select the value of Keys 1 & 3 for all IDs, the return should be like this ID | 1 | 2 ================ 1 ...

Is it possible to deny access to SQL Server from specific programs?

Currently one of our databases (SQL Server 2008) is accessed via a number of different mechanisms: .Net SqlClient Data Provider; SQL Server Management Studio; various .Net applications and 2007 Microsoft Office system (basically Excel). I see that in the sys.dm_exec_sessions DMV it is possible to see the program name of the program acc...

Migrating from hand-written persistence layer to ORM

Hi community, We are currently evaluating options for migrating from hand-written persistence layer to ORM. We have a bunch of legacy persistent objects (~200), that implement simple interface like this: interface JDBC { public long getId(); public void setId(long id); public void retrieve(); public void setDataSource(...

Recursive COUNT Query (SQL Server)

Hello Guys! I've two MS SQL tables: Category, Question. Each Question is assigned to exactly one Category. One Category may have many subcategories. Category Id : bigint (PK) Name : nvarchar(255) AcceptQuestions : bit IdParent : bigint (FK) Question Id : bigint (PK) Title : nvarchar(255) ... IdCategory : bigint (FK) How do I re...

SQL joins "going up" two tables

I'm trying to create a moderately complex query with joins: SELECT `history`.`id`, `parts`.`type_id`, `serialized_parts`.`serial`, `history_actions`.`action`, `history`.`date_added` FROM `history_actions`, `history` LEFT OUTER JOIN `parts` ON `parts`.`id` = `history`.`part_id` LEFT OUTER JOIN `serialized...

Write a SQL Query to replace values and include all the Dates.

Well I have this - Table DimDate- Date Table Employee- Id,Name,Points,Date Now the Employee table has points for everyday unless they did not come...so the Date does not have all the Dates entries... I mean for e.g in a week he did not come for 2 days the Employee table has only 5 rows...so I have this dimdate table which has all t...

Creating a trigger that manipultes a field in SQL Server

Guys, I have a table called tblNames and one of my fields in this table is called 'UpFileName'. Is it possible to create an insert trigger that would automatically replace all '%20' in the UpFileName field to underscores '_'? I'm using SQL Server 2005. ...