/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

0 vote186 views3 answers
Postgresql allow remote connections
SQL
17 votes224 views6 answers
MySQL ORDER BY
SQL
0 vote201 views1 answer
Convert int to varchar sql
SQL
1 vote179 views2 answers
OSError: mysql_config not found mysql_config --version mariadb_config --version mysql_config --libs ---------------------------------------- ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command outpu
SQL
0 vote171 views1 answer
Mysql drop multiple tables at once
SQL
5 votes172 views3 answers
Postgres show databases
SQL
0 vote176 views1 answer
Criteria builder select subset of column
SQL
12 votes169 views4 answers
Import mysql database command line linux
SQL
0 vote232 views1 answer
TSQL Find csv file in folder
SQL
1 vote176 views1 answer
Tsql get beginning of year
SQL