Filling empty dates with stored procedure in sql

Question

I have found similar questions that have been answered but I can't seem to get it working. I have the following SQL query but I want to fill the missing dates with 0-values

SELECT 
    Lines.Item, 
    CAST(Lines.Date AS Date) AS SalesDate, 
    ABS(SUM(Lines.Invoiced)) AS QtySoldOnDate
FROM 
    Lines
WHERE 
    Lines.Invoiced < 0 
    AND Lines.Item = 'a158wa' 
    AND Lines.Date >= '2014-01-01' 
    AND Lines.Date <= '2014-12-31'
GROUP BY 
    Lines.Item, Lines.Date

I have found the following post, but I can't seem to get it working/figure out how to merge the two queries: What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Any help would be greatly appreciated.

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Mar 14 '15 at 16:32

Khurram Ali · Answer 1 · 2015-03-14T18:04:42.370

0

You can Create storedprocedure with 2 input parameters and 1 output parameters and you can check if row exists then do your job and in the else part you can set output parameter as o-values

CREATE PROCEDURE yourprocedure
@startDate  Date,
@endDate  Date,
@OutputPara as nvarchar(100) output
AS
BEGIN
IF EXISTS( Select Lines.Item FROM Lines [Lines.Date] Where [Lines.Date] >= @startDate AND [Lines.Date] <= @endDate)
  BEGIN
  SELECT 
    Lines.Item, 
    COALESCE(CAST(Lines.Date AS Date),'0') AS SalesDate,
    ABS(SUM(Lines.Invoiced)) AS QtySoldOnDate
FROM 
    Lines
WHERE 
    Lines.Invoiced < 0 
    AND Lines.Item = 'a158wa' 
    AND [Lines.Date] >= @startDate
    AND [Lines.Date] <= @endDate
GROUP BY 
    Lines.Item, [Lines.Date]
    Set @OutputPara =' result exists'
    END
    ELSE
    BEGIN
    SET @OutputPara='0-results';
    END
    END
    GO

edited Mar 14 '15 at 18:04

answered Mar 14 '15 at 16:54

Khurram Ali

1,659
4
20
37

When there are no sales for a specific date there is no entry in the table. Your code gives me the same result. I need 0-values for the messing entries in the table. – John Mar 14 '15 at 16:58
@Klelund the solution i provided is about if your `line.date` column has null value then coalesce will replace null from `0` – Khurram Ali Mar 14 '15 at 17:01
Yep - but it doesn't :( – John Mar 14 '15 at 17:05
@Klelund let me clear something you want if your query does not find any records then reutrn `0 values` instead empty result set ? – Khurram Ali Mar 14 '15 at 17:07
@Klelund you can reutrn `0-values` by adding output parameter in storedprocedure – Khurram Ali Mar 14 '15 at 17:08
Yes, If there is not entry for a specific date I want to return 0-values. I referred to another stackoverflow post where they mention a stored procedure as a solution but as my initial post said I can't get this to work. – John Mar 14 '15 at 17:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72987/discussion-between-khurram-ali-and-klelund). – Khurram Ali Mar 14 '15 at 17:54

score 0 · Answer 2 · answered Mar 14 '15 at 16:57

0

The easiest way is to get a list of 365 values for forming the dates. One way is with master..spt_values, something like this:

with dates as (
      select dateadd(day, row_number() over (order by (select null)),
                     cast('2014-01-01' as date)) as thedate
      from master..spt_values
    )
SELECT l.Item, d.thedate AS SalesDate, 
       ABS(SUM(l.Invoiced)) AS QtySoldOnDate
FROM dates d left join
     Lines l
     ON l.Date = d.thedate and
        l.Invoiced < 0 AND
        l.Item = 'a158wa' 
WHERE d.thedate BETWEEN '2014-01-01' AND Lines.Date <= '2014-12-31'
GROUP BY l.Item, d.theDate;

Note: You can also read the Number column directly from master..spt_values if you use type = 'P'. I'm likely to forget the type part, so I just used row_number(). Perhaps Microsoft could add a view called Numbers that did this for us.

answered Mar 14 '15 at 16:57

Gordon Linoff

1,242,037
58
646
786

Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 102, Level 15, State 1, Line 14 Incorrect syntax near '<'. – John Mar 14 '15 at 17:03
I don't just need 1..365. I need the specific dates of the whole year or for a given date range – John Mar 14 '15 at 17:04
This version produces more than enough dates for `dates` and then limits them in the outer `where` clause. – Gordon Linoff Mar 14 '15 at 17:14
What version of SQL Server are you using? CTEs are supported in every supported version of the product. If you have an order version, you can just move the CTE query as a subquery. – Gordon Linoff Mar 14 '15 at 17:27

Stephan · Answer 3 · 2015-03-17T13:19:05.793

I'm assuming you want the days where nothing is sold, to have its own row with 0 as it's value. This should do it perhaps with a few tweaks from you since I don't have your table or any data.

DECLARE @startDate DATE,
        @endDate DATE;
SET @startDate = '2014-01-01';
SET @endDate = '2014-12-31';

--Generates table of each day in range
WITH cte_dates
AS
(
    SELECT @startDate AS startDate
    UNION ALL
    SELECT DATEADD(DAY,1,startDate)
    FROM cte_dates
    WHERE startDate <= @endDate
)

SELECT  cte_dates.startDate,
        Lines.Item, 
        CAST([Lines.Date] AS Date) AS SalesDate, 
        ISNULL(ABS(SUM(Lines.Invoiced)),0) AS QtySoldOnDate
FROM cte_dates
--Left join makes it where if there is no date in Lines, then cte_dates will be there with nulls for columns in your table Lines
LEFT JOIN Lines
    ON cte_dates.startDate = Lines.[Date]
WHERE 
    Lines.Invoiced < 0 
    AND Lines.Item = 'a158wa' 
    AND Lines.Date BETWEEN @startDate AND @endDate
GROUP BY Lines.Item,Lines.[Date],cte_dates.startDate
--It's a recursive CTE. This allows it recursively iterate enough times to generate the list of dates
OPTION (MAXRECURSION 0)

Theoretical results:

StartDate       Item       SalesDate    QtySOldOnDate
---------------------------------------------------
2014-01-01      Item1      2014-01-01         500
2014-01-02       NULL         NULL             0
2014-01-03       Item2     2014-01-03         250

I am getting a "Msg 156, Level 15, State 1, Line 32 Incorrect syntax near the keyword 'OPTION'." — John, Mar 17 '15 at 04:12
Sorry, I left a comma at the end of the group by statement. I fixed it. — Stephan, Mar 17 '15 at 13:17

Filling empty dates with stored procedure in sql

3 Answers3