Profile

Cover photo
Pinal Dave
Works at SQLAuthority.com
Attended University of Southern California
Lives in Bangalore, India
9,181 followers|950,934 views
AboutPostsPhotosYouTube+1's

Stream

Pinal Dave

Shared publicly  - 
5
Geeta Davey's profile photoveeramani narayanasamy's profile photo
2 comments
 
<pre>
USE tempdb
GO
-- Creating Test Table
CREATE TABLE Energy(
Cont_No int, Cust VARCHAR(6), Product VARCHAR(20),MonthRef varchar(6)
,Tier Int,Index_Code varchar(5),InitPrice money, QTY INT,CurPrice money)
GO
-- Inserting Data into Table
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','2NNSPA','201501',1,'CPI',0.5,1000,1.5)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','2NNSPA','201501',2,'CPI',0.5,2000,1.5)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','2NNSPA','201502',1,'CPI',0.5,1100,1.7)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','2NNSPA','201502',2,'CPI',0.5,1200,1.7)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','1NNSPA','201501',1,'CPI',0.5,1000,1.5)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','1NNSPA','201501',2,'CPI',0.5,1500,1.5)
INSERT INTO Energy(Cont_No,Cust, Product,MonthRef,Tier,Index_Code,InitPrice, QTY,curPrice)
VALUES(100101,'SIC701','1NNSPA','201502',1,'CPI',0.5,500,1.7)
</pre>
Actual Result as follows,
<pre>
Cont_No Cust Product MonthRef  Tier Index_Code InitPrice   QTY    CurPrice
100101 SIC701 2NNSPA 201501    1    CPI        0.50    1000 1.50
100101 SIC701 2NNSPA 201501    2    CPI        0.50    2000 1.50
100101 SIC701 2NNSPA 201502    1    CPI        0.50    1100 1.70
100101 SIC701 2NNSPA 201502    2    CPI        0.50    1200 1.70
100101 SIC701 1NNSPA 201501    1    CPI        0.50    1000 1.50
100101 SIC701 1NNSPA 201501    2    CPI        0.50    1500 1.50
100101 SIC701 1NNSPA 201502    1    CPI        0.50    500        1.70
</pre>

I'm expecting the result as follows
--Result should be in the below format (Monthly sales volume and price by cont_no,Cust,Product & Index_code) 
<pre>
CONT_NO CUST PRODUCT Index_Code TIER  DataType INITPRICE 201501  201502
100101 SIC701 2NNSPA CPI 1 Volume    0.50    1000    1100
100101 SIC701 2NNSPA CPI 2 Volume    0.50    2000    1200
Total  3000    2300
CurPrice  1.50  1.70
100101 SIC701 1NNSPA CPI 1 Volume    0.50  1000    500
100101 SIC701 1NNSPA CPI 2 Volume    0.50  1500    0
Total  2500    500
CurPrice  1.50  1.70
</pre>

I did something like below to get the result
<pre>
SELECT * FROM (
SELECT  CONT_NO
, CUST
, PRODUCT
, Index_Code
, tier
,InitPrice
, SUM(CASE WHEN MONTHREF = '201501' THEN  QTY ELSE 0 END) AS '201501'
, SUM(CASE WHEN MONTHREF = '201502' THEN  QTY ELSE 0 END) AS '201502'
FROM Energy 
GROUP BY Cont_No, Cust, Product, Index_Code,InitPrice,tier WITH ROLLUP
)A WHERE Cont_No IS NOT NULL AND CUST IS NOT NULL and A.Product is not null and A.Index_Code is not null and A.InitPrice is not null

</pre>

I'm not able get what exactly i want, please help me out
Add a comment...

Pinal Dave

Shared publicly  - 
 
Can you solve it?
Pinal Dave originally shared:
 
It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight. The question is in the…
It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month ...
6
1
Marcin Gierdalski's profile photo
Add a comment...

Pinal Dave

Shared publicly  - 
 
Pinal Dave originally shared:
 
Here is a one of the very common question I keep on getting via email. “I just restored all the backup files of my database, however, my database is still stuck in restoring state. How do I fix it?” Well, if you have already restored all the database, you…
4
Add a comment...

Pinal Dave

Shared publicly  - 
 
 
Happy Dussehra!
3
Vikrant Patel's profile photochaitanya hajare's profile photo
2 comments
 
Happy dassera.. 
Add a comment...
Have him in circles
9,181 people
Naved Ansari's profile photo
Jervin Jervin's profile photo
Kailash Bhakat's profile photo
Jon Williams's profile photo
pinky sharma's profile photo
Vikas Ahlawat's profile photo
Meredith Ryan's profile photo
nidhi shah's profile photo
Srbuhi Badalyan's profile photo

Pinal Dave

Shared publicly  - 
1
Add a comment...

Pinal Dave

Shared publicly  - 
 
 
pinaldave - Here is a one of the very common question I keep on getting via email. “I just restored all the backup files of my database, however, my database is still stuck in restoring state. How do I fix it?” Well, if you have already restored all the database, you can execute the following code and it will bring your database from recovery to operational state. RESTORE DATABASE NameofDatabase WITH RECOVERY If due to any reason, above query returns error about restori... http://ow.ly/2PDROG
1
Add a comment...

Pinal Dave

Shared publicly  - 
 
 
Monitor, diagnose and optimize #SQLServer operations with @DellSoftware's Spotlight on #SQL Server - Try it today http://dell.to/1enPM6g
Automated SQL Server performance monitoring, diagnostics, and tuning tool. Get started with the award winning Spotlight on SQL Server Enterprise.
1
Add a comment...

Pinal Dave

Shared publicly  - 
 
Use my Uber promo code, ubersql, and get ₨300 off your first Uber ride. Redeem it at https://www.uber.com/invite/ubersql
Sign up for Uber with my promo code
1
Add a comment...
People
Have him in circles
9,181 people
Naved Ansari's profile photo
Jervin Jervin's profile photo
Kailash Bhakat's profile photo
Jon Williams's profile photo
pinky sharma's profile photo
Vikas Ahlawat's profile photo
Meredith Ryan's profile photo
nidhi shah's profile photo
Srbuhi Badalyan's profile photo
Work
Occupation
SQL DBA, SQL Server Developer, SQL Blogger
Employment
  • SQLAuthority.com
    Founder, 2006 - present
  • Pluralsight
    Developer Evangelist, 2012 - present
  • Microsoft
    Technical Evangelist, 2011 - 2012
Places
Map of the places this user has livedMap of the places this user has livedMap of the places this user has lived
Currently
Bangalore, India
Contact Information
Home
Email
Work
Email
Story
Tagline
SQL Server , SQLAuthority and Simple Words
Introduction
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. 

Education
  • University of Southern California
    MS - Computer Networks, 2001 - 2003
  • Newcastle University
    BE - Electronics and Communication, 1997 - 2001
Basic Information
Gender
Male
Other names
pinaldave, sqlauthority, Pinal Dave, SQL Authority
Links
YouTube
Contributor to
Pinal Dave's +1's are the things they like, agree with, or want to recommend.
MySQL Backup and Recovery Fundamentals
pluralsight.com

Data is very important to any application and business. It is very important that every business plan for data safety. Database backup strat

MySQL Query Optimization and Performance Tuning
pluralsight.com

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best effici

Building a High Traffic, Profitable Blog
pluralsight.com

Everybody can write, and everybody can build a blog. But not everyone can make their blog a popular point of reference for their subject mat

MySQL Fundamentals Part 2
pluralsight.com

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web applic

Building a Successful Blog
pluralsight.com

In this introductory course on blogging we will go over a few of the basics of blogging and show the way to get started with blogging immedi

MySQL Fundamentals
pluralsight.com

Fundamentals of MySQL, an essential part of the LAMP stack.

Technology « SQL Server Journey with SQL Authority
blog.sqlauthority.com

“No Guests PLEASE!” “Doesn&#39;t your Indian tradition suggest welcoming guests and treating them in the best way possible?” “Yes, but I am

Pinal Dave « SQL Server Journey with SQL Authority
blog.sqlauthority.com

SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example. Posted in Pinal Dave, PostADay, SQL, SQL Au

SQL Server Performance: Indexing Basics
www.pluralsight-training.net

This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes.

Beginning SQL Joes 2 Pros (Set Of 5 Books) - Rick A. Morelan,Pinal Dave,...
www.flipkart.com

Beginning SQL Joes 2 Pros (Set Of 5 Books) by Rick A Morelan Pinal Dave Doug Fritz Jessica Brown. Rs.2796, Save 20%. Buy Beginning SQL Joes

Pluralsight Author
www.pluralsight-training.net

I'm learning more about Pinal Dave

SQL SERVER – 2008 – Missing Index Script – Download
sqlauthority.wordpress.com

Download Missing Index Script with Unused Index Script and Duplicate Index Script Performance Tuning is quite interesting and Index plays a

SQL SERVER – 2008 – Unused Index Script – Download
sqlauthority.wordpress.com

Download Missing Index Script with Unused Index Script and Duplicate Index Script Performance Tuning is quite interesting and Index plays a

SQL Server Interview Questions and Answers for all Database Developers a...
www.flipkart.com

SQL Server Interview Questions and Answers for all Database Developers and Database Administrators by Pinal Dave Vinod Kumar Rhonda Chesley

SQL Server Interview Questions and Answers - Pinal Dave,Vinod Kumar
www.flipkart.com

Flipkart.com: SQL Server Interview Questions and Answers by Pinal Dave Vinod Kumar. Rs.218, Save 25%. Buy SQL Server Interview Questions and