European Windows 2019 Hosting BLOG

BLOG about Windows 2019 Hosting and SQL 2019 Hosting - Dedicated to European Windows Hosting Customer

European SQL Server 2022 Hosting :: Unlocking Business Potential: The Foundations of Successful Data Resource Administration

clock July 23, 2025 09:56 by author Peter

Data is a strategic asset in today's information-driven economy, not just a byproduct of operations. Organizations may innovate, maximize performance, and make well-informed decisions that support growth when data is handled methodically. However, achieving this promise necessitates a disciplined approach to Data Resource Management (DRM) in addition to technology investment.

Managing Data as a Strategic Resource
Treating data as a valuable resource begins with a mindset shift. Like financial or human capital, data deserves deliberate stewardship. This includes:

  • Inventory and Classification: Cataloging data assets based on sensitivity, usage, and business value.
  • Ownership and Accountability: Defining clear roles for data stewardship across departments.
  • Value Realization: Linking data to outcomes—such as improved customer experience, reduced risk, and operational efficiency.

By embedding these principles in organizational culture, enterprises align data usage with business strategy.

Data Governance: Building Trust Through Structure
Governance ensures that data remains secure, compliant, and aligned with organizational objectives. Key components include:

  • Policy Frameworks: Establishing rules for data usage, sharing, and retention.
  • Compliance Assurance: Meeting regulatory requirements (GDPR, HIPAA, etc.) across jurisdictions.
  • Decision Rights: Clarifying who can make decisions about data quality, access, and modification.

Effective governance is not a one-time initiative—it is an evolving framework that adapts to technological and regulatory change.

Data Quality Assurance and Cleansing

High-quality data is foundational to reliable insights. Quality assurance and cleansing techniques address structural and semantic inconsistencies:

  • Data Profiling: Evaluating datasets to detect anomalies and redundancies.
  • Validation Rules: Implementing checks for accuracy, completeness, and consistency.
  • Cleansing Strategies: Removing duplicate records, correcting formatting errors, and standardizing entries.

Proactive assurance mechanisms reduce analytical blind spots and improve downstream data reliability.

Data Integration Across Ecosystems
Seamless integration of data across platforms is vital for a unified business view. Effective integration practices include:

  • ETL/ELT Processes: Extracting, transforming, and loading data from heterogeneous sources into a central repository.
  • API-Driven Architecture: Facilitating real-time data flow between applications and services.
  • Semantic Harmonization: Aligning data definitions and schemas to ensure consistency across systems.

Integrated data supports enterprise-wide decision-making and eliminates silos.

Data Storage Optimization
Efficient storage strategies ensure scalability and cost-effectiveness. Key considerations involve:

  • Tiered Storage Models: Assigning data to storage media based on access frequency and business value.
  • Compression Techniques: Reducing file size without losing fidelity to optimize space.
  • Retention and Archival Policies: Automating data lifecycle transitions to minimize clutter and ensure compliance.

Storage optimization balances performance with sustainability, especially in data-intensive environments.

Data Visualization Techniques
Visual representation converts complex datasets into intuitive insights. Common techniques include:

  • Dashboards and Reports: Real-time overviews of KPIs tailored for executive and operational users.
  • Interactive Charts: Drill-down capabilities using bar graphs, pie charts, scatter plots, and heatmaps.
  • Geospatial Mapping: Visualizing data across geographic dimensions for logistics and market intelligence.

Effective visualization bridges the gap between raw data and strategic understanding.

Ensuring Integrity and Accessibility
Data integrity and accessibility are twin engines of operational continuity and user empowerment:

  • Data Integrity: Protects against unauthorized changes, corruption, or loss through encryption, backups, and versioning.
  • Accessibility: Ensures users can retrieve necessary data—securely and efficiently—through robust access control and search capabilities.

Well-structured access policies preserve trust while empowering business users.

Strategic Stewardship in the Digital Age
Organizations must make investments in comprehensive DRM procedures that cut across departmental lines if they want to fully utilize data. Data becomes a dynamic force for innovation and resilience when it is supported by robust governance, reliable quality standards, smooth integration, and insightful visualizations.

HostForLIFEASP.NET SQL Server 2022 Hosting



SQL Server Hosting - HostForLIFE :: Moving Information Between Tables in Different Databases

clock July 17, 2025 08:14 by author Peter

One common task that a Database Administrator performs is the movement of data from one database to another. This scenario often arises when organizations need to consolidate data from multiple sources, migrate data to a data warehouse, or perform data analytics on a set of data stored in different databases. A classic scenario is ingesting data from one table in a database to another table in a different database, especially when both tables have identical columns. In this article, we'll explore the steps to accomplish this task effectively in SQL Server Management Studio

Establish the Database Connection
Before you can transfer data between tables in different databases, you must ensure that you have the necessary permissions and connectivity to access both databases, which often involves configuring connection settings, including server addresses, authentication credentials, and database names. 

Understand the Tables
It is important to ensure that you have a clear understanding of both the source and destination tables. In our case, the tables have the same columns, which simplifies the process. However, you should still verify that the column names, data types, and constraints match between the two tables. Any discrepancies can lead to data integrity issues during the transfer.

Write & Execute the SQL Query
To perform the data transfer, you'll need to write the SQL query by using the INSERT INTO ... SELECT the statement, which is commonly used for that purpose. The query should specify the source table, the columns to be transferred, and the destination table. Once you've written the SQL query, execute it by clicking on the Execute button. In our example, we want to transport the data in the DimCustomer table of the SalesData database into the DimCustomer table in the data warehouse database. Both tables have the same table structure.

In the screenshot below, we wrote a simple query to fetch all the records from that table.
SELECT * FROM DimensionCustomer;

In the next screenshot below, we switched to the destination database: DataWarehouse, and we selected all the columns in the dimCustomer table without any data ingested thus far. This is necessary to verify the structure of the destination table.

In the next screenshot below, we wrote and executed the query, and from the message displayed, a total of 18,484 records were inserted into the destination table.

INSERT INTO DataWarehouse.dbo.DimCustomer (
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
)
SELECT
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
FROM SalesData.dbo.DimensionCustomer;

Verify the Results
After the query execution is complete, it's essential to verify the results. To verify, we executed a simple query mentioned below, and all the records were returned.
SELECT * FROM dimCustomer;

HostForLIFEASP.NET SQL Server 2022 Hosting

 



European SQL Server 2022 Hosting :: ASP.NET's DataList Control for SQL CRUD Operations

clock July 10, 2025 09:28 by author Peter

Since this is all about using Stored Procedures to conduct actions on SQL tables, I will walk you through each step of using a DataList control to insert, select, edit, cancel, update, and remove data.

First Step: SQL Database
To add a data value to an EmployeeData table, first build a table in a database store in the manner shown below.

Table: EmployeeData
    Create Table EmployeeData (  
    EmpID int identity (1, 1) Primary Key,  
    EmpName varchar(30),  
    Contact nchar(15),  
    EmailId nvarchar(50)  
    )  

Stored Procedure:
Also create an insert procedure, a select procedure, an update procedure and a delete procedure as in the following.  
    Select procedure for DataList control.
        create procedure sp_FillData  
        As  
        Begin  
        set nocount on;  
        select EmpID, EmpName, Contact, EmailID from EmployeeData  
        End  
    Insert procedure.
        create procedure sp_InsertEmployeeData @EmpName varchar(30),  
        @Contact nchar(15),  
        @EmailId nvarchar(50) As Begin  
        set  
        nocount on;  
        Insert into dbo.EmployeeData (EmpName, Contact, EmailId)  
        values  
        (@EmpName, @Contact, @EmailId) End  
    Delete procedure.
        Create procedure sp_DeleteEmployeeData  
        @EmpID int  
        As  
        Begin  
        set nocount on;  
        Delete from EmployeeData where EmpID=@EmpID  
        End  
    Select procedure.
        create procedure sp_SelectEmployeeData  
        @EmpID int  
        As  
        Begin  
        set nocount on;  
        select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID  
        End  
    Update procedure.
        Create procedure sp_UpdateEmployeeData @EmpID int,  
        @EmpName varchar(30),  
        @Contact nchar(15),  
        @EmailId nvarchar(50) As Begin  
        set  
        nocount off;  
        UPDATE  
        Employeedata  
        SET  
        EmpName = @EmpName,  
        Contact = @Contact,  
        EmailId = @EmailId  
        WHERE  
        EmpID = @EmpID End  

Step 2: Maintain database connection
Provide the database connection string inside the project's Web.Config as in the following:
    <connectionStrings>  
       <add name="connstring" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
    </connectionStrings>  


Step 3: UI design
In Visual Studio create a UI design using the following procedure:
Go to Solution Explorer.
Right-click on the project and click Add tab.
Click Add New Item as in the following:

Figure 1: Add web form 
Now I will write the design code inside DataList.aspx. In this page add some TextBox controls, Button controls and a DataList Control.
Also add two button controls and TextBox controls inside the DataList data row update and delete with DataList event Item commands. The following is the UI design code.
    <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="DataList.aspx.cs" Inherits="UI_DataList" %>  
      
    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
         
    </asp:Content>  
      
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
        <div>  
            <fieldset style="width: 269px" ><legend><b>DataList Example</b></legend>  
                <div style="width: 250px; background-color: #99FF66;">  
            <asp:Table runat="server">  
                <asp:TableRow>  
                    <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server"></asp:TextBox ></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox ID="txtContact" runat="server"></asp:TextBox></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell>Email id</asp:TableCell><asp:TableCell><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnSave" Text="Add Record" runat="server" OnClick="btnSave_Click" /></asp:TableCell>  
                </asp:TableRow>  
            </asp:Table>  
            </div>  
            </fieldset>  
            <br />  
        <fieldset style="width: 535px"><legend><b>Employee Information</b></legend>  
        <div style="background-color: #66FF66">  
        <asp:DataList ID="DataListEmp" runat="server"   
                 DataKeyField="EmpID"   
                 OnDeleteCommand="DataListEmp_DeleteCommand"   
                 OnEditCommand="DataListEmp_EditCommand"  
                 OnUpdateCommand="DataListEmp_UpdateCommand"   
                 OnCancelCommand="DataListEmp_CancelCommand" Width="527px" >  
                <HeaderTemplate>  
                <table><tr style="background-color: #800000; color: #FFFFFF">  
                <th>Name</th><th>Contact</th><th>Email ID</th><th>Action</th></tr>  
                </HeaderTemplate>  
                <ItemTemplate>  
                <tr >  
                <td><%# DataBinder.Eval(Container.DataItem, "EmpName")%></td>  
                <td><%# DataBinder.Eval(Container.DataItem,"Contact")%></td>  
                <td><%# DataBinder.Eval(Container.DataItem, "EmailId")%></td>  
                <td><asp:Button ID="imgbtnedit" runat="server" Text="Edit"  ToolTip="Edit" CommandName="Edit"/></td>  
                <td><asp:Button ID="btndelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete"/></td>  
                </tr>            
                </ItemTemplate>  
                <EditItemTemplate>             
                <tr>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true"  ID="txtName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox></td>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtContact" runat="server" Text='<%# Eval("Contact") %>'></asp:TextBox></td>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtEmail" runat="server" Text='<%# Eval("EmailId") %>'></asp:TextBox></td>  
                 <td><asp:Button ID="btnupdate" runat="server"  ToolTip="Update" Text="Update" CommandName="Update" /></td>  
                 <td><asp:Button ID="btncancel" runat="server"  ToolTip="Cancel" Text="Cancel" CommandName="Cancel" /></td>  
                </tr>  
                </EditItemTemplate>  
            </asp:DataList>  
            </div>  
            </fieldset>  
            </div>  
    </asp:Content>  


Step 4: UI code
Here is all about an operation on a DataList row records.
In this first write code for the TextBox value insertion into the SQL table by the preceding Insert Procedure, then write code for the inserted record display in the DalaList control. Write the code for two buttons, one is the delete and the second is an update button code.

Now create an update command, an edit command, a cancel command, an Item command according to the following Figure 3 and also maintain a datakeyfield in the property data section as in Figure 2. 

Figure 2: Add Data Key Field

Figure 3: Add Action Command
Code
    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Web;  
    using System.Web.UI;  
    using System.Web.UI.WebControls;  
    using System.Configuration;  
    using System.Data;  
    using System.Data.SqlClient;  
      
    public partial class UI_DataList : System.Web.UI.Page  
    {  
        string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            if (!IsPostBack)  
            {  
                GetEmpDataList();  
            }  
        }  
        protected void btnSave_Click(object sender, EventArgs e)  
        {  
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))  
                {  
                    cmd.CommandType = CommandType.StoredProcedure;  
      
                    cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
                    cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
                    cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
      
                    con.Open();  
                    cmd.ExecuteNonQuery();  
                    con.Close();  
      
                    Clear();  
                    Response.Write("<script type=\"text/javascript\">alert('Record Inserted Successfully');</script>");  
                    GetEmpDataList();  
                }  
            }  
        }  
        void Clear()  
        {  
            txtName.Text = String.Empty;  
            txtContact.Text = String.Empty;  
            txtEmail.Text = String.Empty;  
        }  
        private void GetEmpDataList()  
        {  
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                SqlDataAdapter sd = new SqlDataAdapter("sp_FillData", con);  
                sd.SelectCommand.CommandType = CommandType.StoredProcedure;  
                DataTable dt = new DataTable();  
      
                sd.Fill(dt);  
      
                if (dt.Rows.Count > 0)  
                {  
                    DataListEmp.DataSource = dt;  
                    DataListEmp.DataBind();  
                }  
             }  
        }  
        protected void DataListEmp_DeleteCommand(object source, DataListCommandEventArgs e)  
        {  
           int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
      
           using (SqlConnection con = new SqlConnection(connection))  
           {  
               using (SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con))  
               {  
                   cmd.CommandType = CommandType.StoredProcedure;  
                   cmd.Parameters.AddWithValue("@EmpID",EmpID);  
      
                   con.Open();  
                   cmd.ExecuteNonQuery();  
                   con.Close();  
      
                   Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully');</script>");  
                   GetEmpDataList();  
               }  
           }  
        }  
        protected void DataListEmp_EditCommand(object source, DataListCommandEventArgs e)  
        {  
            DataListEmp.EditItemIndex = e.Item.ItemIndex;  
            GetEmpDataList();  
        }  
        protected void DataListEmp_CancelCommand(object source, DataListCommandEventArgs e)  
        {  
            DataListEmp.EditItemIndex = -1;  
            GetEmpDataList();  
        }  
        protected void DataListEmp_UpdateCommand(object source, DataListCommandEventArgs e)  
        {  
            int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
      
            TextBox txtName = (TextBox)e.Item.FindControl("txtName");  
            TextBox txtContact = (TextBox)e.Item.FindControl("txtContact");  
            TextBox txtEmail = (TextBox)e.Item.FindControl("txtEmail");  
      
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con))  
                {  
                    cmd.CommandType = CommandType.StoredProcedure;  
      
                    cmd.Parameters.AddWithValue("@EmpID", EmpID);  
                    cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
                    cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
                    cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
      
                    con.Open();  
                    cmd.ExecuteNonQuery();  
                    con.Close();  
      
                    Clear();  
                    Response.Write("<script type=\"text/javascript\">alert('Record Update Successfully');</script>");  
                    DataListEmp.EditItemIndex = -1;  
                    GetEmpDataList();  
                }  
            }  
        }  
    }  

Step 5: Browser Side
Now run your new page in the browser. 
I hope you understood how to work with the DataList control Action Command Event and SQL Procedures.
Have a nice day. 

HostForLIFEASP.NET SQL Server 2022 Hosting




European SQL Server 2022 Hosting :: SQL Server Cumulative Transaction for Credit Debit Transaction

clock July 2, 2025 07:39 by author Peter

report on credit and debit transactions in banking.

USE [SqlBank]

CREATE   FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN

DECLARE @TempAC_ID BIGINT;

SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
  C ON A.CID=C.CID WHERE c.CID=@CID)

DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
 CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime  ,
Remarks varchar(max)  , IsmailSend int
)


INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,

 CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
 THEN tr.TR_Amt ELSE 0 END  Balance,
 Tr.TR_CrDrType  ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
 Tr.Remarks , Tr.IsmailSend

FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
      join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;

WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate  ,t2.Remarks ,t2.IsmailSend
)

INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
 )
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType  ,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran  with(nolock)
WHERE AC_ID=@TempAC_ID

RETURN
END


STEP 2 : Create Procedure & Call above funtion in Procedure

USE [SqlBank]

CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN

DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint

DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;

BEGIN TRAN Tbl_Transaction_Tran
   BEGIN  TRY


      IF(@Flag = 'Tran')
      BEGIN

IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
   BEGIN


 SELECT  a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
 ,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
   FROM dbo.FNGetTransaction(@CID) a
--      JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id

--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType


END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
    END

ELSE IF(@Flag = 'IN')
    BEGIN

    SET @Temp_ACID = (SELECT Top 1 A.AC_ID  FROM Tbl_Account A with(nolock)
     Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)

    DECLARE @SenderName varchar(max)
        SET @SenderName = (SELECT Top 1 c.CName  FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)

    DECLARE @ReciverName varchar(max)
       SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID
       WHERE A.AC_No=@AC_No)


SET @TempTRAmount = (
 SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
   FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id

GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)

if(@TR_Amt > @TempTRAmount)
BEGIN

Select 'Insuffitient Balance' as msg

END
ELSE
  BEGIN
  Declare @FixScratchAmt decimal(18,2)=500;

  --if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
  --begin
  Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
  ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)

  set @Tran_ID = @@IDENTITY;
  set @TR_ID= @Tran_ID;
  set @Tran_ScopID= SCOPE_IDENTITY();
  Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)


  if(@TR_Amt >= @FixScratchAmt)
  begin
   Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
  END

Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
  CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
  +Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
  set @MMTR_ID = @@IDENTITY;
  set @MTR_ID = @MMTR_ID;


 -- Select TOP 1 c.CEmail ReciverEmail,c.CName From Tbl_Cust c join Tbl_Account a on a.CID=c.CID where a.AC_No=@AC_No

 -- SELECT top 1 A.AC_No,t.TR_Amt,t.TR_CrDrType,@SenderName SenderName,'http://saipathrikar.blogspot.com/' Url ,

 --   (
 --  SELECT top 1 fna.Balance

 --  FROM dbo.FNGetTransaction(@CID) fna

    --order by fna.id desc
 -- ) Bal , @ReciverName ReciverName


  -- FROM Tbl_Transaction T join Tbl_Account A on
  -- T.AC_ID= A.AC_ID Where A.CID=@CID
  --order by t.TR_ID desc

 -- SELECT top 1 A.AC_No,t.TR_Amt,t.TR_CrDrType,t.Ref_TranACC +'  ' +
 --  @SenderName SenderName,'http://saipathrikar.blogspot.com/' Url  ,

 --  (
 --  SELECT top 1 fna.Balance

 --  FROM Tbl_Account a cross apply dbo.FNGetTransaction(a.cid) fna
 --   where a.ac_ID=fna.AC_ID and a.AC_No=@AC_No
    --order by fna.id desc
 -- ) Bal


 --  FROM Tbl_Transaction T join Tbl_Account A on
 --  T.AC_ID= A.AC_ID Where A.AC_No=@AC_No
 -- order by t.TR_ID desc

 -- end

 -- This Sectioin is for pdf data

 --SELECT  a.id Tran_id ,a.Tr_Type Type,a.TranDate Date,a.CreditAmt,a.DebitAmt,a.TType CrDr,a.Balance

 --  FROM dbo.FNGetTransaction(@CID) a


 --  SELECT  a.id Tran_id ,a.Tr_Type Type,a.TranDate Date,a.CreditAmt,a.DebitAmt,a.TType CrDr,a.Balance
 --  FROM Tbl_Account ac cross apply dbo.FNGetTransaction(ac.cid) a
 --   where ac.ac_ID=a.AC_ID and ac.AC_ID=@Temp_ACID


    END

    END

IF(@@TRANCOUNT > 0)
  BEGIN
  COmmit tran Tbl_Transaction_Tran
  END

END TRY
BEGIN CATCH

IF(@@TRANCOUNT > 0)
            BEGIN
            ROLLBACK TRAN Tbl_Transaction_Tran
            END

            DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
            ,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
            ,@ERRORSTATE varchar(max), @ErroFrm varchar(max)

            SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
                   @ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
                  @ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'

        EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm

END CATCH

END

SQL

Step 1.  Create Table valued function for calculate credit,debit amount with total balance

STEP 2. Execute Procedure to Check Report.

exec [dbo].[PROC_TRansaction]

@CID =2,@Flag='Tran'


STEP 3. Output

Thank you for reading this article.

HostForLIFEASP.NET SQL Server 2022 Hosting

 



About HostForLIFE

HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.


Month List

Tag cloud

Sign in