Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If I make a line chart with the values below for returns by month, I would like to ONLY see the months in green. Essentially suppress zeros outside the first and last occurrence of a nonzero value. How can this be accomplished? Obviously suppressing zero values would eliminate March and April which is not what I want. Please advise. Thanks in advance for your assistance.
Date | Sales | Returns |
1/1/2018 | 10000 | 0 |
2/1/2018 | 10010 | 50 |
3/1/2018 | 10008 | 0 |
4/1/2018 | 10036 | 0 |
5/1/2018 | 10048 | 50 |
6/1/2018 | 10022 | 0 |
Decided this task was better suited for a tool like sql server reporting services. Used the code below:
IF OBJECT_ID('tempdb.dbo.#exampleData', 'U') IS NOT NULL
DROP TABLE dbo.#exampleData;
SELECT *
INTO #exampleData
FROM (
SELECT '1/1/2018' as vDate, 10000 as vSales, 0 as vReturns UNION ALL
SELECT '1/15/2018', 10010, 0 UNION ALL
SELECT '2/1/2018', 10010, 50 UNION ALL
SELECT '3/1/2018', 10008, 0 UNION ALL
SELECT '4/1/2018', 10036, 0 UNION ALL
SELECT '5/1/2018', 10048, 50 UNION ALL
SELECT '6/1/2018', 10022, 0 UNION ALL
SELECT '7/1/2018', 10022, 0
) as exampleData
SELECT * FROM #exampleData
WHERE vDate between (Select Min(vDate) From #exampleData WHERE vReturns <> 0) AND (Select Max(vDate) From #exampleData WHERE vReturns <> 0)
The logic could be converted to using resident tables if you had to use QS.