August 16, 2016 at 11:37 am
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 16, 2016 at 11:52 am
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
Sure. this is a homework question that I want to use the logic for some analysis I want to do. I am translating this to point to actual table and actual data. The #of customers is in millions and date ranges can be anywhere between 1/1/2015 - current date.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days. Please let me know if you want more data. Happy to provide more mock data.
August 16, 2016 at 12:26 pm
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
Sure. this is a homework question that I want to use the logic for some analysis I want to do. I am translating this to point to actual table and actual data. The #of customers is in millions and date ranges can be anywhere between 1/1/2015 - current date.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days. Please let me know if you want more data. Happy to provide more mock data.
okay...
I have no issue helping people with homework, but you may find that the responses you get, expect you to do some work as well, rather than getting a cut and paste solution.
seems an interesting project......please provide a larger sample data set (cover multiple customers and creditsIDs/dates etc) and expected results based on the sample data
please see this article on how to provide
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 16, 2016 at 12:59 pm
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
Sure. this is a homework question that I want to use the logic for some analysis I want to do. I am translating this to point to actual table and actual data. The #of customers is in millions and date ranges can be anywhere between 1/1/2015 - current date.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days. Please let me know if you want more data. Happy to provide more mock data.
okay...
I have no issue helping people with homework, but you may find that the responses you get, expect you to do some work as well, rather than getting a cut and paste solution.
seems an interesting project......please provide a larger sample data set (cover multiple customers and creditsIDs/dates etc) and expected results based on the sample data
please see this article on how to provide
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Of course. life is never that simple right? 😉 jk... been working on this issue for a while now. keep getting stuck at the same point. I am very interested to learn your and other approaches work (hence the reason I have been asking to get some explanation on the solutions.) I do understand your are taking time from your busy schedule to help with this. So appreciate it a lot.
Create table #t1
(
TransactionID int
,Customer int
,TransactionType varchar(10)
,[Date] date
,Amount money
,CreditID int)
Insert into #t1
Values (1,1,'Credit', '1/1/16',-20 ,1 )
Insert into #t1
Values (2,1,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (3,1,'Credit','1/3/16',-100,2)
Insert into #t1
Values (4,1,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (5,1,'Debit' ,'1/5/16',50,Null)
Insert into #t1
Values (6,1,'Credit','1/6/16',-50 ,3)
Insert into #t1
Values (7,1,'Debit' ,'1/7/16',20,Null)
Insert into #t1
Values (8,1,'Credit','1/8/16',-50 ,4)
Insert into #t1
Values (9,1,'Debit' ,'1/9/16',20, Null )
Insert into #t1
Values (1,2,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (2,2,'Credit','1/3/16',-100,1)
Insert into #t1
Values (3,2,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (4,2,'Debit' ,'1/5/16',40,Null)
Insert into #t1
Values (1,3,'Credit','1/3/16',-100,1)
Insert into #t1
Values (2,3,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (3,3,'Credit' ,'1/5/16',-200,2)
Insert into #t1
Values (1,4,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,4,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,4,'Credit' ,'1/5/16',-45,2)
Insert into #t1
Values (1,5,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,5,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,5,'Credit' ,'1/5/16',-40,2)
Insert into #t1
Values (4,5,'Debit' ,'1/6/16',40,Null)
Insert into #t1
Values (5,5,'Credit' ,'1/7/16',-48,3)
;
;
I think this should cover all.
August 16, 2016 at 1:05 pm
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
Sure. this is a homework question that I want to use the logic for some analysis I want to do. I am translating this to point to actual table and actual data. The #of customers is in millions and date ranges can be anywhere between 1/1/2015 - current date.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days. Please let me know if you want more data. Happy to provide more mock data.
okay...
I have no issue helping people with homework, but you may find that the responses you get, expect you to do some work as well, rather than getting a cut and paste solution.
seems an interesting project......please provide a larger sample data set (cover multiple customers and creditsIDs/dates etc) and expected results based on the sample data
please see this article on how to provide
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Of course. life is never that simple right? 😉 jk... been working on this issue for a while now. keep getting stuck at the same point. I am very interested to learn your and other approaches work (hence the reason I have been asking to get some explanation on the solutions.) I do understand your are taking time from your busy schedule to help with this. So appreciate it a lot.
Create table #t1
(
TransactionID int
,Customer int
,TransactionType varchar(10)
,[Date] date
,Amount money
,CreditID int)
Insert into #t1
Values (1,1,'Credit', '1/1/16',-20 ,1 )
Insert into #t1
Values (2,1,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (3,1,'Credit','1/3/16',-100,2)
Insert into #t1
Values (4,1,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (5,1,'Debit' ,'1/5/16',50,Null)
Insert into #t1
Values (6,1,'Credit','1/6/16',-50 ,3)
Insert into #t1
Values (7,1,'Debit' ,'1/7/16',20,Null)
Insert into #t1
Values (8,1,'Credit','1/8/16',-50 ,4)
Insert into #t1
Values (9,1,'Debit' ,'1/9/16',20, Null )
Insert into #t1
Values (1,2,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (2,2,'Credit','1/3/16',-100,1)
Insert into #t1
Values (3,2,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (4,2,'Debit' ,'1/5/16',40,Null)
Insert into #t1
Values (1,3,'Credit','1/3/16',-100,1)
Insert into #t1
Values (2,3,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (3,3,'Credit' ,'1/5/16',-200,2)
Insert into #t1
Values (1,4,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,4,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,4,'Credit' ,'1/5/16',-45,2)
Insert into #t1
Values (1,5,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,5,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,5,'Credit' ,'1/5/16',-40,2)
Insert into #t1
Values (4,5,'Debit' ,'1/5/16',-40,Null)
Insert into #t1
Values (5,5,'Credit' ,'1/5/16',-48,3)
;
;
I think this should cover all.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days.
and your results will be what?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 16, 2016 at 2:27 pm
Hello all,
I have realised that my new data set has more data and more scenarios to be considered to come up with a solution.
During this process I learnt a lot of how to ask a question and thanks everyone who were patient with me and helped to understand this process.
I want to ask if you all are ok if I start a new thread and write up my problem , provide Insert statements and expected results. Please let me know. If not I am happy to use this thread.
August 16, 2016 at 3:15 pm
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
jssashank (8/16/2016)
J Livingston SQL (8/16/2016)
can I please ask you to explain why you wish to exclude rows where the first row is a debit?Sure. My intention for adding that is to make the data set similar to the test data set (for which the code works) . So every set of transactions for a customer begins with a credit . I am still trying out few test cases to see if that theory works.This is a temporary fix until I figure out how the code works 🙂
okay...but your test data set is so small and you havent provided anything else...so the question I have is this a "homework" question or based on a real business system.
If it is a real business system, then I would not expect any code to specifically exclude any actual data be that either "credit" or "debit"...irrespective of your your [date] column.
Can I ask please what are you actaully doing with the result set, how is it being presented to the the end users and how often and by how many users is it intended to be run.
what is the overall sclae of the underlying data (eg number of customers, creditIds, date range etc)
Sure. this is a homework question that I want to use the logic for some analysis I want to do. I am translating this to point to actual table and actual data. The #of customers is in millions and date ranges can be anywhere between 1/1/2015 - current date.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days. Please let me know if you want more data. Happy to provide more mock data.
okay...
I have no issue helping people with homework, but you may find that the responses you get, expect you to do some work as well, rather than getting a cut and paste solution.
seems an interesting project......please provide a larger sample data set (cover multiple customers and creditsIDs/dates etc) and expected results based on the sample data
please see this article on how to provide
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Of course. life is never that simple right? 😉 jk... been working on this issue for a while now. keep getting stuck at the same point. I am very interested to learn your and other approaches work (hence the reason I have been asking to get some explanation on the solutions.) I do understand your are taking time from your busy schedule to help with this. So appreciate it a lot.
Create table #t1
(
TransactionID int
,Customer int
,TransactionType varchar(10)
,[Date] date
,Amount money
,CreditID int)
Insert into #t1
Values (1,1,'Credit', '1/1/16',-20 ,1 )
Insert into #t1
Values (2,1,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (3,1,'Credit','1/3/16',-100,2)
Insert into #t1
Values (4,1,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (5,1,'Debit' ,'1/5/16',50,Null)
Insert into #t1
Values (6,1,'Credit','1/6/16',-50 ,3)
Insert into #t1
Values (7,1,'Debit' ,'1/7/16',20,Null)
Insert into #t1
Values (8,1,'Credit','1/8/16',-50 ,4)
Insert into #t1
Values (9,1,'Debit' ,'1/9/16',20, Null )
Insert into #t1
Values (1,2,'Debit' , '1/2/16',20,Null)
Insert into #t1
Values (2,2,'Credit','1/3/16',-100,1)
Insert into #t1
Values (3,2,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (4,2,'Debit' ,'1/5/16',40,Null)
Insert into #t1
Values (1,3,'Credit','1/3/16',-100,1)
Insert into #t1
Values (2,3,'Debit' ,'1/4/16',50,Null)
Insert into #t1
Values (3,3,'Credit' ,'1/5/16',-200,2)
Insert into #t1
Values (1,4,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,4,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,4,'Credit' ,'1/5/16',-45,2)
Insert into #t1
Values (1,5,'Credit','1/3/16',-45,1)
Insert into #t1
Values (2,5,'Debit' ,'1/4/16',45,Null)
Insert into #t1
Values (3,5,'Credit' ,'1/5/16',-40,2)
Insert into #t1
Values (4,5,'Debit' ,'1/5/16', 40,Null)
Insert into #t1
Values (5,5,'Credit' ,'1/5/16',-48,3)
;
;
I think this should cover all.
The analysis is what percentage of credit issued is being redeemed within 15 days, 15-30 days and 30-60 days.
and your results will be what?
Here is the expected Results
CustomerCredit_idLast Reddem DateRemaining Balance
1 1 1/2/16 $0.00
121/5/16$0.00
131/9/16$10.00
14Null $50.00
211/5/16$10.00
311/4/16$50.00
32Null $200.00
411/4/16$0.00
42Null $45.00
511/4/16$0.00
521/6/16$0.00
53Null $48.00
August 16, 2016 at 3:17 pm
Please see that I have corrected one of the values for the inserts. (last but one)
Insert into #t1
Values (4,5,'Debit' ,'1/5/16', 40,Null)
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply