query

easiest way to map ids during database refactoring

i have a number of tables with a column called OrderId. I have just done a refactoring and i want to get rid of the Order table and i have a new table called Transaction. I want all tables that have an OrderId column to now have a TransactionId column This is complete. I now need to populate the transactionId column. I have a mappi...

Join a table to itself with no specfic ID

Join a table to itself with no specfic ID I have a table of customer records which has no ID column, a customer may appear in the table more than once with various of the columns populated. I have managed to build a column INIT_SURNAME which is fully poplated but not unique to a customer. How can I merge the rows for matching customer...

How do I properly join tables and use a group by to summarize data in MySQL?

I am working in MySQL and having a bit of trouble with building a query that will summarize columns from two tables. I want to compare the quantity of requests per day, for a table containing hourly records and a table containing daily aggregation, per day. Ideally the sums of each would be identical. Here is the schema: Hourly Table:...

Why do SQL FullText queries slow down when you OR?

In SQL Server (2008), I have a FullText index on two columns, call them Table1.FirstNames and Table2.LastNames. After profiling some queries, I came up with the following results: SELECT * FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey) WHERE CONTAINS(FirstNames, 'Bob') OR CONTAINS(LastNames, 'Bob') => 31 197ms SELEC...

CAML query to retrieve only published pages from SharePoint 2007?

I am currently retrieving all pages and filtering out ones that are not published in the code, checking whether DateTime.Now is smaller than this: static readonly DateTime IMMEDIATE_PUBLISH = new DateTime(1900, 1, 1); public static DateTime PublicationDate(this SPListItem item) { // get start publish date PublishingPage page = ...

grails named queries

I started to write a simple named query in grails ..but it says missing method on the domain for the named query:( am i doing something wrong? I referred the documentation and i find no problem with the code.Any help?? I just tried the example in the documentation. class Publication { String title String author Date datePublis...

Filemaker: making queries of large data more efficient.

OK I have a Master Table of shipments, and a separate Charges table. There are millions of records in each, and it's come into Filemaker from a legacy system, so all the fields are defined as Text even though they may be Date, Number, etc. There's a date field in the charges table. I want to create a number field to represent just the y...

sql simple sproc - filter

Hi, I have a table called tblProducts with 3 columns: intID, dateMain, intStatus. I have a stored procedure like so: ALTER PROCEDURE [dbo].[spList_Report] @id INT, @startDate DATETIME = NULL, @endDate DATETIME = NULL, @includeStatus1 BIT, @includeStatus2 BIT, @includeStatus3 BIT, @includeStatus4 BIT AS SET NOCOUNT ON ...

Progressive count using a query ?

I use this query to SELECT userId, submDate, COUNT(submId) AS nSubms FROM submissions GROUP BY userId, submDate ORDER BY userId, submDate obtain the total number of submissions per user per date. However I need to have the progressive count for every user so I can see how their submissions accumulate over time. Is this possible to i...

mongodb search results

I'd like to display the total of number of records a query found and then the search results below it. Currently I do this with 2 queries like so: $total_results = $mydb->count($qry); $r = $mydb->find($qry) ->skip($pagination*$results_per_page) ->limit($results_per_page); Is it possible to somehow combine the coun...

Is it possible to define a local function in a TSQL query?

I have a complex expression calculating a value from a date that I have to use on multiple date columns. Can I define a temporary local function in my query to avoid copy and pasting this expression. ? like: create MyLocalFunc(@ADate datetime) returns int as begin blablabla end select MyLocalFunc(col1), col2, MyLocalFunc(col3), c...

Java getQueryString

So im trying to capture a certian section of a getQueryString(). I know I could try and go through and parse the string to get the certain section I wanted but was hoping to just be able to grab the piece I need. Here is my query result: N=0&Ntk=General&Ntt=info&Nty=1&D=info&Ntx=mode+matchallpartial&Dx= mode+matchall Im looking just to...

Changing the value of a table field after it has already been inserted

I am working with a CMS system, and using a kind of smartform am inserting fields into a table. In these fields are the name of a product, quantity and cost. cost and product name are taken from a products table, while quantity is input manually. I understand this is not the best architecture, but I am limited with the system I am wor...

mysql: list of rows in table that aren't referenced in another.

Hi There, I have two tables, timetable and lesson_booking these are linked via timetable_id. timetable represents timetable entries for a given day (mon, tue etc) that can be selected and used to make a booking for a client in lesson_booking What I would like to do is get a list of timetable entries that have no bookings associated w...

Generating reports, such as income/loss etc

I have 2 tables, purchases and sales with each having a date field and a cost field. I want to generate reports of a few different types, for example I want to show profit and loss over each month or year, or to show how often a particular item sold for a given arbitrary time period ( probably based on user input) Is it terribly comple...

SQL abs calculation - using latitudes and longitudes - MATH!

I've got an sql query that pulls locations from a database based on coordinates within a certain range. Now I'm trying to order them by proximity with a bit of math - but no matter how many times im writing it out on paper I can't seem to figure out a working solution. lets describe a few variables: $plat (the latitude of the place) ...

PHP-MySQL query -- Please explain

Ran across this, but I cannot quite grasp it, especially beginning with the LIMIT phrase. Thanks for any help. $sql = sprintf ("SELECT * FROM $tbl_name WHERE title LIKE '%s' OR description LIKE '%s' LIMIT $start, $limit", mysql_real_escape_string('%'....

Object query language | execute both "order by" and "where" clauses

How do I sort out some data according to an where clause and a order by clause using object query language this is the query that I'm using but i'm not sure if it is working or not. SELECT user from user.User as user WHERE user.status=1 order by user.username Thanks. ...

how to "one column of table minus 1"?

i have a TABLE named FLY,i have a column in it named FLY_NUM.im trying to decrease the value of this column in a mysql query(in PHP). something like this : mysql_query("UPDATE fly SET `fly_Num` = `fly_Num` -1 WHERE fly_ID ='1' "); this is wrong!everytime this query run,FLY_NUM column set to zero.how can i do such a thing? ...

SQL query date null check

Hi, I have the following stored procedure. ALTER PROCEDURE [dbo].[spList_Report] @id INT, @startDate DATETIME = NULL, @endDate DATETIME = NULL, @includeStatus1 BIT, @includeStatus2 BIT, @includeStatus3 BIT, @includeStatus4 BIT AS SET NOCOUNT ON SELECT * FROM tblProducts as products WHERE p...