Force data to appear from query... 1

[Webkins]

Here is my SQL 2000 query code which works just fine:

SELECT 2012 AS completed_year, completed_week, count(completed_week) AS completed_wo
FROM mwo.dbo.mwo
WHERE (responsible_status = 'complete') and (completed_year = 2012)
GROUP BY completed_week;

My issue is that when this query runs and returns the data, week #27 and week #47 are missing from the data set and ultimately my graph because no data exists for these 2 weeks. So, my question is how to "force" these 2 weeks to appear with a 0 (zero).

Thanks so much and a very Merry Christmas to all.

 

[gmmastros]

What is the data type for completed_week and what are some of the values?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

All data are integers, for example taken from the actual data set:

completed_year completed_week completed_wo
2012 25 5
2012 26 4
2012 28 5
2012 29 4

 

[gmmastros]

There are probably several ways to do this.

My favorite method is to use a numbers table. The numbers table would have a single column with values incrementing from 1 to n.

With a numbers table, your query would look like this:

SELECT 2012 AS completed_year, 
       Numbers.Num As completed_week, 
       Coalesce(count(completed_week), 0) AS completed_wo
FROM   Numbers
       Left Join mwo.dbo.mwo
         On Numbers.Num = mwo.completed_week
WHERE  Numbers.Num Between 1 and 52
       And (responsible_status = 'complete') 
       and (completed_year = 2012)
GROUP BY completed_week;

If you don't have a numbers table in your database and want to create one, you can execute this in a query window.

Create Table Numbers(Num Int Identity(1,1) Primary Key)
Go
Insert Into Numbers Default Values
GO 10000

The code above will create a numbers table and also insert 10,000 rows in to it. You can adjust the number of rows easily enough, but I would suggest a relatively high value because a numbers table is useful for a lot of different things. With 10,000 rows, this table only requires 40 kb (plus or minus a few kb) to store.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

I am having issues to populate the table. The create table works fine, but I get the below error with your code:

Insert Into Numbers Default Values
GO 10000

Line 16: Incorrect syntax near 'go'

Thanks for the assistance..

 

[gmmastros]

Are you running this in SQL Server Management Studio? It works on my computer.

Try this instead:

SET NOCOUNT ON

Declare @i Int
Set @i = 1

Begin Transaction

While @i < = 10000
  Begin
    Insert Into Numbers Default Values
	Set @i = @i + 1
  End
Commit Transaction

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I overlooked the fact you were running SQL2000, which probably means you are using Query Analyzer, which I haven't used in years.

With SQL Server Management Studio, you go do:

GO 100

And it will execute the batch 100 times. This is a feature of SQL Server Management Studio, but not query analyzer. The code I just posted should work with Query Analyzer and SQL2000.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

Ok, great, thank you! So far so good, I have the numbers table created.
Now when I run the following query (from SQL 2000 query analyzer) I get no errors, but also no (0) results. All that is returned are the 3 column names: completed_year, completed_week and completed_wo

Query:
SELECT 2012 AS completed_year,
Numbers.Num As completed_week,
Coalesce(count(completed_week), 0) AS completed_wo
FROM Numbers
Left Join mwo.dbo.mwo
On Numbers.Num = mwo.completed_week
WHERE Numbers.Num Between 1 and 52
And (responsible_status = 'complete')
and (completed_year = 2012)
GROUP BY completed_week, Numbers.Num

What am I missing? I understand what is supposed to be happening, but not sure how or what is wrong.

Thanks so much and HAPPY NEW YEAR !

 

[Webkins]

Still having problems with returning the data. The weeks 1 thru 45 are missing, see below. Maybe it is because this data does not exist and is returned as a <NULL> value? I don't know, I am just guessing here.

Here is the query:

SELECT 2011 AS completed_year,
Numbers.Num As completed_week,
Coalesce(count(completed_week), 0) AS completed_wo
FROM Numbers
Left Join mwo.dbo.mwo
On Numbers.Num = mwo.completed_week
WHERE Numbers.Num Between 1 and 52
And (responsible_status = 'complete')
and (completed_year = 2011)
GROUP BY completed_week, Numbers.Num

Here is the actual data returned:
completed_year------completed_week------completed_wo
2012-------------------------46-------------------------1
2012-------------------------47-------------------------2
2012-------------------------48-------------------------2
2012-------------------------49-------------------------3
2012-------------------------50-------------------------5
2012-------------------------51-------------------------3
2012-------------------------52-------------------------5

Thanks so very much.

 

[Webkins]

Oops, sorry, here is the correct query:

SELECT 2012 AS completed_year,
Numbers.Num As completed_week,
Coalesce(count(completed_week), 0) AS completed_wo
FROM Numbers
Left Join mwo.dbo.mwo
On Numbers.Num = mwo.completed_week
WHERE Numbers.Num Between 1 and 52
And (responsible_status = 'complete')
and (completed_year = 2012)
GROUP BY completed_week, Numbers.Num

 

[gmmastros]

Try this:

SELECT 2012 AS completed_year, 
Numbers.Num As completed_week, 
Coalesce(count(completed_week), 0) AS completed_wo
FROM Numbers
Left Join mwo.dbo.mwo
On Numbers.Num = mwo.completed_week
And Numbers.Num Between 1 and 52
And (responsible_status = 'complete') 
and (completed_year = 2012)
GROUP BY completed_week, Numbers.Num

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

OMG! THAT'S IT!
THANK YOU!
Wish I could give you 10 stars for your help!
Thanks again!