Using SQL Server Windowing Function with Aggregates Function

In this post we will see how we can use SQL Server Windowing function in conjunction with Aggregate functions. In SQL Server 2005 Windowing (Over Clause) was supported with all Aggregate functions (Avg , Count, Min, Max and Sum) , in SQL Server 2012 it got further better with support of "Order By" Clause.


Let's look at some Query and their O/P..

Query

1. Lets Create a TransData Table, with following Schema


CREATE TABLE TransData (AcctId int, TransDate date, Amount decimal)
INSERT INTO TransData (AcctId, TransDate, Amount) VALUES
  (1, DATEFROMPARTS(2011, 8, 10), 500),  -- 5 transactions for acct 1
  (1, DATEFROMPARTS(2011, 8, 22), 250),
  (1, DATEFROMPARTS(2011, 8, 24), 75),
  (1, DATEFROMPARTS(2011, 8, 26), 125),
  (1, DATEFROMPARTS(2011, 8, 28), 175),
  (2, DATEFROMPARTS(2011, 8, 11), 500),  -- 8 transactions for acct 2
  (2, DATEFROMPARTS(2011, 8, 15), 50),
  (2, DATEFROMPARTS(2011, 8, 22), 5000),
  (2, DATEFROMPARTS(2011, 8, 25), 550),
  (2, DATEFROMPARTS(2011, 8, 27), 105),
  (2, DATEFROMPARTS(2011, 8, 27), 95),
  (2, DATEFROMPARTS(2011, 8, 29), 100),
  (2, DATEFROMPARTS(2011, 8, 30), 2500),
  (3, DATEFROMPARTS(2011, 8, 14), 500),  -- 4 transactions for acct 3
  (3, DATEFROMPARTS(2011, 8, 15), 600),
  (3, DATEFROMPARTS(2011, 8, 22), 25),
  (3, DATEFROMPARTS(2011, 8, 23), 125)


2. Now we write a Query which will partition data by AccountId and Order them by TransDate


SELECT AcctId, TxnDate, Amount,
  RAvg = AVG(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RCnt = COUNT(*)    OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMin = MIN(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMax = MAX(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RSum = SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate)
 FROM TxnData
 ORDER BY AcctId, TxnDate
GO

3. Here is the O/P of above Query

SQL Azure : Migrating ASPNet Database to Sql Azure

If you ever wondered about how you will migrate your ASPNet DB to cloud, here is the solution.

1) Create a Blank  Database "ASPNet" using your SQL Azure connection.
2) Launch "Microsoft SQL Server Management Studio" and connect to your SQL Azure Database by using your credentials. Now Run following script against database created in Step. I have created this script by using "SQL Azure Migration Wizard" available at codeplex.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applications]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Applications](

    [ApplicationName] [nvarchar](235) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [ApplicationId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Users](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [UserId] [uniqueidentifier] NOT NULL,

    [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [IsAnonymous] [bit] NOT NULL,

    [LastActivityDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [UserApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [UserApplication]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Memberships]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Memberships](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [UserId] [uniqueidentifier] NOT NULL,

    [Password] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PasswordFormat] [int] NOT NULL,

    [PasswordSalt] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Email] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PasswordQuestion] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PasswordAnswer] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [IsApproved] [bit] NOT NULL,

    [IsLockedOut] [bit] NOT NULL,

    [CreateDate] [datetime] NOT NULL,

    [LastLoginDate] [datetime] NOT NULL,

    [LastPasswordChangedDate] [datetime] NOT NULL,

    [LastLockoutDate] [datetime] NOT NULL,

    [FailedPasswordAttemptCount] [int] NOT NULL,

    [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,

    [FailedPasswordAnswerAttemptCount] [int] NOT NULL,

    [FailedPasswordAnswerAttemptWindowsStart] [datetime] NOT NULL,

    [Comment] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipApplication]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipUser]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Profiles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Profiles](

    [UserId] [uniqueidentifier] NOT NULL,

    [PropertyNames] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PropertyValueStrings] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PropertyValueBinary] [image] NOT NULL,

    [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND parent_object_id = OBJECT_ID(N'[dbo].[Profiles]'))

ALTER TABLE [dbo].[Profiles]  WITH CHECK ADD  CONSTRAINT [UserProfile] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND parent_object_id = OBJECT_ID(N'[dbo].[Profiles]'))

ALTER TABLE [dbo].[Profiles] CHECK CONSTRAINT [UserProfile]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Roles](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [RoleId] [uniqueidentifier] NOT NULL,

    [RoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [RoleId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[RoleApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Roles]'))

ALTER TABLE [dbo].[Roles]  WITH CHECK ADD  CONSTRAINT [RoleApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[RoleApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Roles]'))

ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [RoleApplication]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[UsersInRoles](

    [UserId] [uniqueidentifier] NOT NULL,

    [RoleId] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC,

    [RoleId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleRole] FOREIGN KEY([RoleId])

REFERENCES [dbo].[Roles] ([RoleId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleRole]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleUser]

GO

-- BCPArgs:1:[dbo].[Applications] in "c:\SQLAzureMW\BCPData\dbo.Applications.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:1:[dbo].[Users] in "c:\SQLAzureMW\BCPData\dbo.Users.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:1:[dbo].[Memberships] in "c:\SQLAzureMW\BCPData\dbo.Memberships.dat" -E -n -b 10000 -a 16384

GO