/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 vote326 views1 answer
Mysql change root mysql_native_password
SQL
1 vote345 views1 answer
Postgres cast as currency format
SQL
0 vote339 views1 answer
The report definition has an invalid target namespace 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
SQL
0 vote339 views1 answer
Sql developer connect to sql server
SQL
0 vote323 views1 answer
Which sql statement would you use to remove a view called emp_dept_vu from your schema?
SQL
0 vote310 views1 answer
Postgres float to int
SQL
0 vote317 views2 answers
How to see table associated with a schema in sql
SQL
2 votes335 views2 answers
How to rename column name in sql server using query
SQL
6 votes298 views2 answers
Spring data jpa application.properties mysql
SQL
1 vote326 views1 answer
SQL server query column yes or no
SQL