Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Sunday, January 29, 2012

SQL to get First, Last and Largest Transaction based on Transaction Amount and Date

The below query helps an user to get more details of transaction like first, last transaction, largest transaction based on transaction amount, etc.
  1. Consider a table dbo.TransactionSummary as shown below:

TransactionID
AccountID
TransDate
TransType
BranchID
TransAmount
1001
11234
2000-01-01
Direct
1
1200
1002
11235
2001-03-02
Direct
1
2000
1003
11234
2001-05-12
Direct
2
1345
1004
11234
2002-05-23
Direct
1
2050
1005
11234
2002-07-23
Indirect
2
1000
1006
11235
1999-09-12
Indirect
1
2000
1007
11235
2010-09-17
Indirect
1
3000

Now, I want to get the First Trans Date, First Trans Amount, Last Trans Date, Last Trans Amount, Largest Trans Date (Date on which largest transaction took place) and Largest Trans Amount for each AccountID grouped by TransType, BranchID.


The below query helps to get those details:

WITH TransData AS (
 SELECT  AccountID
                 , TransType
                 , BranchID
                 , MIN(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [FirstTransactionData]
                 , MAX(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [LastTransactionData]
                , MAX(CONVERT(BINARY(10),TS.TransAmount)+ CONVERT(BINARY(10),TS.[TransDate])) [LargestTransactionData]
FROM dbo.TransactionSummary
GROUP BY  AccountID
                      , TransType
                      , BranchID)
 SELECT  AccountID
                  , TransType
                  , BranchID
                  , SUBSTRING(@FirstTransactionData, 1, 10) AS FirstTransDate
                  , SUBSTRING(@FirstTransactionData,CHARINDEX('*',@FirstTransactionData)+1, 20) [FirstTransAmount]
                 , SUBSTRING(@LastTransactionData, 1, 10) AS LastTransDate
                 , SUBSTRING(@LastTransactionData,CHARINDEX('*',@LastTransactionData)+1, 20) [LastTransAmount]
                , CONVERT(DATE,SUBSTRING(@LargestTransactionData,11,10)) [LargestTransDate]
                , CONVERT(MONEY,SUBSTRING(@LargestTransactionData, 1,10)) [LargestTransAmount]
FROM TransData

2 comments:

  1. Hi..I am new to MSBI..searching for a job on SSIS and SSRS.Your blog is very useful for me.I have a doubt..generally how you get the data and what is the workflow?Do you get the requirement to your mail or how u'l receive the data..

    ReplyDelete
  2. It is very usefull to get idea for me
    Thanks for You.

    Regards,
    NSRAO.

    ReplyDelete