• Asp.Net
  • MVC
  • C#
  • SQL Server
  • JavaScript
  • Learn from Life

Prakash Rathod

~ Passion is not a logic; it's an emotion.

Prakash Rathod

Monthly Archives: March 2014

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

20 Thursday Mar 2014

Posted by Prakash in SQL Server, Uncategorized

≈ Comments Off on 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

Dynamically create columns in a temp table and generate pivot report

18 Tuesday Mar 2014

Posted by Prakash in SQL Server, Uncategorized

≈ Comments Off on 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 [email protected]

	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 [email protected] 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 [email protected] 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',@[email protected];

	Select * from #tempSurveyAnswer;

	drop table #distictCaption
	drop table #tempSurveyAnswer
END
GO


Add Comma into number using javascript

07 Friday Mar 2014

Posted by Prakash in JavaScript, Uncategorized

≈ Comments Off on 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()));
});
};

Recent Posts

  • Create a custom autocomplete control in ASP.NET MVC
  • Nth Highest Salary
  • Generate x509 certificate in pem, cer and pfx and export public key
  • Send an image (stored as base64 string) inline in email
  • Put/Delete http verb not working server

Categories

  • Android
  • Asp.Net
  • BANKING
  • c#.net
  • Crystal Report
  • HTML5 CSS3
  • iPhone
  • JavaScript
  • Life – Spiritual – Reality
  • Lightswitch
  • Links
  • MAC
  • MVC
  • Netoworking
  • Silverlight
  • SQL Interview Questions
  • SQL Server
  • Uncategorized
  • Version Controls
  • Windows Store

Archives

  • August 2019
  • May 2019
  • April 2018
  • October 2017
  • August 2017
  • April 2017
  • March 2017
  • February 2017
  • December 2016
  • April 2016
  • January 2016
  • November 2015
  • January 2015
  • December 2014
  • October 2014
  • September 2014
  • June 2014
  • April 2014
  • March 2014
  • February 2014
  • December 2013
  • November 2013
  • October 2013
  • September 2013
  • July 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • October 2012
  • September 2012
  • July 2012
  • May 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009

© 2021 Prakash Rathod