Suppress zero in Crystal Report

Right Click on Report Field, go to Format Object, Click on Number, Check the Suppress if Zero Field. Report will not show zero values.

Crystal  Report  Shown as below

Show/ Hide a data field and a label for some records in crystal report

From Crystal report designer,
select the field you want to hide + Right mouse click to open Format object + Common + Check the Suppress and open format formula editor and an expression like:

{tableName.Amount}=0

how do you reorder rows in a table based on a TR attribute using JQuery

A table with rows similar to the following.
These rows get updated from time to time via jquery calls.

 Using jquery,
 how would I construct a function that reorders these table rows based on the CSSclass value putting the highestCSSclass value rows at the top and the lowest CSSclass  value rows at the bottom? There could be up to n rows in the table.

<table id="dtmarksList">
<tbody>
<tr>
 <th >S.NO</th><th >Name</th><th >Marks</th><th >Subject</th>
</tr>
<tr>
 <td> 1</td>
<td> test</td>
<input name="txtmarks1" type="text"  id="txtmarks1" Class="markvalue">
<td> Science</td>
</tr>
</tr>
 <td> 2</td>
<td> test2</td>
<input name="txtmarks2" type="text"  id="txtmarks2" Class="markvalue">
<td> Maths</td>
</tr>

</tr>
 <td> 3</td>
<td> test3</td>
<input name="txtmarks3" type="text"  id="txtmarks3" Class="markvalue">
<td> Maths</td>
</tr>
......
.....
......
</tr>
 <td> n</td>
<td> testn</td>
<input name="txtmarksn" type="text"  id="txtmarksn" Class="markvalue">
<td> Maths</td>
</tr>
</tbody>
</table>

Method: 

$("input[type=text].markvalue").blur(function () {

var $table=$('#dtmarksList');

var rows = $table.find('tr').get();

rows.sort(function(a, b) {

var txtmark=$(a).find('.markvalue').val();

if (txtmark) return  0;    /* Small Value for sorting */ 

return 1;     /* High value for sorting */

});

$table.children('tbody').html(rows);

/*

$.each(rows, function(index, row) {

$table.children('tbody').append(row);

});
*/

});

Method 2:

            $("#dtmarksList tr input[type=text].markvalue").each(function (i) {
                if ($(this).val() != '')
                {
                  $($("#dtmarksList").find('tr:first')).after($("#" + $(this).attr("id")).closest('tr'));
                } 
            });

Creating a Date Range from Multiple Rows Based on a Single Date

Solution (SQL 2005): Using a Self-JOIN

There are probably other ways to establish an exact match for the JOIN criteria of a self-JOIN that would be compatible back to SQL 2000, but we’ll simplify the task somewhat by using ROW_NUMBER(), which was introduced in SQL 2005.


-- Solution (SQL 2005): Using a self-JOIN (ROW_NUMBER establishes exact match criteria)

WITH ProductPrices AS
(
    SELECT ProductID, EffectiveStartDT
        ,rn=ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY EffectiveStartDT)
    FROM dbo.ProductPrices
-- add filtering Condition
)
SELECT a.ProductID, a.EffectiveStartDT, EffectiveEndDT=b.EffectiveStartDT
FROM ProductPrices a
LEFT JOIN ProductPrices b ON a.ProductID = b.ProductID AND a.rn =b.rn-1
-- add filtering Condition
ORDER BY a.ProductID, a.EffectiveStartDT;


Result :
ProductID EffectiveDate        EffectiveEndDT
7103         2016-12-20           2017-01-01
7103        2017-01-01           2017-01-03
7103        2017-01-03           2017-03-28
7103        2017-03-28           2017-04-04
7103        2017-04-04           NULL

Disable Enable Trigger SQL server

Use the following Commands :-

ALTER TABLE your_table_name DISABLE TRIGGER your_trigger_name


ALTER TABLE your_table_name ENABLE TRIGGER your_trigger_name

SELECT from 3rd comma in string(Get the 4th occurrence of a value in a delimited string)

Syntax
declare @v varchar(max) = 'Text1,Text2,Text3,Text4'

select substring(@v, CHARINDEX(',', @v, CHARINDEX(',', @v, CHARINDEX(',', @v)+1)+1)+1, len(@v))


Result :
Text4

How to split a comma-separated value to columns in sql server (How to split a single column values to multiple column values? )

Syntax:
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable](
[id] [int] NULL,
[FullName] [varchar](5000) NULL
) ON [PRIMARY]

GO

Insert into mytable values (1,'Test,King,Demo')


mytable:

Id     FullName
---  ----------------
1     Test,King,Demo


ResultTable:

To separate the comma delimited string into 3 columns

Id    FullName                  Name      Surname         Last
---   ------------                -------        -----------          ------
1     Test,King,Demo           Test        King            Demo



Method 1 :-

Use the CommonTableExpression(CTE)

-- Define the CTE expression name and column list.
;WITH Split_Names (ID,Name, xmlname)
AS
-- Define the CTE query.
(
    SELECT ID,
    Name,
    CONVERT(XML,'<Names><name>'
    + REPLACE(YourCommaSeparatedValueColumn,',', '</name><name>') + '</name></Names>') AS xmlname
      FROM YourTableName
)
-- Define the outer query referencing the CTE name.
 SELECT ID,   
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name, 
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname,
 xmlname.value('/Names[1]/name[3]','varchar(100)') AS Last
 FROM Split_Names

Method 2:-

 SELECT DISTINCT id
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS SurName
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Last
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(fullname, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM mytable) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

unable to pass # character via querystring

To create a querystring with a # in it, from jquery, and read that value accurately in C# when the page loads



Use

encodeURIComponent()

before passing that.

Like:
var id="test # 232425 #454646"
"www.example.com?id=" + encodeURIComponent(id);
or just encode the entire url.

Result :
www.example.com?id=test # 232425 #454646";

How to get a list of database objects changed for specific time sql server

To get List of database objects changed for last 30 days from currect date.

SELECT type, name,create_date,modify_date

FROM    sys.objects

WHERE   modify_date >= DATEADD(DAY, -30, GETDATE())

order by [TYPE] asc

parse float with Comma Decimal Values

javascript's parseFloat doesn't take a locale parameter. So you will have to replace comma(,), dollar sign($) with empty

Example :
 Full Cost  Value : $1,500.00

convert the commas to Normal value(decimal value).

var fullcost = parseFloat($("#fullcost").text().replace('$', '').replace(',', '').replace(',', '.')); //1500.00

fullcost =parseFloat(1500.00)

Result value : 1500.00

The parseFloat() function parses a string and returns a floating point number.

This function determines if the first character in the specified string is a number. If it is, it parses the string until it reaches the end of the number, and returns the number as a number, not as a string.

Note: Only the first number in the string is returned!

Note: Leading and trailing spaces are allowed.

Note: If the first character cannot be converted to a number, parseFloat() returns NaN.

   

Common Table Expressions

Common Table Expressions are also called CTEs. This feature was introduced with SQL Server 2005. The CTE is preferred to use as an alternative to a Subquery/View.

Sub-queries

A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.

Example
Select Name,Age, employeeID   
From table1   
Where employeeID in   
( 
   Select employeeID from table2 where salary >=7500 /******Sub Query******/ 
)

use a CTE

With aliastablename (column1) 
 
AS 
 
(Query)

example
With table1CTE(EmployeeID) 
 
AS   
(Select employeeID from table1 where salary >=7500)

Advantages
CTE improves the code readability.
CTE provides recursive programming.
CTE makes code maintainability easier.
Though it provides similar functionality as a view, it will not store the definition in metadata.

Find all tables containing column with specified name

select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%TableColumnName%'
order by TABLE_NAME


Select * from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE '%TableColumnName%'

How can I list all foreign keys referencing a given table in SQL Server?

EXEC sp_fkeys 'TableName'

Add $ symbol to column values

To add $ symbol to column values and convert the column values to western number system

Example 1.

declare @value int = 4255

select '$ ' + replace(convert(varchar(100), convert(money, @value), 1), '.00', '')

-- output: $ 4,255

Example 2

declare @value Decimal(18,2)= 5488.00

select '$ ' + convert(varchar(100), convert(money, @value), 1)

-- output: $ 5488.00

What are the pros/cons of using a synonym vs. a view?

They are different things. A synonym is an alias for the object directly, a view is a construct over one or more tables.

Some reasons to use a view:

May need to filter, join or otherwise frig with the structure and semantics of the result set

May need to provide legacy support for an underlying structure that has changed but has dependencies that you do not want to re-work.

May provide security where some of the contents of the table should be visible to a class of users but not all. This could involve removing columns with sensitive information or filtering out a subset of the records.

May wish to encapsulate some business logic in a form that is accessible to users for reporting purposes.

You may wish to unify data from more than one source.

Reasons to use a synonym:

You may wish to alias an object in another database, where you can't (or don't want to) hard code the reference to the specific database.

You may wish to redirect to a source that changes over time, such as an archive table.

You want to alias something in a way that does not affect the query optimiser.

syntax: -


Create View vwtablename

as 

Select *  from tablename  where CreatedOn>'2014-01-26'

union

Select *  from tablename  where CreatedOn<='2014-01-26'

go
select * from vwtablename
go
Drop  View vwtablename

Synonym is just a second name of table used for multiple link of database.

View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

Alias is temporary and used with one query. Synonym is permanent and not used as alias.


With a view, I can

hide columns
add predicates (WHERE clause) to restrict rows
rename columns
give a column name to a SQL expression
With a synonym, I can:

reference objects in other schemas and databases without qualifying the name

Javacript: Set or Update a URL/QueryString Parameter, and update URL

Method1 :-

// Explicitly save/update a url parameter using HTML5's replaceState().
function updateQueryStringParam(param, value) {
  baseUrl = [location.protocol, '//', location.host, location.pathname].join('');
  urlQueryString = document.location.search;
  var newParam = key + '=' + value,
  params = '?' + newParam;

  // If the "search" string exists, then build params from it
  if (urlQueryString) {
    keyRegex = new RegExp('([\?&])' + key + '[^&]*');
    // If param exists already, update it
    if (urlQueryString.match(keyRegex) !== null) {
      params = urlQueryString.replace(keyRegex, "$1" + newParam);
    } else { // Otherwise, add it to end of query string
      params = urlQueryString + '&' + newParam;
    }
  }
  window.history.replaceState({}, "", baseUrl + params);
}


Method2:-

var Inputurl ="http://www.example.com/MP/Test.aspx?ID=2&Name=Kumar&Dept=N&Loc=Chennai"

// Function Implemented
 var OutputUrl=replaceKeyValue(Inputurl , "Name", "Karthik");

// Output Url
Result Url as below

http://www.example.com/MP/Test.aspx?ID=2&Name=Karthik&Dept=N&Loc=Chennai
 
// Javascript Function

function replaceKeyValue(url, keyName, newValue) {
            var urlParts = url.split("?");
            if (urlParts.length == 2) {
                var queryString = urlParts[1];
                queryString = queryString.split("&");
                var newQueryString = [];
                if (queryString.length > 0) {
                    $.each(queryString, function (i, e) {
                        var keyValue = e.split("=");
                        if (keyValue.length == 2 && $.trim(keyValue[0]) == keyName) {
                            keyValue[1] = newValue
                        }
                        newQueryString.push(keyValue.join("="));
                    })
                }
                return urlParts[0] + "?" + newQueryString.join("&");
            }
            return url;
        }