Why ISNULL Eliminates Blank Lines 2

[valis99]

I have created a view using this code, my intent is to return a line for each day for each product, with ISNULL causing zero values for days on which that product had no sales. Instead it omits product/day combinations that had zero sales. It is important to have the zero value lines because this is being dropped into an Excel report and the same number of lines need to always be present. Can anyone explain how I am mis understanding the use of ISNULL?:

ALTER VIEW [dbo].[vwLicense] AS
/*The Nested CAST functions round the time part of the orderdate
to zero effectivly preserving the date without the time information*/
SELECT (CAST(
FLOOR(CAST(order_date AS FLOAT))
AS DATETIME
)) AS 'Order_Date',
CASE
WHEN od.product_id = 1
THEN 'Prod 1'
WHEN od.product_id = 2
THEN 'Prod 2'
WHEN od.product_id = 3
THEN 'Prod 3'
WHEN od.product_id = 4
THEN 'Prod 4'
WHEN od.product_id = 5
THEN 'Prod 5'
ELSE 'Unkown Product'
END
AS 'Product',
ISNULL(SUM(od.o_quantity),0) AS 'Licenses',
ISNULL(COUNT(DISTINCT o.order_id),0) AS 'Orders',
ISNULL(SUM(od.o_quantity * od.o_price),0) AS 'Total'
FROM orders AS o
JOIN orderdetail AS od
ON o.order_id = od.order_id
WHERE od.product_id BETWEEN 1 AND 5
AND o.status = 0
AND o.order_total <> 0
GROUP BY CAST(
FLOOR(CAST(order_date AS FLOAT))
AS DATETIME
),
CASE
WHEN od.product_id = 1
THEN 'Prod 1'
WHEN od.product_id = 2
THEN 'Prod 2'
WHEN od.product_id = 3
THEN 'Prod 3'
WHEN od.product_id = 4
THEN 'Prod 4'
WHEN od.product_id = 5
THEN 'Prod 5'
ELSE 'Unkown Product'
END



GO

 

[bborissov]

No way ISNULL could cause record filetrin if it is NOT in WHERE clause. You could get wrong result, but not record filtering.

Maybe you have no no sales for ALL dates?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

IsNull will not magically manufacture data for you. What you will need to do is cross join your query with a full set of data that you want to report on. Let me explain with an example.

Suppose you have a table that looks like this.

[tt][blue]
order_date Product_Id Quantity
----------------------- ----------- -----------
2010-02-01 10:41:00.000 1 20
2010-02-01 12:51:00.000 2 40
2010-02-01 15:21:00.000 1 60
2010-02-02 10:41:00.000 1 80
2010-02-02 10:01:00.000 1 90
[/blue][/tt]

Notice that there were no sales for Product_id = 2 for 2/2/2010. When we run a query to return the number of sales for product 2 on that date, nothing is returned. Ex:

Declare @Sales Table(order_date DateTime, Product_Id Int, Quantity Int)

Insert Into @Sales Values('20100201 10:41 AM', 1, 20)
Insert Into @Sales Values('20100201 12:51 PM', 2, 40)
Insert Into @Sales Values('20100201  3:21 PM', 1, 60)
Insert Into @Sales Values('20100202 10:41 AM', 1, 80)
Insert Into @Sales Values('20100202 10:01 AM', 1, 90)

Select * From @Sales Where Product_ID = 2 And Cast(Floor(Cast(order_date As Float)) As DateTime) = '20100202'

When you run the code shown above, there are no results. It does not matter what you do with ISNULL because there is no data for IsNull to operate on.

Hopefully it makes sense why your data is not returning. Of course, the next question is.... how do I make this work for me? Here's how...

What you need to do is create a result set that contains all of the information you want. We can write a query that returns all the days. We can write another query that returns all the products. Then we can write a query that returns the combination of both. For example...

Declare @Sales Table(order_date DateTime, Product_Id Int, Quantity Int)

Insert Into @Sales Values('20100201 10:41 AM', 1, 20)
Insert Into @Sales Values('20100201 12:51 PM', 2, 40)
Insert Into @Sales Values('20100201  3:21 PM', 1, 60)
Insert Into @Sales Values('20100202 10:41 AM', 1, 80)
Insert Into @Sales Values('20100202 10:01 AM', 1, 90)

-- Get a list of products
Select Distinct Product_ID From @Sales

-- Get a list of dates
Select Distinct Cast(Floor(Cast(order_date As Float)) As DateTime) As OrderDate From @Sales

-- get the combination of products and dates
Select	OrderDate, Product_ID
From    (
        Select Distinct Product_ID From @Sales
        ) As ProductIds
        Cross Join (
          Select Distinct Cast(Floor(Cast(order_date As Float)) As DateTime) As OrderDate From @Sales
          ) As Dates

Once we have a full list, we can use it to left join the results from the query that returns our sums. Like this.

Declare @Sales Table(order_date DateTime, Product_Id Int, Quantity Int)

Insert Into @Sales Values('20100201 10:41 AM', 1, 20)
Insert Into @Sales Values('20100201 12:51 PM', 2, 40)
Insert Into @Sales Values('20100201  3:21 PM', 1, 60)
Insert Into @Sales Values('20100202 10:41 AM', 1, 80)
Insert Into @Sales Values('20100202 10:01 AM', 1, 90)

-- get the combination of products and dates

Select AllData.OrderDate, AllData.Product_Id, Sum(Quantity) 
From   ( Select	OrderDate, Product_ID
        From    (
                Select Distinct Product_ID From @Sales
                ) As ProductIds
                Cross Join (
                  Select Distinct Cast(Floor(Cast(order_date As Float)) As DateTime) As OrderDate From @Sales
                  ) As Dates
        ) As AllData
        Left Join @Sales S
        On AllData.OrderDate = Cast(Floor(Cast(S.order_date As Float)) As DateTime)
        And AllData.Product_ID = S.Product_Id
Group BY AllData.OrderDate, AllData.Product_Id

Notice when you run the code above that you get all the data you want, but now product 2 for 2/2/2010 returns NULL. This is where IsNull would convert that NULL to 0.

Declare @Sales Table(order_date DateTime, Product_Id Int, Quantity Int)

Insert Into @Sales Values('20100201 10:41 AM', 1, 20)
Insert Into @Sales Values('20100201 12:51 PM', 2, 40)
Insert Into @Sales Values('20100201  3:21 PM', 1, 60)
Insert Into @Sales Values('20100202 10:41 AM', 1, 80)
Insert Into @Sales Values('20100202 10:01 AM', 1, 90)

-- get the combination of products and dates

Select AllData.OrderDate, AllData.Product_Id, [!]IsNull([/!]Sum(Quantity)[!], 0)[/!] As Quantity
From   ( Select	OrderDate, Product_ID
        From    (
                Select Distinct Product_ID From @Sales
                ) As ProductIds
                Cross Join (
                  Select Distinct Cast(Floor(Cast(order_date As Float)) As DateTime) As OrderDate From @Sales
                  ) As Dates
        ) As AllData
        Left Join @Sales S
        On AllData.OrderDate = Cast(Floor(Cast(S.order_date As Float)) As DateTime)
        And AllData.Product_ID = S.Product_Id
Group BY AllData.OrderDate, AllData.Product_Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

George - a detailed explanation like that definitely deserves a star, have one on me.

 

[valis99]

George, Thank you for a very detailed and helpful explanation!