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)