Tuesday, February 3, 2009

Calculating Running Totals in SQL

 

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query.  So instead of using the technique I used in this post you should use technique mentioned in my new post -  How to Calculating Running Totals

In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -

  • Running Total for all records.
  • Running Total on each Group of Records.
  • Running Total on each Date.

Here is our sample table :

ShipId OrderId ShipDate Quantity
1 1 2009-02-01 12:06:16.820 10
2 1 2009-02-02 16:16:16.820 15
3 2 2009-02-01 13:26:16.820 20
4 2 2009-02-03 17:40:16.820 12
5 2 2009-02-04 13:05:16.820 15
6 3 2009-02-03 14:25:16.820 25
7 3 2009-02-04 15:50:16.820 50
8 3 2009-02-05 19:40:16.820 30
9 4 2009-02-06 15:30:16.820 20
10 4 2009-02-07 16:20:16.820 10
11 5 2009-02-05 17:05:16.820 35

Create Sample Data:

-- Create Table
CREATE TABLE Sales
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT )
GO
-- Load Sample Data

INSERT INTO Sales SELECT
1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT
2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT
3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT
4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT
5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT
6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT
7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT
8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT
9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT
10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT
11,5, 'Feb 05 2009 05:05:16:820PM',35



Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity


Here is the result returned by above query :
ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 45
4 2 2009-02-03 17:40:16.820 12 57
5 2 2009-02-04 13:05:16.820 15 72
6 3 2009-02-03 14:25:16.820 25 97
7 3 2009-02-04 15:50:16.820 50 147
8 3 2009-02-05 19:40:16.820 30 177
9 4 2009-02-06 15:30:16.820 20 197
10 4 2009-02-07 16:20:16.820 10 207
11 5 2009-02-05 17:05:16.820 35 242

Case II: Running Total on each Group of Records.

Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
AND A.OrderId = B.OrderId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY OrderId

And the Output :
ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 20
4 2 2009-02-03 17:40:16.820 12 32
5 2 2009-02-04 13:05:16.820 15 47
6 3 2009-02-03 14:25:16.820 25 25
7 3 2009-02-04 15:50:16.820 50 75
8 3 2009-02-05 19:40:16.820 30 105
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 30
11 5 2009-02-05 17:05:16.820 35 35

Case III: Running Total on each Date.

Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.shipDate >= B.Shipdate
AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0)
= DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0)
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY A.ShipDate

And the output :

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
3 2 2009-02-01 13:26:16.820 20 30
2 1 2009-02-02 16:16:16.820 15 15
6 3 2009-02-03 14:25:16.820 25 25
4 2 2009-02-03 17:40:16.820 12 37
5 2 2009-02-04 13:05:16.820 15 15
7 3 2009-02-04 15:50:16.820 50 65
11 5 2009-02-05 17:05:16.820 35 35
8 3 2009-02-05 19:40:16.820 30 65
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 10

Hopefully these examples will help you understand different methods that can be used to calculate running totals.

- Mangal Pardeshi

Tuesday, January 27, 2009

RPAD and LPAD Functions in Sql Server

Edit: If you are using SQL Server 2012 or 2014 you may like to see my new post on this topic: LPAD and RPAD Functions in SQL Server

In Sql Server we don’t have RPAD and LPAD functions as they are in Oracle. But it is not that difficult to implement the same thing in Sql Server. With the help of RIGHT and LEFT functions we can achieve that easily. Let’s see how…

First create this table:
CREATE TABLE Temp(Id INT)

GO

Now insert the table Temp with some sample data:
INSERT INTO Temp SELECT
1 UNION SELECT
2 UNION SELECT
12 UNION SELECT
123 UNION SELECT
1234 UNION SELECT
12345
GO

And here is our RPAD and LPAD query

SELECT ID
,RIGHT( '00000' + CONVERT(VARCHAR(5), Id), 5) as LPAD_Example
,LEFT( CONVERT (VARCHAR(5), Id)+'00000' , 5) as RAPD_Example
FROM Temp


--END--

- Mangal Pardeshi

Friday, January 9, 2009

Linked Server Error : Msg 7399, Level 16, State 1

In this post I'll show you a simple solution to a very common error you get while running a query against a linked Server.
You create a Linked Server with Flat Files, Excel or DBF in SQL Server using Microsoft.Jet.OLEDB.4.0 and when you try run a query against it you get the error
--Error--
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server " " returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".


Well whatever study I have done on this particular error, I have observed that you will get this error only when try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.

Solution to this problem is very simple. Simply follow steps -
1.Open the Management Studio and navigate to Server Objects and then to Linked Server.
2.Right click on your Linked Server Name, and click on Properties.
3.Go to Security Page. Now for solving above problem you have 2 option, you can try any of the below 2 option.


A] Select the option “Be made without using a security context” by clicking on radio button.

OR

B] Select the last option “Be made using this security context ”.
As soon as click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.


- Mangal Pardeshi.

Thursday, January 8, 2009

How to delete the Duplicate Rows from SQL Server table

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID NAMES CODE
1 Mangal 101
1 Mangal 101
1 Mangal 101
2 Ricky 102
3 Brian 103
4 shiv 104
4 Shiv 104
5 Kunal 105
5 Kunal 105
6 Kate 106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID NAMES CODE
1 Mangal 101
2 Ricky 102
3 Brian 103
4 Shiv 104
5 Kunal 105
6 Kate 106

Create And Load Sample Data -

-- Create Sample table
CREATE TABLE Temp
(
Id INT,
Names VARCHAR(100),
Code INT
)
GO
-- Load sample data
INSERT INTO Temp SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
2, 'Ricky', 134  UNION ALL SELECT
3, 'Brian', 435 UNION ALL SELECT
4, 'Shiv', 223   UNION ALL SELECT
4, 'Shiv', 223   UNION ALL SELECT
5, 'Kunal', 654  UNION ALL SELECT
5, 'Kunal', 654  UNION ALL SELECT
6, 'Kate', 611
GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.


To Find Duplicate rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
SELECT DISTINCT ID, Names, Code
FROM Cte
WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
DELETE FROM cte
WHERE Seq > 1



- Mangal Pardeshi

Saturday, December 27, 2008

Removing Time from DateTime column in Sql Server 2005

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

  • Format: mm/dd/yy
    SELECT CONVERT( Varchar(12), GetDate(),1)
    Output: 12/27/08

  • Format: mm/dd/yyyy
    SELECT CONVERT(Varchar(12),GetDate(),101)
    Output: 12/27/2008

  • Format: yy.mm.dd
    SELECT CONVERT(Varchar(12),GetDate(),2)
    Output: 08.12.27

  • Format: yyyy.mm.dd
    SELECT CONVERT(Varchar(12),GetDate(),102)
    Output: 2008.12.27

  • Format: dd/mm/yy
    SELECT CONVERT(Varchar(12),GetDate(), 3)
    Output: 27/12/08

  • Format: dd/mm/yyyy
    SELECT CONVERT(Varchar(12),GetDate(), 103)
    Output: 27/12/2008

  • Format: dd.mm.yy
    SELECT CONVERT(Varchar(12),GetDate(), 4)
    Output: 27.12.08

  • Format: dd.mm.yyyy
    SELECT CONVERT(Varchar(12),GetDate(),104)
    Output: 27.12.2008

  • Format: dd-mm-yy
    SELECT CONVERT(Varchar(12),GetDate(), 5)
    Output: 27-12-08

  • Format: dd-mm-yyyy
    SELECT CONVERT(Varchar(12),GetDate(),105)
    Output: 27-12-2008

  • Format: dd mon yy
    SELECT CONVERT(Varchar(12),GetDate(), 6)
    Output: 27 Dec 08

  • Format: dd mon yyyy
    SELECT CONVERT(Varchar(12),GetDate(),106)
    Output: 27 Dec 2008

  • Format: mon dd, yy
    SELECT CONVERT(Varchar(12),GetDate(),7)
    Output: Dec 27, 08

  • Format: mon dd, yyyy
    SELECT CONVERT(Varchar(12),GetDate(),107)
    Output: Dec 27, 2008

  • Format: mm-dd-yy
    SELECT CONVERT(Varchar(12),GetDate(),10)
    Output: 12-27-08

  • Format: mm-dd-yyyy
    SELECT CONVERT(Varchar(12),GetDate(),110)
    Output: 12-27-2008

  • Format: yy/mm/dd
    SELECT CONVERT(Varchar(12),GetDate(),11)
    Output: 08/12/27

  • Format: yyyy/mm/dd
    SELECT CONVERT(Varchar(12),GetDate(),111)
    Output: 2008/12/27

  • Format: yymmdd
    SELECT CONVERT(Varchar(12),GetDate(),12)
    Output: 081227