SQL Server Innovations

SQL Server Functions -- PARSE     SELECT PARSE ( 'Monday, 13 December 2010' AS date USING 'en-GB' ) AS OrderDate , PARSE ( '£345.98' AS money USING 'en-GB' ) AS OrderAmount -- TRY_PARSE     SELECT TRY_PARSE ( 'Monday, 32 December 2010' AS date USING 'en-US' ) AS OrderDate , TRY_PARSE ( '£345.98' AS money USING 'en-US' ) AS OrderAmount -- TRY_CONVERT     SELECT TRY_CONVERT ( float , '1.2' ), TRY_CONVERT ( int , 'One' ) 　   -- DATEFROMPARTS     SELECT DATEFROMPARTS ( 2010 , 12 , 31 ) --DATETIMEFROMPARTS     SELECT DATETIMEFROMPARTS ( 2010 , 12 , 31 , 23 , 59 , 59 , 0 ) --SMALLDATETIMEFROMPARTS     SELECT SMALLDATETIMEFROMPARTS ( 2010 , 12 , 31 , 23 , 59 ) --DATETIME2FROMPARTS     SELECT DATETIME2FROMPARTS ( 2010 , 12 , 31 , 23 , 59 , 59 , 1 , 7 ) --TIMEFROMPARTS     SELECT

Windows Function - How to Calculate Running Total   Query used:   -- Create a view that sums sales per city per year           CREATE VIEW CitySalesByYear             AS     SELECT c . City , YEAR ( h . OrderDate ) OrderYear , SUM ( d . OrderQuantity ) OrderQuantity FROM SalesOrderHeader h JOIN SalesOrderDetail d ON d . SalesOrderNumber = h . SalesOrderNumber JOIN Customers c ON h . CustomerKey = c . CustomerKey GROUP BY c . City , YEAR ( h . OrderDate )       GO           -- View the data in the view     SELECT * FROM CitySalesByYear ORDER BY City , OrderYear       GO           -- Use OVER to show a running total by year     SELECT City , OrderYear , OrderQuantity , SUM ( OrderQuantity ) OVER ( PARTITION BY City ORDER BY OrderYear ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningQty FROM

To run SQL Script stored in a particular folder using Command Prompt: REM - Run SQL Script to prepare the database environment ECHO Preparing Databases... SQLCMD -E -i C:\ETL_Solution\SetupFiles\Setup.sql

To restart SQL Server, you can use below commands How to Start and Stop SQL Server using Commend Prompt or Batch file Batch file should be as below: @Echo Off REM - Restart SQL Server Service to force closure of any open connections NET STOP SQLSERVERAGENT NET STOP MSSQLSERVER NET START MSSQLSERVER NET START SQLSERVERAGENT

Problem: Creating SSRS report from Cube, I need to write MDX query to compare current month with previous 2 months figures Solution used as below: /* Using Lag function to calculate before 2 Months */ WITH MEMBER [Measures].[Previous 2 Months] AS ([Date].[Calendar Date]. Lag (2), [Measures].[Total Sales Amount]), Format_String = "Currency" SELECT { [Measures].[Total Sales Amount], [Measures].[Previous 2 Months]} on columns , non empty [Date].[Calendar Date].[Month Name]. Members on rows FROM [Analysis Services Tutorial]                                                                                           

Problem: Compare current Month Sale with Previous Month Sales MDX I used to solve the problem is as below: // Retrieve Number of Prior Periods WITH MEMBER [Measures].[PreviousMonthSale] AS ([Date].[Calendar Date]. PrevMember , [Measures].[Reseller Sales-Sales Amount]), Format_String = 'Currency' SELECT {[Measures].[Reseller Sales-Sales Amount],[Measures].[PreviousMonthSale] }  ON COLUMNS , NON EMPTY [Date].[Calendar Date].[Month Name] ON ROWS FROM [Analysis Services Tutorial]

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];

Problem:  I had a requirement to compare current year Profit, Daily Turnover, and other measures with Previous year measures Solution:  I have used Parallel Period function to compare my current year stats with Previous year and MDX statement, finally I produced was as below: /* Final Query to calculate Requested Attributes*/ WITH MEMBER [Measures].[Previous Year Daily Turnover] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Total Daily Turnover]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Profit] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Profit]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Avg Daily Turnover] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Avg Daily Turnover]), FORMAT_STRING = 'Currency' MEMBER [Measures].[Previous Year Avg Daily Revenue] AS ( ParallelPeriod ([Calendar].[Year Num],1), [Measures].[Avg Daily Revenue]), F