Help SQL query, count and group by day on multiple fields

• shani19831

  SSCommitted

  Points: 1737

  More actions

  April 20, 2017 at 8:42 am

  #320562

  Hi All,
  I have a table with records, which are determined by transaction_type.
  I would therefore like to have a count of each transaction_type per day.
  In the end the extract should have a count of a transaction type for a specific day of a month.

  Attached is the excel with the actual data to create a table..

  Below is what I currently have..
  
select b.SendMoney1-b.SendMoney2 as SendMoneyCount
        ,b.airtime1-b.airtime2 as AirtimeCount
        ,b.electric1-b.electric2 as ElectricCount
        ,b.chasout1-b.chasout2 as CashOutCount
        ,b.chasin1-b.chasin2 as CashInCount
        ,b.chaspay1-b.chaspay2 as PaymentCount
        ,b.interacc1-b.interacc2 as InterAcctCount
        ,b.releaseimme1-b.releaseimme2 as ReleaseImmediateCount
        ,b.release321-b.release322 as Release32DaysCount
        ,b.cashback1-b.cashback2 as MTCCashBack
from (
select (
------Send Money
    select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'AC2U'
    AND HISTORY_NUMBER NOT IN (2)
    ) AS 'SendMoney1'
,
(select count(RECID) sum2
    from V_FBNK_FUNDS_TRANSFER001
    where HISTORY_NUMBER NOT IN (1)
    AND TRANSACTION_TYPE = 'AC2U'
) as 'SendMoney2'
    ,
(
----MTC AIRTIME Topup
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTP'
    AND HISTORY_NUMBER NOT IN (2)
) as 'airtime1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTP'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'airtime2'
,
(
----Electricity Purchase
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACUD'
    AND HISTORY_NUMBER NOT IN (2)
) as 'electric1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACUD'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'electric2'
,
(
----ECode Cash Out
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTC'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chasout1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTC'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chasout2'
,
(
----ECode Cash In
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTD'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chasin1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTD'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chasin2'
,
(
----ECode Payment
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACPT'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chaspay1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACPT'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chaspay2'
,
(
----Internet Account Transfer
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACIB'
    AND HISTORY_NUMBER NOT IN (2)
) as 'interacc1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACIB'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'interacc2'
,
(
----Release From Savings immediate
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSV'
    AND HISTORY_NUMBER NOT IN (2)
) as 'releaseimme1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSV'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'releaseimme2'
,
(
----Release From Savings 32 days
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSW'
    AND HISTORY_NUMBER NOT IN (2)
) as 'release321'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSW'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'release322'
,
(
----MTC Topup CashBack
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACCB'
    AND HISTORY_NUMBER NOT IN (2)
) as 'cashback1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACCB'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'cashback2'

  ) AS b

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 20, 2017 at 9:12 am

  #1939109

  would be far easier for potential answers if you would follow the following advice.....https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

  whilst you have given us sample data...you expect us to do your work for you in building a tested solution.....not really fair on volunteers !
  Also...you have not given your expected results based on your sample data....this would be most beneficial

  thanks

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• shani19831

  SSCommitted

  Points: 1737

  More actions

  April 20, 2017 at 9:18 am

  #1939111

  J Livingston SQL - Thursday, April 20, 2017 9:12 AM

  would be far easier for potential answers if you would follow the following advice.....https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

  whilst you have given us sample data...you expect us to do your work for you in building a tested solution.....not really fair on volunteers !
  Also...you have not given your expected results based on your sample data....this would be most beneficial

  thanks

  Thanks.. I would read the https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ and understand whats really needed.

  Its not like that, all im seeking for help, cause im now stuck in building that query.. yes it will be really nice to have a tested solution which i can always build on.

  I will provide the expected results on the sample..

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 20, 2017 at 9:20 am

  #1939112

  Sample data as SQl statement attached as a txt file (far too large to paste, and .sql files are not allowed...?).

  Is this not a simple GROUP BY? If not, could you provide expected output?
  DECLARE @TransactionDay date;
SET @TransactionDay = '20170313'

  SELECT TRANSACTION_TYPE,
       COUNT(HOLD_REF) AS Transactions
FROM SSC.V_FBNK_FUNDS_TRANSFER001
WHERE DEBIT_VALUE_DATE = @TransactionDay
GROUP BY TRANSACTION_TYPE;

  We can't run the SQL you provided as it references a field [RECID], which does not exist in your sample data.If your sample data is incomplete, please ensure you provide it in the same format that mine is in, not an Excel document.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 20, 2017 at 9:21 am

  #1939113

  shani19831 - Thursday, April 20, 2017 8:42 AM

  Hi All,
  I have a table with records, which are determined by transaction_type.
  I would therefore like to have a count of each transaction_type per day.
  In the end the extract should have a count of a transaction type for a specific day of a month.

  Attached is the excel with the actual data to create a table..

  Below is what I currently have..
  
select b.SendMoney1-b.SendMoney2 as SendMoneyCount
        ,b.airtime1-b.airtime2 as AirtimeCount
        ,b.electric1-b.electric2 as ElectricCount
        ,b.chasout1-b.chasout2 as CashOutCount
        ,b.chasin1-b.chasin2 as CashInCount
        ,b.chaspay1-b.chaspay2 as PaymentCount
        ,b.interacc1-b.interacc2 as InterAcctCount
        ,b.releaseimme1-b.releaseimme2 as ReleaseImmediateCount
        ,b.release321-b.release322 as Release32DaysCount
        ,b.cashback1-b.cashback2 as MTCCashBack
from (
select (
------Send Money
    select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'AC2U'
    AND HISTORY_NUMBER NOT IN (2)
    ) AS 'SendMoney1'
,
(select count(RECID) sum2
    from V_FBNK_FUNDS_TRANSFER001
    where HISTORY_NUMBER NOT IN (1)
    AND TRANSACTION_TYPE = 'AC2U'
) as 'SendMoney2'
    ,
(
----MTC AIRTIME Topup
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTP'
    AND HISTORY_NUMBER NOT IN (2)
) as 'airtime1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTP'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'airtime2'
,
(
----Electricity Purchase
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACUD'
    AND HISTORY_NUMBER NOT IN (2)
) as 'electric1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACUD'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'electric2'
,
(
----ECode Cash Out
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTC'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chasout1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTC'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chasout2'
,
(
----ECode Cash In
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTD'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chasin1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTD'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chasin2'
,
(
----ECode Payment
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACPT'
    AND HISTORY_NUMBER NOT IN (2)
) as 'chaspay1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACPT'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'chaspay2'
,
(
----Internet Account Transfer
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACIB'
    AND HISTORY_NUMBER NOT IN (2)
) as 'interacc1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACIB'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'interacc2'
,
(
----Release From Savings immediate
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSV'
    AND HISTORY_NUMBER NOT IN (2)
) as 'releaseimme1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSV'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'releaseimme2'
,
(
----Release From Savings 32 days
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSW'
    AND HISTORY_NUMBER NOT IN (2)
) as 'release321'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACSW'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'release322'
,
(
----MTC Topup CashBack
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACCB'
    AND HISTORY_NUMBER NOT IN (2)
) as 'cashback1'
,
(
select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACCB'
    AND HISTORY_NUMBER NOT IN (1)
    ) as 'cashback2'

  ) AS b

  First, here's a rewrite of the core part of your query:
  SELECT
 [SendMoney1] = COUNT(CASE WHEN TRANSACTION_TYPE = 'AC2U' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [SendMoney2] = COUNT(CASE WHEN TRANSACTION_TYPE = 'AC2U' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [airtime1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTP' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [airtime2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTP' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [electric1]  = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACUD' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [electric2]  = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACUD' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [chasout1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTC' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [chasout2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTC' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
  [chasin1]    = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTD' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [chasin2]    = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACTD' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
  [chaspay1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACPT' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [chaspay2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACPT' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [interacc1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACIB' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [interacc2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACIB' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [releaseimme1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACSV' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [releaseimme2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACSV' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [release321]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACSW' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [release322]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACSW' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END),
 [cashback1]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACCB' AND HISTORY_NUMBER NOT IN (2) THEN 1 ELSE NULL END),
 [cashback2]   = COUNT(CASE WHEN TRANSACTION_TYPE = 'ACCB' AND HISTORY_NUMBER NOT IN (1) THEN 1 ELSE NULL END)

  FROM V_FBNK_FUNDS_TRANSFER001

  Second, do you still need help? Which other columns do you want to see in the output?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 20, 2017 at 9:26 am

  #1939114

  select (
------Send Money
  select count(RECID) sum1
  from V_FBNK_FUNDS_TRANSFER001
  where TRANSACTION_TYPE = 'AC2U'
  AND HISTORY_NUMBER NOT IN (2)
  ) AS 'SendMoney1'
,
(select count(RECID) sum2
  from V_FBNK_FUNDS_TRANSFER001
  where HISTORY_NUMBER NOT IN (1)
  AND TRANSACTION_TYPE = 'AC2U'
) as 'SendMoney2'
  ,
(
----MTC AIRTIME Topup
select count(RECID) sum1
  from V_FBNK_FUNDS_TRANSFER001
  where TRANSACTION_TYPE = 'ACTP'
  AND HISTORY_NUMBER NOT IN (2)
) as 'airtime1'
,

  Regardless of what your other needs are, anytime I see clients hitting the same table over and over like that (which is quite often), I always advise them to come up with ways to combine such hits. CASE is often the solution, especially in aggregate scenarios.

  select sum(case when tTRANSACTION_TYPE = 'AC2U' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end) as sendmoney1,
       sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'AC2U' then 1 else 0 end) as sendmoney2,
etc
from V_FBNK_FUNDS_TRANSFER001
etc


  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 20, 2017 at 9:28 am

  #1939116

  TheSQLGuru - Thursday, April 20, 2017 9:26 AM

  select (
  ------Send Money
    select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'AC2U'
    AND HISTORY_NUMBER NOT IN (2)
    ) AS 'SendMoney1'
  ,
  (select count(RECID) sum2
    from V_FBNK_FUNDS_TRANSFER001
    where HISTORY_NUMBER NOT IN (1)
    AND TRANSACTION_TYPE = 'AC2U'
  ) as 'SendMoney2'
    ,
  (
  ----MTC AIRTIME Topup
  select count(RECID) sum1
    from V_FBNK_FUNDS_TRANSFER001
    where TRANSACTION_TYPE = 'ACTP'
    AND HISTORY_NUMBER NOT IN (2)
  ) as 'airtime1'
  ,

  Regardless of what your other needs are, anytime I see clients hitting the same table over and over like that (which is quite often), I always advise them to come up with ways to combine such hits. CASE is often the solution, especially in aggregate scenarios.

  select sum(case when tTRANSACTION_TYPE = 'AC2U' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end) as sendmoney1,
       sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'AC2U' then 1 else 0 end) as sendmoney2,
etc
from V_FBNK_FUNDS_TRANSFER001
etc


  This gets my vote too!

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 20, 2017 at 9:42 am

  #1939122

  HAH! Looks like our posts were being typed up at the same time, and you beat me by 5 minutes. Great minds think alike! 😎

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 20, 2017 at 10:14 am

  #1939129

  TheSQLGuru - Thursday, April 20, 2017 9:42 AM

  HAH! Looks like our posts were being typed up at the same time, and you beat me by 5 minutes. Great minds think alike! 😎

  This query pattern is soooo common!

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• shani19831

  SSCommitted

  Points: 1737

  More actions

  April 20, 2017 at 10:22 am

  #1939130

  Hi guys
  Thank you for the help.

  Sometimes when the brain is under pressure, it failes to processes instructions well..But thank you for the solution.

  Final query for thosethat will be interested..

  
select day(PROCESSING_DATE) as 'Day',
sum(case when TRANSACTION_TYPE = 'AC2U' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'AC2U' then 1 else 0 end) as SendMoneyCount,
sum(case when TRANSACTION_TYPE = 'ACTP' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACTP' then 1 else 0 end) as AirtimeCount,
sum(case when TRANSACTION_TYPE = 'ACUD' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACUD' then 1 else 0 end) as ElectricCount,     
sum(case when TRANSACTION_TYPE = 'ACUD' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACUD' then 1 else 0 end) as CashOutCount,     
sum(case when TRANSACTION_TYPE = 'ACTD' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACTD' then 1 else 0 end) as CashInCount,
sum(case when TRANSACTION_TYPE = 'ACPT' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACPT' then 1 else 0 end) as PaymentCount,
sum(case when TRANSACTION_TYPE = 'ACIB' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACIB' then 1 else 0 end) as InterAcctCount,
sum(case when TRANSACTION_TYPE = 'ACSV' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACSV' then 1 else 0 end) as ReleaseImmediateCount,
sum(case when TRANSACTION_TYPE = 'ACSW' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACSW' then 1 else 0 end) as Release32DaysCount,
sum(case when TRANSACTION_TYPE = 'ACCB' AND HISTORY_NUMBER NOT IN (2) then 1 else 0 end)-sum(case when HISTORY_NUMBER NOT IN (1) AND TRANSACTION_TYPE = 'ACCB' then 1 else 0 end) as MTCCashBack
from V_FBNK_FUNDS_TRANSFER001
GROUP BY day(PROCESSING_DATE)
ORDER BY day(PROCESSING_DATE) asc


• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 20, 2017 at 11:18 am

  #1939142

  When I first looked at this thread it just seemed relatively easy, so rather than look at all the other posts, I just started coding, and here's what I came up with:
  SELECT DAY(PROCESSING_DATE) AS PROCESSING_DATE,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'AC2U' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'AC2U' THEN RECID ELSE NULL END) AS SendMoneyCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACTP' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACTP' THEN RECID ELSE NULL END) AS AirtimeCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACUD' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACUD' THEN RECID ELSE NULL END) AS ElectricCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACTC' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACTC' THEN RECID ELSE NULL END) AS CashOutCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACTD' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACTD' THEN RECID ELSE NULL END) AS CashInCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACPT' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACPT' THEN RECID ELSE NULL END) AS PaymentCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACIB' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACIB' THEN RECID ELSE NULL END) AS InterAcctCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACSV' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACSV' THEN RECID ELSE NULL END) AS ReleaseImmediateCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACSW' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACSW' THEN RECID ELSE NULL END) AS Release32DaysCount,
    COUNT(CASE WHEN HISTORY_NUMBER <> 2 AND TRANSACTION_TYPE = 'ACCB' THEN RECID ELSE NULL END) -
    COUNT(CASE WHEN HISTORY_NUMBER <> 1 AND TRANSACTION_TYPE = 'ACCB' THEN RECID ELSE NULL END) AS MTCCashBackCount
FROM V_FBNK_FUNDS_TRANSFER001 AS FT
WHERE TRANSACTION_TYPE IN ('AC2U','ACTP','ACUD','ACTC','ACTD','ACPT','ACIB','ACSV','ACSW','ACCB')
    AND YOUR_DATE_TIME_FIELD = @DESIRED_DATE_VALUE
GROUP BY DAY(PROCESSING_DATE)
ORDER BY DAY(PROCESSING_DATE);

  It's very similar to what else has been posted, and nearly identical to your own final post.   Great minds DO think alike!

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)