Cumulative Distribution

/* Distribution Value:

A distribution value indicates the possibility of occurance of that value among a group of values.
For Example, when a coin is thrown, it can be head or tail. Distribution value of heads is 1/2 = 0.5 , distribution value of tails is 1/2 =0.5
For example, when a dice is thrown, it can be any number among 1 to 6. So, distribution value of 1 is 1/6 = 0.1666, distribution value of 2 is 1/6 = 0.666.....

Cumulative Distribution Value:

Cumulative distribution value will tells you the possibility of occurance of that value or lesser than that among a group of values.
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 1 is, 1/6 = 0.1666
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 2 is, 2/6 = 0.3333
Suppose, when a dice is thrown, possibility that it can be a number less than or equal to 3 is, 3/6 = 0.5000
*/

Use [ExploringSQL]
GO

-- Creating table
CREATE TABLE [Results]
(
[Subject] varchar(10),
[Student] varchar(50),
[Marks] int
)

-- Inserting sample records
INSERT INTO [Results] (Subject,Student,Marks)
VALUES
('Maths','Student1',45),
('Physics','Student2',45),
('Physics','Student1',50),
('Chemistry','Student3',20),
('Physics','Student3',35),
('Biology','Student1',20),
('Biology','Student2',60),
('Biology','Student3',65),
('Chemistry','Student1',75),
('Biology','Student4',30)
GO

select * from [Results] order by Student , Marks

-- Querying with CUME_DIST()

SELECT [Student],[Subject],[Marks],
CUME_DIST() OVER(PARTITION BY [Subject] ORDER BY [Marks]) as CD
FROM [Results]
ORDER BY [Subject],[Marks]
GO

DROP TABLE [Results]

CONCAT

/*
CONCAT function can be used to concatenate two or more strings.
This function is available before in other databases like Oracle,MySQL etc.
This feature will offer below advantages over standard concatenation operator(+)

    If any of the string is null, standard concatenation operator will return null as output.
    However, CONCAT() function will ignore null values while concatenating.
    As CONCAT() function is already available in other databases such as Oracle and MySql, while porting from one DB to other,
    this will gives more flexibility compared to standard operator.
*/


DECLARE
@firstname VARCHAR(20) = 'Pardha Saradhi',
@middlename VARCHAR(10) = NULL,
@lastname VARCHAR(20) = 'V'

-- Without CONCAT function
SELECT @lastname + ' ' + @middlename + '' + @firstname


-- Using CONCAT function
SELECT CONCAT(@lastname, ' ', @middlename, ' ', @firstname)

Choose

-- CHOOSE() function will returns the value based on the specified index from the list of values.

DECLARE @Index INT,
        @FirstValue VARCHAR(10),
        @SecondValue VARCHAR(10),
        @ThirdValue VARCHAR(10)

SET @Index = 2
SET @FirstValue = 'One'
SET @SecondValue = 'Two'
SET @ThirdValue = 'Three'

SELECT CHOOSE (@Index, @FirstValue, @SecondValue, @ThirdValue)

SELECT * FROM [Person].[Address] ORDER BY CHOOSE (4 ,AddressLine1, AddressLine2, City, AddressID) -- Can Replace Dynamic SQL.

New in SSMS 2012

-- 1. CODE SNIPPET:

press "Ctrl+k,Ctrl+x"

To add a new code snippet:

    Create the snippet file with .snippet extension(Its advisable to take existing snippet file and modify it)
    Go to Tools -> Code Snippets Manager
    Select the folder, under which you wants to add code snippet and click on Add button and select the snippet file.

-- 2. TASK LIST : GO TO VIEW -> TASK LIST
    We often use some external tools to manage our task list, where we will add our tasks and will follow the tasks according to the task list.
    Now with SQL Server 2012, external tool is no longer required.

-- 3. Zoom/Magnify feature helps in presentations

-- 4. Multi Monitor Support:

    Until SQL Server 2012, in SSMS, we were able to open only one query window. 2 or more query windows can't share the same screen.
    Only one query window can be visible on the screen.

    Now SQL Server 2012 SSMS provides the option to open multiple query windows at a time.
    Easily we can drag the query window and can place that query window where ever we want.

-- 5. Debugging Enhancements:
        Before SQL Server 2012, SSMS already provided a feature to debug the queries.
        In SQL Server 2012, this has been enhanced more to provide more rich experience.

        Conditional Hit breakpoints ? Break point will hit only when the specified condition met.

        Hit Count -> Break point will hit when that breakpoint is hit specified number of times.
        Export/Import the breakpoints to an XML file.
        and more..

        You can get these additional options by Right click on a break point and explore these additional capabilites

-- 6. New Sequence Node to manage Sequences:

        SQL Server 2012 SSMS has a new node “Sequences” in object explorer under Database-> programmability.
        This will allows you to create new sequences or update existing sequences by using GUI.

            Go to Object Expolorer -> Expland database -> Programmability -> Sequences.
            Right click the folder -> New Sequence

        This will open the dialog box, where you can create new sequence and mention its properties, such as minimum value, maximum value and increment value and set its other properties like "Cycle","Cache" etc.

-- 7. “Surround With” feature to enclose your query with IF,BEGIN..END,WHILE blocks

Often in stored procedures/queries, we will use IF, BEGIN..END, WHILE statements. Now, In SQL Server 2012, SSMS provides an option “Surround with”,
which simplifies in enclosing query statements with these blocks.

    Right Click and choose “Surround with”, which shows IF,BEGIN,WHILE options. Or Alternatively press Ctrl+k,Ctrl+s

-- 8. Keyboard Shortcuts enhancement

Few users are used to shortcuts in SSMS, where as users coming from Visual Studio background, might be used to certain shortcuts,
which might be different from SSMS. Now for the users, who are used to Visual Studio, SQL Server 2012 allows to change the keyboard shortcuts similarly like Visual Studio.
This feature also gives ability to export keyboard shortcuts to a file or import these shortcuts from a file.

To change keyboard shortcuts like Visual Studio,

    Go to Tools -> Options -> Keyboard
    Change dropdown selection from "Default" to "Visual Studio 2010 compatible" and Click OK.

To export/import settings to/from a file, you can find it under Tools -> import/export settings

-- 9 . Cycle Clipboard Ring feature to access clipboard and previously copied items

Often we will copy various parts of a query window content to another query window. While copying we often switch between multiple windows.
Now in SQL Server 2012, SSMS supports a new feature "Cycle Clipboard Ring", which allows us to access previously copied items from clipboard.
With the help of this feature, we can copy all the queries at once in a query window and now in another query window, we can paste all those consecutively..

Eg:

    From a query window, copy the query content by pressing Ctrl+C
    Copy another query content by pressing Ctrl+C
    To access recently copied content, press Ctrl+Shift+V
    Now to access the first copied content, press Ctrl+Shift+ V two times.

-- 10. One of the challenges involved in moving/copying databases to a new location (prior to SQL Server 2012) was copying the dependencies along with the database.
External dependencies such as login names, linked servers etc are not automatically copied to the new location when you restore a regular database backup.

SQL Server 2012 (Denali) introduces Contained Databases, a new feature that allows you to embed all the dependencies right into your database.
When the database is moved/copied to a new location, all the dependencies are also moved/copied along with the database.

GO TO Database Properties -> Options -> Containment Type.


-- 11. Following permissions has been added in SQL Server 2012

    Alter any availability Group - Users having this permission can update any availability group.
    Alter any server role - Users having this permission can update server roles.
    Create availability Group - Users having this permission can create availability group.
    Create any server role - Users having this permission can create server role.

Before SQL Server 2012, we have 8 Server Roles. There was no provision to create our own Custom Server Role. Now, in SQL Server 2012, we can create our own Custom Server Role.
While creating Custom Server Role, we can configure the required Endpoints,Logins,Servers,TSQL Named pipes etc.

To add your own custom server role,

    Connect to SSMS
    In object expolorer, Go to Security-> Server Roles.
    Right click and choose "Add new server role"

Date Ranges using SQL 2012 Functions

 -- Fiscal Year : July to June
DECLARE @Today        DATE = CAST(GETDATE() AS DATE)
DECLARE @BeginDate    DATE = CAST(CASE WHEN @DateSelection = 6 -- Last 12 Months
                                     THEN dbo.fn_LocalTimeToUtcTime(STR(MONTH(DATEADD(MONTH, -12,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -12, @Today)),4))
                                     WHEN @DateSelection = 5 -- This Fiscal Year
                                     THEN dbo.fn_LocalTimeToUtcTime(IIF(MONTH(@Today) > 6, '7/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4),'7/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)) - 1,4)))
                                    WHEN @DateSelection = 4 -- This Fiscal Quarter
                                        THEN dbo.fn_LocalTimeToUtcTime(CHOOSE(MONTH(@Today),'1', '1', '1','4','4','4','7','7','7', '10', '10', '10') + '/1/' + STR(YEAR(DATEADD(MONTH, -1, @Today)),4))
                                    WHEN @DateSelection = 3 -- Last 3 Months
                                        THEN dbo.fn_LocalTimeToUtcTime(STR(MONTH(DATEADD(MONTH, -3,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4))
                                    WHEN @DateSelection = 2 -- Next Month
                                        THEN dbo.fn_LocalTimeToUtcTime(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) + 1, 0))
                                    WHEN @DateSelection = 1 -- Last Month
                                        THEN dbo.fn_LocalTimeToUtcTime(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) - 1, 0))
                                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today), 0) -- This Month
                                END AS DATE)
DECLARE @EndDate DATE  = CAST(CASE WHEN @DateSelection = 6
                                      THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today) - 1, 0)))
        WHEN @DateSelection = 5
        THEN dbo.fn_LocalTimeToUtcTime(IIF(MONTH(@Today) > 6, '6/30/' + STR(YEAR(DATEADD(MONTH, -3, @Today)) + 1 ,4),'6/30/' + STR(YEAR(DATEADD(MONTH, -3, @Today)),4)))
        WHEN @DateSelection = 4
            THEN dbo.fn_LocalTimeToUtcTime(CHOOSE(MONTH(@Today),'3/31/', '3/31/', '3/31/','6/30/','6/30/','6/30/','9/30/','9/30/','9/30/', '12/31/', '12/31/', '12/31/') + STR(YEAR(DATEADD(MONTH, -1, @Today)),4))
        WHEN @DateSelection = 3
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(STR(MONTH(DATEADD(MONTH, -1,@Today)),2) + '/1/' + STR(YEAR(DATEADD(MONTH, -1, @Today)),4)))
        WHEN @DateSelection = 2
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(@BeginDate))
        WHEN @DateSelection = 1
            THEN dbo.fn_LocalTimeToUtcTime(EOMONTH(@BeginDate))
    ELSE EOMONTH(@Today)
                                END AS DATE)

SELECT @BeginDate, @EndDate

Get Last 12 Months and their Start Dates

CREATE PROCEDURE P_Last12Months
AS
BEGIN
    DECLARE @Months TABLE
    (
    MID INT IDENTITY(1,1),
    MonthNumber INT,
    MonthDesc VARCHAR(15),
    StartDate DATE
    )

    DECLARE @Loop INT = 0, @Today DATETIME = GETDATE()

    WHILE (@Loop > -12)
    BEGIN

    INSERT @Months
    SELECT      IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) AS MonthNumber
            , DATENAME(MONTH,DATEADD(MONTH, @Loop, @Today)) AS MonthDesc
            , DATEFROMPARTS(IIF(MONTH(@Today) + @Loop <= 0, YEAR(@Today) - 1,YEAR(@Today)),IIF(MONTH(@Today) + @Loop <= 0 , 12 + MONTH(@Today) + @Loop, MONTH(@Today) + @Loop) , 1)

    SET @Loop = @Loop - 1

    END

    SELECT * FROM @Months
END

Displaying Powerview report using a sharepoint silverlight webpart

1. Edit your page and click on "Add a Web Part".
2. From the "Media and Content" category, select "Silverlight Web Part" and click the "Add" button.
3. You will now need to enter a URL to the Silverlight XAP file. I found this location by opening the Power View editor and looking in the HTML source to see where it was loaded. In any case, it is something like this (your mileage may vary):
• http://[sharepoint_server]/_layouts/ReportServer/ClientBin/Microsoft.Reporting.AdHoc.Shell.Bootstrapper.xap?v=11.0.2100.60
4. You will see that SharePoint tries to load the Silverlight component, but fails. Not to worry, there are more settings to fill in before it actually works!
5. First of all, you should see the Web Part's properties on the right hand side of your screen. If not, select the little drop down in the title bar of the Web Part and click on "Edit Web Part".
6. I gave my Web Part a fixed width and height, but you can set it as you like.
7. Chrome Type can be set to None to give a nice integrated look and feel.
8. The most import settings are to be found under the heading "Other Settings".
9. Click the (empty) textbox "Custom Initialization Parameters" and click on the button with the ellipsis (...).
10. Here are my settings:
    • ItemPath=<LINK TO RDLX file>,
      ReportServerUri=http://<SHAREPOINT_SERVER>/_vti_bin/reportserver/,
      ViewMode=Presentation,
      ServerTraceLevel=1,
      AuthenticationMode=Windows,
      ReportSection=ReportSection,
      Fit=True,
      PreviewBar=False,
      BackgroundColor=White,
      Border=True,
      AllowEditViewMode=False,
      AllowFullScreenViewMode=False
    • The tricky bit is of course the url to the ItemPath, but the easiest way to find this is by opening the Power View editor, open "View Source" and search for the text: param name="InitParams"
    • Right after this text is the value parameter. Its contents pretty much gives you the ItemPath you need. Be sure to translate the encoded characters such as %3A to :, %2F to /, %20 to a space, etc.
11. Finally, hit OK or Apply to make sure that it actually works!

If you would rather start with Page 2 (the second view) of your Power View report, simply change the value of the parameter ReportSection. For instance, ReportSection=ReportSection2 will show you the second view.