Query to fetch the Yesterday's Data

Seems to be easy question. And many of you may be already knew 2-3 ways of doing it. Here is the query I use normally to get the all the data from the previous day(Yesterday).

SELECT Id, OrderId, CustomerId FROM Orders WHERE Date >= DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AND Date < DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

Make it more readable and scalable using variable

DECLARE @YesterDay DATETIME, @Today DATETIME SET @YesterDay = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) SET @Today = DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) SELECT Id, OrderId, CustomerId FROM Orders WHERE  Date >= @YesterDay AND Date < @Today

Logic : Basically above WHERE conditions is like - Greater than Equal to Yesterday and Less than Today.

This DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) gives Yesterday's date with time as 00:00:00.

And this DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)  gives today's date with time as 00:00:00.

You can do a quick check by executing -

SELECT DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AS YDay ,DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) as ToDay

Reason for doing this way instead of using some obvious ones – As you may heard this from many SQL experts, “Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.”

Question is Why?

Well whenever you use the any function on a column , SQL Server no longer uses the Index define on particular column. And use of a CAST or CONVERT functions slows down the query execution considerably.

You may say, I used the DateAdd and DateDiff function in the WHERE clause. Yes, I used them, but NOT on Date column from the table. I used them with the Getdate function which is more of a constant and NOT part of the table's column.

Query Optimization Tip :

Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.

In future I'll have some more posts on Date comparison in WHERE condition. Also some more simple tricks to optimized the everyday's queries.

 

Mangal Pardeshi
SQL MVP

UNPIVOT Multiple Columns

Unlike PIVOT which de-normalizes the data, UNPIVOT is more helpful in getting your data back to a more normalized form.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

For a single column UNPIVOT examples and for more on PIVOT and UNPIVOT you can read from Microsoft Books Online - Using PIVOT and UNPIVOT

In this article I’ll try to show you how to UNPIVOT multiple columns in a single query. There is simple trick to do that, if you are familiar with the UNPIVOT operator you will find it easy to understand.

First lets create some sample data :

-- Create sample table CREATE TABLE Suppliers (Id INT, Product VARCHAR(500), Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500), City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO Suppliers SELECT 1, 'Car', 'Tata Motors', 'Bajaj', 'Mahindra', 'Jamshedpur','Pune','Mumbai' UNION ALL SELECT 2, 'Bike', 'Bajaj', 'Hero Honda', 'Suzuki', 'Pune', 'New Delhi', 'Chandigarh' UNION ALL SELECT 3, 'Cycle', 'Hercules', 'Hero', 'Atlas', 'Mumbai', 'Banglaore', 'Pune' GO -- Test sample data SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3 FROM Suppliers GO

Here is how our sample table looks like :

Id	Product	Supplier1	Supplier2	Supplier3	City1	City2	City3
1	Car	TataMotors	Bajaj	Mahindra	Jamshedpur	Pune	Mumbai
2	Bike	Bajaj	Hero Honda	Suzuki	Pune	New Delhi	Chandigarh
3	Cycle	Hercules	Hero	Atlas	Mumbai	Bangalore	Pune

And as you can see here we have 2 columns which needs to be UNPIVOT, first is Supplier and second is there respective Cities.  If it was single column to be UNPIVOTed it could have been a straight forward task.  Thing is you can write a query with 2 UNPIVOT operators, but question is how to JOIN two UNPIVOT sub queries correctly so we will get the each supplier with its respective city next to it in second column?

As I said earlier there is simple trick to do that.  See the following query and its WHERE condition :

SELECT Id,     Product,     ROW_NUMBER()OVER(Partition By Id Order By Suppliers) as SuppId,     SupplierName,     CityName FROM ( SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3 FROM Suppliers ) Main UNPIVOT ( SupplierName FOR Suppliers IN (Supplier1, Supplier2, Supplier3) ) Sup UNPIVOT ( CityName For Cities IN (City1, City2, City3) ) Ct WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)

And here is the Output of the query :

Id	Product	SuppId	SupplierName	CityName
1	Car	1	Tata Motors	Jamshedpur
1	Car	2	Bajaj	Pune
1	Car	3	Mahindra	Mumbai
2	Bike	1	Bajaj	Pune
2	Bike	2	Hero Honda	New Delhi
2	Bike	3	Suzuki	Chandigarh
3	Cycle	1	Hercules	Mumbai
3	Cycle	2	Hero	Bangalore
3	Cycle	3	Atlas	Pune

So the trick was this where condition -

WHERE RIGHT(Suppliers,1) =  RIGHT(Cities,1)

There are other ways also to UNPIVOT the multiple columns, may be some other time I’ll post them too. 

Source of inspiration for this post is following thread on MSDN forums - http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/54626179-ee9f-4db7-a31e-c02104bdd6beAt 

At the end of it, OP also come up with his own solution he got from SQLTeam’s forums.

Mangal Pardeshi