DECLARE
SET @FROMDATE DATETIME,@TODATE DATETIME @FROMDATE='2012-07-01'SET @TODATE='2012-07-10'
CREATE TABLE #DATES(SELECTEDDATE DATETIME)
DECLARE @FINALCOL NVARCHAR(MAX),@ACOL NVARCHAR(MAX), @columns nvarchar(max)DECLARE @FINALCOLIN NVARCHAR(MAX)SET @FINALCOL =''SET @FINALCOLIN=''SELECT @ACOL ='',@columns=''DECLARE
DECLARE @TEMPDATE DATETIME @Total Varchar(max)set @Total =''SET @TEMPDATE =@FROMDATEWHILE @TEMPDATE <=@TODATEBEGIN
SET @columns=@columns+'['+CONVERT(VARCHAR(10),@TEMPDATE,120)+'],'--SET @Total =@Total +SET @ACOL=@ACOL+'ISNULL(['+CONVERT(VARCHAR(10),@TEMPDATE,120)+'],0)+'
SET @FINALCOLIN =@FINALCOLIN + ' CONVERT(VARCHAR(10),ISNULL(['+CONVERT(VARCHAR(10),@TEMPDATE,120)+'],0)) AS '+'['+CONVERT(VARCHAR(10),@TEMPDATE,120)+'],' PRINT @FINALCOLININSERT INTO #DATES(SELECTEDDATE) VALUES(@TEMPDATE )SET @TEMPDATE =DATEADD (DAY,1,@TEMPDATE )END
SET @columns=SUBSTRING(@columns,1,LEN(@columns)-1)SET @FINALCOLIN =SUBSTRING(@FINALCOLIN,1,LEN(@FINALCOLIN)-1)SET
@ACOL=SUBSTRING(@ACOL,1,LEN(@ACOL)-1)select
( CONVERT(VARCHAR(10),#DATES.SELECTEDDATE,120) SELECTEDDATE,OUTERVALUEIN.created_by,sum(ISNULL (OUTERVALUEIN.timetaken,0)) as timetaken INTO #FINALDATA from #DATES right OUTER JOIN select request_dt =convert(varchar(10),request_dt,120), timetaken=sum(convert(decimal(5,2),isnull(timetaken,0))),created_by from t_crm_requestwhere request_dt >=@FROMDATE and request_dt <=@TODATE and callplaced=1group
by convert(varchar(10),request_dt,120),created_by ) OUTERVALUEINON CONVERT(VARCHAR(10),OUTERVALUEIN.request_dt,120)=CONVERT(VARCHAR(10),#DATES.SELECTEDDATE ,120)group
by #DATES.SELECTEDDATE,OUTERVALUEIN.created_byDECLARE @qry NVARCHAR(MAX) SET @qry='select Created_By,SELECTEDDATE, timetaken from #FINALDATA order by Created_By'SET
(SELECT
#FINALDATA.SELECTEDDATE,
#FINALDATA.Created_By,
#FINALDATA.timetaken
FROM #FINALDATA
)DATA
PIVOT(
SUM(DATA.timetaken )
FOR
DATA.SELECTEDDATE
IN('
) PVT' @qry ='SELECT * ,'+@ACOL+'AS Total FROM + @columns + ')print @qryexec
(@qry)drop table #DATESdrop table #FINALDATA
Tuesday, July 10, 2012
Sql Query For Subgroup and Total of Subgroup
select created_by,convert(varchar(10),request_dt,120) as request_dt, timetaken=sum(convert(decimal(5,2),isnull(timetaken,0))),GROUPING([created_by]) AS 'CreatedAgg' ,GROUPING([request_dt]) AS 'ReqdtAgg'from t_crm_requestwhere request_dt >='2012-07-01' and request_dt <='2012-07-10' and callplaced=1group by created_by, request_dt WITH ROLLUP
Tuesday, July 3, 2012
Validation in JavaScript Using Regular Expression
<script type="text/javascript">
function ValidateInput(evt)
{
var valRegExp = new RegExp("^[a-zA-Z0-9]");
if (valRegExp.test(String.fromCharCode(evt.which)))
{
return true;
}
else
{
return false;
}
}
</script>
</head>
<body>
<label for="txtUsername">Username</lable>
<input type="text" id="txtUsername" placeholder="Enter Username" onkeypress="return ValidateInput(event)"/>
</body>
function ValidateInput(evt)
{
var valRegExp = new RegExp("^[a-zA-Z0-9]");
if (valRegExp.test(String.fromCharCode(evt.which)))
{
return true;
}
else
{
return false;
}
}
</script>
</head>
<body>
<label for="txtUsername">Username</lable>
<input type="text" id="txtUsername" placeholder="Enter Username" onkeypress="return ValidateInput(event)"/>
</body>
Friday, June 22, 2012
Message box using JQuery
<script language="javascript" type="text/javascript">
var flag = true;
function fnCheckValue()
{
ShowMessage(if (flag == false) {"Please fill proper values in Red colour boxes");// alert("Please fill proper values in Red colour boxes");
}
function
$(
setTimeout(
setTimeout(
}
$(
setTimeout(
}
ShowMessage(message) {"<div class='msg' id='dvMsg'><table width='100%'><tr><td width='90%'>" + message + "</td><td width='10%' valign='top' style='float:right;text-decoration:none;'><a href='javascript:void();' onclick='closethis(myMessage);'>x</a></td></tr></table></div>").attr('id', 'myMessage').appendTo('body');"$('#myMessage').fadeOut('slow');", 2900);"$('#myMessage').remove();", 4000);function closethis(id) {'#myMessage').fadeOut('slow');"$('#myMessage').remove();", 1000); Add css
.msg{
background-color: #FFB6C1;text-align: left;position: absolute;top: 35%;left: 40%;margin-left: -70px;width: 350px;height: auto;padding: 10px 10px 8px 10px;-moz-border-radius: 10px;-webkit-border-radius: 10px;-khtml-border-radius: 10px;border-radius: 10px;Call on button click
}
OnClientClick="return fnCheckValue();"
var flag = true;
function fnCheckValue()
{
ShowMessage(if (flag == false) {"Please fill proper values in Red colour boxes");// alert("Please fill proper values in Red colour boxes");
}
function
$(
setTimeout(
setTimeout(
}
$(
setTimeout(
}
ShowMessage(message) {"<div class='msg' id='dvMsg'><table width='100%'><tr><td width='90%'>" + message + "</td><td width='10%' valign='top' style='float:right;text-decoration:none;'><a href='javascript:void();' onclick='closethis(myMessage);'>x</a></td></tr></table></div>").attr('id', 'myMessage').appendTo('body');"$('#myMessage').fadeOut('slow');", 2900);"$('#myMessage').remove();", 4000);function closethis(id) {'#myMessage').fadeOut('slow');"$('#myMessage').remove();", 1000); Add css
.msg{
background-color: #FFB6C1;text-align: left;position: absolute;top: 35%;left: 40%;margin-left: -70px;width: 350px;height: auto;padding: 10px 10px 8px 10px;-moz-border-radius: 10px;-webkit-border-radius: 10px;-khtml-border-radius: 10px;border-radius: 10px;Call on button click
}
OnClientClick="return fnCheckValue();"
Subscribe to:
Posts (Atom)