/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 vote352 views1 answer
Add a new column into table
SQL
7 votes341 views3 answers
Sql fill table with info from anothe rtable
SQL
1 vote349 views1 answer
Postgresql not in
SQL
2 votes351 views4 answers
Mysql get all tables row count
SQL
0 vote395 views2 answers
How to check if a column exists in a SQL Server table?
SQL
4 votes378 views1 answer
An error occurred while installing pg (1.2.3), and Bundler cannot continue. Make sure that `gem install pg -v '1.2.3' --source 'https://rubygems.org/'` succeeds before bundling.
SQL
0 vote332 views1 answer
Use where instead of join
SQL
0 vote330 views1 answer
Pl sql search in all packages
SQL
1 vote348 views3 answers
Mysql connection string
SQL
10 votes355 views4 answers
Drop primary key
SQL