How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them? Answer The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DI…
Tag: sql
How to change only the year of a date datatype
I have a list of birthdays and the year is the only part that is incorrect. I have a list of ID #s for these individuals. Is there a way to change only the year for all of these people? I was thinking something like making a table of the query results and then using an UPDATE SET query, but
Using group by on two fields and count in SQL
I have a table in my mysql db that has two columns: group and subgroup. See below. group, subGroup grp-A, sub-A grp-A, sub-A grp-A, sub-B grp-B, sub-A grp-B, sub-B grp-B, sub-B I am …
Finding rows with consecutive increase in the values of a column
I have a sql table that stores the daily prices of stocks. New records are inserted every day after the market closes. I want to find the stocks that have consecutive increases in price. The table has lots of columns, but this is the relevant subset: The quoteid column is a primary key. In the table, the clos…
How can I select from list of values in Oracle
I am referring to this stackoverflow answer: How can I select from list of values in SQL Server How could something similar be done in Oracle? I’ve seen the other answers on this page that use UNION …
SQL writing format
I would like to inner join to tables with sql like this: $check_unscored = “select * from [user] INNER JOIN [tenderrc] on [user].[id] = [tenderrc].[userid]”; $…
DB2 Date format
I just want to format current date into yyyymmdd in DB2. I see the date formats available, but how can I use them? http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm….
Use SQL Server recursive common table expression to get full path of all files in a folder(with subfolders)
There is a SQL Server undocumented extended stored procedure called xp_dirtree, which can return all files and folders name (include subfolders) in a table format. To practice my understanding of …
tSQLt fails when attempting to FakeTable a synonym table
I am using tSQLt (through Red Gate’s SQL Test version 1.0.0.455). tSQLt is installed on database A. I am trying to do a tSQLt.FakeTable on a table in database B on the same SQL server instance through a synonym on database A. Code: ALTER PROCEDURE [ErrorType109NonTankHasSizeOrVolume].[test AliasTest] AS…
Most recent datetime column and count for each table
I have a DB that has 1000+ tables. 100 of those tables are prefixed with a three letters (let’s say ‘ABC’) Only half of those prefixed tables have MODIFIEDDATETIME column. I’m trying to do a simple select query to get all the last updated MODIFIEDDATETIME stamp for each Table that actu…