Rounding Numbers to Nearest Integer

 In one of my project, we are preparing tech refresh for the system and we need to set the ID numbers for all the tables with higher values in the setup table. The values of ID numbers are vary and need to set full integer for example the last ID number is 35,577,991, we would like to set as 30,000,000 or 40,000,000. It is easy to achieve in T-sql using the Round function as follow:

SELECT ROUND(35577991, (LEN(35577991)-1)*-1)

SQL Server will give you 40,000,000 as the value is greater than 35,000,000. If the value is less than 35,000,000, the result will be 30,000,000.

Entity Framework and SQL Storeprocedure

I'm using Entity Framework since 2012, but no idea how to execute SQL Storeprocedures from DBContext. While developing one portal, I have a situation to call SQL Storeprocedure for performance purpose. Google and google around, and I found out a solution to execute storeprocedure in DBContext. Here is the example of DBContext example to run Storeprocedure:

var ResultSet = DBContext.Database.SqlQuery("exec [Storeprocedure name] @[parameter]", [parameter value]).ToList<[Model]>();

Here is the implementation of the above example:
var Customers = ctx.Database.SqlQuery("exec getAllCustomers").ToList();

First Day and Last Day of Current Week

While developing one of the portal, I come in difficulty to generate first day and last day of the current week to filter data. After googling, I found out the followings to generate first day of current week and last day of current week:

To get first day of current week:
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

To get last day of current week:
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

That really save me a lot to filter data.

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