Hi All, i've a query that is supposed to return the sum for "status"-duration entries. The duration is calculated by using datediff(n, datestamp, (subquery that returns the datestamp ending the current status, i.e. finds the next fitting "status change"-entry after the one locked at) My Problem is that the following query returns an mu...
Is it possible to group by more than one column e.g GROUP BY fV.tier_id AND 'f.form_template_id' in a mySQL statement? ...
Edit: Please answer one of the two answers I ask. I know there are other options that would be better in a different case. These other potential options (partitioning the table, running as one large delete statement w/o committing in batches, etc) are NOT options in my case due to things outside my control. I have several very large ...
I am using the Entity Framework now and constantly having to write inline sql because Entity framework does not support full text search and other features. Is there a ORM out there that has many features that supports advanced queries? I feel if I am going to sometimes write inline sql I might as well do all of it in inline sql. Any hel...
I have tables with listings, categories and one that maps them to each other. So a listing can then be placed in multiple categories. Something like the following: listings table id title etc categories table id category_name etc map table listing_id category_id When I need to get all of the informat...
What's the difference between the SQL datatype NUMERIC and DECIMAL ? If databases treat these differently, I'd like to know how for atleast: SQL Server Oracle Db/2 MySQL PostgreSQL Furthermore , are there any differences in how database drivers interpret these types ? ...
I have a table with a list of contract line items in the form of CREATE TABLE contracts_lines ( contract_id integer, product_id integer, contract_line_start datetime, contract_line_end datetime, amount float ) What I would like to produce is a VIEW (or populate a table) that allows me to determine how much revenue I can expe...
Hi! I'm trying to find a way to match a query to a regular expression in a database. As far as I can tell (although I'm no expert), while most DBMS like MySQL have a regex option for searching, you can only do something like: Find all rows in Column 1 that match the regex in my query. What I want to be able to do is the opposite, i.e.:...
I have a table with the following data: Fiscal Year | Fiscal Quarter | Fiscal Week | Data 2009 | 2 | 22 | 9.5 2009 | 2 | 24 | 8.8 2009 | 2 | 26 | 8.8 2009 | 3 | 28 | 8.8 2009 | 3 | 31 | 9.1 2...
I've got 10 tables that I'm joining together to create a view. I'm only selecting the ID from each table, but in the view, each ID can show more than once, however the combination of all ID's will always be unique. Is there a way to create another column in this view that will be a unique ID? I'd like to be able to store the unique ID a...
I'm new to PHP and SQL, but I need a way to store the result of an SQL Query into a variable. The query is like this: $q = "SELECT type FROM users WHERE username='foo user'"; $result = pg_query($q); The query will only return one string; the user's account type, and I just need to store that in a variable so I can check to see if the...
I am using SQL Server 2005 and trying to write a query where I want to retrieve payments for a given month. I currently have: select sum(p1.paymentamount) as subtotal, CONVERT(char(10), p1.paymentdate, 103) as paymentdate from tblpayment p1 where 1=1 and p1.paymentdate >= @fromdate and p1.paymentdate <= @todate group by...
Assume a table with the fields TransactionId, ItemId, Code, EffectiveDate, and CreateDate. +---------------+--------+------+------------------+------------------+ | TransactionId | ItemId | Code | EffectiveDate | CreateDate | +---------------+--------+------+------------------+------------------+ | 1| 1| ...
SQL Server newbie The following query returns SRA by Student and month only if there is a record for a student in Discipline table. I need a query to return all students and month totals even if there is no record for student in Discipline table. Any direction appreciated SELECT TOP 100 PERCENT MONTH(dbo.Discipline.DisciplineDate) AS ...
I am working on a couple of link tables and I got to thinking (Danger Will Robinson, Danger) what are the possible structures of a link table and what are their pro's and con's. I came up with a few possible strictures for the link table: Traditional 3 column model id - auto-numbered PRIMARY table1fk - foreign key table2fk - foreign ...
I need to copy over rows from Table B to Table A. The requirement is to only insert rows that are not already in A. My question is, which is of the the following two is more efficient: A) INSERT INTO A (x, y, z) SELECT x, y, z FROM B b WHERE b.id NOT IN (SELECT id FROM A); B) INSERT INTO A (x, y, z) SELECT b.x, b....
I have two tables (bank statement and client requests) where column [date] is type of DATETIME2(0). I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds). How can I dot that? I tried CAST and CONVERT but it didn't helped. Update: Marc_s's answer works DATEADD(dd, 0, D...
In SQL server if you have nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywords are the correct way to do it. But why does SQL server behave this way? ...
This is my first question here and I hope it is simple enough to get a quick answer! Basically, I have the following code: $variable = curPageURL(); $query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE '$variable' ; If I echo the $variable, it prints the current page's url( which is a javascript on my page) Ultimately, what I...
My project needs a SQL Server 2000 User Id and password created for an ASP.NET website. How do I create a user account in SQL Server 2000? ...