I work with Sales and problem is that this table does not have records for each client for every year. Records are missing randomly. Instead i need to have those years there and put 0 for sales for those years for my analysis.
I have limited knowledge of SQL. Can anybody help on this one? What i have as of now and what i would like to have is shown below.
I have thoughts to use LAG() function, but missing records can be for 2 years in a row or 3. I am not sure how to tackle such problem.
What I have now:
| Client_ID | SalesYear | Sales |
| ------------ | ------------ | ------------ |
| 1 | 2010 | 12 |
| 1 | 2012 | 20 |
| 1 | 2013 | 21 |
| 1 | 2016 | 14 |
What i need to have:
| Client_ID | SalesYear | Sales |
| ------------ | ------------ | ------------ |
| 1 | 2010 | 12 |
| 1 | 2011 | 0 |
| 1 | 2012 | 20 |
| 1 | 2013 | 21 |
| 1 | 2014 | 0 |
| 1 | 2015 | 0 |
| 1 | 2016 | 14 |
2条答案
按热度按时间xpcnnkqh1#
You need a complete list of years to outer-join with.
You can do this a number of ways, the basic principle would be:
oogrdqng2#
Something like this might help:
You can change "2000" to something more appropriate.