Conditional Where Clause

I didn’t write any post on my blog for quite long time. Today, I spend some times to write a post. Actually, this topic is saved in my draft for quite long also. :) And I received one topic from SQL Server Central which is same as my topic. We know last time that we cannot put conditional filter in where clause of T-SQL statements. But that’s not true that I find out one thing which can use conditional filter in where clause.

I'm having a request from my customer to generate dates which are based on their shifts, 135 and 246. To get all dates in a month is not difficult which I already posted in Get All Dates section. So I'm spending some times to get that conditional where clause as follow:

DECLARE @Days AS varchar(3)
SET @Days='135'

SELECT Days FROM GetAllDates
WHERE datename(dw, Days) IN (CASE WHEN SUBSTRING(@Days, 1, 1)='1' THEN 'Monday'
WHEN SUBSTRING(@Days, 1, 1)='2' THEN 'Tuesday' END,
CASE WHEN SUBSTRING(@Days, 2, 1) = '3' THEN 'Wednesday'
WHEN SUBSTRING(@Days, 2, 1) = '4' THEN 'Thursday' END,
CASE WHEN SUBSTRING(@Days, 3, 1) = '5' THEN 'Friday'
WHEN SUBSTRING(@Days, 3, 1) = '6' THEN 'Saturday' END)

The following is some another example of conditional where clause from SQL Server Central.

SELECT *
FROM Production.Product
WHERE
ProductNumber LIKE CASE
WHEN @ProductNumber IS NULL THEN '%'
ELSE @ProductNumber END
AND Color LIKE CASE
WHEN @Color IS NULL THEN '%'
ELSE @Color END

SELECT *
FROM Production.Product
WHERE ReorderPoint =
CASE
WHEN @ReorderPoint IS NULL THEN ReorderPoint
ELSE @ReorderPoint
END

Constant Column and Stock or Service Discount Setup

I didn’t update my blog for quite long time. Today, let me share some knowledge on T-SQL Programming. Last time, I posted constant table. This time, I wanna post constant fields and joining 2 tables without having any relationship.  Interesting, right? Sometimes, our customer request to generate setup data for their promotion and discount. And that time, both their services or stocks table and promotion or discount table have no relationship between them. So how we generate those data as customer requested? Let us assume stock table, STOCK, has the following format:

StockCode | Description | Price
A001 | Book | 10
A002 | Pen | 5
A003 | Pencil | 3

And the discount table, DISCOUNT, has the following format:

DiscountCode | DiscountPercentage
D010 | 10
D020 | 20
D025 | 25

Customer wants the following data:

StockCode | Price | DiscountCode | Percentage
A001 | 10 | D010 | 10
A001 | 10 | D020 | 20
A001 | 10 | D025 | 25
A002 | 5 | D010 | 10
A002 | 5 | D020 | 20
A002 | 5 | D025 | 25
A003 | 3 | D010 | 10
A004 | 3 | D020 | 20
A005 | 3 | D025 | 25

Do you notice that the data is duplicating based on DiscountCode and StockCode? Where we seen this kind of duplication? We always facing duplicate error when we join two tables wrongly, right? So now you’ll get some idea how to generate that customer request. If you still have no idea how to generate, see the following how to get those data.

At first I add constant column in each tables in query as follow:

SELECT JoinSr = 1, StockCode, Price FROM STOCK

SELECT JoinSr = 1, DiscountCode, Percentage = DiscountPercentage FROM DISCOUNT

Do you notice the column name JoinSr? That’s the constant field which will show you always one in the query result. After doing this, I’ll use our normal join statement to get the require result as follow:

SELECT S.StockCode, S.Price, D.DiscountCode, D.Percentage FROM
(SELECT JoinSr = 1, StockCode, Price FROM STOCK) S
JOIN
(SELECT JoinSr = 1, DiscountCode, Percentage = DiscountPercentage FROM DISCOUNT) D
ON S.JoinSr = D.JoinSr

Now do you notice that the error we ever facing when we join two tables is helping us to get those stock discount mapping setup.

Formatting Characters In SQL

If you want to format result values in SQL, you can use the following methods:

Let assume we have integer value variable.

DECLARE @DataValue AS int
SET @DataValue = 2

and wanna format as '00002'

There're 2 ways to format:

SELECT REPLICATE('0', 5 - LEN(@DataValue)) + CAST(@DataValue AS VARCHAR(MAX))

OR

SELECT RIGHT('00000' + CAST(@DataValue AS VARCHAR(max)), 5)

Row Data to Column Data

This is also one of my question in Experts-Exchange forum. I wanna display row data to in a columnar format. I’ve one tables which include three columns such as Sr, StartDate and EndDate. I wanna display count of Sr group by month depending on the StartDate and the resulted format is as follow:

Sr|Jan|Feb|Mar|..to..|Dec

The solution for this problem is as follow:

SELECT Sr, Jan = SUM(CASE WHEN MONTH(StartDate)=1 THEN 1 ELSE 0 END),
Feb = SUM(CASE WHEN MONTH(StartDate)=2 THEN 1 ELSE 0 END),
..
FROM Table
GROUP BY Sr

Really thanks for expert StephenCairns from Experts-Exchange for his solution which also opened my eyes on creating SQL queries later.

I also contributed some in experts exchange but my contributions are not very much so I won't post my contributions in my blog. :D

Display Different Values In Respective Columns

I haven’t updated my blog for quite long time. But today I’ve times to post some information related to the SQL query. When I prepare reports for one of the hospital in Myanmar, I’m facing some difficulties to display data and raised question in Experts-Exchange. This post is my re-collection of solutions from Experts-Exchange. And also thanks for the expert, angellll, who answer this solution for me. The problem is as follow:

I’ve one query which join 3 tables and produce different values in 2 columns.
Table A Contain (ID,SubID)
Data for Table A as example:
ID, SubID
1, 1
2, 7

Table B Contain (ID, Values)
Data for Table B as example:
ID, Values
1, 1000
2, 2500

Table C Contain (ID, SubID, Values)
Data for Table C as example:
ID, SubID, Values
1, 1, 100
1, 2, 150
1, 3, 20
2, 5, 0
2, 7, 150

I want to display as follow
ID, SubID, ID Values, SubID Values
1, 1, 1000, 100
1, 2, 0, 150
1, 3, 0, 20
2, 5, 0, 0
2, 7, 2500, 150

The solution for this problem is as follow:
SELECT a.id, a.subid, COALESCE(b.values,0) as [id values]
FROM tableA a
JOIN tableB b
ON a.ID = b.ID
RIGHT JOIN tableC c
ON c.id = a.id
AND c.subid = a.subid

Joining to the Next Sequential Row

Because of the format problem, please see this article here. Sorry for this inconvenience. ;(

Get All Dates

When we develop application, we need all the days from the month sometimes. We are thinking of the way to create StoreProcedure to generate days using temp table and cursor. But using cursor will slow down our application process and we want faster way to get all days. So we think of another way to create one table which store all the days from 1-Jan of current year to 31-Dec. That’s also not easy to maintain this table and need to insert all days every year. Actually, I’m using this method in one of my application :D. Don’t try to use this method. This is very risky to get all days and insert all days. Here is very useful query statement to generate days from the month or the given months. I got this statement from one SQL forum but I forgot from which forum and who posted it. Anyway, this statement is really helpful to me and I’m sure you’ll also. Here’s the statement:

DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080301', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CalendarDay FROM Days

If you set 2 to @NumberOfMonths variable, you’ll get all the days from 2 months such as Mar and Apr.

Constant Table

In database development, sometimes we really need to display some constant data and don’t wanna let user change that constant data. But those data are not so many and don’t wanna create table. For this situation, we’ve one alternative way in SQL to create constant table. The trick is not so advanced but it’s really easy and we already know those statements. We just create one simple view using the following statements:

SELECT CustomerTypeID = 1, CustomerType = ‘Ordinary’
UNION ALL
SELECT CustomerTypeID = 2, CustomerType = ‘Executive’
UINON ALL
SELECT CustomerTypeID = 3, CustomerType = ‘Ultimate’

The above statement will give you one constant table for customer type. If you’ve more type, just edit the view and append another types. You can apply this in All SQL Version. :)

Add Serial Number to Our Query Result

Sometimes, we need to add serial number in our query result to show on the report. I’m not sure SQL 2000 has this feature or not but I never use that feature in SQL 2000. :D In SQL 2005, we’ve one function call ROW_NUMBER(). So we can display serial number in our query result. The example for that function is as follow:

SELECT ROW_NUMBER() OVER(ORDER BY customername) FROM customer WHERE customername LIKE ‘John%’

You can see detail description of this function at Microsoft MSDN Online
.

What Are The Backgroud Files of Your Database?

If you are boring on reading long text in my previous post, here’s some little text for you. Do you know how many data file type created from SQL 2005 after you created your database? You didn’t notice or forgot, right? If you didn’t create partition(s), there’re two files created which are .MDF file and .LDF file. .MDF for your primary data file and .LDF for your log file. If you created partition(s), you see .NDF file as secondary data file(s). In one database, there’s only one primary data file.

Databases and Performance We Should Consider

I’m sure most of us are happy with wizards which Microsoft provide in SQL and forget what is happening behind the wizards. This is one example for our ignorance in SQL 2005. Normally we right-click on the database node in Enterprise Manager, click New Database, type the database name and click OK to create new database in 2005 Version. But we forget about options from the wizard and ignore what is happening behind the wizard dialog-box. Because we satisfy our needs of creating database and the wizard create database for us very easily within 3 clicks. If you are a little bit take care of your server hard-disk space and have knowledge to put your database file on the other disk drive, you’ll change the default path of the database in the wizard. That’s the most we do. The rest we ignore and let SQL Server handle itself.

When our database file is bigger and bigger, and the performance is drop, I’m sure we’ll think of memory and hard-disk space first. Some more, we’ll blame on the windows configuration. Actually, we forgot what we ignore at the stage of database creation. There’re some points we should consider for our database performance which we ignore or forgot. Here are some points we should consider for our database performance.

The first thing is that if we put our database files on the same partition which resides windows system files, the database performance will slow. You’ll wonder why SQL database wizard set default location to that partition. Because the server application cannot know you disk drives partitions and allocation. So always show current installed folder as default location. And you’ll wonder why the performance is slow if the database is exists on the same partition with windows. Because Windows is using its system files from that partition, put page files on that partition and temporary files are also put on that partition. So that partition is the busiest partition on your system and you add more load on that partition to read and write your database. So your database will definitely slow in transaction. And the worst thing is that if you reinstall Windows and you forgot to backup your database, that’s it. Therefore, we should place our database on the other partition or if possible on the other hard-drive.

Another thing is to set the file size AUTOGROWTH property. You should consider your data read and write amount. If you set your file AUTOGROWTH property as by 1 MB default and your data write amount is more than 2 MB, your transaction will need to wait for SQL Server to auto growth to finish your one insert transaction. So set AUTOGROWTH property to cover your data read and write amout.

If possible partition your database files based on the period and put most of the historical data on the secondary file group. So that your reports will not disturb your data transaction on the primary partition.

And if you can effort RAID system and your server support 2 or 3 types of RAID system, use the RAID feature to boost your SQL performance. Use RAID 1 for your database log file(s) which is important for database all the time. And use RAID 0 or 5 for your database file. Especially RAID 0 is preferable if you don’t mind fault tolerance. So RAID 10 or RAID 01 is good for your SQL Server performance.

The above are some points for database performance consideration before we start creating our database(s).

You also can find some performance tunning topics on SQL Server Central

Why This Blog

I’ve created one blog called “My First Blog” which I posted some of jokes and information related to my personal life. But I also wanna create blog which support technical person and also note book for myself as in technical difficulties or some tricks. Actually, I’m not very good in technology that’s why finding tips and tricks from other developers from any web-site for my reference. While finding those tips and tricks from internet, I found out some useful tips for my development and really help my development process. I’m actually developer from Microsoft platform and using Microsoft technologies in my on-going development lifetime. Especially, I’m using Microsoft SQL for almost whole life-time in my career and still using SQL. I’ve some believe that if I’m strong enough in SQL, I’ll finished 60 to 70 percent of my development and I can deliver earlier than the dead-line. So in this blog, I’ll post most of the SQL knowledge which I’ve learned and find out from internet. As you know, nowadays we can find information very easily on the internet but there’re really little places you can get all the information you want together. I hope my blog can help some of your needs and help to boost your knowledge of SQL.