July 5, 2018 at 1:16 pm
Hi ,
I have a table with Loannumbers , and each Lonnumber has different Price for each date( something like market value in each year) as shown as below. I want to find the difference between latest value and the previous value. So i took rank by date and got the two values. How can I calculate the difference ?
| loannumber | Date | FairMarketValue | rnk |
| 12345 | 5/19/2017 | 975000 | 1 |
| 12345 | 5/31/2016 | 1040000 | 2 |
Create table #Temp(
Loannumber varchar(10),
Inspectiondate varchar(10)
,Marketvalue float
);
Insert into #Temp values (12345,'2017-05-19',975000.00)
Insert into #Temp values (12345,'2016-05-31',1040000.00)
July 5, 2018 at 2:09 pm
Does this work for you?
drop table if exists #Temp
Create table #Temp(
LoanNumber varchar(10),
Inspectiondate varchar(10)
,Marketvalue float
);
Insert into #Temp values (12345,'2017-05-19',975000.00)
Insert into #Temp values (12345,'2016-05-31',1040000.00)
;with cte as (select LoanNumber, Inspectiondate, Marketvalue, Row_number() over (partition by LoanNumber order by InspectionDate) RowNum from #Temp)
select CurRow.LoanNumber, NextRow.MarketValue - CurRow.MarketValue, NextRow.MarketValue, CurRow.MarketValue
from CTE CurRow
left join CTE NextRow
on CurRow.LoanNumber = NextRow.LoanNumber
and CurRow.RowNum = NextRow.RowNum - 1
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2018 at 7:29 pm
komal145 - Thursday, July 5, 2018 1:16 PMHi ,
I have a table with Loannumbers , and each Lonnumber has different Price for each date( something like market value in each year) as shown as below. I want to find the difference between latest value and the previous value. So i took rank by date and got the two values. How can I calculate the difference ?
loannumber Date FairMarketValue rnk 12345 5/19/2017 975000 1 12345 5/31/2016 1040000 2 Create table #Temp(
Loannumber varchar(10),
Inspectiondate varchar(10)
,Marketvalue float
);
Insert into #Temp values (12345,'2017-05-19',975000.00)
Insert into #Temp values (12345,'2016-05-31',1040000.00)
>> I have a table with Loannumbers , and each Lonnumber has different Price for each date (something like market value in each year) as shown as below. I want to find the difference between latest value and the previous value. So I took rank by date and got the two values. How can I calculate the difference ? <<
The first thing we need to do is correct your DDL. #Temp is not a good table name; get in the habit of naming a table for the set of entities or the relationship which it models. Numeric identifiers are seldom variable length; have you ever had a course in basic data modeling? We were very proud of the fact that we put the date and time datatypes in SQL; this saved people from having to use the old COBOL strings. You've just reversed and created a lot of trouble for yourself by throwing away the last 30+ years of the language. Then on top of that you got the format for a date wrong; the only format allowed in ANSI/ISO standard SQL is based on the ISO 8601 standards (yyyy-mm-dd).
But the error which can put you in jail is using floating-point for money. The EU and the United States have standards for how money is stored, and they all depend on fixed decimal places. Floating-point math is what is called floating-point rounding errors and it means that you can't do accurate calculations with it. Back in the 1960s was a Fortran programmer and that language only had floating-point, we had to do all kinds of tricks to minimize (but we could not prevent) these rounding errors. I seriously doubt if you have taken the two week course required to learn how to do that math.
CREATE TABLE Loans --- meaningful name
(loan_nbr CHAR(10) NOT NULL, --- really need to check constraint to assure that it's only digits
inspection_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, --- correct data type
fairmarket_value DECIMAL(10,2) NOT NULL CHECK (fairmarket_value >= 0.00), --- correct data type
PRIMARY KEY (loan_nbr, inspection_date));
Here is another way to do this:
WITH X
AS
(SELECT loan_nbr, inspection_date, fairmarket_value,
LAG(fairmarket_value) OVER (PARTITION BY loan_nbr ORDER BY inspection_date ASC) AS prior_fairmarket_value
FROM Loans)
SELECT loan_nbr, inspection_date, fairmarket_value,
(fairmarket_value - prior_fairmarket_value) AS fairmarket_value_delta
FROM X ;
The lead and lag functions are relatively new and were designed for exactly your problem. You can Google it to read the details of how these functions work.The lead and lag functions are relatively new and were designed for exactly your problem. You can Google it to read the details of how these functions work.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 5, 2018 at 9:44 pm
jcelko212 32090 - Thursday, July 5, 2018 7:29 PMThe first thing we need to do is correct your DDL. #Temp is not a good table name;
It's just a test table in TempDB for the forum, Joe. Most folks have real tables with real names.
But the error which can put you in jail is using floating-point for money. The lead and lag functions are relatively new and were designed for exactly your problem. You can Google it to read the details of how these functions work.The lead and lag functions are relatively new and were designed for exactly your problem. You can Google it to read the details of how these functions work.
Totally agree on both points.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2018 at 7:48 am
But the error which can put you in jail is using floating-point for money. The EU and the United States have standards for how money is stored, and they all depend on fixed decimal places.
Bitcoins ? 😛
Ben
(Or storing prices with less than a cent for each item. )
July 6, 2018 at 10:08 am
I can probably get a good article out of this, but you have the guys that grew up with tape drives and discs that had alphabetic names, so the name their tables "A", "B", etc. to keep the same mindset. The guys grew up with procedural languages that had length limits on names (my first language was Fortran, so I got real good at six letter first names for variables when I first started programming. I honestly couldn't see it until somebody did a code review on me after Ihad been programming for a couple of years). People who don't understand a table is a set of things and therefore should have a collective or plural name; the unit of work in file languages is the individual record, so they you singular names ("Employee" instead of "Personnel"). I know not everybody likes to sit down with the ANSI or ISO standards, and read them, but the ISO 11179 and metadata standards are really pretty easy (once you get past "standard speak" language they use).
>> It's just a test table in TempDB for the forum, Joe. Most folks have real tables with real names. <<
Please post DDL and follow ANSI/ISO standards when asking for help.
July 6, 2018 at 10:23 am
jcelko212 32090 - Thursday, July 5, 2018 7:29 PMI have to disagree about "real names" being used in real code. It's not just SQL programmers, but other languages have the same problem ("Clean Code" by Robert C Martin mentions this for agile programmers, too). I really think people should get in the habit, even on forum post, of thinking about their code and how to name things. How many garbage names have you seen in production?I can probably get a good article out of this, but you have the guys that grew up with tape drives and discs that had alphabetic names, so the name their tables "A", "B", etc. to keep the same mindset. The guys grew up with procedural languages that had length limits on names (my first language was Fortran, so I got real good at six letter first names for variables when I first started programming. I honestly couldn't see it until somebody did a code review on me after Ihad been programming for a couple of years). People who don't understand a table is a set of things and therefore should have a collective or plural name; the unit of work in file languages is the individual record, so they you singular names ("Employee" instead of "Personnel"). I know not everybody likes to sit down with the ANSI or ISO standards, and read them, but the ISO 11179 and metadata standards are really pretty easy (once you get past "standard speak" language they use).
>> It's just a test table in TempDB for the forum, Joe. Most folks have real tables with real names. <<
Joe, Why do you insist on living in the 60's and 70's still? Get a life and move into the 21st Century. Most people posting questions on this site were probably born in the 80's and 90's and have NO IDEA what you are talking about.
And again, I am still waiting for you to send me my free editions of the standards you keep telling us we all should be following. I don't have the money to pay for them.
July 8, 2018 at 1:33 am
komal145 - Thursday, July 5, 2018 1:16 PMHi ,
I have a table with Loannumbers , and each Lonnumber has different Price for each date( something like market value in each year) as shown as below. I want to find the difference between latest value and the previous value. So i took rank by date and got the two values. How can I calculate the difference ?
loannumber Date FairMarketValue rnk 12345 5/19/2017 975000 1 12345 5/31/2016 1040000 2 Create table #Temp(
Loannumber varchar(10),
Inspectiondate varchar(10)
,Marketvalue float
);
Insert into #Temp values (12345,'2017-05-19',975000.00)
Insert into #Temp values (12345,'2016-05-31',1040000.00)
Here is one way of doing this using the LAG window function
USE TEEST;
GO
SET NOCOUNT ON;
-- https://www.sqlservercentral.com/Forums/1975049/Difference-between-minimum-and-maximum-value
IF OBJECT_ID(N'dbo.TBL_LOANS') IS NOT NULL DROP TABLE dbo.TBL_LOANS;
-- Sample table with numeric constraint on the loan number column, this can be adjusted to
-- another patterns such as [A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] etc.
CREATE TABLE dbo.TBL_LOANS
(
TL_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_LOANS_TL_ID PRIMARY KEY CLUSTERED
,TL_LOANNUMBER VARCHAR(10) NOT NULL CONSTRAINT CHK_DBO_TBL_LOANS_TL_LOANNUMBER CHECK (TL_LOANNUMBER LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
,TL_DATE DATETIME NOT NULL
,TL_AMOUNT NUMERIC(15,3) NOT NULL
);
-- Sample data set
INSERT INTO dbo.TBL_LOANS(TL_LOANNUMBER,TL_DATE,TL_AMOUNT)
VALUES
('1000000001','20180101', 100000)
,('1000000002','20180101',1100000)
,('1000000003','20180101', 100000)
,('1000000004','20180101', 100000)
,('1000000005','20180101', 100000)
,('1000000001','20180201', 110000)
,('1000000002','20180301', 120000)
,('1000000003','20180401', 130000)
,('1000000004','20180501', 410000)
,('1000000005','20180601', 150000)
;
-- Pattern suggestion, use the LAG function to get the
-- previous value.
SELECT
TL.TL_ID
,TL.TL_LOANNUMBER
,TL.TL_DATE
,TL.TL_AMOUNT
,TL.TL_AMOUNT - LAG(TL.TL_AMOUNT,1,0) OVER
(
PARTITION BY TL.TL_LOANNUMBER
ORDER BY TL.TL_DATE ASC
) AS TL_DIFFERENCE
FROM dbo.TBL_LOANS TL;
Output
TL_ID TL_LOANNUMBER TL_DATE TL_AMOUNT TL_DIFFERENCE
------ ------------- ----------------------- ------------ --------------
1 1000000001 2018-01-01 00:00:00.000 100000.000 100000.000
6 1000000001 2018-02-01 00:00:00.000 110000.000 10000.000
2 1000000002 2018-01-01 00:00:00.000 1100000.000 1100000.000
7 1000000002 2018-03-01 00:00:00.000 120000.000 -980000.000
3 1000000003 2018-01-01 00:00:00.000 100000.000 100000.000
8 1000000003 2018-04-01 00:00:00.000 130000.000 30000.000
4 1000000004 2018-01-01 00:00:00.000 100000.000 100000.000
9 1000000004 2018-05-01 00:00:00.000 410000.000 310000.000
5 1000000005 2018-01-01 00:00:00.000 100000.000 100000.000
10 1000000005 2018-06-01 00:00:00.000 150000.000 50000.000
July 8, 2018 at 8:47 am
jcelko212 32090 - Thursday, July 5, 2018 7:29 PMI have to disagree about "real names" being used in real code. It's not just SQL programmers, but other languages have the same problem ("Clean Code" by Robert C Martin mentions this for agile programmers, too). I really think people should get in the habit, even on forum post, of thinking about their code and how to name things. How many garbage names have you seen in production?I can probably get a good article out of this, but you have the guys that grew up with tape drives and discs that had alphabetic names, so the name their tables "A", "B", etc. to keep the same mindset. The guys grew up with procedural languages that had length limits on names (my first language was Fortran, so I got real good at six letter first names for variables when I first started programming. I honestly couldn't see it until somebody did a code review on me after Ihad been programming for a couple of years). People who don't understand a table is a set of things and therefore should have a collective or plural name; the unit of work in file languages is the individual record, so they you singular names ("Employee" instead of "Personnel"). I know not everybody likes to sit down with the ANSI or ISO standards, and read them, but the ISO 11179 and metadata standards are really pretty easy (once you get past "standard speak" language they use).
>> It's just a test table in TempDB for the forum, Joe. Most folks have real tables with real names. <<
jcelko212 32090 - Thursday, July 5, 2018 7:29 PMI have to disagree about "real names" being used in real code. It's not just SQL programmers, but other languages have the same problem ("Clean Code" by Robert C Martin mentions this for agile programmers, too). I really think people should get in the habit, even on forum post, of thinking about their code and how to name things. How many garbage names have you seen in production?I can probably get a good article out of this, but you have the guys that grew up with tape drives and discs that had alphabetic names, so the name their tables "A", "B", etc. to keep the same mindset. The guys grew up with procedural languages that had length limits on names (my first language was Fortran, so I got real good at six letter first names for variables when I first started programming. I honestly couldn't see it until somebody did a code review on me after Ihad been programming for a couple of years). People who don't understand a table is a set of things and therefore should have a collective or plural name; the unit of work in file languages is the individual record, so they you singular names ("Employee" instead of "Personnel"). I know not everybody likes to sit down with the ANSI or ISO standards, and read them, but the ISO 11179 and metadata standards are really pretty easy (once you get past "standard speak" language they use).
>> It's just a test table in TempDB for the forum, Joe. Most folks have real tables with real names. <<
I deplore the use of plurals for table names whether the ISO standards require them or not but that's a discussion that we can have elsewhere instead of totally hijacking this thread 😉
As for the reading of ISO standards, the biggest problem is more likely that that the damned things cost a small fortune to buy and they take forever to get through. For example, ISO 11179 has six sections to it and 11179-3 has 320 pages in it. Do YOU have a specific section an paragraph number that refers to how table names or even "entity" names should be identified? If so, please post it so that we can read specifically about what you're speaking of rather than having to find it ourselves (that's provided that we can find a free, if not illegal, copy of the standards on the internet).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2018 at 10:41 am
jcelko212 32090 - Thursday, July 5, 2018 7:29 PM
As for the reading of ISO standards, the biggest problem is more likely that that the damned things cost a small fortune to buy and they take forever to get through. For example, ISO 11179 has six sections to it and 11179-3 has 320 pages in it. Do YOU have a specific section an paragraph number that refers to how table names or even "entity" names should be identified? If so, please post it so that we can read specifically about what you're speaking of rather than having to find it ourselves (that's provided that we can find a free, if not illegal, copy of the standards on the internet).
Over here in America, the ANSI standards are paid for by member dues. We actively want them to be available to the public, so be implemented. In the ISO world, the organization is funded by sales, so they want very tight control (and very high prices) on their source of income.
Every country that is an ISO member gets a vote. This means that Elbonia and Germany are equal. The members of ISO tend to be government or NGO agencies in ANSI, the members tend to be companies for trying to get out of product, with a few academics thrown in. Governments tend not to care when they get something done, while companies would like to get it out as fast as possible.
The advantage of the ANSI system is that people are free to write "popular science" explanations of the standards. The bad news is we wind up with a lot of specialized groups in industry to deal with particular things. For example, your best look at the table naming conventions come from the metadata standards people.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 8, 2018 at 11:08 am
Jeff Moden - Thursday, July 5, 2018 9:44 PMjcelko212 32090 - Thursday, July 5, 2018 7:29 PM
As for the reading of ISO standards, the biggest problem is more likely that that the damned things cost a small fortune to buy and they take forever to get through. For example, ISO 11179 has six sections to it and 11179-3 has 320 pages in it. Do YOU have a specific section an paragraph number that refers to how table names or even "entity" names should be identified? If so, please post it so that we can read specifically about what you're speaking of rather than having to find it ourselves (that's provided that we can find a free, if not illegal, copy of the standards on the internet).
Over here in America, the ANSI standards are paid for by member dues. We actively want them to be available to the public, so be implemented. In the ISO world, the organization is funded by sales, so they want very tight control (and very high prices) on their source of income.
Every country that is an ISO member gets a vote. This means that Elbonia and Germany are equal. The members of ISO tend to be government or NGO agencies in ANSI, the members tend to be companies for trying to get out of product, with a few academics thrown in. Governments tend not to care when they get something done, while companies would like to get it out as fast as possible.
The advantage of the ANSI system is that people are free to write "popular science" explanations of the standards. The bad news is we wind up with a lot of specialized groups in industry to deal with particular things. For example, your best look at the table naming conventions come from the metadata standards people.
Given that the published standards prices starts at around $100 - 150, the whole approach looks unsustainable, better go for the RFC model where no one has to shell out just for the purpose of reading the bl###y thing.
😎
This mentality is archaic and does not work in the modern world, reminds me of convicts in a prison canteen, guarding the grub they are eating with the stronger arm whilst holding the spoon in a knife in an attack position in the other.
Very sad indeed!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply