Filter by Comma Separated Values in IN Clause ?

The Customer table has ‘PrimaryCode’ and ‘OtherCode’ columns store Code from Codes table:Image

However some of the codes store in Customer table do not exist in Codes table.

I need to let Codes table left join Customer table, this is the first version SQL:

select * from Codes a
left outer join Customer b
on a.CodeID in (b.PrimaryCode)

That did not give me the correct outcome as query above miss out the values in OtherCodes column, so I try this:

select * from Codes a
 left outer join Customer b
 on a.CodeID in (CAST(b.PrimaryCode as varchar) + ',' + b.OtherCodes)

I try to concat the PrimaryCode + OtherCodes to have a clause like this: IN(‘1,2,3,4,5’), but that does not seems right, and I get the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘23,24,26,27’ to data type int.

Ok, now I know that is not right doing that, there are few solutions provided in internet (search from Google of course!). The final solution would be using LIKE keyword, then I have this which work perfect:

select * from Codes a
left outer join Customer b
on ',' + Cast(b.PrimaryCode as varchar) + ',' + b.OtherCodes + ',' LIKE '%,' + Cast(a.CodeID as varchar) + ',%'

The result I want:

Image

 

Hints to search in Google: Comma separated values in IN Clause

Keywords: LIKE

Reference: http://stackoverflow.com/questions/4613273/sql-use-comma-separated-values-with-in-clause

Create your own system stored procedure

Try to add our own global s.proc/system s.proc to keyboard shortcut, and need it available to all instances under one server.

Firstly we need to have a s.proc, says it select * from certain table, therefore we have below:

USE [master]
GO

Create Procedure sp_SelectAll
(
     @TableName varchar(50)    -- Receive highlighted parameter
)
As
Declare @sql varchar(500)
Select @sql = 'select * from ' + DB_NAME() + '.' + SCHEMA_NAME() + '.' + @TableName
execute (@sql)

Go

Line 1,2: sp_SelectAll need to be created under [master] since we want it available to all instances.

Line 6: An input parameter is required as later it will receive the table highlighted in query windows.

Line 10: Same table name may appear in different databases, therefore above s.proc need to recognize where the table come from. Use DB_Name() and SCHEMA_NAME() to make a distinct table call, so we will get something like

select * from Northwind.dbo.Customers

that clear enough to differentiate with

select * from pubs.dbo.Customers

 

*Note 1: In sql server 2008, or even sql server 2005, you may not able to run the sp_SelectAll in all databases, you need to run undocumented query below to fix this:


Use [master]
Go
sp_MS_marksystemobject sp_SelectAll

*Note 2: sp_SelectAll start with “sp_” prefix because we need to “cheat” sql server to treat it as system s.proc, since only system s.proc can be call from all instances, also undocumented s.proc sp_MS_marksystemobject only work for s.proc start with prefix “sp_“.

After s.proc created, allocate it with a keyboard shortcut, I’ve  allocated CTRL + 3 shortcut to this new s.proc.

sp_SelectAll with keyboard shortcut

— [ End break ] —

Reference:

  1. sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
  2. Add keyboard query shortcuts to your SQL Query Windows

Keywords: Add customize system stored procedure to keyboard shortcut.

Determine if a time is within Time Range

We know 1pm is within time range 11am-2pm, also 11pm is within 10pm-2am.

And we need to know how to do that in code, sql, etc…

===== [ Preparation ] =====

Assume the followings are true for later comparison:

  1. No date involved in time comparison.
  2. 24-hours time format is in use.

Basically there are three kinds of time range we need to cater when compare with time:

  1. end time > start time. Eg 0000 – 2359, 0800 – 1800 (working hrs…)
  2. end time = start time. Eg 0100-0100, 1530-1530. Means only need that moment.
  3. end time < start time, cross midnight time range. Eg 1200 – 0300, 2000 – 0600 (sleeping hrs…)

===== [ How it works ? ] =====

Scenario 1 and 2 quite straight forward. To compare it, see example:

Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN StartTime AND EndTime

-- Result return if StartTime = 080000 and EndTime = 180000

However above example will give wrong outcome for scenario 3 because EndTime always less than StartTime, therefore no result will return.

To compare time in scenario 3, lets take time range 20:00 – 06:00 as example.

Firstly, we need to divide the time range at time 00:00, so will get two time range which is before 00:00 (2000 – 0000) and after 00:00 (0000 – 0600).

Secondly, we have a time T1 to be compare, and we know the time T1 will fall in three ranges, they are 2000-0000 (just name it TR1), 0000 – 0600 (TR2) and 0600 – 2000 (TR3).

So we have:

TR1 = 2000 – 0000

TR2 = 0000 – 0600

TR3 = 0600 – 2000

* Notice that original time range is 2000 – 0600, but we split it to 2000 – 0000 and 0000 – 0600. 0600 – 2000 is the time range that never mention and not cover by original time range 2000 – 0600.

* TR = Time Range

For TR1, we can replace the ‘0000’ with ‘2400’ since we know TR1 is taking time range before midnight. End up we have

TR1 = 2000 – 2400

TR2 = 0000 – 0600

TR3 = 0600 – 2000

Things become straight forward now. Just need to check whether time T1 fall within TR1, TR2 or TR3. Notice that we can make a simple time comparison for TR1, TR2 and TR3, like what we did in scenario 1 and 2. Refer to sample code below:


Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN

 -- Start Time
 (CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then '000000' Else StartTime End) ELSE StartTime END)
 AND
 -- End Time
(CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then EndTime Else '240000' End) ELSE EndTime END)

As we can see in last sample code, it only take TR1 or TR2 for comparison. A simple checking still needed to determine which time range is use for comparison.

We can summarize the last sample code into following:


if (endTime < startTime)  // This is cross midnight time range
   if (compareTime <= endTime)
      // compareTime is between '000000' and endTime?
   else
      // compareTime is between startTime and '240000'?
else
   // compareTime is between startTime and endTime?

~~~ * ~~~ * ~~~

Keyword: Check time range.  How to determine time, time period.

SQL Select random row from table

Reference: SQL to Select a random row from a database table

Quick reference to article above:


-- Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

-- Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

-- Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()

-- Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

-- Thanks Tim

-- Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Keyword:  random record

UDF, User Defined Functions in MSSQL

There are three types of UDF (refer to reference), however I will talk about Scalar UDF  here (Which means that function only return single value, you may refer to reference links below for other types).

UDF is quite useful for sometimes, ok, lets go to the point:

Environment:

  1. MS SQL Server 2000 or above.
  2. Northwind sample database from Microsoft.

Objective:

  • List all products in ONE row according to supplier id.

You can try it without UDF, do post your method if you know how to do that.

Here is the part of Product data:

Northwind - ProductsNorthwind – Products

First: We need to create a function to concat all product names into one line for each supplier id.


USE NORTHWIND
go

CREATE FUNCTION dbo.ConcatProductName (@sSupplierID as varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN

   DECLARE @sProduct as varchar(500)

   SET @sProduct = ''

   SELECT @sProduct = @sProduct + ProductName + ', ' FROM PRODUCTS
   WHERE SupplierID = @sSupplierID
   --	group by ProductName

   SET @sProduct = Left(@sProduct, Len(RTrim(@sProduct)) - 1)

   RETURN @sProduct
END

Second: Run and create the function above.

Third: Run the query below the check the result:


Select dbo.ConcatProductName(SupplierID) as ProductName

, SupplierID

From Products

Group by SupplierID

-- drop function ConcatProductName

Query with UDFQuery with UDF

See? It work great.

Refer to references below to know pros & cons, and more details about UDF.

References:

Northwind & pubs sample database from Microsoft.

User Defined Functions in Microsoft SQL Server 2000

User Defined Functions

Keywords: Customize function in MSSQL

Reset auto-increment ID to some number

For table with auto-increment primary key, sometime we need to reset the running number to smaller number, code below shows the way (MSSQL only)


DBCC CHECKIDENT('<TABLE_NAME>', RESEED, <Base number>) ;

Example


-- EMPLOYEE table auto-increment number will start from '1'

-- (Make sure your ID=1 is not exist to avoid PK crash)

DBCC CHECKIDENT('EMPLOYEE', RESEED, 0) ;

-- CUSTOMER table auto-increment number will start from '500'

-- (Make sure your ID=500 is not exist to avoid PK crash)

 DBCC CHECKIDENT('CUSTOMER', RESEED, 499) ;