INSERT MONTH IF NOT IN

Question

Post reply

INSERT MONTH IF NOT IN

cmaciasg

Old Hand

Points: 321
More actions
April 19, 2009 at 2:21 am

#130193

Hi all, i'm working in a facturation issue.
I have several clients and several month for each client:
Table 1
month client sells
1 Adidas 10
2 Adidas 4
4 Adidas 2
5 Adidas 3
10 Adidas 3
1 Reebok 3
2 Reebok 3
3 Reebok 3
8 Reebok 23
9 Reebok 3
Like you can see, in both cases there are month that not in. How can I get a table like that, with all month?
Table 1
month client sells
1 Adidas 10
2 Adidas 4
3 Adidas 0
4 Adidas 2
5 Adidas 3
6 Adidas 0
7 Adidas 0
8 Adidas 0
9 Adidas 0
10 Adidas 3
11 Adidas 0
12 Adidas 0
1 Reebok 3
2 Reebok 3
3 Reebok 3
4 Reebok 0
5 Reebok 0
6 Reebok 0
7 Reebok 0
8 Reebok 23
9 Reebok 3
10 Reebok 0
11 Reebok 0
12 Reebok 0
I try it with cursors and temp tables, but is not results.
Thank you for your time

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Hi,

I'd like to help, but with the best will in the world: I can't make sense of your post.

Sorry - I know it's difficult if English isn't your first language.

Perhaps someone you know could help you make it clearer?

Thanks

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

cmaciasg Old Hand Points: 321 More actions · Answer 2

Sorry for my English. I'll try to explain better.

I've a table with the sales of a clients for each month, sometimes there are months without sales. For example: Adidas in January has sales (10€), but in March hasn´t.

I want to obtain a table which all the months, the month in previous table and the missing month with the sales fields to zero.

David-1011936 Ten Centuries Points: 1378 More actions · Answer 3

seems like a number / tally table would do well here; basically you want an insert for each month regardless of if there were sales for that month, if I'm reading you correctly. Tally tables have been discussed frequently here on SSC. Another approach would be to create a temp table with the full range of month / year combinations, as a slight variation on the Tally table.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Actually for something this small you don't even need that. This should work:

Select m.Month

, c.client

, Coalesce(t.sell, 0) as Sell

From (Select 1 as Month

Union All Select 2

Union All Select 3

Union All Select 4

Union All Select 5

Union All Select 6

Union All Select 7

Union All Select 8

Union All Select 9

Union All Select 10

Union All Select 11

Union All Select 12 ) m

Cross Join (Select distinct Client

From Table1) c

Left Join Table1 t on t.Month = m.Month

And t.client = c.client

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 5

RBarryYoung (4/19/2009)
Actually for something this small you don't even need that. This should work:
...
Select 1 as Month
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10
Union All Select 11
Union All Select 12

Hey!!!

That's a tally table in disguise! 😛 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Technically, its a constant scan. They're very fast. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 7

RBarryYoung (4/19/2009)
Technically, its a constant scan. They're very fast. 🙂

Well I did say it was in disguise. 🙂

Constant scans are quite fast.

Not fast enough when SS2K5 uses them to enumerate a partition range for a KEY/RID lookup from a non-clustered index on a partitioned table. The QO generates a partition list (expressed as a constant scan) for every iteration of the inner side of the loop join - potentially 999 partition ids per lookup. I have seen this murder a query which was instant on a non-partitioned table. Sure, the QO produces a sub-optimal plan, but the time was all consumed by the constant scan.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi