Get Null value for days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get Null value for days

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 09:49:40

Hi,

I have the below query. I am trying to get all the dates in the month of June and
get a 0 as count for the days where count =0. But what I am getting is only the dates
where the count <>0 I don't get to see the dates where the count =0

Thanks,
Petronas

Query

SELECT
isnull(count (o.Customer_ID ),0),
o.Product_ID,
convert(datetime,(convert(varchar(10),o.Order_Received_Date,101) ),101),
v.Type

FROM
orders o left outer join

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 09:53:04

make use of F_TABLE_DATE and LEFT join to the orders table.

KH
[spoiler]Time is always against us[/spoiler]

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 10:20:37

Thanks khtan, I have used the calendar table in my query. Won't that work?

Appreciate your help,
Petronas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 10:21:03

yes. If you have a calendar table, use it.

KH
[spoiler]Time is always against us[/spoiler]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-07 : 10:29:26

from orders o right outer join calendar

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 10:40:24

Thank rohitkumar,

I tried orders o right outer join calendar c in the above query. It does not return any records.

Appreciate your help and time,
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 10:42:06

Show us your query.

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 10:45:26

1. post the query that you used
2. Does the Order_Received_Date contain times ?

KH
[spoiler]Time is always against us[/spoiler]

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 10:47:59

Here is my query:

SELECT
isnull(count (o.Customer_ID ),0),
o.Product_ID,
convert(datetime,(convert(varchar(10),o.Order_Date,101) ),101),
v.Type

FROM
orders_base o
right outer join
calendar c
ON (o.order_date=c.calendar_date)
INNER JOIN Marketing v
ON (v.MID=o.MID)

WHERE
o.Product_ID In ('bc1','bc2')
AND o.Order_Date >= '6/1/2009' AND o.Order_Date <= '6/30/2009'
AND v.Type_ID In ( 55,1)

GROUP BY
o.Product_ID,
convert(datetime,(convert(varchar(10),o.Order_Date,101) ),101),
v.Type
order by 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 10:56:25

Does the Order_Date contain times ?


SELECT
	isnull(count (o.Customer_ID ),0),
	o.Product_ID,
--	convert(datetime,(convert(varchar(10),o.Order_Date,101) ),101),
	Order_Date	= dateadd(day, datediff(day, 0, o.Order_Date), 0)
	v.Type
FROM
	orders_base o
	right outer join calendar c	ON	o.order_date >= c.calendar_date
					AND	o.order_date <  dateadd(day, 1, c.calendar_date)
	INNER JOIN Marketing v		ON	v.MID	= o.MID
WHERE
	o.Product_ID In ('bc1','bc2')
-- AND 	o.Order_Date >= '6/1/2009' AND o.Order_Date <= '6/30/2009'
AND	c.calendar_date	>= '20090601'	AND	c.calendar_date	<= '20090630'
AND 	v.Type_ID In ( 55,1)
GROUP BY
	o.Product_ID,
--	convert(datetime,(convert(varchar(10),o.Order_Date,101) ),101),
	dateadd(day, datediff(day, 0, o.Order_Date), 0),
	v.Type
	order by 3

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-07 : 11:59:37

I suspect the INNER JOIN is "invalidating" the RIGHT JOIN to the Calendar

Since it's not that complicated, I would re-write it to be a Left Join

Try this



      FROM calendar c
 LEFT JOIN orders_base o
		ON o.order_date	= c.calendar_date
 LEFT JOIN Marketing v
		ON v.MID		= o.MID

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 13:21:51

Thanks khtan, X002548. I tried both your solutions and none of them are working I am still getting the result set with dates where count <>0.

Appreciate your help & time,
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 13:40:48

[code]SELECT COALESCE(x.Items, 0),
x.Product_ID,
c.Calendar_Date AS Order_Date,
x.Type
FROM Calendar AS c
LEFT JOIN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, o.Order_Date), 0) AS Order_Date,
v.Type,
o.Product_ID,
COUNT(*) AS Items
FROM Orders_Base AS o
INNER JOIN Marketing AS v ON v.MID = o.MID
WHERE o.Product_ID IN ('bc1', 'bc2')
AND o.Order_Date >= '6/1/2009'
AND o.Order_Date < '7/1/2009'
AND v.Type_ID IN (55, 1)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, o.Order_Date), 0),
v.Type,
o.Product_ID
) AS x ON x.Order_Date = c.Calendar_Date
order by c.Calendar_Date[/code]

N 56°04'39.26"
E 12°55'05.63"

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 14:09:23

Thanks Peso,

I used your query and now it is returning count =0 , product_id =NULL and Type=NULL for all the dates from 1990 to 2020 in the Calendar table.

Appreciate your help & time,
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 14:10:56

And that's the way you want it?

N 56°04'39.26"
E 12°55'05.63"

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 14:33:42

Hi Peso,

I want to see all the dates in the month of June even though the count =0. Right now, I can see only those dates where the count <>0. Basically I want to see all the dates even though there are no sales occuring for for that day.

Thanks for your help!
Petronas

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-07 : 14:40:54

Thanks Peso,

I used Right join instead of Left join and it worked for me.

Thanks for your time!
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 15:34:32

You used RIGHT JOIN instead of LEFT JOIN in my suggestion made 07/07/2009 : 13:40:48
and that worked? Very strange...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources