Sunday 31 August 2014

How to get number of days in a month in SQL Server?

Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date.
I found on some blog sites to use this but it will not work for every date
DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, @date, DATEADD(Month, 1, @date))
Try to use date '1/31/2013' or '3/31/2013' or '5/31/2013'
For 1/31 it will give 28 and for '3/31/2013' and '5/31/2013' it will return 30 which is wrong, so we will use the correct one like this
DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, DATEADD(day, 1 - Day(@date), @date),
              DATEADD(Month, 1, DATEADD(Day, 1 - Day(@date), @date)))
Try it with above dates or any other date you want to check and it will always give correct result.
In SQL Server 2012 a new datetime function is introduced (actually 7 new datetime fucntions introduced) named EOMONTH which return last date of month so we can also use this to get the number of days in a month
Let's see first EOMONTH
SELECT EOMONTH(GETDATE()) LastDayofMonth
Result: 2013-05-31 00:00:00.000, so simply we can get days from this result so use like this
DECLARE @date DATETIME
SET @date = '1/31/2013'
SELECT DAY(EOMONTH(@date)) AS DaysInMonth

Delete duplicate records in sql for specific columns

Delete duplicate records from table is the very common question and task we need in our day to day life, so we will see how easily we can delete duplicate records from a table by using subquery and using partition by function supported by Sql Server 2005 and later version.
Say the table name is Address, having following records
AddressID   CustomerID  Address  City    Zip        Country
1           100         A        B       1          USA2           100         A        B       1          USA3           111         A        B       1          USA4           101         B        C       2          USA5           101         B        C       2          USA6           112         B        C       2          USA7           103         C        D       3          USA8           103         C        D       3          USA9           113         C        D       3          USA
So If we will check duplicate records on the basis of CustomerID, Address, City, Zip and Country columns then record 1 and 2 is same, 4 and 5 is same, 7 and 8 is same. How to check this in Sql server, execute fullotin query
select *, 
ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
  Order By addressId) [ranked] from address
Records with “ranked” > 1 is the duplicate record. So how to delete them, simple, we will delete all those records where ranked > 1, in this case record with address Id 2, 5 and 8 should be deleted, right? So let’s write the query to check
With ranked_records AS(
   select *, 
    ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
    Order By addressId) [ranked] 
    from address)
select * from ranked_recordswhere ranked > 1
And here is the result:
AddressID   CustomerID  Address  City    Zip        Country   ranked2           100         A        B       1          USA       2
5           101         B        C       2          USA       2
8           103         C        D       3          USA       2
Means we are getting correct records, so change our last line to delete the record rather than select
select * from ranked_records where ranked > 1 To
delete ranked_records where ranked > 1
Up to now we checked duplicate records on columns Customer ID, Address, City, Zip and Country, but what if we want to check duplicate records on the basis of only three columns (Address, City and Zip)? Don't worry, in this case we need a small change in our query, only remove columns from partition by, let's see this:
select *, 
ROW_NUMBER() OVER(Partition By  address, city, zip 
  Order By addressId) [ranked] from address
and here is the result:
AddressID   CustomerID  Address  City    Zip        Country   Ranked
1           100         A        B       1          USA        1
2           100         A        B       1          USA        2
3           111         A        B       1          USA        3
4           101         B        C       2          USA        1
5           101         B        C       2          USA        2
6           112         B        C       2          USA        3
7           103         C        D       3          USA        1
8           103         C        D       3          USA        2
9           113         C        D       3          USA        3
It shows record number 1, 2 and 3 are duplicate, 4, 5, and 6 are duplicate and 7, 8 and 9 are also duplicate. To delete duplicate records we will use ranked column where ranked > 1 as we done in our previous example.
In all the above examples we delete those records which were added latter and keep the first records but as we are using the address table so last address will be the correct one so we need to delete all duplicate records except the last one. To achieve this we need to make a small change in our Row_number() query, so let’s change it, we will use Order By AddressID DESC:
 ROW_NUMBER() OVER(Partition By customerId, address, city, zip, country 
  Order By addressId DESC) [ranked] 
If there is not Id column then how we can delete duplicate records? ID column is not needed to delete duplicate records but in that case we cannot guarantee which record will be deleted.
But if there is any column like CreationDate or DateUpdate etc. we can order our record to delete.
Another Way;
Lets say we want to delete all those records where address, city and zip is same in address table, then we will use the inner join on same table on the basis of required columns, see this
DELETE A1From Address A1Inner Join Address A2 ON A2.City = A1.City
    AND A2.Address = A1.Address
    AND A2.Zip = A1.Zip
Where A1.AddressID > A2.AddressID
We can also use subquery like this
DELETE A1From Address A1Where Exists (Select 1 From Address A2 
    Where A2.City = A1.City
    AND A2.Address = A1.Address
    AND A2.Zip = A1.Zip
    AND A1.AddressID > A2.AddressID), 
But these both technique is slow, if you will change this query's first line Delete A1 to Select A1.*, you will know why. Suppose you have 3 records with matching address, city and zip, when you will do the join all the 3 records will join From A1 to all the 3 records of A2 so 9 row will come in result, you can think if there is 100,000 matching records then it will create 100,000,000,000 records which is quite huge, while in our previous example where we used partition by, will always return the only those much records which we have in our table.
it's up to you which one will be best for your requirements.
You may ask if there is no column which can help to get the older or latest record then how we can order and delete older or newer records? No way, question itself says there is no way to know which record is older and which one is newer.

Get only date or time from a datetime column in sql server

In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL)
SELECT Getdate() [DateTime]
, Cast(Getdate() as Date)  [DateOnly]
, Cast(Getdate() as Time) [TimeOnly]
-- resultDateTime                    DateOnly        TimeOnly
2013-09-07 15:52:46.793     2013-09-07      15:52:46.7930000
As we see it is quite easy if we are using Sql Server 2008 or latest version but what about 2005 or older version, above query will not work.
So let's write query for older versions:
SELECT Getdate() [DateTime]
, CONVERT(VARCHAR, Getdate(), 101)  [DateOnly]
, CONVERT(VARCHAR, Getdate(), 108) [TimeOnly]
-- resultDateTime                    DateOnly         TimeOnly
2013-09-07 16:06:04.683     09/07/2013      16:06:04
As you can see date formatter we used is 101 and it’s date format, we will see different formatter and their results
SELECT  CONVERT(VARCHAR, Getdate(), 100)  -- Sep  7 2013  4:11PM
SELECT  CONVERT(VARCHAR, Getdate(), 101)  -- 09/07/2013
SELECT  CONVERT(VARCHAR, Getdate(), 102)  -- 2013.09.07
SELECT  CONVERT(VARCHAR, Getdate(), 103)  -- 07/09/2013    
SELECT  CONVERT(VARCHAR, Getdate(), 104)  -- 07.09.2013
SELECT  CONVERT(VARCHAR, Getdate(), 105)  -- 07-09-2013
SELECT  CONVERT(VARCHAR, Getdate(), 106)  -- 07 Sep 2013
SELECT  CONVERT(VARCHAR, Getdate(), 107)  -- Sep 07, 2013
SELECT  CONVERT(VARCHAR, Getdate(), 108)  -- 16:15:41
SELECT  CONVERT(VARCHAR, Getdate(), 109)  -- Sep  7 2013  4:15:48:243PM
SELECT  CONVERT(VARCHAR, Getdate(), 110)  -- 09-07-2013
SELECT  CONVERT(VARCHAR, Getdate(), 111)  -- 2013/09/07
SELECT  CONVERT(VARCHAR, Getdate(), 112)  -- 20130907
SELECT  CONVERT(VARCHAR, Getdate(), 113)  -- 07 Sep 2013 16:16:15:143
SELECT  CONVERT(VARCHAR, Getdate(), 114)  -- 16:16:21:890

SSRS subreport with drill down step by step in sql server 2008

sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report.
To learn basic of reports see my previous article How to create basic report in Sql Server 2008
Here is the report output which we will create in this article.
alt text
Let’s create a new blank project to work (if you are familiar then omit this section)
  • Click Start -> All Programs -> Microsoft Sql Server 2008 -> Sql Server Business Intelligence Development Studio
  • File -> New -> Project..
  • Business Intelligence -> Report Server Report
  • Give a name to the project and click OK
It will create blank project with two folders
  1. Shared Data Source
  2. Reports
Right click on Shared Data Source and select Add New Data Source
Click Edit to Add connection String and set your Sql Server connection
Now we created a new Shared Data Source which we will use in our project for every report.
We will use Northwith database to create an Orders report with Order Details as sub report, so let’s create our main report Orders
  • Right Click on Reports folder
  • Select Add -> New Item...
  • Select Report and give name to it “Orders.rdl”
It will open a blank surface to add our report columns, header Title etc.
Open Toolbox and drag a Table to the report surface
It will open Data Source Property Dialog
Click on Use Shared Data Source reference and select our created data source
Click Next and type your query or procedure name which you want to use for this report, in our case
 Select * from Orders
Execute it to check the records and click Finish
A table will be added to the report area, now time to drag columns one by one, if need more column click on header and add more columns to it.
Adjust header background color and text color and font.
Click on Preview button to see the report, good it is working fine till now, let’s format the date column.
Right click on Order Date column -> Expression and change it to
=Format(Fields!OrderDate.Value, "dd MMM, yyyy")
Now report will show Order date as 04 Jul 2014.
Now time to create Order Details report which we will use as sub report in our main report Orders As earlier we created our Orders report, create a new report with name “Order Detail” and here is the sql statement
Select P.ProductId, P.ProductName, od.UnitPrice, OD.Quantity, OD.Discount From [Order Details] OD
INNER JOIN Products P ON P.ProductId = OD.ProductID
Where OD.[OrderID] = @OrderID
As you can see we used @OrderID as a parameter so this report will take order Id to run it which we will pass on toggle of parent report.
Now we will add our order detail report to main report Orders.
Right click on the item row -> Insert Row -> Inside Group – Below
Select all the column to merge, right click and merge it.
Open Toolbox and drag a Subreport in our merged cells
Right click on it -> Subreport Properties
alt text
From Use this report as a sub report select Order Details
Select Parameters -> Click Add -> Select OrderID in both dropdown
Now if you will see the order report it will show all the order with detail without drill down so let’s add drill down
Again open sub report properties -> Visibility
  1. Select Hide
  2. Select Display can be toggled by this report item
  3. From drop down select Order Id
Now we completed run and see the report.

How to create basic report in Sql Server 2008

In this article we will learn how to create a basic SSRS Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in report.
So why we are waiting, let's start
  • Click on Start
  • Click All Programs
  • Expand Microsoft SQL Server 2008 folder
  • Sql Server Business Intelligence Development Studio
  • Click File menu
  • Click New
  • Click Project…
  • From Project type select Business Intelligence
  • From Templates: select Report Server Report
  • Select folder to save and enter project Name
We have created a blank project without any data source and without any default report because we will create our data source and reports step by step. As you can see there two folders created
  1. Shared Data Source: where we will create our datasource
  2. Reports: where we will create our all the reports
Let’s create our shared data source first which we will use for our all the reports in this project
  • Right click on Shared Data Source Folder
  • Click Add New Data Source
  • It will open Shared Data Source Properties dialog
  • Enter Name for data source say MyDataSource
  • Select Type: since I am going to use Sql Server Northwind database so select “Microsoft Sql Server”
  • Click Edit button to define the connection string
  • Enter server name say localhost
  • Logon to the server: Select your authentication type
  • Select or enter a database name : Northwind
  • Click Test Connection
  • Click Ok
Here is our all the settings:
alt text
We set our shared data source, time to create a procedure or write Sql statement to get data for report, since it is our first report so we will not use complicated procedure with different parameters but very simple Sql statement
 SELECT * FROM [Suppliers]
Now we will create our first report, we will use blank report without using report wizard, so let’s create it.
  • Right click on Reports Folder
  • Click Add
  • Click New Item, it will open Add New Item dialog
  • From Templates: select Report
  • Give name to report, say, Suppliers.rdl
  • Click Add
It will open a blank surface to design our report, here we will add report header, footer and a table to design the report.
To add report header
  • Right click on the surface
  • Click Insert
  • Click Report Header
  • Add Textbox to add the report header text
Similar to report header we can add report footer and its contents, like report generation date.
To add report content (supplier detail)
  • From Toolbox drag a Table on report surface
  • It will open a dialog to select the data source
  • Select Use shared data source reference
  • Select your shared data source from drop down
  • Click Next
  • It will open Query Designer
  • Select Text from Command type drop down
  • Paste our Sql statement and execute it.
  • Click Finish
Now we will add column to this table
  • Open Report Data window
  • If it is not visible then open it from view menu
  • Drag different columns from DataSet in table column
  • If you need more columns, right click on header to add the more columns
Once you added your all the columns, click preview to see the result, it is working but One thing we are missing, alternate row color for data, so let's add it
  • Select entire row
  • Open Property
  • Select BackgroundColor, clieck expression
Paste following into the expression pan
= IIf(RowNumber(Nothing) Mod 2 = 0, "Transparent", "WhiteSmoke")
Here is our created report
alt text