sql

SQL figuring out days remaining based on days interval (using SQL Server 2005)?

I have a table that represents a user's subscription to our system. I have two tables. One represents the subscription for an account and the other represents the type of subscription. The subscription table relates to the subscription type table with a subscriptionTypeID field in the subscription types table. I need to figure out the da...

Is update with nested select atomic operation?

I need to select first (let's say) 10000 rows in database and return them. There may be more clients that do this operation at one time. I came up with this query: update v set v.batch_Id = :batchId from tblRedir v inner join ( select top 10000 id from tblRedir where batch_Id is null ...

counting number of entries in a month

Hi, I have an order table, and it has a datetime column called order_date. I want to count the number of orders for each month in 2009. How would I do that? ...

How do you get SQL to add a variable to a field name in a where clause?

I am working with MS-Access. I have a table I am modifying, the fields of the table are: NumberOfCycles, s1,s2,s3,s4...s8 the rows have different numbers of s's in them, some have all eight and some have less (the number of s's is specified by NumberOfCycles). The way the table is now I have redundant data so I'll have something like...

Calling SQL Scrips from a sql script!

I am just wondering if it is possible to call multiple SQL scripts from a single startup.sql script. i.e. StartUp.sql calls: CreateDatabase.sql CreateStoreProcedureFirst.sql CreateStoreProcedureSecond.sql InsertDummyValues.sql otherscripts.sql... etc At the moment I am loading and running each file one at a time.. there are more scri...

Core Data: communicating extra context to objects fetched with NSFetchedResultsController ?

I'm using NSFetchedResultsController pretty much out-of-box in the way that the documentation describes. Here is an excerpt of my code. NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init]; NSEntityDescription *entity = [NSEntityDescription entityForName:@"SomeEntity" inManagedObjectCont...

Does 1 always equal '1' in SQL?

I am trying to determine that standard SQL behaviour for comparing a number to a character or string version of the same number. Does SELECT 1 = '1' (or the like) always return some sort of "truthy" value (true, 1, 't', etc.)? I have confirmed as much on PostgreSQL and MySQL, but I cannot find a resource for SQL as a whole. Update: The ...

Performing string concatenation from rows of data in a TSQL view (pivot?)

I'd like to create a view in SQL Server that combines several pieces of database metadata. One piece of metadata I want lives in the sys.syscomments table - the relevent columns are as follows: id colid text ---- ------ ------------- 1001 1 A comment. 1002 1 This is a lo 1002 2 ng comment. 1003 1 This is an e...

why is my 126 datetime not coming back with a T in the middle?

I've decided that I'll use 8601 datetimes for all the datetimes that I return from my app. Suddenly, in one particular proc, getdate() isn't returning a datetime with a T in the middle. I should also mention that I'm converting the set containing a datetime to XML using FOR XML PATH. Typically when I convert a table containing datetime t...

SQL Query to aggregate DateDiff greater than given value

I have a logging table used for device “heartbeats”. I have these network devices that check-in/heartbeat with the server every 10 minutes. We are wanting statistics on when they miss their scheduled check-in time. I have a query that can do this on a per-device basis, but I need it to be modified to handle across all devices. The he...

What is the best architecture for tracking field changes on objects?

We have a web application that is built on top of a SQL database. Several different types of objects can have comments added to them, and some of these objects need field-level tracking, similar to how field changes are tracked on most issue-tracking systems (such as status, assignment, priority). We'd like to show who the change is by,...

minimizing runtime of calculating several ranks (based on grades) on large sql tables, how short can it get (ms access)

Hi everyone, I've been stuck with the rather famous problem of ranking students by grade for a couple weeks now, and while I've learned much, I still haven't solved my problem (the ranks are generated, but the process is too slow): I have a large table (320,000 rows) that contains the student codes (serves as an identifier, instead of t...

Does changing the datatype of a column after data is already there destroy the data?

Let's say I have a column of varchar(40) with data already and i change the datatype of that column to integer. does the data change at all in the columns (ie, does the data 'corrupt') or does it not matter and a table of (1,2,3) will still be (1,2,3) regardless of the datatype? ...

SQL - Counting and Summarizing (in ranges) the number of instances of a given transaction

I've got an Occurrences table that contains one row for each time a user took an action. A user take an action multiple times per day. It looks like this: Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/3/9 User1 1/3/9 User2...

Equivalent of LIMIT and OFFSET for SQL Server?

In PostgreSQL there is the Limit and Offset keywords which will allow very easy pagination of result sets. What is the equivalent syntax for Sql Server? ...

Querying for tables & columns named as keywords

Assume SQL Server 2005+. Part A: What is the canonical way to query from the system/internal/meta/whatever tables/views (sorry, not a database ninja) for any user table or column names that use SQL Server keywords (like case)? I don't mind maintaining the list of keywords if that's not query-able, as it only changes with versions of S...

Altering a on attribute data size from a table in SQL Server

So I'm trying to do something I thought would've been straightforward. I have a table in the DB named "Images." It's 'Description' property is of type nvarchar(50). I simply want to make it nvarchar(250). Every time I try, it says it can't save because some tables would have to be redropped. I can't just delete it (i think) because, ther...

MySQL query help (involving joins?)

Although I've figured out several queries that almost do this, I can't quite get it perfectly and I'm getting frustrated. Here is the setup: Table: Issue | id | name | value | +-------------------+ | 1 | a | 10 | | 2 | b | 3 | | 3 | c | 4 | | 4 | d | 9 | Table: Link | source | dest | +---------------+ | ...

Check SQL statements within PHP for bad words such as DROP or DELETE

Hi! I would like to allow some admins to manually enter SQL statements in a textfield (to get statistic data etc.). On the database layer, I protected the data by creating a user which can only select but not update/delete etc. I would like to add a second security by checking the inserted SQL for bad words such as DROP, DELETE or UPDA...

Selecting with 2 JOINs to get a count and a concatenated string

3 tables: items(item_id, ...) tags(item_id, tag_name) downloads(item_id, ...) How do I select a single item together with the number of occurrences of this item in the downloads table and all tag_name values associated with that item? Tried: SELECT ..., COUNT(downloads.item_id) AS download_count, GROUP_CONCAT(tags.tag_nam...