Wednesday, 13 September 2017

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use: DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]).
you cannot reseed a table variable

A workaround might be to use ROW_NUMBER() instead.

declare @a table (sno int identity,aa nvarchar(100))
insert into @a(aa) values ('testw'),('testwsfsf')

select row_number() OVER (ORDER BY sno) as sno,aa from @a

delete from @a
  
insert into @a(aa) values ('testw'),('testwsfsf')
select row_number() OVER (ORDER BY sno) as sno,aa from @a


Saturday, 19 August 2017

How to select alternate rows from a table in SQL Server

Alternate rows from a table:
CREATE TABLE STUDENTS(
      STUDENT_ID int NULL,
      [STUDENT_NAME] [varchar](50) NULL,
      DOB date NULL,
      DEPARTMENT_ID int NULL,
      DOJ date NULL
      )
GO   
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (1, N'ASHISH', '1990-10-28', 2, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (2, N'MANISH', '1991-06-17', 1, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (3, N'AMIT', '1992-06-30', 3, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (4, N'PANKAJ', '1995-10-05', 4, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (5, N'SUMIT', '1998-04-06', 5, GETDATE())





Method 1:
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(
SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS)
WHERE ROW%2=0



Method 2:
WITH CTE AS
(
SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS
)
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM CTE WHERE ROW%2=0




Method 3:
SELECT  ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* INTO_TEMP_STUDENTS FROM  STUDENTS
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM_TEMP_STUDENTS WHERE  ROW%2=0



Method 4:
 SELECT IDENT=IDENTITY(int, 1,1),* into TEMP_STUDENTS  FROM STUDENTS
 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM TEMP_STUDENTS         WHERE IDENT%2=0


Thursday, 27 July 2017

Number to Work in Excel using MICRO

Function SpellIndian(ByVal MyNumber)
   
 
     '**** Excel UDF to spell Indian Currency -- Rupees and Paise into text
     '**** Indian currency starts off with 1000s, and after that only with 100s
     '**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore) -- 1,00,00,00,000 (Arab)
     '**** (this UDF is based on SpellNumber by Microsoft)
     '****************' Main Function *'****************
   
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Lac "
    Place(4) = " Crore "
    Place(5) = " Arab " ' String representation of amount
    MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
    DecimalPlace = InStr(MyNumber, ".")
     ' Convert Paise and set MyNumber to Rupee amount
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
        If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
        If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
        If Count = 1 And Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            If Count > 1 And Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        Count = Count + 1
     Loop
    Select Case Rupees
    Case ""
        Rupees = "No Rupees"
    Case "One"
        Rupees = "One Rupee"
    Case Else
         '****************************************************************
         'Yogi Anand on 20-Sep-2003
         'modified the following two lines to display "Rupees" to precede
         ' rem'd the first line and added the second line
         '****************************************************************
         'Rupees = Rupees & " Rupees"
        Rupees = "Rupees " & Rupees
       
    End Select
    Select Case Paise
    Case ""
         '****************************************************************
   
       
         '****************************************************************
       
         'Paise = " and No Paise"
         '****************************************************************
       
         '****************************************************************
         'Paise = ""
        Paise = " Only"
    Case "One"
        Paise = " and One Paisa"
    Case Else
        Paise = " and " & Paise & " Paise"
       
    End Select
    SpellIndian = Rupees & Paise
End Function
 '*******************************************
 ' Converts a number from 100-999 into text *
 '*******************************************
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
     'Convert the tens and ones place
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
 '*********************************************
 ' Converts a number from 10 to 99 into text. *
 '*********************************************
Function GetTens(TensText)
    Dim Result As String
    Result = "" ' null out the temporary function value
    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
        Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
        End Select
    Else ' If value between 20-99
        Select Case Val(Left(TensText, 1))
        Case 2: Result = "Twenty "
        Case 3: Result = "Thirty "
        Case 4: Result = "Forty "
        Case 5: Result = "Fifty "
        Case 6: Result = "Sixty "
        Case 7: Result = "Seventy "
        Case 8: Result = "Eighty "
        Case 9: Result = "Ninety "
        Case Else
        End Select
        Result = Result & GetDigit _
        (Right(TensText, 1)) 'Retrieve ones place
    End If
    GetTens = Result
End Function
 '*******************************************
 ' Converts a number from 1 to 9 into text. *
 '*******************************************
Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
    End Select
End Function


Excel Download

Tuesday, 18 July 2017

STEPS TO SAVE YOUR EYES WHILE WORKING ON COMPUTER


STEPS TO SAVE YOUR EYES WHILE WORKING ON COMPUTER
This is the report for all you people who works more and more on computer for long time. Working on computer for long time may cause the symptom of dry eyes which further leads to weakening and straining of eyes. Some tips are given below to avoid getting this ailment:


  1. Blink: while working on computer continuously, blink your eyes or close it for few minutes to get relief from stress.


  1. Logic of 10: in every 10 minutes look away from your computer to 10 feet away object for 110 seconds.


  1. Screen play: adjust the contrast of your screen to avoid the pupil dilatation which is caused due to extra light entering your eyes.


  1. Maintain the difference: keep the sufficient distance from screen, 16- 24 inches is sufficient distance depending on eye sight.


  1. Use less: maintain the speed while typing so that you will get rid of computer soon.







Are You Getting Enough of the Best Vitamins for Men?


HOW TO GET UP EARLY


Friday, 7 July 2017

MVC model validation Example & RegularExpression


  using System.ComponentModel.DataAnnotations;

 [AllowHtml]
  [Required]
  [Display(Name = "IFSC CODE")]
  [RegularExpression(@"[A-Za-z]{4}[0]\d{6}", ErrorMessage = "* Invalid IFSC CODE")]
  //[RegularExpression(@"[A-Za-z]{5}\d{4}[A-Za-z]{1}", ErrorMessage = "* Invalid PAN Number")]
  public string IFSCCODE { get; set; }
  

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use:  DBCC  CHECKIDENT( ' [table_name]' , RESEED, [new_reseed_value]). you cannot reseed...