CODEWITHSUNDEEP
sqlsql-servernull
user3261909
user3261909

Reputation: 145

T-SQL NULL-Friendly Query

I have a table "tblSalesOrder" in Microsoft T-SQL with some sample records:

SalesOrderID    OrderDate    ItemID    Quantity   PromotionCode
====================================================================
1               2014-09-01   100       5          NULL
2               2014-09-01   120       10         ABC
3               2014-09-05   150       7          NULL
4               2014-09-08   200       15         NULL

I need to return NULL-friendly resultset for records which do not exist.

As an example, I want a monthly query for September 2014:

SELECT SalesOrderID, OrderDate, ItemID, Quantity, PromotionCode
FROM tblSalesOrder
WHERE OrderDate = BETWEEN '2014-09-01' AND '2014-09-30'

I need it to return at least 1 row for each day (i.e. 0 valued row, if the entry for that day is not available) 

SalesOrderID    OrderDate    ItemID    Quantity   PromotionCode
====================================================================
1               2014-09-01   100       5          NULL
2               2014-09-01   120       10         ABC
0               2014-09-02   0         0          0
0               2014-09-03   0         0          0
0               2014-09-04   0         0          0
3               2014-09-05   150       7          NULL
0               2014-09-06   0         0          0
0               2014-09-07   0         0          0
4               2014-09-08   200       15         NULL
0               2014-09-09   0         0          0
    ...
    ...
    ...
0               2014-09-30   0         0          0

Upvotes: 0

Views: 124

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

master..spt_values is a table in all microsoft sql databases containing 2506 rows, by cross joining, it will have 2506*2506 rows to calculate dates between from and to. Other tables can be used as well, this is just a table used to create the dates. A calendar table would be even easier to use.

The EXCEPT will remove all dates already in use. Then by combining the rows from tblSalesOrder and CTE with union all, empty days will be filled with the required hardcoded values:

DECLARE @from date = '2014-09-01'
DECLARE @to date   = '2014-09-30'

;WITH CTE as
(
  SELECT top (case when @to < @from then 0 else datediff(day, @from, @to) + 1 end)
    dateadd(day, row_number() over (order by (select 1)) - 1, @from) OrderDate
  FROM 
    master..spt_values t1
  CROSS JOIN 
    master..spt_values t2
  EXCEPT
  SELECT 
    OrderDate
  FROM 
    tblSalesOrder
)
SELECT 
  0 SalesOrderID, OrderDate, 0 ItemID, 0 Quantity, '0' PromotionCode
FROM
  CTE
UNION ALL
SELECT
  SalesOrderID, OrderDate, ItemID, Quantity, PromotionCode
FROM
  tblSalesOrder
ORDER BY
  OrderDate, SalesOrderId

Upvotes: 2

AlexK
AlexK

Reputation: 9927

DECLARE @startDate date= '20140901'
    ,@endDate date = '20140930';

WITH Calendar as (
    SELECT @startDate as OrderDate
    UNION ALL
    SELECT DATEADD(DAY, 1, OrderDate) as OrderDate
    FROM Calendar
    WHERE OrderDate < @endDate
)

SELECT coalesce(t.SalesOrderID, 0) SalesOrderID
,      coalesce(t.OrderDate, Calendar.OrderDate) OrderDate
,      coalesce(t.ItemID, 0) ItemID
,      coalesce(t.Quantity, 0) Quantity
,      CASE WHEN t.OrderDate IS NULL THEN  '0' ELSE t.PromotionCode END as PromotionCode    FROM Calendar
    LEFT JOIN tblSalesOrder t ON Calendar.OrderDate = t.OrderDate
ORDER BY Calendar.OrderDate, t.SalesOrderID
OPTION (MAXRECURSION 0);

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156948

You can join the a date parameter in an empty select and coalesce the values:

select coalesce(t.SalesOrderID, 0) SalesOrderID
,      coalesce(t.OrderDate, d.OrderDate) OrderDate
,      coalesce(t.ItemID, 0) ItemID
,      coalesce(t.Quantity, 0) Quantity
,      coalesce(t.PromotionCode, 0) PromotionCode
from   (select @dateParameter OrderDate) d
left
outer
join   ( SELECT SalesOrderID, OrderDate, ItemID, Quantity, PromotionCode
         FROM tblSalesOrder
       ) t
on     t.OrderDate = d.OrderDate

Upvotes: 1

Related Questions