/SQL/Comparing with today and yesterday record in sql query

Comparing with today and yesterday record in sql query

declare @bgn_dt date = '2017-12-15' --set by OP
    , @end_dt date = '2017-12-22' --set by OP
    , @lag_dt date;

set @lag_dt = (select max(MyDate) from #myTable where MyDate < @bgn_dt) --get the "yesterday" that the @bgn_dt will need

select a.MyDate
, a.SalesTotal
, format(((1.0 * a.SalesTotal) / a.SalesTotalPrevDay) - 1, '0%') as SalesTotalChange
from (
    select t.MyDate
    , t.SalesTotal
    , lag(t.SalesTotal, 1, NULL) over (/*partition by (if needed)*/ order by t.MyDate asc) as SalesTotalPrevDay
    from #myTable as t
    where 1=1
    and t.MyDate between @lag_dt and @end_dt
    ) as a
where 1=1
and a.MyDate >= @bgn_dt

Source: stackoverflow.com

Similar codes

1 vote296 views2 answers
Populate sql table with random data
SQL
0 vote298 views1 answer
Plpgsql if statement
SQL
-1 vote290 views1 answer
Sql syntax create timestamp column
SQL
0 vote527 views1 answer
The SELECT permission has not been granted on 'sys.sql_logins' for the 'master' database. You must be a member of the 'loginmanager' role to access this system view.
SQL
2 votes307 views1 answer
Grant revoke privileges to mysql username
SQL
0 vote314 views1 answer
Mysql query to check record exists in database table or not
SQL
4 votes312 views1 answer
Mysql copy table with new name
SQL
0 vote302 views1 answer
Get the Db column names from a SqlDataReader
SQL
1 vote354 views1 answer
UseSqlServer no definition
SQL
1 vote280 views1 answer
Mysqli connect
SQL