SQL Help Needed - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Help Needed

Author

Topic

koushik
Starting Member

11 Posts

Posted - 2010-10-01 : 04:52:13

Hi,

I have a table like below :

CustomerCode YearMonth Description Points
00000241 201005 2. Dispenser Share (?????) 1400
00000241 201005 4. Lighting / No Blockage (????? / ?????) 1000
00000241 201006 2. Dispenser Share (?????) 1400
00000241 201006 4. Lighting / No Blockage (????? / ?????) 1000
00002139 201005 2. Dispenser Share (?????) 600
00002139 201005 4. Lighting / No Blockage (????? / ?????) 300
00002139 201006 1. PM Exclusives (PM ??) 1100
00002139 201006 2. Dispenser Share (?????) 500
00002139 201006 3. Planogram (??????) 300
00002139 201006 4. Lighting / No Blockage (????? / ?????) 300
00004414 201005 1. PM Exclusives (PM ??) 1500
00004414 201005 2. Dispenser Share (?????) 600
00004414 201005 3. Planogram (??????) 450
00004414 201005 4. Lighting / No Blockage (????? / ?????) 450
00004414 201006 1. PM Exclusives (PM ??) 2400
00004414 201006 2. Dispenser Share (?????) 900
00004414 201006 3. Planogram (??????) 600
00004414 201006 4. Lighting / No Blockage (????? / ?????) 600

And I want to display (all columns shown above) only those rows where sum(Points) is not same for two consicutive years for any customer. In the above example sum(points) for CustomerCode='00000241' is same for YearMonth='201005' and YearMonth='201006', so this customer should not appear in the output.

Please help me write here an efficient (performance wise) query.

Regards,
Koushik

Regards,
Koushik Chandra

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-01 : 09:51:24

The description you wrote:
"not same for two consicutive years"

conflicts with your sample of:
"is same for YearMonth='201005' and YearMonth='201006'"

So I'm not sure if you want to base this on the YearMonth or just Year. But here is an example for YearMonth:


declare @t table (CustomerCode int, YearMonth int, Description varchar(50), Points int)
insert @t
select 00000241, 201005, '2. Dispenser Share (?????)' ,1400 union all
select 00000241, 201005, '4. Lighting / No Blockage (????? / ?????)' ,1000 union all
select 00000241, 201006, '2. Dispenser Share (?????)' ,1400 union all
select 00000241, 201006, '4. Lighting / No Blockage (????? / ?????)' ,1000 union all
select 00002139, 201005, '2. Dispenser Share (?????)' ,600 union all
select 00002139, 201005, '4. Lighting / No Blockage (????? / ?????)' ,300 union all
select 00002139, 201006, '1. PM Exclusives (PM ??)' ,1100 union all
select 00002139, 201006, '2. Dispenser Share (?????)' ,500 union all
select 00002139, 201006, '3. Planogram (??????)' ,300 union all
select 00002139, 201006, '4. Lighting / No Blockage (????? / ?????)' ,300 union all
select 00004414, 201005, '1. PM Exclusives (PM ??)' ,1500 union all
select 00004414, 201005, '2. Dispenser Share (?????)' ,600 union all
select 00004414, 201005, '3. Planogram (??????)' ,450 union all
select 00004414, 201005, '4. Lighting / No Blockage (????? / ?????)' ,450 union all
select 00004414, 201006, '1. PM Exclusives (PM ??)' ,2400 union all
select 00004414, 201006, '2. Dispenser Share (?????)' ,900 union all
select 00004414, 201006, '3. Planogram (??????)' ,600 union all
select 00004414, 201006, '4. Lighting / No Blockage (????? / ?????)' ,600

;with cte as 
(
       select customerCode
              ,YearMonth
              ,sum(Points) sumPoints
              ,row_number() over (order by customercode, yearMonth) as rownum
       from   @t
       group by customerCode, YearMonth
)
select c.*
from   cte a
left outer join cte b on b.rownum-1 = a.rownum
inner join @t c on c.customerCode = a.customerCode and c.YearMonth = a.YearMonth
where  a.sumPoints != isNull(b.sumPoints,-1)

OUTPUT:
(18 row(s) affected)

CustomerCode YearMonth   Description                                        Points
------------ ----------- -------------------------------------------------- -----------
241          201006      2. Dispenser Share (?????)                         1400
241          201006      4. Lighting / No Blockage (????? / ?????)          1000
2139         201005      2. Dispenser Share (?????)                         600
2139         201005      4. Lighting / No Blockage (????? / ?????)          300
2139         201006      1. PM Exclusives (PM ??)                           1100
2139         201006      2. Dispenser Share (?????)                         500
2139         201006      3. Planogram (??????)                              300
2139         201006      4. Lighting / No Blockage (????? / ?????)          300
4414         201005      1. PM Exclusives (PM ??)                           1500
4414         201005      2. Dispenser Share (?????)                         600
4414         201005      3. Planogram (??????)                              450
4414         201005      4. Lighting / No Blockage (????? / ?????)          450
4414         201006      1. PM Exclusives (PM ??)                           2400
4414         201006      2. Dispenser Share (?????)                         900
4414         201006      3. Planogram (??????)                              600
4414         201006      4. Lighting / No Blockage (????? / ?????)          600

(16 row(s) affected)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources