sql

Format XML returned from SQL for XML

Here is my Query : select EmployeeName, EmployeeSalary from Employee2 for xml path('EmployeeDetails') returns <EmployeeDetails> <EmployeeName>xxxxx</EmployeeName> <EmployeeSalary>5000.00000</EmployeeSalary> </EmployeeDetails> how can i get output as <EmployeeDetails> <EmployeeName = xxxxx /> <EmployeeSalary = 500...

Coalescing rows based on field value in row.

I have a table with tax rates where NULL entity type rows represent the default tax rates. Year End | EntityType | RateType | TaxRate ------------------------------------------ 2009 | NULL | Interest | 13 2009 | NULL | Other | 8 2009 | NULL | Interest | 13 2010 | NULL | Other | 9 2009...

Help with writing a MySQL query involving date differences.

I need to delete rows where a datetime field is over 2 weeks old. This is what I have came up with $duration = Date::WEEK * 2; // int(1209600) $query = 'DELETE FROM properties WHERE TIMEDIFF(' . date(DATE_ISO8601) . ', reserved_datetime) > ' . $duration; I don't often write complicated queries (preferring to do stuff in P...

Howto query for random rows?

Possible Duplicate: Alerternative to MySQL Order By Rand() What is an efficient way to query for random result sets in the following scenarios: Select a single random row from many. Select (at least) n random rows from many. Select all rows in a random order. In particular interested in MySQL, but might be a reason to try ...

Simple query in DB2 for AS400

This is a very easy query (I think) but I stil can't do it. I have some data like this: A B C 1 1100 5 1 1100 5 1 500 1 2 1200 4 2 1200 4 2 600 1 3 1300 3 3 1300 3 3 700 1 And I want to return the ...

How do I partition efficiently in SQL Server based on foreign keys?

I am working on SQL Server and want to create a partition on a table. I want to base it off of a foreign key which is in another table. table1 ( fk uniqueidentifier, data ) fk points to table2 table 2 ( partition element here ) I want to partition table1 base on table2's data, ie if table2 contains categories ...

Date split-up based on break-up

Hi, Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for. Breakups are: 1) Weekly: This should be followed from Monday through Sunday. 2) Monthly: This should be 1st of a month through end of the month. 3) Quarterly Since the Fiscal ...

Concerned with replicate fn in sql

hi guys, I am using following query create table #Attendence (id int identity(1,1),det varchar(2000)) insert into #Attendence (det ) select --convert(char(10),@Date,3) +REPLICATE(' ', 20 - LEN(convert(char(10),@Date,3)))+ staff.StaffNAme +REPLICATE(' ', 20 - LEN(staff.StaffNAme ))+ case Att.FN when 1 then 'Prese...

Update query that needs to find the 'newest' row

My logout function needs to update the latest row of a list of logins. This is what I have came up with, however it doesn't even pass syntax validation. $query = 'UPDATE user_logins SET active = 0 WHERE user_id = ' . Database::instance()->escape($this->getCurrentUserId()) . ' AND datetime = MAX(datetime) LIMIT 1'; ...

Oracle syntax error

I got the following error in Oracle: SELECT * FROM abcd WHERE name LIKE 'a%' LIMIT 10 * ERROR at line 1: ORA-00933: SQL command not properly ended What is the problem with the command? ...

How to write a postgresql query for getting only the date part of timestamp field, from a table

How to write a postgresql query for getting only the date part of timestamp field, from a table ...

Converting date to this format

I have a date in this format: 24-12-2010 // DAY - MONTH - YEAR I need to get it in this format: 1995-12-31T23:59:59.999Z // The Z is for the TimeZone I think. Check this link out: http://lucene.apache.org/solr/api/org/apache/solr/schema/DateField.html The above link is the way I need the date. I am using PHP now, so this n...

Zend Framework: How to combine three tables in one query using Joins?

I have three tables like this: Person table: person_id | name | dob -------------------------------- 1 | Naveed | 1988 2 | Ali | 1985 3 | Khan | 1987 4 | Rizwan | 1984 Address table: address_id | street | city | state | country -----------------------------...

how do i combine and sum two results in sql ?

hello all, i have one table, let's call it 'TBL'. i have one column that have only 3 values available.(let's say 'AAA', 'BBB', 'CCC') the values can return multiple times. for example: TBL --- Column1 ------- AAA AAA BBB CCC BBB CCC BBB CCC AAA i want to create a table result that looks like this: TBL-RESULT ---------- AAA+BBB 60% C...

Solr date range storing help

How can I store a date-range in solr? I need to store the start-date and the end-date. Lets say it is a villa which is for rent during a period (start and end). I must use two fields right? In the search the user may chose start-date and end-date, so when querying solr I think I could use something like: date:[$start TO $end] ...

How do I SELECT for the following?

I have a table with two columns of ids. TABLE id1 id2 1 1 1 2 1 3 2 1 2 6 2 2 3 1 3 10 3 3 I would like to SELECT every id1 that is matched to a certain combination of id2s. For example, if I have id2 = [1, 2, 3] I would like to return id1 = [1]; If I have id2 = [1, 6] I woul...

Tricky logic problem - how to create a set-based solution for this query?

Using SQL Server 2008 and any available features of TSQL, I am trying to work out how to if a set-based solution that does not involve a temp table exists for the following problem: Given a set of nodes that have a parent-child relationship, and a set of key-value pairs that apply to each, and given that the value (for a given key-value...

What is the difference between C and Posix locales on Postgres?

I know that database locales on Postgres are responsible for proper order of national characters, proper lower/upper-casing etc. But why there are two language-neutral locales: posix and c? Is there any difference between them or is it just one neutral locale with two different names? UPDATE As Magnus Hagander states in his answer, POS...

Problem using ROW_NUMBER() to get records randomly (SQL Server 2005)

Hi all, I want to get 1000 records from a table randomly, so I use: SELECT top 1000 mycol1 , mycol2 , ROW_NUMBER() OVER (ORDER BY NEWID()) rn FROM mytable However, I don't want to see rn in my resultset, so I do: SELECT mycol1 , mycol2 FROM ( SELECT top 1000 mycol1 , mycol2 , ...

Should I use If exist or Count in this statement?

Hi, How can I use If exist in this statement ? select count(*) as found from names where myname = "Hulk" which one will be better ? ...