sql

SELECT AS problem

Hi, I tried to perform the following query: SELECT t1.[user1], t1.[user2], (CAST(t1.[total_event_duration] AS DECIMAL)) / (CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 ON t1.[user1] = t2.[user1] WHERE bu...

SQL Query to delete records occuring within 3000 milliseconds of each other

I have a table of data recording certain user events. The results looks something like: ID Username EventDate 1 UserA 2010-10-21 16:59:59.367 2 UserA 2010-10-21 17:00:00.114 3 UserA 2010-10-21 17:00:00.003 4 UserA 2010-10-21 17:00:02.867 5 UserB 2010-10-21 18:43:26.538 6 UserB 2010-10-21...

Can I use MySql Create Select on two different servers with PHP?

Hi, I am trying to use PHP and MySQL's Create Table Select between two different MySQL servers. I am not sure this can be done like this with SQL. I get no errors but I get nothing done either: <?php $dbname = 'cms'; $dbmaster = 'cmsms'; $db1 = mysql_connect('localhost', 'root', 'secret'); if (!$db1) { echo 'Could not connect to ...

Creating multiple databases or one

Our company has 4 business entities, so I have created 4 different databases for each company to be used for Human Resource service. Let's call that as a Group, Company1, Company2, and Company3. Even though they are all different databases, tables and store procedures(SP) are almost same except Group. Group is a database that gets summa...

SQL JOIN problem in MS Access 2007

SELECT DISTINCT bw.Bor_name FROM Borrower AS bw, Loan AS l JOIN Book_Copy AS bc ON l.Bc_id = bc.Bc_id WHERE bw.Bor_id = l.Bor_id GROUP BY l.Bor_id, bc.Bt_id HAVING COUNT( bc.Bt_id ) > 1 AND COUNT( l.Bor_id ) > 1; This works perfectly in a MySQL testing environment but won't work in MS Access 2007 where I actually need it run. I have a ...

UNIQUE constraint controlled by a bit column

Hi. I have a table, something like FieldsOnForms( FieldID int (FK_Fields) FormID int (FK_Forms) isDeleted bit ) The pair (FieldID,FormID) should be unique, BUT only if the row is not deleted (isDeleted=0). Is it possible to define such a constraint in SQLServer 2008? (without using triggers) P.S. Setting (FieldID, FormID, isDel...

sql multiple count

Hi, I have 3 tables, where the first one's primary key, is the foreign key in the other 2. I want to extract one field from the first table, and then a count from the other 2, all joined using the pk and fk. This is what I have so far: SELECT MBDDX_STUDY.STUDY_NAME, COUNT(MBDDX_EXPERIMENT.STUDY_ID) AS NUMBER_OF_EXPERIMENTS FROM MBDDX_...

SQL Server 2008: why do I see only system tables when I login ODBC from ACCESS

I am using Access to try to get some links to tables in SQL Server 2008. I've created a user name under security in SSMS for SQL Server and I've mapped it to a user that is under the database that I need. I've also given that user a default database that I need. When I try to connect through ODBC I am not seeing the correct tables. Wha...

PostGIS: register a "geometry" column without AddGeometryColumn

The usual way to create a geometry column is AddGeometryColumn, however I have to work with pre-existing columns, so I can't use that function (as far as I know). Thanks to the PostGIS docs, I can already register the column in the "geometry_columns" table, however AddGeometryColumn seems to do more than create a column and add a row in...

Calculate date ranges

Hi, I am trying to calculate the items sold 90 days prior to 6/1/2009 and 90 days after 6/1/2009 with the query below. it shows some error. Would someone kindly edcuate me please??? SELECT location, SUM((CASE WHEN t.order_date DATEADD (DAY, -90, '6/1/2009') THEN t.Item ELSE NULL END) as Prior_Items, SUM(CASE WHEN...

Communicating with Informix from SQL Server

Right... I've got a program I'm doing some maintenance on. Urgh. Even describing it makes me shudder... Right, okay. Every night, a database running on what we think is SQL Server 2000 hooks up to an Informix database and copies it over into SQL Server. The Informix/SQL data is accessed by the program I'm maintaining, which then store...

How to query for a value from the last month of each quarter?

SELECT YEAR(aum.AUM_Timeperiod) as Year, DATEPART(q, aum.AUM_TimePeriod) AS Quarter, SUM(cast(aum.AUM_AssetValue AS money)) as total_AssetValue FROM AssetUnderManagement as aum, LineOfBusiness where aum.LOB_ID = LineOfBusiness.LOB_ID and LineOfBusiness.LOB_Name = 'Asset Management' GROUP BY YEAR...

How to write a function with Oracle regexp to test a domaine name ?

I would like to write a PLSQL function that returns true if the domain name I pass is valid. I should use regular expression, but I don't know how to do this. declare ignore boolean; begin isDomainSyntaxOk('www.laclasse.com'); --> should return true. isDomainSyntaxOk('www.la classe.com'); --> should return false because ...

selecting top N rows for each group in a table

I am facing a very common issue regarding "Selecting top N rows for each group in a table". Consider a table with id, name, hair_colour, score columns. I want a resultset such that, for each hair colour, get me top 3 scorer names. To solve this i got exactly what i need on Rick Osborne's blogpost "sql-getting-top-n-rows-for-a-grouped-...

SQL: what kind of relation (1:1, 1:m, m:m,...) there is between this two tables?

Hi, what kind of relation (1:1, 1:m, m:m, whatever) there is between this two tables? CREATE TABLE IF NOT EXISTS `my_product` ( `id` int(11) NOT NULL auto_increment, `price` float default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `my_product_i18n` ( `id` int...

SQL Server combining data question

Hey all I am trying to combine my data into one sum. This is my output right now: Amount --------- $258.0 $400.0 $1011.0 $628.0 $628.0 $340.0 $340.0 $1764.0 of course the total would be $5369. This is the type of output I need Description | Quantity | Price | Amount -------------------------------------------- Fees 8 ...

SQL Server 2008: how do I grant privileges to a username?

I need to be able to establish an ODBC connection through SQL Server authentication. In SSMS how do I grant permission for a user to be able to have ALL RIGHTS on a specific database? is there a way to do this graphically with SSMS? ...

If using Functional Oriented Programming does the "impedance mismatch" go away?

I see a lot of work at the moment on Entity Modelling in Code and interest in functional Programming. After some years working in Object Oriented Systems I have time and time again come up against the "Impedance Mismatch". Since Transact SQL implements some FOP with datasets being described as Sets in a declarative way, does the "imped...

SQL Server 2008: ODBC connection problems

I am able to successfully connect from access 2007 into SQL Server 2008 using ODBC; however it prompts me with the password each time. When I try to open the linked table from access it says connection filed: SQLSTATE '28000' SQL SERVER ERROR 18452 [microsoft][odbc.........[][]] login failed. the loin is frmo an untrusted domain and can...

WCF Service Not Connecting to SQL Database

Problem When making calls to a SQL database from a WCF service we receive the following error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to...