/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 vote112 views2 answers
Create new table plsql
SQL
2 votes108 views1 answer
Oracle drop table free up space
SQL
0 vote110 views1 answer
Port 5432 is already in use Usually this means that there is already a PostgreSQL server running on your Mac. If you want to run multiple servers simultaneously, use different ports.
SQL
3 votes106 views1 answer
Convert sqlite table to pandas dataframe
SQL
2 votes103 views1 answer
Date_format for time sql
SQL
4 votes104 views1 answer
Sql group by year
SQL
0 vote106 views1 answer
Import all databases mysql
SQL
1 vote97 views1 answer
Jooq convert using gbk
SQL
4 votes136 views2 answers
Raise errors.NotSupportedError( mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported
SQL
4 votes103 views1 answer
Oracle show error line number
SQL