How to use next record's date as End date of current record?
-
I have a select statement which its result is something like this:
I would like to have an end date column which is the next start date.
I would appreciate it if someone could guide me.
-
We need table structres of the tables used in your query and if possible the query itself.
Is the table having the End Date already used in the query? if not you need to know what combination of columns identifies a single row of thistable to get you the according data set with the wanted End Date
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
My table is about promotion related to materials and has just start date, the next start date of promotion is the end date of the previous promotion. I need a report to show each promotion when starts and ends.
Select sto_kod As [Stock Code]
, sas_cari_kod As [Vendor]
, sas_brut_fiyat As [Gross Price]
, sas_isk_yuzde1 As [Discount 1]
, sas_isk_yuzde2 As [Discount 2]
, sas_isk_yuzde3 As [Discount 3]
, sas_net_alis_kdvli As [Purchase Price]
, sas_basla_tarih As [Start Date]
FROM dbo.SATINALMA_SARTLARI
Inner Join
dbo.STOKLAR
On sas_stok_kod=sto_kod
Order by sto_kod
, [Start Date]
-
my bet would be that you would want to use the LEAD() function and it's partitioning option of TSQL
If you order and partition your data by the colums that define which belong together like stock code
what you would have to do is:
- partition by atleast stockcode + whatever column restricts if the next row belongs or not
- Order by Partition attributes + the date column
- The partitioned columns should be identical, but the ordering by start date tells the lead function to use the correct next start date as End Da
This lead value of the start date will be the next higher start date, which is what you are looking for?
If you are stuck and not getting the result you need show us what u have done
https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-lead-function/
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
Your solution has worked. Thanks a lot. I put my query here if it helps someone else.
With CTE_EndDate As (
SELECT sto_kod As [Stock Code]
, sto_isim As [Name]
, sto_yabanci_isim As [Foreign Name]
, sas_cari_kod As [Vendor]
, sas_brut_fiyat As [Gross Price]
, sas_isk_yuzde1 As [Discount 1]
, sas_isk_miktar1 As [Discount 1 Amount]
, sas_isk_yuzde2 As [Discount 2]
, sas_isk_miktar2 As [Discount 2 Amount]
, sas_isk_yuzde3 As [Discount 3]
, sas_isk_miktar3 As [Discount 3 Amount]
, sas_net_alis_kdvli As [Purchase Price]
, sas_basla_tarih As [Start Date]
FROM dbo.SATINALMA_SARTLARI
Inner Join
dbo.STOKLAR
On sas_stok_kod = sto_kod
)
Select *
, IsNull(LEAD([Start Date],1) Over (
Partition By [Stock Code]
Order By [Stock Code]
, [Start Date]
),'') EndDate
from CTE_EndDate
-
>> The very first thing that I did is to create a table that will store these Descriptive Labels along with the Actual Start and End Dates for that period. <<
This was a good start, except you didn't actually create a table of all! By definition, a table must have a key. Your DDL had none. So it's basically a deck of punch cards.. Then you put in the physical count of record insertions, also known as identity in the local SQL Server dialect. It is both meaningless, physical and redundant; we hate all these things in RDBMS. You also have no constraints on the two timestamps that form the interval. SQL Server currently has no interval data type, so you have to use a pair but you need to put constraints on the pair to protect ordering and uniqueness. I also have the feeling that your use of a fifty character name was not the result of careful planning, or the use of check constraints to make sure the string is always valid. Why don't you sit down and actually design the encoding scheme for those. Names?
We have a new temporal data type for a date, so there's no need to stay stuck in the original Sybase/SQL Server proprietary data types.
CREATE TABLE TimePeriods
(period_name VARCHAR(35) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK(period_start_date < period_end_date));
There are some other constraints. You can add to guarantee that no two intervals overlap, that you have complete coverage of the calendar, etc. that might be a little advanced for now.
>> Once the table was created, I quickly created a Stored Procedure that will populate values into this table so that our base for the reports will be ready. <<
Instead of writing a procedure, I found it's usually better to get a spreadsheet from the accounting department and make sure that the dates I'm using will match the ones the accounting department is using. Since this is a one-shot, it's just as easy to edit it as text.
Your procedure code doesn't look like SQL at all. It's very procedural, like BASIC or Fortran. But SQL is a declarative language designed for data not for computation. Determining the relative positions like "today", "yesterday" and so forth should not be done in the database layer. It belongs in the presentation layer. Also, for future reference, putting metadata affixes like "usp_" is considered bad programming. The valid data model the data element names should describe what the data element is, not how it's used or implemented in the system. I see you are still using the original Sybase GETDATE() and not the ANSI/ISO standard SQL uses current_timestamp. It will still work, but does not port and it makes you look very very old fashion. Also, ANSI/ISO standard SQL uses only the "yyyy-mm-dd"; since this is the most common ISO standard. After the metric system, this is the most common standard on earth. Experienced programmers will tend to use it instead of some local dialect like you are.
Finally, your intervals, overlap in your print out. This is ambiguous and will result in double counting at the borders. You will now have to write slow code and be very, very careful. It also violates the standard definition of a temporal interval.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/contiguous-time-periods/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply