Conditional Running Total

Hai,

I have a set of data (approx 8 million rows on average and growing) that is broken up into months per account. With each new month that goes by, a calculation is done to determine if the client has paid his account or not. For every month he makes a payment his account is marked with a Zero (0) and every month he doesn't make a payment his account is marked with a One (1).

The goal is to determine a running total to see how many consecutive months the client has missed payments.

-- Sample Data
declare @data as table 

(
 account varchar(6),
 mnth int,
 paid_indicator tinyint
)

insert into @data(account,mnth,paid_indicator)
values ('ABC123',201101,1),
  ('ABC123',201102,0),
  ('ABC123',201103,1),
  ('ABC123',201104,1),
  ('ABC123',201105,0),
  ('ABC123',201106,0),
  ('ABC123',201107,1),
  ('ABC123',201108,1),
  ('ABC123',201109,1),
  ('ABC123',201110,1),
  ('ABC123',201111,1),
  ('ABC123',201112,0),
  ('ABC123',201201,1),
  ('ZZZ555',201105,1),
  ('ZZZ555',201106,1),
  ('ZZZ555',201107,0),
  ('ZZZ555',201108,0),
  ('ZZZ555',201109,0),
  ('ZZZ555',201110,1)
  
  
  
-- Running Total
select d.*, isnull(rt.rt,0) as running_total
from @data d

outer apply 

(
 -- For this account, get the month of the last non-payment.
 select top 1 lnp.mnth
 from @data lnp
 where lnp.account = d.account
 and lnp.mnth < d.mnth
 and lnp.paid_indicator = 0
 order by lnp.mnth desc
) last_non_pmt

outer apply

(
 -- Then, add up the paid_indicator values for months 

--  since the last non-payment.
 select sum(d2.paid_indicator) as rt
 from @data d2
 where d2.mnth between isnull(last_non_pmt.mnth, d2.mnth) and d.mnth
 and d2.paid_indicator = 1
 and d.paid_indicator = 1
 and d2.account = d.account
) rt

order by d.account, d.mnth

Running value in T-sql

--// Table Variable Declaration
DECLARE @Table AS TABLE
(
    Account VARCHAR(25),
    Mnth    INT,
    Salary    MONEY
)

--// Data Insertion
INSERT INTO @Table(Account,Mnth,Salary)
VALUES    ('ABC123',201101,100),
        ('ABC123',201102,200),
        ('ABC123',201103,150),
        ('ABC123',201104,300),
        ('ABC123',201105,50),
        ('ABC123',201106,40),
        ('ABC123',201107,500),
        ('ABC123',201108,600),
        ('ZZZ555',201105,150),
        ('ZZZ555',201106,450),
        ('ZZZ555',201107,200),
        ('ZZZ555',201108,50),
        ('ZZZ555',201109,80),
        ('ZZZ555',201110,20)

       
--// Result with Running value of each account

SELECT * FROM @Table d1
OUTER APPLY
(
    SELECT    SUM(d2.Salary) RunningValue
    FROM    @Table d2
    WHERE d1.account = d2.Account
    AND d2.mnth <= d1.mnth
) t

Result:

Account	Mnth	Salary	RunningValue
ABC123	201101	100	100
ABC123	201102	200	300
ABC123	201103	150	450
ABC123	201104	300	750
ABC123	201105	50	800
ABC123	201106	40	840
ABC123	201107	500	1340
ABC123	201108	600	1940
ZZZ555	201105	150	150
ZZZ555	201106	450	600
ZZZ555	201107	200	800
ZZZ555	201108	50	850
ZZZ555	201109	80	930
ZZZ555	201110	20	950

IF..ELSE Statement v/s OR Statement in SQL Server

Hai,

I wrote a conditional query with OR Statement by avoiding If..Else Statement.

Declare @AccountMaster Table
(
    AccID    Int,
    CustID    Int,
    Name    Varchar(50)
)

Insert Into @AccountMaster Values
(1,101,'Chandu'),
(2,102,'Aromal'),
(3,103,'Thacholi'),
(4,104,'Unniyarcha')


Declare @Flag Bit = 0, --// If 0 -> AccID, 1 --> CustID
        @Account Int = 2
       
Select AccID,CustID,Name
From @AccountMaster
Where    (@Flag = 1 And CustID = @Account)
OR        (@Flag = 0 And AccID  = @Account)
       

Eg.,Normal Query Using If..Else Statement

If @Flag = 1
Begin
    Select AccID,CustID,Name
    From @AccountMaster
    Where    CustID = @Account
End
Else
Begin
    Select AccID,CustID,Name
    From @AccountMaster
    Where (AccID  = @Account)
End

How to get Nth Highest Salary

--// Table Variable Creation
Declare @Employee Table
(
    EmpID            int NOT NULL,
    EmpName            nvarchar(50) NOT NULL,
    Designation        nvarchar(50) NOT NULL,
    Salary            money NOT NULL
)

--// Data Insertion
INSERT INTO @Employee (EmpID, EmpName, Designation, Salary) VALUES
  (1001, 'Anju', 'Manager', 8000.0000)
 ,(1002, 'Babu', 'Engineer', 9000.0000)
 ,(1003, 'Cenoy', 'Doctor', 7000.0000)
 ,(1004, 'Deepthi','Manager', 6000.0000)
 ,(1005, 'Emilin','Engineer', 5000.0000)
 ,(1006, 'Febin','Doctor', 8000.0000)
 ,(1007, 'Stephen','IT Consultant', 6000.0000)


--// Query to find Nth Largest Salary from the table.

Declare @Nth Int = 2

Select * From @Employee E1 Where @Nth = (Select Count(Distinct Salary) From @Employee E2 Where E1.Salary <= E2.Salary)

Count No.Of character occurences on a string

DECLARE @String  VARCHAR(500) = 'Sequel Server 2012 ',
        @Char     CHAR(1)    = 'e'
       
       
SELECT DATALENGTH(REPLACE  (@String, @Char, @Char+@Char)) - DATALENGTH (@String) As CharacterCount


Result
------
4

SQL Server 2012 RC0

Microsoft announces the release of SQL Server 2012 RC0.


Download SQL Server 2012 RC0

Last day of Month

First add one month with given date AND substract the day from given result date.

DECLARE @Dt DATETIME
SET @dt = '29-Jan-2011'

SELECT DATEADD(DAY,-DAY(DATEADD(MONTH,1,@dt)),DATEADD(MONTH,1,@dt)) As LastDayOfMonth

CANONICAL Function

--// To get month name using ODBC canonical function.
SELECT {fn MONTHNAME('03/15/2011')}

--// To get month name using ODBC canonical function.
SELECT {fn DAYNAME('03/15/2011')}

--// To get Current Datetime using ODBC canonical function.
SELECT {fn NOW()}

--// To get year using ODBC canonical function.
SELECT {fn YEAR('15-jan-2011')}

--// To get Week number of given year using ODBC canonical function.
SELECT {fn WEEK('01/15/2011')}

--// To Concatenate string using ODBC canonical function.
SELECT {fn CONCAT('Nikhil','das')}

--// To get Quarter of the year
SELECT {fn QUARTER('01/15/2011')}

Reference :
http://msdn.microsoft.com/en-us/library/bb738626.aspx

How To Reset Identity column in SQL Server

The following DBCC Command will reset the Identity value for the given table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT(<TableName>, RESEED, 0)


For Eg., DBCC CHECKIDENT('Employee', RESEED, 0)

We can also use Truncate Table methode to reset identity field on table but it won't be done if the table exists foreign key relation ship.

Rows to Column in SQL Server

Method 1: Rows to Column Using COALESCE Function

--// Table Variable Declarion
Declare @Product Table
(
     ProductID        Int
    ,ProductName    Varchar(50)
)

--// Data Insertion to Table varaible.
Insert Into @Product Values
(1,'PC'),
(2,'Laptop'),
(3,'Mouse'),
(4,'Keyboard'),
(5,'Modem')

--// Get all Data from Table Variable normal manner.
Select * from @Product

--// Query for to get product name in single column model.
Declare @str Varchar(5000)

Select @str = Coalesce(@str + ',','') + ProductName From @Product

Select @str

The COALESCE Function Returns the first nonnull expression among its arguments.


Method 2: Rows to column without using variable.

Use Adventureworks
go

SELECT
    Name = SUBSTRING(
                        (
                            SELECT ',' + ISNULL(Name,'')
                            FROM Production.Product p
                            FOR XML PATH('')
                         ),2,1000000000
                    )
                   
                   
I suggest method 2 using a good practice in t-sql coding for rows to column purpose.

Generate schedule data in SQL Server

CREATE TABLE ScheduleMaster
(
    rcrPK                int NOT NULL PRIMARY KEY,
    rcrName                varchar(500) NOT NULL,
    rcrStartDate        date NOT NULL,
    rcrEndDate            date NULL,
    rcrPatternType        tinyint NOT NULL,
    rcrPattern            varchar(5000) NULL,
    rcrInterval            int NOT NULL,
    rcrDayOfMonth        int NOT NULL,
    rcrWeekNo            int NOT NULL,
    rcrDayNo            int NOT NULL,
    rcrMonthOfYear        varchar(50) NULL,
    rcrDayOfYear        int NOT NULL,
    rcrTimeOccurence    time(7) NOT NULL,
    rcrNoOfOccurence    int NULL,
    rcrActive            tinyint NOT NULL
)

GO

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (1, N'Yearly wise Schedule with First pattern',  CAST(0x07240B00 AS Date), CAST(0xAE400B00 AS Date), 4, NULL, 2, 0, 0, 0, N'01,05,10', 25, CAST(0x07004EB119B90000 AS Time), 0,1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (2, N'Yearly wise Schedule with Second pattern',  CAST(0x07240B00 AS Date), CAST(0xAE400B00 AS Date), 4, NULL, 2, 0, 2, 3, N'01,05,10', 0, CAST(0x07004EB119B90000 AS Time), 0,1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (3, N'Monthly Schedule with First pattern',  CAST(0x15240B00 AS Date), CAST(0xED410B00 AS Date), 3, NULL, 1, 8, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (4, N'Monthly Schedule with Second pattern',  CAST(0x15240B00 AS Date), CAST(0xED410B00 AS Date), 3, NULL, 3, 0, 3, 4, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (5, N'Weekly Schedule',  CAST(0x07240B00 AS Date), CAST(0xCC400B00 AS Date), 2, N'MON,FRI', 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (6, N'Daily Schedule',  CAST(0x07240B00 AS Date), CAST(0x90400B00 AS Date), 1, NULL, 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0,1)

INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName],  [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (7, N'Custom Schedule',  CAST(0x07240B00 AS Date), CAST(0x90400B00 AS Date), 5, N'01-JAN-2011,08-JAN-2011,10-JAN-2011,05-OCT-2013', 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)

GO

Create Procedure dbo.spGetSchedule
(
    @FromDt            Date ,
    @ToDt            Date
)
As
Begin

    Set Nocount On;
    --// Temporary table for handle final result.
    Declare @tmp Table
    (
        dt                DateTime not null,
        Name            Varchar(500) not null
    )
    --// Declaration section
    Declare
         -- Variables for Cursor looping
        @Name            Varchar(500),   
        @StartDate        Datetime,
        @EndDate        Datetime,
        @PatternType    TinyInt,
        @Pattern        Varchar(5000),
        @Interval        Int, -- Interval period
        @DayOfMonth        Int, -- Monthwise scheduling on which day.
        @WeekNo            Int, -- Month/Year wise Second pattern, indicates which week 1'st,2'nd,3'rd,4'th
        @DayNo            Int,  -- Sunday - 1,Monday - 2,Tuesday - 3,Wednesday -4,Thursday - 5,FrNameay - 6,Saturday - 7
        @MonthOfYear    Varchar(50), -- Year wise scheduling on which month , 1-Jan,2-Feb.. etc.
        @DayOfYear        Int, -- Year wise scheduling on which month of day
        @TimeOccurence    Time,
        @NoOfOccurence    TinyInt,
    ------------------------------
    -- Variables for Prgm flow
        @firstofmonth    Datetime, -- Optional variable  for taking first date of the month in pgrm flow
        @CurDate        Datetime,    -- Optional variable  for taking date in pgrm flow   
        @Pos            Int = 0, -- To keep Position of week or Custom type.
        @intYearMonth    Int, --for take month value from month pattern in year wise shedule.
    -------------------------------
        -- Code pending using with following variable
        @LastOccurence    Int,
        @LastProcessed    Datetime
    -------------------------------

    Declare Cur Cursor Fast_Forward Read_Only For
        Select
             rcrName   
            ,rcrStartDate   
            ,rcrEndDate       
            ,rcrPatternType   
            ,rcrPattern       
            ,rcrInterval       
            ,rcrDayOfMonth   
            ,rcrWeekNo       
            ,rcrDayNo       
            ,rcrMonthOfYear   
            ,rcrDayOfYear   
            ,rcrTimeOccurence
            ,rcrNoOfOccurence
        From ScheduleMaster Where
        (
            (@FromDt        Between rcrStartDate    And rcrEndDate) Or
            (@ToDt            Between rcrStartDate    And rcrEndDate) Or
            (rcrStartDate    Between @FromDt            And @ToDt) Or
            (rcrEndDate        Between @FromDt            And @ToDt)
        ) And (rcrActive = 1)
    Open Cur
    Fetch Next From Cur Into
                             @Name           
                            ,@StartDate       
                            ,@EndDate       
                            ,@PatternType   
                            ,@Pattern       
                            ,@Interval       
                            ,@DayOfMonth       
                            ,@WeekNo           
                            ,@DayNo           
                            ,@MonthOfYear   
                            ,@DayOfYear       
                            ,@TimeOccurence   
                            ,@NoOfOccurence   

    While @@FETCH_STATUS <> -1
    Begin
        If @StartDate    < @FromDt    Set @StartDate    = @FromDt
        If @EndDate        > @ToDt        Set @EndDate    = @ToDt
       
        --// Main loop schedule.
        While (@StartDate <= @EndDate)
        Begin
            If @PatternType = 5 --// Custom option
            Begin
                If Cast(@StartDate As Date) = Cast(substring(@Pattern,@Pos + 1,11) As Date) --// if date matching with given custom date.
                Begin
                    Insert Into @tmp Select @StartDate,@Name
                   
                    Set @Pos = @Pos + 12 --// increasing position.
                End
                Set @StartDate = DATEADD(day,1,@StartDate) --// Adding oneday day with current date.
            End
            Else If @PatternType = 4 --// Year wise schedule
            Begin
                --if DATEADD(YEAR,@interval,@StartDate) <= @EndDate -- Next date less than current date
                --begin   
                    If /*@MonthOfYear <> 0 and */ @DayOfYear > 0 --// First pattern for year shedule -- Every fixed month & Date
                    Begin
                        --// Logic:
                        --    Date set to first date of the current year then adding with given month and date.
                        -- Eg, '15 nov 2011' => '01 jan 2011' => given month march (3), => '01 mar 2011' => given Day 20 => '20 Mar 2011'
                        While (@Pos <= Len(@MonthOfYear))
                        Begin
                            Set @intYearMonth = Cast(Substring(@MonthOfYear,@Pos + 1,2) As Int)
                            Set @CurDate = Dateadd(Month,0,Dateadd(Day,@DayOfYear-1,Dateadd(Month,@intYearMonth-1,Dateadd(Month,-Month(Dateadd(Day,-Day(@StartDate)+1,@StartDate))+1,Dateadd(Day,-Day(@StartDate)+1,@StartDate)))))
                           
                            If @CurDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.
                           
                            Insert Into @tmp Values(@CurDate,@Name)
                           
                            Set @Pos = @Pos + 3
                            Set @StartDate = Dateadd(Day,1,@CurDate) --// Adding oneday day with current date.
                        End
                    End
                    Else
                    Begin
                        --// Logic:
                        -- In first Step, Date set to first date of the current month of year.
                        -- Eg, '15 nov 2011' => '01 nov 2011'
                        While (@Pos <= Len(@MonthOfYear))
                        Begin                       
                            Set @intYearMonth = Cast(Substring(@MonthOfYear,@Pos + 1,2) As Int)
                            Set @firstofmonth = Dateadd(Day,@DayOfYear,Dateadd(Month,@intYearMonth-1,Dateadd(Month,-Month(Dateadd(Day,-Day(@StartDate)+1,@StartDate))+1,Dateadd(Day,-Day(@StartDate)+1,@StartDate))))
                            --// Second Step, add requested week with '01 nov 2011' -- Suppose 2'nd weeek then (2-1) * 7 => 7 with '01 nov 2011' then => date will be '08 nov 2011'
                           
                            --// Third step ,No of days to add = (7 + which day (Sunday - 1,Monday - 2,Tuesday - 3,Wednesday -4,Thursday - 5,FrNameay - 6,Saturday - 7) - Weekday Of first date) % 7 with second step result.
                           
                            -- Second week of monday november month -- 2(monday)
                            -- Eg, '08 nov 2011' + ((7+2) -  3) = 9-3 = 6%7 = 6 = '14 nov 2011' Second monday
                            Select @CurDate = Dateadd(Day,((7+@DayNo)-Datepart(DW,t.mydate))%7,t.mydate) From
                                    (Select Dateadd(Day,(@WeekNo-1)*7 ,@firstofmonth) As mydate )t

                            If @CurDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.
                                   
                            Insert Into @tmp Values(@CurDate,@Name)
                           
                            Set @Pos = @Pos + 3
                            Set @StartDate = Dateadd(Day,1,@CurDate) --// Adding oneday day with current date.
                        End
                    End
                --end
                --// Setting added month as current month for next process
                Set @StartDate = Dateadd(Year,@interval,Dateadd(Day,-Day(@StartDate)+1,Dateadd(Month,-Month(@StartDate)+1,@StartDate)))
                Set @Pos = 0
            End
            Else If @PatternType = 3 --// Month wise schedule
            Begin
                --insert into @tmp Select DATEADD(MONTH,@interval,@StartDate),@Name
                If @DayOfMonth = 0
                Begin
                    --// year wise second pattern formula logic used.
                    Select @firstofmonth = Dateadd(Day,-Day(@StartDate)+1,@StartDate)
                   
                    Select @CurDate = Dateadd(Day,((7+@DayNo)-Datepart(DW,t.mydate))%7,t.mydate) From
                            (Select Dateadd(Day,(@WeekNo-1)*7 ,@firstofmonth) As mydate )t
                   
                    If     @CurDate <= @EndDate                                           
                        Insert Into @tmp values (@CurDate,@Name)
                End
                Else
                Begin
                    --// current date take as first date of the month and add with given day.
                    Set @CurDate = Dateadd(Day,@DayOfMonth-1,Dateadd(Day,-Day(@StartDate)+1,@StartDate))
                    If     @CurDate <= @EndDate
                        Insert Into @tmp Select @CurDate,@Name
                End
                If @DayOfMonth > 0
                Begin
                    Set @StartDate = Dateadd(Month,@interval,@CurDate)
                End
                Else
                Begin
                    Set @StartDate = Dateadd(Month,@interval,Dateadd(Day,-Day(@StartDate)+1,@StartDate))
                End
            End   
            Else If @PatternType = 1 --// Day wise schedule
            Begin
                If @StartDate <= @EndDate
                Begin   
                --// simple logic -- day add with current date considering interval period.
                    Insert Into @tmp Select @StartDate,@Name
                End
                Set @StartDate = Dateadd(Day,@interval,@StartDate)
            End   
            Else If @PatternType = 2  --// Week wise schedule
            Begin
                While (@Pos <= Len(@Pattern))    --// looping Position less than  given week type.
                Begin
                    If @StartDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.

                    If Left(Upper({fn Dayname (@StartDate)}),3) = Substring(@Pattern,@Pos + 1,3) --// if day character of the current month matching with given week string.
                    Begin
                        If @Pos = 0 Set @CurDate = @StartDate
                       
                        Insert Into @tmp Select @StartDate,@Name
                       
                        Set @Pos = @Pos + 4 --// increasing position.
                    End
                   
                    Set @StartDate = Dateadd(Day,1,@StartDate)
                End
                 Set @StartDate = Dateadd(Week,@interval,@CurDate)
                 Set @Pos = 0 --// We need to clear the position for next level loop.
            End
               
        End
    Fetch Next From Cur Into
                             @Name           
                            ,@StartDate       
                            ,@EndDate       
                            ,@PatternType   
                            ,@Pattern       
                            ,@Interval       
                            ,@DayOfMonth       
                            ,@WeekNo           
                            ,@DayNo           
                            ,@MonthOfYear   
                            ,@DayOfYear       
                            ,@TimeOccurence   
                            ,@NoOfOccurence
    End
    Close cur
    Deallocate cur

    --// Final Result
    Select dt + @TimeOccurence As Date,Name From @tmp
End

GO

Quick Search SQL Server Object

Create Procedure SOS
(
    @ObjectName    VarChar(Max),
    @Object        Char(1) = 'P'
)
As
Begin
    /*
        Execution:    SOS b,p
    */
    Set NoCount On
   
    If Upper(@Object) =  'T' --// Table
        select name 'Table' from sysobjects where xtype = 'U' And name like '%' + @ObjectName + '%'
        order by name
    Else If Upper(@Object) =  'V' --// View
        select name 'View' from sysobjects where xtype = 'V' And name like '%' + @ObjectName + '%'
        order by name
    Else If Upper(@Object) =  'P' --
        select name 'Procedure' from sysobjects where xtype = 'P' And name like '%' + @ObjectName + '%'
        order by name
    Else If Upper(@Object) =  'F' --
        select name 'Function' from sysobjects where xtype = 'FN' And name like '%' + @ObjectName + '%'
        order by name
End



The above Procedure helps to quick search for SQL Server Objecs at developing

For Eg,

SOS 'Master'

It Returns Procedure names that contains searched value.

Result :
--------------
spEmployeeSave
SpEmployeeGet
spEmployeeDelete

SOS 'Master',T

It Returns Table names that contains searched value.

Result :
--------------
EmployeeMaster

SOS 'Master',V

It Returns view names that contains searched value.

Result :
-------------
viEmployee


SOS 'Master',F

It Returns function names that contains searched value.

Result
------
fnEmployeeCodeGet

Quick Data Selection

 Create Procedure S
(
    @Table    VarChar(Max),
    @TopN    VarChar(18)     = '999999999999999999',
    @fld    VarChar(Max) = ''
)
As
Begin
    Set NoCount On
    Declare @s VarChar(Max)

    Set @s = 'Select Top('+ @TopN +') * From ' + @Table

    If @fld <> ''
        Set @s = @s + ' Where ' + @fld
      
    Exec (@s)
End


Mainly the above sp used for quick selection from table or view data at develeping.

Suppose we have a table named Employee. We want show all data from that table.Normaly we just type "Select * from Employee" and it will run.We can simply execute this as " S 'Employee' "
Another , we want to show only the top ten record then execute as " S 'Employee',10 " and finally we need to filter record with Department field. How to do?

just run " S 'Employee',10,"Department = 100"

Sorry for my bad English.

SQL Server Objects Help

The following procedure is used for to get objects information. It executes with parameter as table name the result shows all information about table and instead of table parameter as stored procedure name then it shows the code information.And also we can use for View, Function etc.





Create Procedure [dbo].[H]
(
    @Object    Varchar(Max)
)
As
/*
    Execution : Exec H <Table Name/ SP / View / Fn>
*/

Begin
    Declare @Schema        varchar(1000)
    Declare @ObjectName    varchar(Max)

    Set @Schema = Left(@Object,Case when CharIndex('.',@Object)> 0 then CharIndex('.',@Object) else 1 end -1)
    Set @ObjectName    = SubString(@Object,CharIndex('.',@Object)+1,Len(@Object)-CharIndex('.',@Object))

    If Exists(Select TABLE_NAME From Information_Schema.Tables
                Where TABLE_SCHEMA = Case When @Schema = '' then TABLE_SCHEMA else @Schema end
                And TABLE_NAME = @ObjectName
                And Table_Type = 'BASE TABLE'
             )
        Exec SP_HELP @ObjectName
    Else
        Exec SP_HELPTEXT @ObjectName
End

How to Remove Duplicate Record

CREATE TABLE Test
(
    PK        INT        NOT NULL,
    ID        INT        NOT NULL,
    NAME    VARCHAR(10) NOT NULL
)

INSERT INTO Test VALUES
(1,10,'A'),
(2,20,'B'),
(3,30,'C'),
(4,10,'A'),
(5,30,'C'),
(6,10,'A')


--// Query to get all record set
SELECT * FROM Test

--// To get duplicate Recordset with count of each duplicate record.
SELECT ID,NAME,COUNT(ID) Cnt
FROM Test
GROUP BY ID,NAME HAVING COUNT(ID) > 1


 --Method 1: Using RowCount

--// Deleting duplicate record without one record for each duplicate.
DECLARE @Cnt INT,@ID INT

DECLARE Cur CURSOR FOR
    SELECT ID,COUNT(ID) Cnt FROM Test
    GROUP BY ID,NAME HAVING COUNT(ID) > 1
OPEN Cur
FETCH NEXT FROM Cur INTO @ID,@Cnt          
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @Cnt = (@Cnt - 1)
    SET ROWCOUNT @Cnt
  
    DELETE FROM Test WHERE ID = @ID
  
    SET ROWCOUNT 0
  
FETCH NEXT FROM Cur INTO @ID,@Cnt
END
CLOSE Cur
DEALLOCATE Cur



--// Method 2 : Common Table Expression
--// Deleting duplicate record without one record for each duplicate.

;WITH cte
AS
(
    SELECT ID,DENSE_RANK() OVER (PARTITION BY ID,NAME ORDER BY PK) Ranks FROM Test

)

DELETE FROM cte WHERE Ranks > 1

If not PK field in table , we can write the code with NewID()

;WITH cte
AS
(
    SELECT ID,DENSE_RANK() OVER (PARTITION BY ID,NAME ORDER BY NewID()) Ranks  FROM Test

)

DELETE FROM cte WHERE Ranks > 1

--// Method 3 : Using Correlated Subquery (Removing All Duplicates)
DELETE t FROM Test t WHERE ID IN (SELECT ID FROM Test WHERE ID = t.ID GROUP BY ID,NAME HAVING COUNT(ID) > 1)




--// Method 4 : Using Join (Removing All Duplicates)
DELETE a
FROM Test a
INNER JOIN
(
    SELECT ID,COUNT(ID) Cnt
    FROM Test
    GROUP BY ID,NAME HAVING COUNT(ID) > 1
) t
ON a.ID = t.ID

Find all Tables with Column name

SQL Query that search given column name part and returns the related table , schema and column


DECLARE @SearchColumn AS VARCHAR(255) = '%City%'

SELECT       t.name AS TableName
            ,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
            ,c.name AS ColumnName
FROM        sys.tables         AS t
INNER JOIN  sys.columns        AS c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE @SearchColumn

Table Column depends Object

SQL query that findout objects (Stored Procedure,View,Function,Trigger) used given Table Column on a database.


SELECT Name
FROM syscomments c
JOIN sysobjects o ON c.id = o.id
WHERE TEXT LIKE '%TableName%' AND TEXT LIKE '%ColumnName%'

Kill Databases on SQL Server

Hai,

I wrote a stored procedure for drop all databases from the SQL Server except sql server system databases.

The logic behind the stored procedure that when we execute this stored procedure , firstly a cursor open with all the database name from master db except system databases name. And each cursor loop ,it kill all the process running in sql server that related to corresponding database. So we can easily drop that database from the server.

I just wrote this procedure for an academic interest. Don't try to use in a bad way.


Create Procedure spKillDB

With Encryption
As
Begin
    Set Nocount On;

    Declare @DBName        varchar(50)
    Declare @spidstr    varchar(8000)

    Set @spidstr = ''

    Declare cur Cursor Fast_Forward Read_Only For
        Select name From Master..sysdatabases
        where dbid not in (1,2,3,4)
        Order By dbid Desc
    Open cur
    Fetch Next From cur Into @DBName
    While @@fetch_status <> -1
    Begin
        Select @spidstr=coalesce(@spidstr,',' )+'kill '+Convert(varchar(10), spid)+ '; '
        From Master..sysprocesses Where dbid=db_id(@dbname)

        If Len(@spidstr) > 0
        Begin
            Exec(@spidstr)
        End   
       
        Set @spidstr = 'DROP DATABASE ' + @dbname
        Exec(@spidstr)       
       
    Fetch Next From cur Into @DBName
    End
    Close cur
    Deallocate cur
End 

MSBI Interview Questions

ETL / SSIS Questions:

1. Name some Control flow tools in SSIS
2. What is difference between For Loop and For Each Loop Container?
3. What is FTP Task and how to configure it?
4. What is the use of Transfer SQL Server Objects Task?
5. Explain Lookup Transform

6. What is the difference b/w Pivot and Unpivot Transformations?
7. Difference b/w Merge and Merge Join Transformations?
8. Fuzzy Lookup
9. Fuzzy Grouping
10. What are Package Configurations and why do we need it.
11. Explain how to deploy SSIS Packages
12. What are Connection Manager and how to configure dynamic connections?
13. What is the life cycle of ETL Process?
14. What is the life cycle of ETL Project Development?

SSAS Questions:

1. What is SSAS?
2. Explain UDM (Unified Data Model)?
3. What are Data Sources and Data Source Views?
4. What is a Cube?
5. What is a named Calculation?
6. What is a named Query?
7. What are role-playing dimensions?
8. What is a Partition?
9. What is Multi Dimensional Expressions?
10. What are perspectives?
11. What is a metadata?
12. What is a measure group?
13. What is a difference b/w a derived measure and a calculated measure?

SSRS Questions:

1. Life cycle of SSRS?
2. What is report manger?
3. What is report server? Difference between report manger and report server?
4. What is subscription? Type?
5. How do implement security for report?
6. How many ways are there for deploying the report?
7. What is difference between SSRS 2005 and 2008?
8. What are options there in properity tab in report manger?
9. What is use of execution and History?
10. What is snapshort in SSRS?
11. How do you create share schedule in SSRS?
12. What are data regions?
13. What is difference between table and metrix report?
14. What is use of List data region? is available SSRS 2008?
15. What is expression?
16. How do we write custom code in reports? and ways? and how will you call them in reports?
17. How many collections available in report? What are they?
18. How do you get row by row group in 2008?
19. How do you implement page breaking for Metrix reports?
20. Do you have any idea about markers in ssrs?
21. What is sub report? how will you call?
22. What will you implement Drill down reports?
23. How can we implement optional parameters in ssrs?
24. we are getting blank pages in report after exporting to PDF, how do you avoid?
25. Is there any option to hide the parameter(Filter) based on conditions?
26. Difference between Logical Page an Physical Page in SSRS.
27. How to configure SSRS for Disaster recovery.
28. Describe the role of Encrpytion key in Report Server Configuration manager.
29. User want only to display only pdf as export option in report Manager .. Describe Steps to perform this .
30. Name and Describe few console utilities for SSRS.
31. Name few Endpoints exposed by SSRs 2008.
32. How can you access the Code in You report. Describe the Static and intance based method with example.
33. how to add custome Assemblies to Report.
34. What is Linked Report.
35. What are different types of roles provided by SSRS.
36. Describe different Processing Modes offered by SSRS.
37. When to Use Null Data driven Subscription?
38. What Factors need to take into consideration while desigining a international report ( Localaization ).
39. What are different option to deploy report from Dev to Prod.
40. What are the new Controls / Features added in SSRS 2008 / SSRS 2008 R2
41. How can you monitor the report Usage.
42. How can you add a new report User to report manager.
43. How can you create the dynamaic Datasource. Can a Shared Datasource be Dynamic?
44. A report is Performing poorly . What steps you would take to troubleshoot the Issue.
45. Write an Expression to perform a Division of two Integers fields and to avoid NAN error.
46. have you ever used Rank, Dense Rank Ntile, CTE and Pivot..
47. Is SSRs 2008 dependent on IIS? if not how it perform the operations.