Friday, May 9, 2008

Know the ID is used in Referenced tables--SqlServer

CREATE VIEW VW_ISREFERENCED_LOC
SELECT
LEVELOFCAREID,
SHORTDESCRIPTION,
CODE,
CAST(
CASE WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITY WHERE CHILDACTIVITY.LevelOfCareID IS NOT NULL AND CHILDACTIVITY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITYHISTORY WHERE CHILDACTIVITYHISTORY.LevelOfCareID IS NOT NULL AND CHILDACTIVITYHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTER WHERE SHELTER.LevelOfCareID IS NOT NULL AND SHELTER.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERINQUIRY WHERE SHELTERINQUIRY.LevelOfCareID IS NOT NULL AND SHELTERINQUIRY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATE WHERE RATE.LevelOfCareID IS NOT NULL AND RATE.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATEHISTORY WHERE RATEHISTORY.LevelOfCareID IS NOT NULL AND RATEHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERHISTORY WHERE SHELTERHISTORY.LevelOfCareID IS NOT NULL AND SHELTERHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILD WHERE FOSTERCHILD.LevelOfCareID IS NOT NULL AND FOSTERCHILD.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILDHISTORY WHERE FOSTERCHILDHISTORY.LevelOfCareID IS NOT NULL AND FOSTERCHILDHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FINANCIALPOSTINGDETAIL WHERE FINANCIALPOSTINGDETAIL.LevelOfCareID IS NOT NULL AND FINANCIALPOSTINGDETAIL.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM MENTALSERVICEREQUEST WHERE MENTALSERVICEREQUEST.LevelOfCareID IS NOT NULL AND MENTALSERVICEREQUEST.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
ELSE 0 END AS INT) AS IsBeingReferenced
FROM LEVELOFCARE

SqlServer

Dispaly the years&rem months when the col is int datatype
----------------------------------------------------

select cast((age1/12) as varchar(20)) +' yrs ' + cast((age1%12) as varchar(20)) + ' months' from age


when col is varchar
-------------------

select cast( (substring(age1,1,2)/12) as varchar(20)) + ' yrs ' +
cast( (substring(age1,1,2)%12) as varchar(20)) + ' months' from age

code when the Months are zero i.e sdn't display when the months are zero
------------------------------------------------------------------------
select AGeDet.years + AGeDet.age1 from
(
select cast(age1/12 as varchar(30)) + ' years ' years ,+ case age1%12
when 0 then ''
ELSE cast(age1%12 as varchar(10))+ ' months'
END AS age1
from age ) AGeDet



Below code used for preparing the Insert script for the Existing rows in a table
----------------------------------------------------------------------------------
Declare @qt as varchar(1000)
set @qt = ''''
print @qt
select 'insert into program values ',
ProgramID, ',', ProgramCode ,',', @qt+ Description + @qt from program


Script used to display the vertical rows as Horizantal rows by comma separated
------------------------------------------------------------------------------

declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + cast(memno as varchar) from sample where sno=1
print @retstr




For updating the column by removing the chars 'September 2004' can be updated as sept 04 of a single col
--------------------------------------------------------------------------------------------------------
update #t1 set period = left(period,4) + ' ' + right(period,2) where Period like '%sept%'



Supply a Date and it Returns with the Month Last Date by considering the Year i.e if its Leap Year
---------------------------------------------------------------------------------------------------

declare @date datetime
set @date = '02/01/2004'
set @date = getdate()

--get first day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, @date), 0),101) as hi

--get last day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1),101) as enddate

Thursday, April 17, 2008

Yes/No Msg Box

asp:Button id="Button1" style="Z-INDEX: 101; LEFT: 440px; POSITION: absolute; TOP: 248px" runat="server"
Text="Button"/asp:Button
div id="divConfMessage" runat="server" style="BORDER-RIGHT:black thin solid; BORDER-TOP:black thin solid; DISPLAY:none; Z-INDEX:200; BORDER-LEFT:black thin solid; BORDER-BOTTOM:black thin solid"
div style="BACKGROUND-COLOR: #6699cc;TEXT-ALIGN: center" id="confirmText" style=
/div
div style="Z-INDEX: 105;HEIGHT: 2%;BACKGROUND-COLOR: white;TEXT-ALIGN: center"
/div
div style="TABLE-LAYOUT: auto; Z-INDEX: 105; TEXT-ALIGN: center"
asp:Button ID="btnConfOK" Runat="server" Text="Yes"/asp:Button
asp:Button ID="btnConfCancel" Runat="server" Text="Cancel"/asp:Button
/div
/div

function DisplayConfirmMessage(msg, width, height)
{
// Set default dialogbox width if null
if (width == null)
divWidth = 180
else
divWidth = width;

// Set default dialogBox height if null
if (height == null)
divHeight = 90
else
divHeight = height;


// Ge the dialogbox object
var divLayer = document.getElementById('divConfMessage');
// Set dialogbox height and width
SetHeightWidth(divLayer)
// Set dialogbox top and left
SetTopLeft(divLayer);

// Show the div layer
divLayer.style.display = 'block';
// Change the location and reset the width and height if window is resized
window.onresize = function()
{
if (divLayer.style.display == 'block')
{
SetTopLeft(divLayer);
SetHeightWidth(divLayer)
}
}
var agt=navigator.userAgent.toLowerCase();
if (agt.indexOf("msie") != -1 || agt.indexOf("firefox") != -1)
{
// Set the dialogbox display message
document.getElementById('confirmText').innerText = msg;
}
else if (agt.indexOf("netscape") != -1)
{

document.getElementById('confirmText').innerHTML = msg;
}

}

function SetTopLeft(divLayer)
{
var agt=navigator.userAgent.toLowerCase();

if (agt.indexOf("msie") != -1 || agt.indexOf("firefox") != -1)
{
// Get the dialogbox height
var divHeightPer = divLayer.style.height.split('px')[0];

// Set the top variable
var top = (parseInt(document.body.offsetHeight) / 2) - (divHeightPer / 2)

// Get the dialog box width
var divWidthPix = divLayer.style.width.split('px')[0];

// Get the left variable
var left = (parseInt(document.body.offsetWidth) / 2) - (parseInt(divWidthPix)
/ 2);
// set the dialogbox position to abosulute
divLayer.style.position = 'absolute';

// Set the div top to the height
divLayer.style.top = top;

// Set the div Left to the height
divLayer.style.left = left;
}

if (agt.indexOf("netscape") != -1)
{
// Get the dialogbox height
var divHeightPer = divLayer.style.height.split('px')[0];
//alert(divHeightPer);

// Set the top variable
//var top = (parseInt(document.body.offsetHeight) / 2) - (divHeightPer / 2)
var top = (parseInt(document.body.offsetHeight) / 2) - (divHeightPer / 2)

// Get the dialog box width
var divWidthPix = divLayer.style.width.split('px')[0];

// Get the left variable
var left = (parseInt(document.body.offsetWidth) / 2) - (parseInt(divWidthPix)
/ 2);
// set the dialogbox position to abosulute
divLayer.style.position = 'relative';

// Set the div top to the height
divLayer.style.top = 200;
//divLayer.style.top = top;
//alert(top);

// Set the div Left to the height
divLayer.style.left = 500;
//divLayer.style.left = left;
//alert(left);
}

}


function SetHeightWidth(divLayer)
{
// Set the dialogbox width
divLayer.style.width = divWidth + 'px';
//alert(divWidth);
// Set the dialogbox Height
divLayer.style.height = divHeight + 'px'
//alert(divHeight);
}

function SetText(txtButton1, txtButton2)
{
// Set display text for the two buttons
if (txtButton1 == null)
document.getElementById('btnConfOK').innerText = txtFirstButton;
else
document.getElementById('btnConfOK').innerText = txtButton1;
//alert(document.getElementById('btnConfOK').innerText);

// Set display text for the two buttons
if (txtButton2 == null)
document.getElementById('btnConfCancel').innerText = txtSecondButton;
else
document.getElementById('btnConfCancel').innerText = txtButton2;
//alert(document.getElementById('btnConfCancel').innerText);
}

function ShowMessage()
{
SetText('Yes', 'No');
var agt=navigator.userAgent.toLowerCase();
//alert(agt);
if (agt.indexOf("msie") != -1)
{
//agt='Internet Explorer';
//alert(agt);
DisplayConfirmMessage('Are You Sure want?', 180, 90);
}

if (agt.indexOf("netscape") != -1)
{
//agt='Netscape';
//alert(agt);
DisplayConfirmMessage('Are You Sure want?', 180, 90);
}




//SetDefaultButton('btnConfOK');

return false;

}


namespace WebApplication1
{
///
/// Summary description for NetScapeChk.
///

public class NetScapeChk : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Button btnConfOK;
protected System.Web.UI.WebControls.Button btnConfCancel;
protected System.Web.UI.HtmlControls.HtmlGenericControl divConfMessage;

private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
Button1.Attributes.Add("onclick","return ShowMessage();");

}
else
{
if ( Request["__EVENTTARGET"] != null)
{
string strchk= Request["__EVENTTARGET"].ToString().ToLower();
}
}
Control c =GetPostBackControl(this.Page);
if(c!=null)
{
if(((System.Web.UI.WebControls.Button)(c)).Text=="Yes")
{
Response.Write("Clicked Yes");
}
else if(((System.Web.UI.WebControls.Button)(c)).Text=="Cancel")
{
Response.Write("Clicked No");
}
}
}

public static Control GetPostBackControl(Page page)
{
Control control = null;

string ctrlname = page.Request.Params.Get("__EVENTTARGET");
if (ctrlname != null && ctrlname != string.Empty)
{
control = page.FindControl(ctrlname);
}
else
{
foreach (string ctl in page.Request.Form)
{
Control c = page.FindControl(ctl);
if (c is System.Web.UI.WebControls.Button)
{
control = c;
break;
}
}
}
return control;
}