Find all the dates in a date range ? TIP #100

 

It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)
DECLARE @SeedDate AS DATE
SET @SeedDate = '2005-07-01'
WHILE @SeedDate <= @EndDate
BEGIN
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
END
SELECT * FROM @tblDateRange

Now second and interesting approach

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)

;WITH DatesCTE
AS (
SELECT @StartDate AS SalesDate
UNION ALL
SELECT DATEADD(d,1, SalesDate) As salesDate
FROM DatesCTE
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)
SELECT * FROM DatesCTE

SELECT * FROM @tblDateRange

These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.

Thanks

RJ

Enjoy !!!

Data Compression–A unique feature PART–II TIP #92

 

In last post TIP #91  we talked about  What is Data compression ? What are the  features ?  Now in this tip we will take implement the data compression with basic steps.

So lets follow the steps

1) Right click on the table on which you need to implement compression. You will get following menu just select manage compression as shown in below figure

2) Once you click on the above option you will get following screen.

3) Now you can choose compression type from dropdown of the row either Page or Row . You can calculate estimated space saving by selecting the compression type. as shown in below figure

a) Page compression (In the last column you will find requested compressed space)

b) Row Compression (in the last column you will find request compression space)

Now once you selected appropriate compression type you can click on Next button of the wizard you will get below screen

Now you can generate the script for this compression type. We can directly run the script on database itself if we are sure which with compression type.

Once we run the above command on database we are good to go and our table is compressed.

I hope this steps will help you to compress our database tables.

Enjoy compression !!

Rj !!

How to insert value in Identity column ? TIP #80

 

Happy new year 2015 .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.

Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

First_value & Last_Value according to group set is that easy ? tip #44

 

Problem:- 

Most of the time we require data in which we require first value and last value from different group of rows. Now how easy we can get result this is one of the challenge for us.

Solution:-

Lets understand this by an example. Suppose you have a sales table in which you maintain daily sales. Now your want a result sent in which you know what is first sale of the day and what is last sale of the day.

SQL SERVER 2012 provides you facility to achieve this task easily with First_Value & Last_Value function.

The syntax of first_value & Last value is exactly same as Row_Number, Dense_Rank & Rank_function.

See below example in which I have used Adventureworks SalesOrderHeader table.

Now if you see below snap I took a random date 2005-07-12 and fetched record and highlighted is first row & Last Row.

So on date 2005-07-2012 Sales order have 3953.9884 as a frist value and  772.5036 as a second value.

 

Now above specific result  we can achieve by first_Value & Last_Value function of SQL SERVER 2012 as shown below

so, In this way you can achieve the first_Value & last_Value from a group of rows

I hope this may help you somewhere.

Enjoy !!!

RJ

Asp.net Basic Knowledge base

Regardless of the laguage that you use to develope your asp.net pages, you need to understand that asp.net pages compile before they execute.
It means that they execute very quickly. The first time you request an asp.net page the page is compiled into a .net class and the resulting class file is saved at a special directory on your server in "Temporary Asp.net" files. for each & every asp.net pages a corrsoponding class file appears int the temporary asp.net file directory.
When a asp.net page is compiled it is not directlty compiled into machine code Instead it is compiled into intermediate level language called Microsoft Intermediate language (MSIL)
All the .net compatible laguages are compilted into this intermediate language.
Thats all
for today
enjoy.