Get working weekdays rage from given date DAY,5,@fromdate means 6 days working week

GO
/****** Object:  StoredProcedure [dbo].[KPI_SplitStartEndDateInTimePeriod]    Script Date: 08/26/2015 21:06:35 ******/--[dbo].[KPI_SplitStartEndDateInTimePeriod_Backup20150826]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 --KPI_SplitStartEndDateInTimePeriod_Backup20150826
alter PROC [dbo].[KPI_SplitStartEndDateInTimePeriod]
(
    @pfromdate DATETIME,
    @ptodate DATETIME,
@pTimePeriod int,
@pOpCode int =0,
@pExtraParameter varchar(100) = ''
)
    AS
    BEGIN

    BEGIN TRY


/*****************Testing*************************

Exec KPI_SplitStartEndDateInTimePeriod '2015-08-03 00:00:00.000','2015-08-31 00:00:00.000',0

******************Testing End*********************/


DECLARE
    @fromdate DATETIME = @pfromdate,
    @todate DATETIME = @ptodate;


  DECLARE  @Result TABLE (
Applicable_starting DATETIME
,Applicable_ending DATETIME
)



SET @fromdate = DATEADD(WEEK,DATEDIFF(WEEK,0,@fromdate),0) --start of week from a year ago
SET @todate = DATEADD(WEEK,DATEDIFF(WEEK,0,@todate),0) --start of current partial week;

DECLARE  @weeks TABLE (
weekID INT IDENTITY(1,1)
,week_starting DATETIME
,week_ending DATETIME
)

WHILE DATEPART(wk, @fromdate) <= DATEPART(wk,@todate)
BEGIN
    INSERT INTO @weeks (week_starting,week_ending) VALUES (@fromdate,DATEADD(DAY,5,@fromdate))
    SET @fromdate = DATEADD(DAY,7,@fromdate)
END


DECLARE @vMaxWeekID INT = (SELECT MAX(weekID) FROM @weeks)

DECLARE @vFirstWeek INT = ISNULL(
(SELECT weekID FROM @weeks WHERE CONVERT(VARCHAR(11),@pfromdate,111) = CONVERT(VARCHAR(11),week_starting,111)AND weekID =1),2)
DECLARE @vLastWeek INT = ISNULL(
(SELECT weekID FROM @weeks WHERE CONVERT(VARCHAR(11),@ptodate,111) = CONVERT(VARCHAR(11),week_ending,111)AND weekID = @vMaxWeekID),(@vMaxWeekID-1))

INSERT INTO @Result(Applicable_starting,Applicable_ending)

SELECT week_starting,week_ending FROM @weeks WHERE weekID BETWEEN @vFirstWeek AND @vLastWeek

SELECT Applicable_starting,Applicable_ending FROM @Result



END TRY
    BEGIN CATCH    
     
    END CATCH



END

complete information from database

select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN '-' ELSE g.name  END [Ref Table],
CASE WHEN h.value is null THEN '-' ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT  so.id,sc.colid,sc.name
      FROM    syscolumns sc
      JOIN sysobjects so ON so.id = sc.id
      JOIN sysindexkeys si ON so.id = si.id
                    and sc.colid = si.colid
      WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id  
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name

EXTENDED METHODS IN jQuery

/******* EXTENDED METHODS IN jQuery ***************/

/*Mehod1: this will remove any inline css attribute.  */
(function ($) {
    $.fn.removeStyle = function (style) {
        var search = new RegExp(style + '[^;]+;?', 'g');

        return this.each(function () {
            $(this).attr('style', function (i, style) {
                return style.replace(search, '');
            });
        });
    };
}(jQuery));

/*Mehod2: this will change existing class with red. pre-requites class name should be postfixed with _red with existing class name e.g. Existing class name .top_lt  so new name for redclass will be .top_lt_red
It will only work when setRedClass will set to 1 e.g. setRedClass(1)-> All class will renamed with redclass setRedClass(0)-> nothing will happen.
*/
(function ($) {
    $.fn.setRedClass = function (baddebtValue) {
        if (baddebtValue == "1") {
            $("[setBDColor='yes']").removeStyle('background');
            $("[setBDColor='yes']").css("background", 'url(../../images/box_hdg_bg_red.gif) left top repeat-x;');
            $("[setBDColor='redclass']").each(function (a, b, c) {
                var className = $(this).attr('class').trim();
                $(this).removeClass(className);
                className = className + '_red';
                $(this).addClass(className);
            });
        }
    };
}(jQuery));
//How to call
$('#abc').find('a').removeStyle('color');
Method2
 $.fn.setRedClass($("[id$=hfIsBaddebt").val());

How to create scripts of stored procedure modified on specific period

SET NOCOUNT ON;
DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(128)
);

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures  where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187'

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187');

WHILE (@sp_count > @count)
BEGIN
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;
  declare @dropIfExists varchar(250) = '  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@sp_name+']'') AND type in (N''P'', N''PC''))
     DROP PROCEDURE [dbo].['+@sp_name+'] '
     PRINT 'GO'
     print @dropIfExists
     PRINT 'GO'
print @text
    DELETE FROM @HelpText;

 
END

get numbers of table in database used by front end application or any user

SELECT
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
FROM
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
ORDER BY last_user_seek DESC
Folowing command will tell the procedure name executed

SELECT TOP 1000 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
,DB_NAME(d.database_id) AS [SCHEMA_NAME]
FROM sys.dm_exec_procedure_stats AS d
where DB_NAME(d.database_id) = 'databaseName'
ORDER BY [last_execution_time] DESC;

update sort order column in datatable.

If we are sorting based on SorteOrder column in Datatable. many time we need to organize columns based on certain condition following is command to update sort Oder column.

UPDATE Table1
SET SortOrder = vx.RowNum
FROM Table1
CROSS APPLY
(

-- step 1 first execute this comamnd and once you agreed for sortign execute update
SELECT ColumnNameToSetSorOrdre,PrimaryKey, ROW_NUMBER() OVER (ORDER BY ISNULL(ColumnNameToSetSorOrdre, 9999999), ColumnNameToSetSorOrdre)  AS RowNum
FROM Table1
WHERE IsActive = 0
-- AND other conditions
) vx
WHERE vx.PrimaryKey = Table1.PrimaryKey
-- and Other conditions If requried

BR
Mahesh

How to get number of rows in the data table

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString

How to add dynamic colums in seelect statement

Create Temp table, and alter the temp table to add new columns and update values.

Create Table #Temp

(

ItemID INT IDENTITY(1,1),

Name varchar(50),

Value varchar(50)

)

DECLARE @vEntityID int = @pPropertyId

IF EXISTS(SELECT * FROM [dbo].[Property] WHERE PropertyID = @vEntityID)

BEGIN

INSERT INTO #Temp (Name,Value)

Select A.[GlobalCodeName],C.[GlobalCodeValue] from [dbo].[GlobalCodes] A

inner join [dbo].[GlobalCodeCategory] b on A.[GlobalCodeCategoryID]=b.[GCCategoryID]

Inner join [dbo].[EntityGlobalCodesValues] c on c.[GlobalCodeID]=A.[GlobalCodesID]

Where C.[EntityName]='PRO' and A.[IsActive]=1  

and C.[EntityID]=@vEntityID

order by [SortOrder]

CREATE TABLE #PropTemp(PropertyId int,[PropertyName] varchar(100),[PropertyDescription] varchar(max),[Address1] varchar(max),[Address2] varchar(max),[MainImageURL] varchar(100), [PublishedOn] varchar(50),[UpdatedOn] varchar(50),[UpdatedTime] varchar(20),[Latitude] varchar(20), [Longitude] varchar(20))

INSERT INTO #PropTemp (PropertyId,[PropertyName],[PropertyDescription],[Address1],[Address2],[MainImageURL],[PublishedOn],[UpdatedOn],[UpdatedTime],[Latitude], [Longitude])

SELECT @vEntityID PropertyId, [PropertyName],[PropertyDescription],[Address1],[Address2],[MainImageURL],CONVERT(VARCHAR, GETDATE(), 107) [PublishedOn],CONVERT(VARCHAR, GETDATE(), 107) [UpdatedOn],convert(varchar, getdate(), 108) [UpdatedTime], [Latitude], [Longitude] from [dbo].[Property] where [PropertyID]  = @vEntityID

DECLARE @vSql varchar(150);

DECLARE @vSqlUpdate varchar(150);

DECLARE @vAttributeLable varchar(150);

DECLARE @vAttributeValue varchar(150);

DECLARE @loopLength int = (select count(*) from #Temp)

DECLARE @index int = 1;

WHILE(@index <= @loopLength)

BEGIN

SET @vSql = '';

SET @vSqlUpdate = '';

SET @vAttributeLable = (SELECT Name FROM #Temp WHERE ItemID = @index)

SET @vAttributeValue = (SELECT Value FROM #Temp WHERE ItemID = @index)

SET @vSql = 'alter table #PropTemp add '+ @vAttributeLable +' varchar(150)'

EXECUTE ( @vSql)

SET @vSqlUpdate = 'update #PropTemp set ' +  @vAttributeLable+' = '''+@vAttributeValue+''' where PropertyId = '+Convert(varchar(50),@vEntityID)

EXEC ( @vSqlUpdate)

SET @index = @index + 1

END

SELECT * FROM #PropTemp

DROP TABLE #PropTemp

DROP TABLE #Temp

END

ELSE

BEGIN

       SELECT 'No record found' AS PropertyDescription

END

Function to Calculate Compound Interest daily basis, penal interest calculation formula

ALTER FUNCTION [dbo].[GetComputedInterestOnDays](
 @Principal DECIMAL(18,2)
,@AnulaInterestRate DECIMAL(18,2)
,@DueDays DECIMAL(18,2)
)
RETURNS DECIMAL(18,2)
AS
-- Returns the stock level for the product.
BEGIN
 
    DECLARE @DaysInYear INT = 360, @InterestFriction DECIMAL(18,10)
 
   SET @Principal = ISNULL(@Principal,0.0);
   SET @AnulaInterestRate = ISNULL(@AnulaInterestRate,0.0);
   SET @InterestFriction = @AnulaInterestRate / @DaysInYear;
   SET @InterestFriction = (100+@InterestFriction)/100;
   SET @InterestFriction = POWER(@InterestFriction,@DueDays)
   RETURN   (@InterestFriction * @Principal)-@Principal;

END;