Skip to main content

T-SQL LEAD LAG and SUM function based query

 

Query on T-SQL window clause

Below is the sales table

Order_Date

Name

Product

SubCategory

Category

Quantity

SalesAmount

DiscountAmount

10-08-2011

Megan Stewart

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

14-09-2011

Elizabeth Clark

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

14-10-2011

Olivia Torres

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

20-10-2011

Ethan Foster

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

14-11-2011

Beth Gutierrez

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

14-11-2011

Dawn Wu

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

20-12-2011

Desiree Dominguez

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

22-12-2011

Kevin Wright

Mountain-100 Silver, 38

Mountain Bikes

Bikes

1

3399.99

0

17-01-2011

Clarence Rai

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

23-01-2011

Elizabeth Johnson

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

03-02-2011

Jaclyn Lu

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

19-03-2011

Jon Zhou

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

04-04-2011

Arthur Carlson

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

07-04-2011

Adam Ross

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

17-04-2011

Carolyn Navarro

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

29-04-2011

Abby Sai

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

05-05-2011

Ross Jordan

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

06-05-2011

April Deng

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

02-06-2011

Ebony Gutierrez

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

08-06-2011

Julian Ross

Mountain-100 Silver, 42

Mountain Bikes

Bikes

1

3399.99

0

 test


  • From the above table display all records along with sum of sales amount for each category.
  • From the above table display all the records along with sales amount per month.
  • Compare the sales between each day with previous sales value for each category.
Labels:

Comments

Post a Comment

Popular posts from this blog

DataZen Syllabus

INTRODUCTION TO DATAZEN PRODUCT ELEMENTS ARCHITECTURE DATAZEN ENTERPRISE SERVER INTRODUCTION SERVER ARCHITECTURE INSTALLATION SECURITY CONTROL PANEL WEB VIEWER SERVER ADMINISTRATION CREATING AND PUBLISHING DASHBOARDS CONNECTING TO DATASOURCES DESIGNER CONFIGURING NAVIGATOR CONFIGURING VISUALIZATION  PUBLISHING DASHBOARD WORKING WITH MAP  WORKING WITH DRILL THROUGH DASHBOARDS
67 comments
Read more

MS BI Syllabus

Microsoft Business Intelligence Course Syllabus SSRS – SQL Server Reporting Services  Getting Started 1. Understanding Reporting (Authoring,Management,Delivery) 2. Installing Reporting (Native Mode, SharePoint Integration mode) 3. Building your first report  Authoring Reports 1. Developing Basic Reports (RDL,wizard,designer,datasource,dataset,formatting) 2. Working with expressions (expression to calculate value, Agg functions, exp for objects) 3. Organizing Data (Data Regions, Table, Matrix, Chart, List) 4. Advance Report (Parameter, drill down, drill through, links, 5. Report Model (Data Source, Data Source View, Model , Report Builder 3.0)  Managing Report ( Report Manager) 1. Managing Content (deploying report, folders, linked reports, datasources, value etc) 2. Managing Security (Item Level , Site navigation, localhost – sql) 3. Managing Server Config (Config Manager, Report Manager, Report Server DB)  Delivering Report 1. Accessing Report (Viewing...
31 comments
Read more

PowerBI Interview Questions and Answers

Power BI Interview Questions – General Questions 1). What is self-service business intelligence? Ans: Self-Service Business Intelligence (SSBI) is an approach to data analytics that enables business users to filter, segment, and, analyse their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI). SSBI has made it easier for end users to access their data and create various visuals to get better business insights. Anybody who has basic understanding of the data can create reports to build intuitive and shareable dashboards. 2). What are the parts of Microsoft self-service business intelligence solution? Ans: Microsoft has two parts for Self-Service BI  Excel BI Toolkit – It allows users to create interactive report by importing data from different sources and model data according to report requirement.  Power BI – It is the online solution that enables you to share the interactive reports and queries that you have created using ...
7 comments
Read more