Force Missing Items In List 1

[Moebius01]

For some reason, I have what should be a simple query giving me some serious fits.

Using example table #Steps as follows

Item        Step
Review	  1	
Review	  2	
Review	  4	
YearEnd	 1	
YearEnd	 3	
YearEnd	 4

Is there a way I could pull a list of each Item, Each Step, and a count of how many are in each, but including missing steps with a 0?

ie..

SELECT Item, s.Step, COUNT(s.Step) [Count]
FROM #Steps s
GROUP BY Item, s.Step
ORDER BY Item

Returns

Item       Step Count
Review	 1	2
Review	 2	1
Review	 4	1
YearEnd	1	2
YearEnd	3	1
YearEnd	4	1

This gives me the counts I need, but without the missing items (Review step 3 count 0). Is there a way I could query this to return a row for each Step?

Item       Step Count
Review	 1	2
Review	 2	1
Review     3    0
Review	 4	1
YearEnd	1	2
YearEnd    2    0
YearEnd	3	1
YearEnd	4	1

I tried to create a temp table that just had the possible list of steps (1-4 in this example) and joining to it, but couldn't get it to work right.

 

[LNBruno]

Maybe because example table #Steps doesn't include a row for "Review step 3"?

< M!ke >
I am not a hamster and life is not a wheel.

 

[Moebius01]

It doesn't at the moment because there are no Review Items current in Step 3. Bascially, the real table that this example is based on lists each individual item and the step that item is currently in.

However, I need to list them all even if there are none currently in that step.

 

[gmmastros]

Here's one way:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Item [COLOR=blue]VarChar[/color](20), Step [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Review'[/color] ,     1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Review'[/color] ,     2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Review'[/color] ,     4) 
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'YearEnd'[/color],     1)  
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'YearEnd'[/color],     3)   
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'YearEnd'[/color],     4)

[COLOR=blue]Select[/color] AllData.Item, AllData.Step, [COLOR=#FF00FF]Count[/color]([COLOR=blue]Temp[/color].Step)
[COLOR=blue]From[/color]   (
       [COLOR=blue]Select[/color] Item, Step
       [COLOR=blue]From[/color]   ([COLOR=blue]Select[/color] [COLOR=#FF00FF]Distinct[/color] Item [COLOR=blue]From[/color] @Temp) [COLOR=blue]As[/color] [COLOR=blue]T[/color]
              [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] ([COLOR=blue]Select[/color] 1 [COLOR=blue]As[/color] Step Union All [COLOR=blue]Select[/color] 2 Union All [COLOR=blue]Select[/color] 3 Union All [COLOR=blue]Select[/color] 4) [COLOR=blue]As[/color] A
       ) [COLOR=blue]As[/color] AllData
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] @Temp [COLOR=blue]Temp[/color]
         [COLOR=blue]On[/color] AllData.Item = [COLOR=blue]Temp[/color].Item
         And AllData.Step = [COLOR=blue]Temp[/color].Step
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] AllData.Item, AllData.Step

Notice that you must first create a table that has all combinations. That is what I was doing here.

Select Item, Step
From (Select Distinct Item From @Temp) As T
Cross Join (Select 1 As Step Union All Select 2 Union All Select 3 Union All Select 4) As A

Take a look at this. See if it will work for you. If you have any questions, let me know and I will explain in greater detail how this query works.

-George

"the screen with the little boxes in the window." - Moron

 

[Moebius01]

George, that gets me very close, but one catch. The Items are somewhat dynamic (where there are only 2 in my example, there may be more, and they may change from period to period). Would there be a way to do that without hard-coding the Item descriptions into the TempTable?

The only other way I've thought of so far would be to use a cursor, and we know what the general opinion of curors is around here.

 

[gmmastros]

The @Temp Table is just so I could dummy up some data to test the code. This means you can copy/paste to see that the query works. Assuming you are satisfied with the query, then remove the @Temp table stuff and replace your table name.

[COLOR=blue]Select[/color] AllData.Item, AllData.Step, [COLOR=#FF00FF]Count[/color]([COLOR=blue]Temp[/color].Step)
[COLOR=blue]From[/color]   (
       [COLOR=blue]Select[/color] Item, Step
       [COLOR=blue]From[/color]   ([COLOR=blue]Select[/color] [COLOR=#FF00FF]Distinct[/color] Item [COLOR=blue]From[/color] [!]@Temp[/!]) As T
              [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] ([COLOR=blue]Select[/color] 1 [COLOR=blue]As[/color] Step Union All [COLOR=blue]Select[/color] 2 Union All [COLOR=blue]Select[/color] 3 Union All [COLOR=blue]Select[/color] 4) [COLOR=blue]As[/color] A
       ) [COLOR=blue]As[/color] AllData
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] [!]@Temp[/!] Temp
         [COLOR=blue]On[/color] AllData.Item = [COLOR=blue]Temp[/color].Item
         And AllData.Step = [COLOR=blue]Temp[/color].Step
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] AllData.Item, AllData.Step

Where the code is [!]Read and bold[/!], put your table name there.

-George

"the screen with the little boxes in the window." - Moron

 

[LNBruno]

you must first create a table that has all combinations

Sometimes I feel like English isn't my <strongest, best, primary, only> language....

;-)

< M!ke >
I am not a hamster and life is not a wheel.

 

[Moebius01]

Ahh, I wasn't reading the query right (never played with cross joins). I'll work with this as a base and see if I can get anywhere with it.

The only hitch I'm hitting is that the number of steps may be dynamic as well (my real table has 0-8 as possible steps). Along that train of thought, if I had a temp table that just had the steps in it (9 rows 0-8), could I use this same approach to join to it?

 

[gmmastros]

yes.

Give that a try. If you get stuck, post all your code and I'll help further.

-George

"the screen with the little boxes in the window." - Moron

 

[Moebius01]

Got it working. Thanks much!!!

 

[gmmastros]

Woo Hoo.

You have my permission to take off the rest of the day.

-George

"the screen with the little boxes in the window." - Moron