Zero count for non existing group item category

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

SQL Server 2008 Forums

Transact-SQL (2008)

Zero count for non existing group item category

Author

Topic

lourivalsjr
Starting Member

1 Post

Posted - 2012-02-25 : 21:09:20

Hi,
i need to find a list of three products and their quantities grouped by date, for the last five days. For this i used the tsql below.

select CONVERT(VARCHAR(10),WM_data,103) as Date, Product = Case When WM_Product = 1 Then 'Product A' When WM_Product = 2 Then 'Product B' Else 'Product C' End, WM_qtd as Quantity from Tb_InfoProduct group by CONVERT(VARCHAR(10),WM_data,103),WM_Product
having CONVERT(VARCHAR(10),WM_data,103) > convert(VARCHAR(10),dateadd(dd, -5, getdate()),103) order by CONVERT(VARCHAR(10),WM_data,103),WM_Product

as a result i receive:

Date Product Quantity
-------------------------------------------------
16/02/2012 Product A 132
16/02/2012 Product C 10
17/02/2012 Product A 163
17/02/2012 Product B 61
17/02/2012 Product C 58
18/02/2012 Product A 7
18/02/2012 Product B 2

but i need:

Date Product Quantity
-------------------------------------------------
16/02/2012 Product A 132
16/02/2012 Product B 0
16/02/2012 Product C 10
17/02/2012 Product A 163
17/02/2012 Product B 61
17/02/2012 Product C 58
18/02/2012 Product A 7
18/02/2012 Product B 2
18/02/2012 Product C 0

How can i archieve this?

Tks.Lourival

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-02-26 : 04:16:21

Can you pleas provide sample data from your table with required columns.

faijurrahuman
Starting Member

15 Posts

Posted - 2012-02-26 : 09:48:04

Could you please more specific...

Share Knowledge team

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-26 : 10:20:58

You will need to create two "reference tables", one a calendar table and another a products table. These could be base tables or virtual tables. For example:

CREATE TABLE #tmpCalendar(Dt DATE NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO #tmpCalendar VALUES ('201200216'),('201200217'),('201200218');
CREATE TABLE #tmpProducts(WM_Product int, NOT NULL PRIMARY KEY CLUSTERED, ProductName VARCHAR(32) );
INSERT INTO #tmpCalendar VALUES ('Product A'),('Product B'),('Product C');

SELECT
	c.Dt AS Date,
	Product = p.ProductName,
	WM_qtd AS Quantity
FROM  
	(#tmpCalendar c
	CROSS JOIN #tmpProducts p )
	LEFT JOIN  Tb_InfoProduct i ON i.WM_Product = p.WM_Product AND i.WM_Data = c.Dt;

I am only trying to show the concept - you may need to cast the data types appropriately etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-26 : 14:28:24

here's a way to create calendar table on the fly

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources