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>
Subscribe to:
Posts (Atom)