Friday, 26 April 2013

Code for Login Page

Code for Login Page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs" Inherits="gati_test.Login" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Login</title>
    <style type="text/css">
        .design
        {
            text-align: right;
            font-family: 'Segoe UI';
            font-size: 12px;
        }
        .design a
        {
            color: #0787b6;
        }
        .style1
        {
            width: 145px;
        }
        .round
        {
     -moz-border-radius: 20px;
    -webkit-border-radius: 20px;
    -khtml-border-radius: 20px;
    border-radius: 20px;
   
   }
    
        .style2
        {
            color: #000000;
        }
        .style3
        {
            color: #003399;
            font-weight: bold;
        }
    
    </style>
    <link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="page">
        <div class="header_login">
            <%--<div class="title" style="margin-top: 60px;">
                <a href="Default.aspx">
                    <img src="images/yt_logo.png" /></a>
            </div>--%>
            <br />
            <br /><br />
        </div>
        <div>
            <%--   <img src="images/login_form.jpg" />--%>
      <br /><br /><br /><br />
        <center>
        <fieldset class="round" style="width:400px; height:240px; background-color:#4682B4">
       
        <center>
        <asp:Label ID="lbl" runat="server" Text="Login"
                style="font-weight: 700; color: #FFFFFF; font-size: x-large"></asp:Label>
            <table cellpadding="5px"
                style="margin-top: 19px; margin-left:0px; height: 170px; width: 100%;">
                <tr>
                    <td style="text-align:center">
                        <asp:Label ID="lbl_name" runat="server" Text="User Name :" CssClass="loginlable"
                            ForeColor="Black"></asp:Label>
                    </td>
                    <td >
                        <asp:TextBox ID="txt_name" runat="server" CssClass="logininput"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Required"
                            ValidationGroup="v" ControlToValidate="txt_name" style="color: #FF0000"></asp:RequiredFieldValidator>
                    </td>
                </tr>
                <tr>
                    <td style="text-align:center">
                        <asp:Label ID="lbl_password" runat="server" Text="Password :"
                            CssClass="loginlable" ForeColor="Black"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="txt_password" runat="server" TextMode="Password" CssClass="logininput"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Required"
                            ValidationGroup="v" ControlToValidate="txt_password"
                            style="color: #FF0000"></asp:RequiredFieldValidator>
                    </td>
                </tr>
                <tr>
                <td class="style1">
                    &nbsp;</td>
                <td>
                        <asp:ImageButton ID="ImageButton1" runat="server" ValidationGroup="v"
                            ImageUrl="~/images/login_button.png" Height="36px"
                            onclick="ImageButton1_Click" />
                        <asp:ImageButton ID="Imgbtn_signup" runat="server"
                            ImageUrl="~/images/signup-button-i6.png" Height="38px"
                            onclick="Imgbtn_signup_Click" />
                    </td>
                </tr>
                <tr>
                    <td class="style1">
                    </td>
                    <td align="left">
                        <asp:Label ID="lblerror" runat="server" style="color:red" Text="Label"></asp:Label>
                    </td>
                </tr>
                </table>
           </center>
        </fieldset>
        </center>
      
        </div>
        <%--    <div class="contant_sinup">
       
        </div>--%>
        <br /><br />
        <div class="clear">
            <span class="style2">Design and Developed by </span>
            <a href="http://www.online24x7.in/" target="_blank" class="style3">Online24x7</a></div>
    </div>
    </form>
</body>
</html>

.cs code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace gati_test
{
    public partial class Login : System.Web.UI.Page
    {
        CS_registration _Lgn = new CS_registration();
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
                lblerror.Visible = false;
        }

        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            if (txt_name.Text.Trim().Length > 0 && txt_password.Text.Trim().Length > 0)
            {
                _Lgn.UserId = txt_name.Text.Trim();
                _Lgn.Password = txt_password.Text.Trim();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                dt = _Lgn.Login();
                if (dt!=null && dt.Rows.Count>0)
                {
                   
                    Session["username"] = dt.Rows[0]["username"].ToString();
                    Session["empid"] = dt.Rows[0]["EmpId"].ToString();

                    Response.Redirect("Default.aspx");
          
                }
                else
                {
                    lblerror.Visible = true;
                    lblerror.Text = "Login failed for user  :  " + txt_name.Text;
                }
                clear();
            }
          
        }
        void clear()
        {

            txt_name.Text = txt_password.Text = string.Empty;
        }

        protected void Imgbtn_signup_Click(object sender, ImageClickEventArgs e)
        {
            if (Imgbtn_signup != null)
            {
                Response.Redirect("UserRegistration.aspx");
            }
        }
    }
}

Function for Generate Auto Unique Id and Procedure for Genereate UserId and Password

Function for Generate Auto Unique Id and Procedure for Genereate UserId and  Password
-- =============================================                             
-- Author:  <salik ram maurya>                             
-- =============================================                             
CREATE PROCEDURE [dbo].[getuniqueid]      
(@UniqueNumber Bigint out)             
AS                             
BEGIN                             
 -- SET NOCOUNT ON added to prevent extra result sets from                             
 -- interfering with SELECT statements.                             
 SET NOCOUNT ON;                       
                            
  IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserUniquid]') AND type in (N'U'))                             
  BEGIN                             
   CREATE TABLE [dbo].UserUniquid(                             
    [ReceiptNo] [bigint] NOT NULL                             
   ) ON [PRIMARY]                
  END                             
                 
                           
  Declare @Num  bigint               
                             
  Select @Num = MAX([ReceiptNo]) from UserUniquid                             
                             
  IF(@Num IS NULL)                             
 SET @Num = 1                
  ELSE                             
    SET @Num = @Num+1;               
                   
  INSERT INTO UserUniquid ([ReceiptNo]) VALUES (@Num)                           
                               
                 
  --Declare @Session  char(9)               
  --Select @Session=Convert(Varchar,(StartsFrom-2000))+'-'+Convert(Varchar,(EndsOn-2000)) From SessionMaster Where SessionID =@SessionID                                 
                 
  --Declare @ReceiptNo Varchar(500)                             
  --Select @ReceiptNo =@Num --'india'+RIGHT(REPLICATE('0',5)+CONVERT(varchar(8),@NUM),8)               
 Set @UniqueNumber=@Num                 
 END    
---
CREATE  proc [user_registration]           
@Fname varchar(50),      
@Mname varchar(50),        
@Lname varchar(50),     
@Dob datetime,     
@Contactno bigint,       
@EmailID varchar(100),   
@Empcode varchar(50)        
as           
begin     
         
declare @userid varchar(50),@Password varchar(50)        
              
SELECT @userid =LTRIM(@Fname)+''+LOWER(@Lname)--+ LEFT(NewID(),2)                   
   
DECLARE @Upper INT; DECLARE @Lower INT                    
                       
SET @Lower = 1111                  
SET @Upper = 9999                   
SELECT @Password =CONVERT(nvarchar(50), year('1985'))+''+CONVERT(nvarchar(50), ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) )    
   
if not exists(Select 1 From Registration Where UserId=@userid and Empcode=@Empcode And IsActive=1)       
begin    
insert into  Registration(Empcode,Firstname,Middlename,LastName,Dob,ContactNo,EmailId,UserId,Password,EnteredOn)       
values(@Empcode,@Fname,@Mname,@Lname,@Dob,@Contactno,@EmailID,@userid,@Password,GETDATE())    
select IDENT_CURRENT('Registration')            
end          
else   
begin   
Declare @Unique Bigint           
Exec getuniqueid @Unique out     
insert into  Registration(Empcode,Firstname,Middlename,LastName,Dob,ContactNo,EmailId,UserId,Password,EnteredOn)       
values(@Empcode,@Fname,@Mname,@Lname,@Dob,@Contactno,@EmailID,@userid+Convert(varchar(4000),@Unique),@Password,GETDATE())     
select IDENT_CURRENT('Registration')           
           
end   
end

Wednesday, 20 February 2013

Export From Multiple Grid to Excel and PDF Formate

Export From Multiple Grid to Excel and PDF Formate


protected void btn_Exportxecel_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=Export.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.xls";

            StringWriter StringWriter = new System.IO.StringWriter();
            HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "Personal Detail" + "</b></td></tr></table>");
            Grid_PersonalDetail.RenderControl(HtmlTextWriter);
            HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Br);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "Address Deteail" + "</b></td></tr></table>");
            gridadress.RenderControl(HtmlTextWriter);
            HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Br);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "Invoice Detail" + "</b></td></tr></table>");
            grd_Invoicedetail.RenderControl(HtmlTextWriter);
            HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Br);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "Product Detail" + "</b></td></tr></table>");
            grd_product.RenderControl(HtmlTextWriter);
            HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Br);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "AMC/CMC Detail" + "</b></td></tr></table>");
            grd_AMC.RenderControl(HtmlTextWriter);
            HtmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Br);

            HtmlTextWriter.Write("<table width='100%'><tr><td colspan='9' align='center'><b>" + "Service Detail" + "</b></td></tr></table>");
            grid_Service.RenderControl(HtmlTextWriter);
          

            Response.Write(StringWriter.ToString());
            Response.End();
        }

        protected void btn_exporttoPdf_Click(object sender, EventArgs e)
        {
            CssClass_GridView(gridadress);

            StringWriter sw0 = new StringWriter();
            HtmlTextWriter hw0 = new HtmlTextWriter(sw0);
            if (gridadress != null && gridadress.Rows.Count > 0)
            {
                hw0.Write("<table width='100%'><tr ><td><font size='5' color='white' align='left' style='text-align:left;'>");
                hw0.Write("Expense Record");
                hw0.Write("</font></td></tr>");
                hw0.Write("<tr><td>");
                hw0.Write("<font size='1' align='left' style='text-align:left;'>");
                StringWriter sw2 = new StringWriter();
                //EnableEventValidation = "false";
                //this.EnableViewState = False;
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9' align='center'><b>" + "Personal Detail" + "</b></td></tr></table>");
                Grid_PersonalDetail.RenderControl(new HtmlTextWriter(sw2));
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9'></td></tr></table>");
                gridadress.RenderControl(new HtmlTextWriter(sw2));
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9' align='center'><b>" + "Invoice Deteail" + "</b></td></tr></table>");
                grd_Invoicedetail.RenderControl(new HtmlTextWriter(sw2));
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9' align='center'><b>" + "Product Deteail" + "</b></td></tr></table>");             
                grd_product.RenderControl(new HtmlTextWriter(sw2));
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9' align='center'><b>" + "AMC/CMC Deteail" + "</b></td></tr></table>");
                grd_AMC.RenderControl(new HtmlTextWriter(sw2));
                sw2.Write("<table width='100%' Font-Bold='true' Font-Size='Large'><tr><td colspan='9' align='center'><b>" + "Service Deteail" + "</b></td></tr></table>");
                grid_Service.RenderControl(new HtmlTextWriter(sw2));
                string html = sw2.ToString();
                hw0.Write(html);
                hw0.Write("</font>");
                hw0.Write("</td></tr></table>");
                StringReader sr = new StringReader(sw0.ToString().Replace("%20", " "));
                Document pdfDoc = new Document(PageSize.A4, 9f, 9f, 9f, 0f);
               //
            
              
             
                iTextSharp.text.html.simpleparser.HTMLWorker htmlparser = new iTextSharp.text.html.simpleparser.HTMLWorker(pdfDoc);
                string filename = Session["UserName"].ToString() + "_Details_" + DateTime.Now.Ticks + ".pdf";
                string file_Path = "~\\ExportFiles\\" + filename;
                PdfWriter.GetInstance(pdfDoc, new FileStream(Server.MapPath(file_Path), FileMode.Create));
                pdfDoc.Open();
                htmlparser.Parse(sr);
                pdfDoc.Close();
                // Response.Redirect("../ExportFiles/" + filename);
                Response.Redirect("../ExportFiles/" + filename, "_blank", "menubar=0,scrollbars=1,width=780,height=900,top=10");
            }
            else
            {
                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "alert", "alert('There Is No Data To Export Here. Please Fill Data  Firstly....')", true);
            }
        }

        protected void CssClass_GridView(GridView NewGrid)
        {
            NewGrid.Style.Add("width", "100%");
            NewGrid.Attributes.Add("ShowFooter", "True");
            NewGrid.Attributes.Add("CellPadding", "1");
            NewGrid.Font.Size = 10;
            NewGrid.ForeColor = System.Drawing.Color.FromArgb(51, 51, 51);
            NewGrid.GridLines = GridLines.None;
            NewGrid.ShowFooter = true;
            NewGrid.AlternatingRowStyle.BackColor = System.Drawing.Color.White;
            NewGrid.EditRowStyle.BackColor = System.Drawing.Color.FromArgb(36, 97, 191);
            NewGrid.FooterStyle.BackColor = System.Drawing.Color.FromArgb(80, 124, 209);
            NewGrid.FooterStyle.Font.Bold = true;
            NewGrid.FooterStyle.ForeColor = System.Drawing.Color.White;
            NewGrid.HeaderStyle.BackColor = System.Drawing.Color.FromArgb(80, 124, 209);
            NewGrid.HeaderStyle.Font.Bold = true;
            NewGrid.HeaderStyle.ForeColor = System.Drawing.Color.White;
            NewGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
            NewGrid.RowStyle.BackColor = System.Drawing.Color.FromArgb(239, 243, 251);
            NewGrid.RowStyle.HorizontalAlign = HorizontalAlign.Left;
        }

Tuesday, 15 January 2013

Sql Cursor

Sql Cursor

DECLARE @ProductID decimal
DECLARE @getProductID cursor
SET @getProductID = CURSOR FOR
SELECT AddTaxRate
FROM salesnew
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID

Bind Accrodian Dynamically

 Bind Accrodian Dynamically

<html>
<head><Title>Dynamically bind Accrodian</title>
</head>
<body>
<tr style="vertical-align: top;">
                    <td valign="top" style="width: 5%;">
                    </td>
                    <td valign="top" style="width: 90%;" align="center">
                        <cc1:Accordion ID="Accordion1" runat="server" CssClass="accordion" Width="100%" HeaderCssClass="accordionHeader"
                            Height="100%" HeaderSelectedCssClass="accordionHeaderSelected" ContentCssClass="accordionContent">
                        </cc1:Accordion>
                    </td>
                    <td valign="top" style="width: 5%;">
                    </td>
                </tr>
</head>
</body>

void bindData()
    {
        dt = new DataTable();
        cmd = new SqlCommand("selectEachCust_TablesID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MainID", MainID);
        con.Open();
        da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        con.Close();
        if (dt.Rows.Count > 0)
        {
            bindAccordian(dt);
        }
    }

    void bindAccordian(DataTable dt1)
    {
        int i = 0;
        for (int j = 0; j < dt1.Columns.Count; j++)
        {
            if ((dt1.Rows[0][j].ToString()!="") && (Convert.ToInt64(dt1.Rows[0][j].ToString()) > 0))
            {
                AccordionPane p1 = new AccordionPane();

                p1.Style.Add("overflow", "scroll");
                //p1.Style.Add("Height", "100%");
                p1.ID = "AccordionPane" + i;
                Label lblHeader = new Label();
                lblHeader.ID = "lblHeader" + i;
                HiddenField hdnFld_tblName = new HiddenField();
                hdnFld_tblName.ID = "hdnFld_tblName" + i;
                if (dt1.Columns.Count > 1 && (tablename == "Sales" || tablename == "Service"))
                {
                    if (j == 0)
                    {
                        lblHeader.Text = "BSO Sales Order";
                        hdnFld_tblName.Value = "tblBsoSalesOrder";
                    }
                    else if (j == 1)
                    {
                        lblHeader.Text = "Billing Address";
                        hdnFld_tblName.Value = "tblBsoBillingAddress";
                    }
                    else if (j == 2)
                    {
                        lblHeader.Text = "Delivery Address";
                        hdnFld_tblName.Value = "tblBsoDeliveryAddress";
                    }
                    else if (j == 3)
                    {
                        lblHeader.Text = "Product Details";
                        hdnFld_tblName.Value = "tblBsoProducts";
                    }
                    else if (j == 4)
                    {
                        lblHeader.Text = "Spare Details";
                        hdnFld_tblName.Value = "tblBsoSpares";
                    }
                    else if (j == 5)
                    {
                        lblHeader.Text = "Charges";
                        hdnFld_tblName.Value = "tblBsoCharges";
                    }
                  
                      
                }
                else
                {
                    if (j == 0 && tablename == "AMC")
                    {
                        lblHeader.Text = "AMC Details";
                        hdnFld_tblName.Value = "tblAmc";
                    }
                    if (j == 1 && tablename == "AMC")
                    {
                        lblHeader.Text = "All AMC Details";
                        hdnFld_tblName.Value = "tblAmcDetails";
                    }
                    else if (j == 0 && tablename == "Query")
                    {
                        lblHeader.Text = "Query Details";
                        hdnFld_tblName.Value = "tblQuery";
                    }
                }
                p1.HeaderContainer.Controls.Add(lblHeader);
                DataTable dt2 = new DataTable();
                cmd = new SqlCommand("selectEachTableDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (j != 3 && j != 4)
                    cmd.Parameters.AddWithValue("@MainID", dt1.Rows[0][j].ToString());
                else
                    cmd.Parameters.AddWithValue("@MainID", MainID);
                cmd.Parameters.AddWithValue("@TableName", hdnFld_tblName.Value.ToString());
                con.Open();
                da = new SqlDataAdapter(cmd);
                da.Fill(dt2);
                con.Close();
                if (hdnFld_tblName.Value == "tblBsoProducts")
                {
                    dt3 = dt2;
                    grv_Product.DataSource = dt2;
                    grv_Product.DataBind();
                    grv_Product.Visible = true;
                    p1.ContentContainer.Controls.Add(grv_Product);
                }
                else if (hdnFld_tblName.Value == "tblBsoSpares")
                {
                    dt4 = dt2;
                    grv_Spare.DataSource = dt2;
                    grv_Spare.DataBind();
                    grv_Spare.Visible = true;
                    p1.ContentContainer.Controls.Add(grv_Spare);
                }
              
                else if (dt2.Rows.Count < 2)
                {
                    dt6 = dt2;
                    GridView grid_AmcNew = new GridView();

                    grid_AmcNew.DataSource = dt2;
                    grid_AmcNew.DataBind();
                    grid_AmcNew.Visible = true;
                    CssClass_GridView(grid_AmcNew);
                    p1.ContentContainer.Controls.Add(grid_AmcNew);
                }
                Accordion1.Panes.Add(p1);
                i++;
            }
        }

Tuesday, 8 January 2013

Onkeypress textbox and value show in label

 Onkeypress on textbox and value show in label

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<link class="jsbin" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.js"></script>
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.js"></script>
<meta charset=utf-8 />
<title>JS Bin</title>
<!--[if IE]>
  <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<style>
  article, aside, figure, footer, header, hgroup,
  menu, nav, section { display: block; }
</style>
<script type="text/javascript">
    $(function () {
        $('#txt1').keyup(function () {
         $('#output1').text($(this).val());
         $('#p1').text($(this).val());
        });
        $('#txt2').keyup(function () {
            $('#output2').text($(this).val());
            $('#p2').text($(this).val());
        });
        $('#txt3').keyup(function () {
            $('#output3').text($(this).val());
            $('#p3').text($(this).val());
        });
    });

</script>
</head>
<body>
<form id="form1" runat="server">
 <%-- <input id="txt1" type="text" />--%>
  <asp:TextBox ID="txt1" runat="server"></asp:TextBox><br />
  <asp:Label ID="output1" runat="server"></asp:Label><br />
   <asp:TextBox ID="txt2" runat="server"></asp:TextBox><br />
  <asp:Label ID="output2" runat="server"></asp:Label><br />
   <asp:TextBox ID="txt3" runat="server"></asp:TextBox><br />
  <asp:Label ID="output3" runat="server"></asp:Label>
  <br /><br />
 
   <div>
   <p id="p1"></p><br />
    <p id="p2"></p><br />
     <p id="p3"></p>
   </div>

  </form>
</body>
</html>

Wednesday, 2 January 2013

Procedure for Calculate Age

Procedure for Calculate Age


alter procedure pn_getDatedifference
(
@startdate datetime
)
as
begin

declare @monthToShow int
declare @dayToShow int
declare @enddate datetime
--set @startdate='01/21/1934'
set @enddate=getdate()

if(DAY(@startdate) > DAY(@enddate))
begin
set @dayToShow=0

if (month(@startdate) > month(@enddate))
begin
set @monthToShow= (12-month(@startdate)+ month(@enddate)-1)
end
else if (month(@startdate) < month(@enddate))
begin
set @monthToShow= ((month(@enddate)-month(@startdate))-1)
end
else
begin
set @monthToShow= 11
end
set @monthToShow= convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))-1
if(@monthToShow<0)
begin
set @monthToShow=0
end



declare @amonthbefore integer
set @amonthbefore=Month(@enddate)-1
if(@amonthbefore=0)
begin
set @amonthbefore=12
end

if(@amonthbefore in(1,3,5,7,8,10,12))
begin
set @dayToShow=31-DAY(@startdate)+DAY(@enddate)
end
if(@amonthbefore=2)
begin
IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR YEAR( @enddate ) % 400 = 0
begin
set @dayToShow=29-DAY(@startdate)+DAY(@enddate)
end
else
begin
set @dayToShow=28-DAY(@startdate)+DAY(@enddate)
end
end
if(@amonthbefore in (4,6,9,11))
begin
set @dayToShow=30-DAY(@startdate)+DAY(@enddate)
end

end
else
begin
set @monthToShow=convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))
if(month(@enddate)< month(@startdate))
begin
set @monthToShow=12+(month(@enddate)-month(@startdate))
end
else
begin
set @monthToShow= (month(@enddate)-month(@startdate))
end

set @dayToShow=DAY(@enddate)-DAY(@startdate)
end


SELECT
--FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],
--@monthToShow as monthToShow ,@dayToShow as dayToShow ,
convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age
end