SQL Server Innovations

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...

How to Login to as Different User or How to RunAs in Command Line Problem: One of the common problem is, how to login to SQL Server tools using different credentials and save it so, don't have to manually enter user name and password again and again Solution: Method1: If you are starting from the Desktop in Windows Vista, Windows 7, or Windows Server 2008 and Windows Server 2008 R2, you can do the following: Click  Start  (also shown as the circular Windows logo in the Start menu). Type  Command Prompt  and right-click  Command Prompt  when it appears in the Start menu. From the resulting context menu, click  Run as administrator . As another example, assume that you are logged on as a typical domain user to a workstation in the domain, but you needed to run several tools using a more privileged account. You could do the following: Open a Command Prompt window. Click  Start , click  Run , type  cmd  a...

How to find and Delete Duplicate records in SQL Server table Solution: Step1: Find duplicate records by using Group by OR CTE (Common Table Expression) /* Using GroupBY */ SELECT FirstName ,              LastName ,              COUNT (*) AS RecordCnt      FROM   dbo.Customer      GROUP  BY FirstName ,              LastName      HAVING COUNT (*) > 1 or /* Using CTE */ WITH CTE              AS (      SELECT FirstName ,                  LastName ,                  Row_n...

How to run sql script using command prompt or How to deploy database in production environment or  How to create database using batch file or  How to run sql scripts stored in a folder or  Automating Database Creation or How to call various SQL Scripts from Master SQL Script (Master - Child Scripts) I have came across various clients, where they would not be have SQL Server Management Studio installed on their production box and if you want to run SQL Scripts using command prompt then you can follow below example Step1 - Create a batch file and when you execute this batch file then it will run create_db.sql file against Master database which is stored in C:\Mihir\Scripts folder and creates an empty database SQLCMD - E - dmaster - i C: \ Scripts \ create_db . sql PAUSE Step2 - Here I have created a Master SQL Script (Create_DB.sql), which will call various child Scripts /* BUILD A DATABASE */ -- This is the Master Script SET NOCOUNT ...