SQL : How can I get previous day

Question

How can I get the result in the PreviousDay column that the day should be -1 from the day in efdt column for all rows? If the efdt date is 2001-04-02 the result in PreviousDay should be 2001-04-01 and DATEDIFF is 334 Image attached with it please refer it.

Answer 1

You can use the SQL Server LEAD window function over the "efdt" field, which will take the following value given two clauses:

• PARTITION BY, which indicates the groups to work on
• ORDER BY, which decided which value to take according to an order imposed by one (or more) of your fields.

SELECT T.*,
       DATEDIFF(DAY, efdt, PreviousDay)
FROM (SELECT T1.*,
             LEAD(efdt) OVER(PARTITION BY emcd ORDER BY efdt) AS PreviousDay
      FROM psf206 T1) AS T
ORDER BY emcd, efdt

Check the demo here.

Note: if you want one day less for each row, just add -1 after the DATEDIFF operation (demo).