June 29, 2018 at 12:19 pm
I have a table something like below:
DROP table #step2
Create Table #steps2(
Loannumber Varchar(10)
,Step1 varchar(10)
,Step2 varchar(10)
,Step3 varchar(10)
,Step4 varchar(10)
,Step5 varchar(10)
,Step6 varchar(10)
,Step7 varchar(10)
,Step8 varchar(10)
,Step9 varchar(10)
,Step10 varchar(10)
,Numberofsteps int
,maxstep varchar(10)
)
Insert into #steps2 (loannumber , step1) Values ('12345','1/2/2016')
Insert into #steps2 (loannumber , step1,step2) Values ('12345','1/3/2016','2/12/2017')
Insert into #steps2 (loannumber , step1,step2,step3,step4,step5) Values ('12345','1/2/2018','1/3/2018','1/4/2018','1/5/2018','1/6/2018')
Insert into #steps2 (loannumber , step1,step2) Values ('12345','1/2/2/2019','1/2/2/2020')
Insert into #steps2 (loannumber ) Values ('12345')
| Loannumber | Step1 | Step2 | Step3 | Step4 | Step5 | Step6 | Step7 | Step8 | Step9 | Step10 | Numberof steps | max step |
| 12345 | 1/2/2016 | 1 | Step1 | |||||||||
| 12345 | 1/3/2016 | 2/12/2017 | 2 | Step2 | ||||||||
| 12345 | 1/2/2018 | 1/3/2018 | 1/4/2018 | 1/5/2018 | 1/6/2018 | 5 | Step5 | |||||
| 12345 | 1/2/2/2019 | 1/2/2/2020 | 2 | step2 | ||||||||
| 12345 | 0 | 0 | ||||||||||
I need to get the Number of steps for each loannumber ( though for example i took same loannumber ) and also maxstep which tells the loan in which step?
June 29, 2018 at 12:35 pm
My first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.
June 29, 2018 at 12:38 pm
Lynn Pettis - Friday, June 29, 2018 12:35 PMMy first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.
+1.
If you can't do that, create a view which presents the data in normalised form and query that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2018 at 1:24 pm
Lynn Pettis - Friday, June 29, 2018 12:35 PMMy first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.
This is just an example , i created for explanation. Actual table is different.
June 29, 2018 at 1:30 pm
komal145 - Friday, June 29, 2018 1:24 PMLynn Pettis - Friday, June 29, 2018 12:35 PMMy first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.This is just an example , i created for explanation. Actual table is different.
So what did you expect?
We can offer advice based only on what you tell us.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2018 at 1:46 pm
Phil Parkin - Friday, June 29, 2018 1:30 PMkomal145 - Friday, June 29, 2018 1:24 PMLynn Pettis - Friday, June 29, 2018 12:35 PMMy first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.This is just an example , i created for explanation. Actual table is different.
So what did you expect?
We can offer advice based only on what you tell us.
I am trying to calculate two fields 1) NumofSteps for each loan 2) Maxstep for each loan. Can you please see above example and suggest how do i do calculation in sql query?
June 29, 2018 at 1:57 pm
komal145 - Friday, June 29, 2018 1:46 PMPhil Parkin - Friday, June 29, 2018 1:30 PMkomal145 - Friday, June 29, 2018 1:24 PMLynn Pettis - Friday, June 29, 2018 12:35 PMMy first suggestion, normalize the table. Right now, if you add another step you have to add another column. If you normalize the data each step becomes a row in a child table and if you need to add another step or two, it is just adding an additional row of data.This is just an example , i created for explanation. Actual table is different.
So what did you expect?
We can offer advice based only on what you tell us.I am trying to calculate two fields 1) NumofSteps for each loan 2) Maxstep for each loan. Can you please see above example and suggest how do i do calculation in sql query?
And why would we bother doing that, if 'actual table is different'? Our solution would not work on 'actual table'.
Show us the real table, or something close, and we can tell you how to normalise it and then how to query it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2018 at 2:08 pm
You could try doing a CROSS APPLY such as in this article to operate on the data in a set based fassion:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Then you can do aggregates on it:SELECT Loannumber, x.NumOfSteps, x.MaxStep
FROM #steps2
CROSS APPLY (SELECT COUNT(*) NumOfSteps, MAX(num) AS MaxStep FROM (VALUES (1,Step1), (2,Step2), (3,Step3), (4,Step4), (5,Step5), (6,Step6), (7,Step7), (8,Step8), (9,Step9), (10,Step10)) AS VALUE(num, val) WHERE val IS NOT NULL) x
I suppose if you have multiple rows with the same LoanNumber as in your sample data, you'd have to do a GROUP BY LoanNumber in your outer query
June 29, 2018 at 2:20 pm
Chris Harshman - Friday, June 29, 2018 2:08 PMYou could try doing a CROSS APPLY such as in this article to operate on the data in a set based fassion:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Then you can do aggregates on it:SELECT Loannumber, x.NumOfSteps, x.MaxStep
FROM #steps2
CROSS APPLY (SELECT COUNT(*) NumOfSteps, MAX(num) AS MaxStep FROM (VALUES (1,Step1), (2,Step2), (3,Step3), (4,Step4), (5,Step5), (6,Step6), (7,Step7), (8,Step8), (9,Step9), (10,Step10)) AS VALUE(num, val) WHERE val IS NOT NULL) xI suppose if you have multiple rows with the same LoanNumber as in your sample data, you'd have to do a GROUP BY LoanNumber in your outer query
Works fine with the sample data returning what is needed. Here is a slight variation on the code Chris posted:
SELECT
[Loannumber]
, Step1
, Step2
, Step3
, Step4
, Step5
, Step6
, Step7
, Step8
, Step9
, Step10
, [x].[NumOfSteps]
, [MaxStep] = ISNULL([x].[MaxStep],0)
FROM
[dbo].[steps2]
CROSS APPLY
(
SELECT
COUNT(*) [NumOfSteps]
, MAX([num]) AS [MaxStep]
FROM
(
VALUES
(1, [Step1])
, (2, [Step2])
, (3, [Step3])
, (4, [Step4])
, (5, [Step5])
, (6, [Step6])
, (7, [Step7])
, (8, [Step8])
, (9, [Step9])
, (10, [Step10])
) AS [VALUE] ([num], [val])
WHERE
[val] IS NOT NULL
) [x];
go
June 29, 2018 at 2:33 pm
Chris Harshman - Friday, June 29, 2018 2:08 PMYou could try doing a CROSS APPLY such as in this article to operate on the data in a set based fassion:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Then you can do aggregates on it:SELECT Loannumber, x.NumOfSteps, x.MaxStep
FROM #steps2
CROSS APPLY (SELECT COUNT(*) NumOfSteps, MAX(num) AS MaxStep FROM (VALUES (1,Step1), (2,Step2), (3,Step3), (4,Step4), (5,Step5), (6,Step6), (7,Step7), (8,Step8), (9,Step9), (10,Step10)) AS VALUE(num, val) WHERE val IS NOT NULL) xI suppose if you have multiple rows with the same LoanNumber as in your sample data, you'd have to do a GROUP BY LoanNumber in your outer query
Thank you , it works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply