sql

SQL - Only select records from weekends

I need to write a SQL Select statement to select all the records where DateCreated falls on a weekend for the last 4 weeks. I know I can do this by hard-coding the select like the one below. But I was wondering if there was an easier way using T-sql date functions. SELECT * FROM audit_table WHERE DateCreated BETWEEN '07-31-2010 00:00...

Day Change Update Problem

C# - Winforms - SQL Server i am building an application which displays some data on the screen of the current day which it gets from the database... the application is like a desktop widget so the data is always visible on the desktop the scheme i implemented is when user commits some change to the data, after the update query i ...

ERD and SQL query help ?

Can you tell me if this is done right or if I need to improve some of them I have a hard time understanding the query's when there is an associative entity. List all donors SELECT* from Donor; List the first and last names of all donors SELECT dfname, dlname FROM donor List the phone numbers of donors number 106 and 125 SEL...

Strange speed changes with sql query

Hi, I'm having trouble getting the correct query (oracle) for what I'm looking for. Essentially what I want is: SELECT count(ck.id) FROM claim_key ck WHERE (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY)) AND ck.clm_type = 5 AND ck.prgrm_id = 1 Explain: | Id | Operation | Name | Rows ...

Need help with an SQL Query

Hi all, I currently have two tables that look something like this: Table 1 Id | AddressNumber | AddressStreet | AddressZip ------------------------------------------------ 1 | 50 | Fake | 60101 2 | 300 | Fake | 60101 3 | 50 | Fake2 | 60101 4 | 50 | Fake ...

SQL Server / 2 select in the same Stored procedure

Here's what I'm trying do to in a single SQL Server procedure: @ID1 int select ID2 from TableA where ID1 = @ID1 (for each selected @ID2) select * from TableB where ID2 = @ID2 Any ideas? ...

SQL syntax joining tables

Hello, I have a SQL syntax question. Here are my tables: default_caps: +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | idx | int(11) | NO | PRI | NULL | auto_increment | | tab | int...

Sorting string characters in MySQL

I have a column (varchar(255)) in a mysql table, lets call it "word". How to write a select query that returns me the values in this column sorted by characters in the string? For example, if one of the records had the word "earth" it should return me "aehrt" and so on, for all the rows. Is there any way to do it in a single query? ...

creating multi dimensional varchar/text arrays in plpgsql (postgres)

i have some functionality i'd like to get from the server-side code into the database. i can't figure out how to set the values of a multi-demensional varchar array in plpgsql. here is an example of what i'm trying to do: `CREATE OR REPLACE FUNCTION my_function (my_arg integer) RETURNS text[][] AS $$ DECLARE my_arr varchar[]...

SQL Statement Question - How to retrieve records of a table where the primary key does not exist in another table as a foreign key.

I have a table (TableA) where the primary key exists as a foreign key on another table (TableB). I am trying to write a SQL query (I am using SQL Server 2005) that will find all of the records in TableA where the primary key does not exist in TableB as a foreign key (we'll say joining on TableAID). I suspect it will use an outer join, ...

Need remote or web-based MS Access SQL tool (e.g. similar to PhpMyAdmin, but ColdFusion-based)

I've been looking all over the web for a ColdFusion-based SQL administration tool for Microsoft Access and I can't find one that's simple, free and allows running SQL statements. Any suggestions? Thanks in advance. ...

Problem with sql query

Hello, I'm using MySQL and I'm trying to construct a query to do the following: I have: Table1 [ID,...] Table2 [ID, tID, start_date, end_date,...] What I want from my query is: Select all entires from Table2 Where Table1.ID=Table2.tID **where at least one** end_date<today. The way I have it working right now is that if Table 2...

Help me rewrite this query

I have this query: SELECT diamond_id, carat, clarity, color, cut, CASE clarity WHEN "FL" THEN 0 WHEN "IF" THEN 1 WHEN "VVS1" THEN 2 WHEN "VVS2" THEN 3 WHEN "VS1" THEN 4 WHEN "VS2" THEN 5 WHEN "SI1" THEN 6 WHEN "SI2" THEN 7 WHEN "I1" THEN 8 WHEN "I2" THEN...

Query performance for criteria against date ranges

Here's a table: CREATE TABLE Meetings ( ID int PRIMRY KEY IDENTITY(1,1) StartDate DateTime NOT NULL, EndDate DateTime NULL, Field1 varchar(50), Field2 varchar(50), Field3 varchar(50), Field4 varchar(50) ) There's several thousand rows. The data ranges can be varying sizes (from a couple days up to 50 years). Here's a q...

How Do I aggregate Data By Day and Still Respect Timezone?

We are currently using a summary table that aggregates information for our users on an hourly basis in UTC time. The problem we are having is that this table is becoming too large and slowing our system down immensely. We have done all the tuning techniques recommended for PostgreSQL and we are still experiencing slowness. Our idea ...

DELETE SQL with correlated subquery for table with 42 million rows?

I have a table cats with 42,795,120 rows. Apparently this is a lot of rows. So when I do: /* owner_cats is a many-to-many join table */ DELETE FROM cats WHERE cats.id_cat IN ( SELECT owner_cats.id_cat FROM owner_cats WHERE owner_cats.id_owner = 1) the query times out :( (edit: I need to increase my CommandTimeout value, default is ...

Varchar(max) 2008 to varchar in 2000

I have a query that queries a linked SQL server 2008 database and joins data from a table in that database to a table in SQL server 2000. It was working find until one of the columns in the SQL server 2008 database was changed to varchar(max). I received an error and I fixed it by using CAST(varchar(max) column AS varchar(50)). Now my ...

What am I doing wrong in this MySQL stored procedure?

I'm trying to use the following stored procedure. DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteField`( IN _TABLENAME Text, IN _FIELDNAME text) BEGIN if exists (select * from information_schema.Columns where table_name = _TABLENAME and column_name = _FIELDNAME) then alter table _TABLENAME drop column _...

activerecord equivalent to SQL 'minus'

What's the rails way to subtract a query result from another? A database specific SQL example would be: SELECT Date FROM Store_Information MINUS SELECT Date FROM Internet_Sales ...

Problem with trigger in oracle

I'm new in oracle and i don't know what is wrong with this trigger: CREATE OR REPLACE TRIGGER "propuesta_casas" BEFORE INSERT ON "PROPUESTA_TIENDA_BARRIO" FOR EACH ROW WHEN (new."CASASCAL" IS NULL) BEGIN SELECT PROPUESTA.CASAS INTO :new."CASASCAL" FROM PROPUESTA WHERE PROPUESTA.IDPROPUESTA=new.IDPROPUESTA ; END; / Er...