sql

how to check the range and number of records in that range without using cursor ?

Hi All, I have a table "RANGES" with the following columns LowRange as int HighRange as int with the data similar to below select * from Ranges LowRange HighRange 1 100 101 200 201 300 . . . 901 1000 And another table "infoDetails" with the following columns Range as INT amount as money ...

how to get the number on months between two dates in sql server 2005

i have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since i also have the date the employee was engaged, i want the "months_In_Service" column to be a computed column. now if i use datediff(month,[DateEngaged],getdate()) as the formula for the months in service computed colum...

Insert data into sqlserver 2000 database, the order change, why ?

Insert 200 data througn for-loop into sqlserver 2000 database, the order change, why ? When I use mysql, it doesn't have the matter. i mean: when you insert 2, then insert 3, then insert 1, in mysql you will see 2,3,1 like the order you insert. But in sqlsever2000 that may not. ...

SQL Server select where datetimeoffset older than 1 hour

I know this should be easy and I do it no probelms in mySQL and Postgresql but I'm struggling with SQL Server. I want to select rows with a datetimeoffset field that's over an hour old. select * from table where mydatetime < getdate() - 1 hour I've tried dateadd and datediff but can't get it right. ...

Using alias in query and using it

I have a doubt and question regarding alias in sql. If i want to use the alias in same query can i use it. For eg: Consider Table name xyz with column a and b select (a/b) as temp , temp/5 from xyz Is this possible in some way ? ...

Getting All the record of particular month - Building SQL Query

Hi all, I need some help to build SQL Query. I have table having data like: ID Date Name 1 1/1/2009 a 2 1/2/2009 b 3 1/3/2009 c I need to get result something like... 1 1/1/2009 a 2 1/2/2009 b 3 1/3/2009 c 4 1/4/2009 Null 5 1/5/2009 Null 6 1/6/2009 Null 7 1/7/2009 Null 8 1/...

Detect which dataset row violates the DB unique constraint during insertion

I'm inserting rows into a table from other tables in the SQL Server management Studio and some records violates a unique constraint because initial data is 'dirty' and is inconsistent. How can I find which records violate? ...

Generating SQL "IN" clauses: how to safely handle input + empty value lists?

In my Python code I often find myself doing the following (using DB-API): yValues = pickInterestingValuesOfY() sql = "..." # includes a clause stating that "y must be in yValues" c.execute(sql, yValues) In the end, the SQL being executed could be something as simple as SELECT x FROM table1 WHERE y IN (1,2,3); The issue is that th...

File Name in the WHERE clause of Mysql returns nothing.

I have the this SELECT query that returns nothing from the specified table yet the path to the file is stored in that table; SELECT * from tableName WHERE imageFile = "C:\Documents and Settings\Albert Bayita\Desktop\MovieImages\TheLordOfTheRingsTheFellowship.jpg"; Any advice is appreciated. ...

sql optimize search many-many

usertable ---- id, username grouptable ---- id, groupname group_user -------- uid, gid books ---- id, groupname Input parameters: groupname, username output: list of books Is it possible to use 1 sql statement to get list of books when username is inside groupname Question 2: any good book to recommand to master complex sql st...

CONTAINS sql function don't see underlinings

I have some table 'TableName' like Id,Name: 1 | something 2 | _something 3 | something like that 4 | some_thing 5 | ... I whant to get all rows from this table where name containes 'some'. I have 2 ways: SELECT * FROM TableName WHERE Name like '%some%' Result is table : 1 | something 2 | _something 3 | something like that 4 | some...

DROP SQL Tables if created date is more than a week - MS SQL Server

Hi, I want to create a Stored procedure and Job in Ms SQL server that would delete all tables in a schema that are more than a weeks old. i create backups every single day and i want to automate the process by scheduling a job to delete any backups(SQL Tables) that are older than a week. Your help would be greatly appreciated. ...

SQL WITH Statement, Unknown Column in where clause

Hi all, I ve got the following query which is throwing the following error Unkown Column 'RowNum' WITH Employees AS ( SELECT (keyTblSp.RANK * 3) AS [Rank], sp.*, addr.Street, addr.PostOfficeBox, addr.StreetNumber FROM Employee sp INNER JOIN FREETEXTTABLE(Employee, *, 'something', 1000) AS keyTbl...

Access SQL - WMI Namespaces from Remote Machine

Hi, i am playing with Powershell and SQL WMI events and i am wondering if i can do this stuff remotely from the admin notebook: I'd like to query the "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER" namespace: On SQLServer directly: get-wmiobject -list -namespace "root\Microsoft\SqlServer" >> Works! get-wmiobject -list -namespac...

In house simple data synchronization

Hi All, I know the post is long but it also contains suggested solutions to my problem. Therefore, the question itself is not that long... don't be scared. :) Please help me with the following scenario: I will have a SQL server where I will store data sets. I will also have a bunch of PC clients (all with their own local SQL server) ...

Pivot sql query

I've SQL Server 2005 table COMPETITOR (Id, Title, cDate, Price) I want to format the query so that its view in Gridview will be like this: Please help me writing the sql query. ...

SQL Recursive Coalesce

Hi all, I'm trying to create a column that contains all cities of the referenced addresses. DECLARE @AddressList nvarchar(max) SELECT @AddressList = COALESCE(@AddressList + ' ', '') + City FROM [Address] SELECT Employee.*, (SELECT @AddressList) AS AddressCities FROM Employee But I dont know where to put the WHERE clause. ...

SQL isolate greatest values in a column

I have the following output from a view: xfer_id client_plt_id xfer_doc_no 2255 80016616 KANORANJE1 2270 80016616 ORANJECFC1 2285 80016616 CFCFPT1 2285 80016623 CFCFPT1 2270 80016623 ORANCFC1 2255 80016623 KANORANJE1 2255 80016630 KANORANJE1 2270 80016630 ...

Determine color quality of image byte stream in sql

Does anyone know of a way to determine if a image stream is in color or black and white, I have thousands of images to process from sql server stored in a varbinary. Can one read the images header (if type of image is known - bitmaps) from byte offsets directly? IF so how can i do this in sql. ...

Subquery question in MS Access

I'm not quite sure why the following query won't run in Access. It asks me to give the value of s1.sku SELECT s1.SkuApexId, s1.sku, s1.apex, s1.btw, s1.gebruikernr, q2.* FROM tblSkuApex AS s1, (SELECT MAX(s2.begindatum) FROM tblskuapex s2 WHERE s1.sku = s2.sku) q2 ...