calculating a measure in T-SQL

Question

calculating a measure in T-SQL

RogerBinny1234 41

Hi,

I am required to create a measure for which I am struggling to come up with a query.

My requirement is:

"Find the number of users who had a transaction over the last running 7 days (for any type of account) and then divide it by all the users with at least one account open in that time period".

Here are my sample data sets.

This is the Accounts Table

This is the Fact table which shows the transactions

If I could give the same data sets in non picture format then this is how it looks like

accountid	accounttype	openedDT	ClosedDT	CurrentStatus	UserID  
1	Checkings	01/01/2020 19:33	12/01/2020 00:00	Closed	101  
1	Checkings	13/01/2020 13:11	15/01/2020 08:30	Closed	101  
1	Checkings	16/01/2020 22:11	Null	Open	101  
2	Checkings	13/01/2020 18:23	Null	Open	101  
3	Checkings	15/01/2020 22:13	Null	Open	102  
4	Savings	15/01/2020 13:13	19/01/2020 00:00	Closed	102  
5	Checkings	02/02/2020 09:33	Null	Open	103  
6	Checkings	22/01/2020 11:33	Null	Open	104  
7	Business	21/01/2020 00:13	Null	Open	105  
8	Checkings	02/02/2020 13:13	05/08/2020 07:42	Closed	106  
9	Checkings	08/09/2020 17:17	Null	Open	106  
10	Savings	06/11/2020 02:32	Null	Open	107  
  

accountid	transactionDate	transCount  
1	02/01/2020 11:12	2  
1	03/01/2020 10:12	5  
1	03/01/2020 12:15	7  
1	14/01/2020 16:18	1  
1	15/01/2020 07:30	2  
1	28/01/2020 06:06	22  
2	14/01/2020 17:17	2  
2	15/01/2020 01:01	1  
2	22/02/2020 06:30	1  
3	30/03/2020 07:11	4  
3	30/04/2020 08:12	4  
3	30/05/2020 07:02	4  
4	12/02/2020 06:06	3  
8	03/03/2020 07:07	1  
10	13/12/2020	22

I require to worry about only open accounts...so we can easily ignore the closed accounts.

Also, this metric should be able to give the result for any given day of the year (but we don't have a time dimension).

Finally, the result should be consistent even if new transactions come in.

Erland Sommarskog 129.4K Reputation points MVP Volunteer Moderator

2021-01-30T13:06:06.753+00:00

For this type of question it is always a good idea to post CREATE TABLE statement(s) for your tables, preferably simplified to focus at the problem at hand, together with INSERT statements for sample data. This makes it easy for us to copy and paste into a query window to develop a solution.

Furthermore, to be able verify the solution, we also need the desired result given the sample.

You say account "open in the period". Does this mean that if the account was open at any time during the period? That is, if the account was closed on Friday, and the period closes on Sunday, does it still count.

What exactly do you mean with " result should be consistent even if new transactions come in."?

Finally, since you have added quite a few tags to your post, in which environment do you want this? Are you looking for a T-SQL solution? Or is it a DAX query or similar for Analysis Services? Or do you want this in SSIS?
RogerBinny1234 41 Reputation points

2021-01-30T14:58:44.537+00:00

Small correction, I have date dimension. I mentioned I didn't have it but I do.
RogerBinny1234 41 Reputation points

2021-01-30T15:33:19.25+00:00
sorry I want it only on in SQL, no DAX and no SSIS.

Here are the Create table scripts.

CREATE TABLE Accounts ( accountid integer, accounttype varchar(20), createddate DateTime,, closeddate DateTime , status varchar(20), userid integer )

CREATE TABLE transactions
(
tdate DateTime ,
accountid integer,
tnum integer
)
RogerBinny1234 41 Reputation points

2021-01-30T15:47:47.393+00:00

Honestly don't worry about consistency or anything.

In fact, I don't understand how the output should look like.

This is how the question was asked and I am struggling to understand what do I need to show.
I think for Jan7th 2020, I need to find the count of all the active users until Jan1st and divide it by all the number of accounts during the same period.

For 6th Jan, 2020, I need to find the count of all the active users until Dec31st 2019 and divide it by all the number of accounts during 6th January 2020 till Dec 31st 2019.

If we don't have any transactions or users on 31st December on the above case, then we should count it as ZERO

Hope I am clear now...I think this is what i need.

Answer accepted by question author

2 additional answers

Your answer

Erland Sommarskog 129.4K Reputation points MVP Volunteer Moderator

2021-01-30T13:06:06.753+00:00

For this type of question it is always a good idea to post CREATE TABLE statement(s) for your tables, preferably simplified to focus at the problem at hand, together with INSERT statements for sample data. This makes it easy for us to copy and paste into a query window to develop a solution.

Furthermore, to be able verify the solution, we also need the desired result given the sample.

You say account "open in the period". Does this mean that if the account was open at any time during the period? That is, if the account was closed on Friday, and the period closes on Sunday, does it still count.

What exactly do you mean with " result should be consistent even if new transactions come in."?

Finally, since you have added quite a few tags to your post, in which environment do you want this? Are you looking for a T-SQL solution? Or is it a DAX query or similar for Analysis Services? Or do you want this in SSIS?
RogerBinny1234 41 Reputation points

2021-01-30T14:58:44.537+00:00

Small correction, I have date dimension. I mentioned I didn't have it but I do.
RogerBinny1234 41 Reputation points

2021-01-30T15:33:19.25+00:00

sorry I want it only on in SQL, no DAX and no SSIS.

Here are the Create table scripts.

CREATE TABLE Accounts ( accountid integer, accounttype varchar(20), createddate DateTime,, closeddate DateTime , status varchar(20), userid integer )

CREATE TABLE transactions
(
tdate DateTime ,
accountid integer,
tnum integer
)
RogerBinny1234 41 Reputation points

2021-01-30T15:47:47.393+00:00

Honestly don't worry about consistency or anything.

In fact, I don't understand how the output should look like.

This is how the question was asked and I am struggling to understand what do I need to show.
I think for Jan7th 2020, I need to find the count of all the active users until Jan1st and divide it by all the number of accounts during the same period.

For 6th Jan, 2020, I need to find the count of all the active users until Dec31st 2019 and divide it by all the number of accounts during 6th January 2020 till Dec 31st 2019.

If we don't have any transactions or users on 31st December on the above case, then we should count it as ZERO

Hope I am clear now...I think this is what i need.

Answer 1

Erland Sommarskog 129.4K MVP Volunteer Moderator

This is how the question was asked

Asked by whom in which context?

Anyway, I think you need to talk to those persons for clarification. I can't make heads or tails of that accounts table. There are multiple rows for the same accountid what does that mean? There are four rows for accountid 1, three for userid 101 and one for userid 107. I could get that accountid may be related to the user, but the we would need a userid in transactions.

Your expected output is also very strange. Between Jan 1st and Jan 10th the number of users for the last 7 days varies between 0 and three, but there is only a single account that has transactions in this interval?

It seems that you need to go back and investigate what the users are really looking for, but here is a query that you maybe can work from. The dates table is your date dimension.

SELECT d.d, t.Last7DaysUserCnt, a.OpenAcc, isnull(t.Last7DaysUserCnt / nullif(a.OpenAcc, 0), 0)
FROM   NorthNumbers..Dates d
CROSS  APPLY (SELECT COUNT(DISTINCT a.userid)
              FROM   transactions t
              JOIN   accounts a ON t.accountid = a.accountid
              WHERE  convert(date, t.tdate) BETWEEN dateadd(DAY, -6, d.d) AND d.d) AS t(Last7DaysUserCnt)
CROSS  APPLY (SELECT COUNT(DISTINCT a.userid)
              FROM   accounts a
              WHERE  a.createddate <= d.d
                AND  a.closeddate >= dateadd(DAY, -6, d.d)) AS a (OpenAcc)
WHERE  d.d BETWEEN '20200101' AND '20200110'
ORDER BY d.d

RogerBinny1234 41 Reputation points

2021-01-30T19:15:13.893+00:00

I am sorry to hear that.

1) Accounts table shows the history of all accounts a user has opened.

For example,

User 101 has opened a Checkings account on January 1st 2020 but he has closed his account on 12th January 2020.

The same account was reOpened on 13th January 2020, got closed again on 15th Jan 2020.

Finally in was reOpened again on 16th Jan 2020 and it's active ever since.

This same user has opened a fresh account It's actually a different account type this time on 13th January 2020 and it's still open.

On 15th Jan 2020, user 102 opened a checking account and its account id is 3.

This same user on the same day also opened a savings account as well and it's account number is 4 but this user closed this account on 19th January 2020.
RogerBinny1234 41 Reputation points

2021-01-30T19:23:33.337+00:00

So, from January 1st 2020 to 12th January our total user count is 1
From 13th January to 14th January also our user count is still 1, although 101 user has created a savings account.

on 15th we have another user 102...so from 14th January to1st February our distinct user count is 2.

on 2nd Feb we have a new user 103 opened an account, which makes our user count on 2nd Feb to 3.

If both 101 ond 102 users close all their accounts on Feb 4th, our total user count will be 1.

If on 5th Feb if we have a new user then our total user count increases to 2.

Hope it is making sense now...Ideally we should be calculating the running total of the users. We just need to make sure we count distinct users and we subtract the count when a user closes their account and add them back to the count whenever their account gets reoopened.
RogerBinny1234 41 Reputation points

2021-01-30T19:27:00.57+00:00

The Fact table which is a transaction table. Currently it holds the data at account level.
An account can have multiple transaction on a single day and a single user can have multiple accounts as well and they may have transactions on both the accounts on the same day as well.

Now as I mentioned earlier we need to find the activity of a user (across different accounts) in a given period of 7 days. We need count all the distinct users in a week and divide that number with the total number of users by that period.

Hope it helps.
Erland Sommarskog 129.4K Reputation points MVP Volunteer Moderator

2021-01-30T19:42:51.36+00:00

I gave you a query to work from. I suggest that you play around with that query. If you have any questions how that query works, you are welcome, but I don't really plan to engage myself in the details of the rules, as they are scattered too many posts already.
RogerBinny1234 41 Reputation points

2021-01-31T10:22:48.337+00:00

I worked around your query and I could get the required output. Thank you for the pointers.

Answer 2

Here are the Insert statments

Insert Into accounts
SELECT 1,'Checkings', '01/01/2020','12/01/2020','Closed',101
UNION ALL
SELECT 1,'Checkings', '13/01/2020','15/01/2020','Closed',101
UNION ALL
SELECT 1,'Checkings', '16/01/2020',Null,'Open',101
UNION ALL
SELECT 2,'Checkings', '13/01/2020',Null,'Open',101
UNION ALL
SELECT 3,'Checkings', '15/01/2020',Null,'Open',102
UNION ALL
SELECT 4,'Savings', '15/01/2020','19/01/2020','Closed',102
UNION ALL
SELECT 5,'Checkings', '02/02/2020',Null,'Open',103
UNION ALL
SELECT 6,'Checkings', '22/01/2020',Null,'Open',104
UNION ALL
SELECT 7,'Business', '21/01/2020',Null,'Open',105
UNION ALL
SELECT 8,'Checkings', '02/02/2020','05/08/2020,'Closed',106
UNION ALL
SELECT 9,'Checkings', '08/09/2020',Null,'Open',106
UNION ALL
SELECT 1,'Savings', '06/11/2020',Null,'Open',107

 Insert Into accounts
    SELECT 1,'Checkings', '01/01/2020','12/01/2020','Closed',101
    UNION ALL
    SELECT 1,'Checkings', '13/01/2020','15/01/2020','Closed',101
    UNION ALL
    SELECT 1,'Checkings', '16/01/2020',Null,'Open',101
    UNION ALL
    SELECT 2,'Checkings', '13/01/2020',Null,'Open',101
    UNION ALL
    SELECT 3,'Checkings', '15/01/2020',Null,'Open',102
    UNION ALL
    SELECT 4,'Savings', '15/01/2020','19/01/2020','Closed',102
    UNION ALL
    SELECT 5,'Checkings', '02/02/2020',Null,'Open',103
    UNION ALL
    SELECT 6,'Checkings', '22/01/2020',Null,'Open',104
    UNION ALL
    SELECT 7,'Business', '21/01/2020',Null,'Open',105
    UNION ALL
    SELECT 8,'Checkings', '02/02/2020','05/08/2020,'Closed',106
    UNION ALL
    SELECT 9,'Checkings', '08/09/2020',Null,'Open',106
    UNION ALL
    SELECT 1,'Savings', '06/11/2020',Null,'Open',107

Answer 3

Jim Young 6

Shouldn't your transaction fact table be individual transactions and not an aggregate by date (if I'm understanding transcount properly)?

RogerBinny1234 41 Reputation points

2021-01-30T16:57:55.25+00:00

No, the fact table I am looking for should be an aggregated one . Aggreagted by data.

So for my result set, the join between the dimension and fact table will yield something like this
RogerBinny1234 41 Reputation points

2021-01-30T16:58:33.64+00:00

And the Output would look like this
RogerBinny1234 41 Reputation points

2021-01-30T17:11:13.237+00:00

Explanation to the answer.

7 days prior to 1st January there were no transactions by any users, so the Last7DaysUsersCount is ZERO and by that time we didn't have any active accounts opened so it is also ZERO. The ratio is 0/0 =0

on 2nd January 2020, we have one transaction 7 days prior to it so the first field's value is 1 and we also got one user by this period so 2nd field's value is also 1. Ration is 1/1=1

on 3rd January 2020, we have 3 transactions 7 days prior to it so the value is 3 and we still got only 1 user by this time. The ratios is 3/1 = 3

similarly until 8th January the ratio remains 3.

However on 9th January, 7 days prior to 9th January is 3rd January. In these 7 days we had only 2 transactions and all the users by 9th January is still 1. The ratio is 2/1=1

on 10th January, 7 days prior date will be 4th January. We didn't have any transactions in this period is ZERO. we had only 1 user by 10th January. The ratio is 0/1=0
RogerBinny1234 41 Reputation points

2021-01-30T17:12:19.353+00:00

So, we should count the number of transactions per last 7 days while we should count all the active users by that time (not just 7 days).

Hope I am clear this time....Sorry, the information has been scattered here and there.

Share via

calculating a measure in T-SQL

2 additional answers

Your answer