Table valued function using cross apply

able Valued Function using cross apply

If you want to pass the parameter of table column in the table value function we can use the cross apply. Also if you want to pass dynamic value to table valued function we can use the cross apply

Example

Consider a table with employee leave status, which has employee id, from date, to date and approved. We can use cross apply to return a particular date of the employee leave status using table valued function as explained in below code.

Now, create a table leave and insert records

1. createtable leave
2. (
3. LeaveId INT IDENTITY(1,1)
4. ,EmployeeId INT
5. ,FromDate Datetime
6. ,ToDate Datetime
7. ,[Status] VARCHAR(50)
8. )
9. Go
10. insertinto leave values(1,'2011-10-25','2011-10-27','Approved')
11. insertinto leave values(2,'2011-10-26','2011-10-27','Approved')
12. insertinto leave values(3,'2011-10-27','2011-10-27','Rejected')
13. insertinto leave values(1,'2011-11-01','2011-11-01','Approved')

The result of the leave table is as follows

LEAVE ID	EMPLOYEEID	FROM DATE	TO DATE	STATUS
1	1	2011-10-25	2011-10-27	Approved
2	2	2011-10-26	2011-10-27	Approved
3	3	2011-10-27	2011-10-27	Rejected
4	1	2011-11-01	2011-11-01	Approved

But as per our requirement we need a result as shown below

Employee Id	Date	Status
1	011-10-25 00:00:00.000	Approved
1	2011-10-26 00:00:00.000	Approved
1	2011-10-27 00:00:00.000	Approved
2	2011-10-26 00:00:00.000	Approved
2	2011-10-27 00:00:00.000	Approved
3	2011-10-27 00:00:00.000	Rejected
1	2011-11-01 00:00:00.000	Approved

We can use Table valued function with CROSS APPLY to resolve the issue.

Step: 1

The first step is to create function

1. CREATEFUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
2. returnstableas
3. return (
4. WITH date_range (startdate) AS (
5. select @startdate
6. UNIONALLSELECT DATEADD(DAY, 1, startdate)
7. FROM date_range
8. WHERE DATEADD(DAY, 1, startdate) <= @enddate
9. )
10. SELECT startdate FROM date_range
11. );

The above function will retrieve dates between two dates.

1. SELECT * FROM dbo.ExplodeDates ('2011-10-25', '2011-10-27')

If we run the above select query it will display result as follows

Start date
2011-10-25 00:00:00.000
2011-10-26 00:00:00.000
2011-10-27 00:00:00.000

Step: 2

We need to use cross apply to pass data to the function to get the specified result. It won’t work if we pass an argument statically.

1. GO
2. SELECT EmployeeId, startdate, [Status] FROM leave
3. CROSS APPLY
4. DBO.ExplodeDates(leave.FromDate, leave.ToDate)
5. GO

If you execute the above the result is achived.

Source : https://sql-programmers.com/table-valued-function-using-cross-apply

How to conditionally set the number of decimal places to display for a number, in Crystal Reports

Symptom

• How to conditionally change the number of decimals?
     
• In the Crystal Reports, a number field that returns decimal places is inserted into a report.
  When previewing the report, the decimal places in this number field contain unnecessary zeroes.
  How can you suppress the unnecessary zeroes in these fields and still leave up to two decimal places for fields that require them?
       
  For example:
     
  A number field is placed on a Crystal Report and is not formatted the way you want.
    
  The field displays: 
    
     1.25 
     2.50 
     8.00
    
  But you want the numeber to display like:
      
     1.25
     2.5
     8

Environment

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

Resolution

• To conditionally suppress unnecessary zero values to the right of the decimal for numeric field in Crystal Reports:
      
1. Right-click the number field and select: 'Format Field'
     
2. In the 'Format Editor' dialog box, under the 'Number' tab,  click the 'Customize' button.
      
3. In the 'Decimals' drop-down box, select the maximum number of decimal places to be displayed. 
       
   If you are not sure what the maximum number of decimals will be, click the maximum (1.0000000000).   
       
4. In the 'Rounding' drop-down box, select the same number of decimal places chosen in the 'Decimal' drop-down box.
         
5. Click the 'X+2' button to the right of the `Decimals` drop-down box and enter the following formula:
     
      WhilePrintingRecords;
      numberVar counter := 0;
      numberVar numericValue := <INSERT YOUR NUMERIC FIELD HERE>;
      While truncate(numericValue) < numericValue do
      (
          numericValue := numericValue * 10;
          counter := counter + 1
      );
      counter;
       
       
6. Save this formula. Click 'OK' to close the 'Custom Style' dilaog box, and then click 'OK' to close the 'Format Editor' dialog box.
      
   Now, when you preview the report, unnecessary zeroes to the right of the decimal will not appear. 
       
   For examples:
         
• 12.30 will display as: 12.3 
• 12.38 will display as: 12.38 
• 12.00 will display as: 12

source : https://apps.support.sap.com/sap/support/knowledge/public/en/1212821

StripNonNumerics

USE [TSWDATA_ClientCustom]
GO

/****** Object:  UserDefinedFunction [dbo].[StripNonNumerics]    Script Date: 6/28/2018 11:57:18 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[StripNonNumerics]
(
  @Temp varchar(255)
)
RETURNS varchar(255)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

GO

Crystal Reports Comparison of Crystal and Basic Formula Syntax

Crystal Reports
Comparison of Crystal and Basic Formula Syntax
Overview
Crystal Reports version 8 and higher offers two different syntaxes for writingformulas: the Crystal syntax and the Basic syntax.This paper describes some of the differences between the two syntaxes andprovides general information on the control structures that are available for thesyntaxes

How to Get (Access) Gridview Footer Controls id in JavaScript

Introduction: 

Here I will explain how to access or get asp.net gridview footer control ids or values in JavaScript or find controls inside gridview in JavaScript or find asp.net gridview footer controls (textbox, dropdownlist, checkbox, radio button etc..) values or ids in JavaScript.

Description: 

In previous posts I explained bind data to textbox control in asp.net gridview, Cascading Dropdownlist in inside of asp.net gridview, populate one dropdown based on another dropdown in asp.net, bind data to dropdownlist in asp.net gridview and many articles relating to gridview, asp.net, c#. Now I will explain how to get asp.net gridview footer control ids or values in JavaScript.

To get asp.net gridview footer control ids or values in JavaScript we need to write the code like as shown below

Syntax to Get Gridview Footer Controls in JavaScript

<script type="text/javascript"> function GetGridFooterRowvalues() { var fuid = document.getElementById('<%=((TextBox)gvUserInfo.FooterRow.FindControl("txtUserId")).ClientID %>'); if (fuid != null && funame != null && fueducation != null) { alert('UserId:' + fuid.value) } } </script>

If you want see it in complete example open your aspx page and write the following code

<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>get gridview footer control id in javascript</title> <script type="text/javascript"> function GetGridFooterRowvalues() { var fuid = document.getElementById('<%=((TextBox)gvUserInfo.FooterRow.FindControl("txtUserId")).ClientID %>'); var funame = document.getElementById('<%=((TextBox)gvUserInfo.FooterRow.FindControl("txtUserName")).ClientID %>'); var fueducation = document.getElementById('<%=((TextBox)gvUserInfo.FooterRow.FindControl("txtEducation")).ClientID %>'); if (fuid != null && funame != null && fueducation != null) { alert('UserId:' + fuid.value + ';UserName:' + funame.value + ';Education:' + fueducation.value) } } </script> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="gvUserInfo" runat="server" AutoGenerateColumns="false" ShowFooter="true"> <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /> <Columns> <asp:TemplateField HeaderText="UserId"> <ItemTemplate> <asp:Label id="lblUserid" runat="server"  Text='<%# Eval("UserId") %>' /> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtUserId" runat="server" Text="100" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="UserName"> <ItemTemplate> <asp:Label id="lblUsername" runat="server" Text='<%# Eval("UserName") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtUserName" runat="server" Text="SureshD" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Education"> <ItemTemplate> <asp:Label id="lblEducation" runat="server" Text='<%# Eval("Education") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtEducation" runat="server" Text="B.Tech" /> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button ID="btnGet" Text="Get Footer Values" runat="server"OnClientClick="GetGridFooterRowvalues()" /> </div> </form> </body> </html>

After that write the following code in code behind

C# Code

using System; using System.Data; public partial class Default3 : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             DataTable dt = new DataTable();             dt.Columns.Add("UserId", typeof(Int32));             dt.Columns.Add("UserName", typeof(string));             dt.Columns.Add("Education", typeof(string));             dt.Rows.Add(1, "Suresh Dasari", "B.Tech");             dt.Rows.Add(2, "Rohini Dasari", "Msc");             dt.Rows.Add(3, "Madhav Sai", "MS");             dt.Rows.Add(4, "Praveen", "B.Tech");             dt.Rows.Add(6, "Sateesh", "MD");             dt.Rows.Add(7, "Mahesh Dasari", "B.Tech");             dt.Rows.Add(8, "Mahendra", "CA");             gvUserInfo.DataSource = dt;             gvUserInfo.DataBind();         }     } }

VB.NET Code

Imports System.Data Partial Class _Default     Inherits System.Web.UI.Page     Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load         If Not IsPostBack Then             Dim dt As New DataTable()             dt.Columns.Add("UserId", GetType(Int32))             dt.Columns.Add("UserName", GetType(String))             dt.Columns.Add("Education", GetType(String))             dt.Rows.Add(1, "Suresh Dasari", "B.Tech")             dt.Rows.Add(2, "Rohini Dasari", "Msc")             dt.Rows.Add(3, "Madhav Sai", "MS")             dt.Rows.Add(4, "Praveen", "B.Tech")             dt.Rows.Add(6, "Sateesh", "MD")             dt.Rows.Add(7, "Mahesh Dasari", "B.Tech")             dt.Rows.Add(8, "Mahendra", "CA")             gvUserInfo.DataSource = dt             gvUserInfo.DataBind()         End If     End Sub End Class

Demo