March 2014

Attendance UNPIVOT first then Pivot for count present, absent etc in SQL

Sample data download: here

Declare @tempAttendance table(EmpID int, EmpName varchar(50), edate int, ecode varchar(5))

	insert into @tempAttendance 
	SELECT empid, empname, code as 'edate', value as 'ecode'
	FROM [dbo].[hattendance]
	UNPIVOT (
		value FOR code IN (
			 [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
		)
	) unpiv

	--select * from  @tempAttendance

	SELECT empid, empname, P , A, FH, NA
	FROM
	(SELECT empid, empname, ecode
	FROM @tempAttendance )  ps
	PIVOT
	(
	count (ecode)
	FOR Ecode IN
	( P , A, FH, NA )
	) AS pvt order by empid

download sql file from here

March 20, 2014 by SQL Server Uncategorized

Dynamically create columns in a temp table and generate pivot report

Sample Data download: http://goo.gl/lcOf8z

Sample survey submitted data

Generate excel report as below.

Username, Address, City, State, Zip, Question 1, Question 2, Question 3, …..
Prakash, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..
Chirag, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Prakash Rathod
-- Create date: 18-Mar-2014
-- Description:	Generate dynamic pivot report of submited survey
-- Exec spSubmitedSurveyExcelReport 'FCDC5B31-58D6-4C25-92ED-74665E343141'
-- =============================================
ALTER PROCEDURE spSubmitedSurveyExcelReport
	@SurveyID uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	create table #distictCaption (caption nvarchar(1000)) -- create temporary table for storing questions.

	insert into #distictCaption
	Select distinct caption From submitedsurvey s join users u on u.id = s.userid where surveyid=@SurveyID

	Create table #tempSurveyAnswer (userid uniqueidentifier, Name varchar(200), [Address] varchar(500), city varchar(200) , [state] varchar(200), zip varchar(10), dummy bit) -- create temp table for creating dynamic columns, questions become columns

	Declare @Script as Varchar(8000);
	Declare @Script_prepare as Varchar(8000);
	 
	Set @Script_prepare = 'Alter table #tempSurveyAnswer Add [?] varchar(100);'
	Set @Script = ''

	Select
				@Script = @Script + Replace(@Script_prepare, '?', caption)
	From #distictCaption 

	Exec (@Script)
	 
	Alter table #tempSurveyAnswer drop column dummy;

	insert into #tempSurveyAnswer(userid ,Name, [Address], City, [State], Zip) 
	select s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip from submitedsurvey s
	join users u on u.id = s.userid
	where surveyid=@SurveyID group by s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip
	 
-- update temp table
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = ''

	select @sql = @sql + ' UPDATE #tempSurveyAnswer ' + CHAR(13) +
	' SET [' + c.name + '] = (SELECT value ' + CHAR(13) +
	' FROM submitedsurvey' + CHAR(13) +
	' where surveyid=@I and #tempSurveyAnswer.userid = submitedsurvey.userid and submitedsurvey.caption = ''' + c.name + ''') '+ CHAR(13) 
	from tempdb.sys.columns c
	where object_id = object_id('tempdb..#tempSurveyAnswer') and name'userid' and name'Name' and name  'Address' and name'city' and name'state' and
	name'zip';

	print(@sql)

	exec sp_executesql @sql,N'@I uniqueidentifier',@I=@SurveyID;

	Select * from #tempSurveyAnswer;

	drop table #distictCaption
	drop table #tempSurveyAnswer
END
GO

March 18, 2014 by SQL Server Uncategorized

Add Comma into number using javascript


<script>

addCommas = function(input){
  // If the regex doesn't match, `replace` returns the string unmodified
  return (input.toString()).replace(
    // Each parentheses group (or 'capture') in this regex becomes an argument 
    // to the function; in this case, every argument after 'match'
    /^([-+]?)(0?)(\d+)(.?)(\d+)$/g, function(match, sign, zeros, before, decimal, after) {

      // Less obtrusive than adding 'reverse' method on all strings
      var reverseString = function(string) { return string.split('').reverse().join(''); };

      // Insert commas every three characters from the right
      var insertCommas  = function(string) { 

        // Reverse, because it's easier to do things from the left
        var reversed           = reverseString(string);

        // Add commas every three characters
        var reversedWithCommas = reversed.match(/.{1,3}/g).join(',');

        // Reverse again (back to normal)
        return reverseString(reversedWithCommas);
      };

      // If there was no decimal, the last capture grabs the final digit, so
      // we have to put it back together with the 'before' substring
      return sign + (decimal ? insertCommas(before) + decimal + after : insertCommas(before + after));
    }
  );
};

</script>

How to call this function?

$.fn.addCommas = function() {
$(this).each(function(){
$(this).text(addCommas($(this).text()));
});
};

March 7, 2014 by JavaScript Uncategorized