Query to return all rows even with out data

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)

Query to return all rows even with out data

Author

Topic

lolan69
Starting Member

1 Post

Posted - 2010-10-19 : 06:13:47

I have a table with some sales data structure and sample data as below:

Order Table
***********
order id order date Region Status Amount
-------- ---------- ------ ------ ------
1 01/Mar/2010 East Pending 10000
2 01/Mar/2010 West Pending 5000
3 01/Mar/2010 East Processed 15000
4 01/Jun/2010 South Closed 12000
5 01/Jul/2010 West Processed 10000
6 03/Mar/2010 East Pending 15000

Region table
*************
Code Description
East Eastern Region
South Southern Region
West Western Region
North Northern Region

I need a query which will return the following result:

Region Month Status Count Amount

East Jan Pending 0 0
Processed 0 0
Closed 0 0
Feb Pending 0 0
Processed 0 0
Closed 0 0
Mar Pending 2 25000
Processed 1 15000
Closed 0 0
Apr Pending 0 0
Processed 0 0
Closed 0 0
May Pending 0 0
Processed 0 0
Closed 0 0
Jun Pending 0 0
Processed 0 0
Closed 0
for all months for all status...
...

South Jan Pending 0
Processed 0
Closed 0
Feb Pending 0
Processed 0
Closed 0
Mar Pending 0
Processed 0
Closed 0
Apr Pending 0
Processed 0
Closed 0
May Pending 0
Processed 0
Closed 0
Jun Pending 0
Processed 0
Closed 1 12000
all regions for all months for all status...
...

even if there is no data for a region all 12 months and all status should appear as 0. The same for month and status.

Can we handle it with an sql query or do we need to have a stored procedure or a table-valued function?

Thanks
Lol

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 08:33:52

Use a left join between the 2 tables with region table being the left table.Use Isnull function to display 0 for the region that does not have any data.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:23:32

i think you might need a cross join between region and month values and use it as left part of query to get region values for all months

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources