SQL Query Help - 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 2000 Forums

Transact-SQL (2000)

SQL Query Help

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-05 : 14:43:26

Hi all,

I need help with this query. One of our Sales tables contains following data.

SKU Week_No Sale_Vol
101 01 1,000
101 02 1,200
101 03 1,200
101 05 1,200
101 06 1,200
101 07 1,200
101 09 1,200
101 10 1,200
---------------------------------------
102 01 1,200
102 02 1,200
102 03 1,200
102 06 1,200
102 07 1,200
102 08 1,200
102 12 1,200

You could see the week numbers are NOT consecutive. How can I write a SQL to catch this and have the output as below:

SKU Week_No Sale_Vol
101 01 1,000 1
101 02 1,200 1
101 03 1,200 1
101 05 1,200 2
101 06 1,200 2
101 07 1,200 2
101 09 1,200 3
101 10 1,200 3
---------------------------------------
102 01 1,200 1
102 02 1,200 1
102 03 1,200 1
102 06 1,200 2
102 07 1,200 2
102 08 1,200 2
102 12 1,200 3

It seems very difficult, I’ve been struggling for the whole day but could find a solution. Thanks very much for your time and help!

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-05 : 21:39:32

I can't tell what you're trying to achieve from your 4th column. Is it that you want 3 of each number? Something like the rowcount divided by 3, but starting again for each SKU?

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-06 : 03:56:12

Is the fourth column jumping when you find there's been a non-consecutive month?

To find gaps in non-consecutive months, you could...

1/ Use an auxiliary table of numbers, and join on that. For example, if you have a table which contains 1-1000, then you can do a left join on that to your table, to find missing rows.

2/ You can do a left join on itself, LEFT JOIN... ON t1.sku = t2.sku and t2.week_no = t1.week_no + 1 where t2.week_no is null. This will find rows that don't have a row after it. It will identify the gaps, but not give you every row that's missing.

This what you're after?

Rob

Rob Farley
http://robfarley.blogspot.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 06:10:45

http://www.sqlservercentral.com/columnists/gsmilevski/anefficientsetbasedsolutionforislandsandgaps.asp

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 06:30:17

Aside from the article above, here's the simplest way I know to do what you need...

Note that (since there is no ordering allowed on an update), this method 'cannot be guaranteed' - although I've never seen an example of it not working, but be wary all the same.

--data
declare @t table (SKU int, Week_No int, Sale_Vol int, Grouping int
    primary key clustered (SKU, Week_No, Sale_Vol))
insert @t (SKU, Week_No, Sale_Vol)
          select 101, 01, 1000
union all select 101, 02, 1200
union all select 101, 03, 1200
union all select 101, 05, 1200
union all select 101, 06, 1200
union all select 101, 07, 1200
union all select 101, 09, 1200
union all select 101, 10, 1200

union all select 102, 01, 1200
union all select 102, 02, 1200
union all select 102, 03, 1200
union all select 102, 06, 1200
union all select 102, 07, 1200
union all select 102, 08, 1200
union all select 102, 12, 1200

--calculation
declare @SKU int, @Week_No int, @Grouping int
update @t set 
    @Grouping = case when not SKU = @SKU then 1
                     when Week_No = @Week_No + 1 then isnull(@Grouping, 1)
                     else isnull(@Grouping + 1, 1) end,
    Grouping = @Grouping, @Week_No = Week_No, @SKU = SKU
from @t

select * from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-06 : 14:17:32

Thanks very much for above replies, very helpful. I took RyanRandall's solution at the end and solve the problem. Thanks again.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-06 : 20:32:51

Ryan, I have to disagree.

Updating variables within update queries can be unreliable. It might do the trick most of the time, but it depends on the execution plan being suitable, based on indexes, etc. What happens if the system decides that the best way of going through the table is to use an order on a different field, like week_no before SKU. Then your results are skewed.

If you like, I'll put together a query which gives you your island numbers which isn't dependent on having an update query that is order-dependent.

Rob Farley
http://robfarley.blogspot.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 05:09:00

quote:
Ryan, I have to disagree.

Rob - What specifically do you disagree with? I don't believe I said anything which is false.

* This method 'cannot be guaranteed' -> True
* I've never seen an example of it not working -> True

If I needed something guaranteed, I'd use the method in the link I posted. If you want to implement that for this example, I'd be happy to see it...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-07 : 20:48:26

I'll try to write something in the next day or two. I'm a bit flat out right now with other stuff. :(

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-09 : 04:43:39

I've written a couple of queries that achieve this. But I'm a little tempted to turn them into an article for SQLServerCentral instead of posting them here... Is that mean of me, or is it fair enough, considering that lcpx has already solved his problem anyway?

Rob Farley
http://robfarley.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 07:00:10

Fair enough. Here is a another set-based, non-update, method.

-- Prepare test data
declare @t table (SKU int, Week_No int, Sale_Vol int)

insert @t
          select 101, 01, 1000
union all select 101, 02, 1200
union all select 101, 03, 1200
union all select 101, 05, 1200
union all select 101, 06, 1200
union all select 101, 07, 1200
union all select 101, 09, 1200
union all select 101, 10, 1200

union all select 102, 01, 1200
union all select 102, 02, 1200
union all select 102, 03, 1200
union all select 102, 06, 1200
union all select 102, 07, 1200
union all select 102, 08, 1200
union all select 102, 12, 1200

-- Do all the magic in one step
SELECT	o1.SKU, t.week_no, t.sale_vol, 
 	(
		SELECT	1 + COUNT(*)
		FROM	(SELECT	LowLimit.SKU,
			LowLimit.Week_No FromWeek,
			ISNULL((
				SELECT	TOP 1 Week_No
				FROM	@t HighLimit
				WHERE	NOT EXISTS	(
						SELECT	B.Week_No
						FROM	@t B
						WHERE	HighLimit.Week_No + 1 = B.Week_No
							AND HighLimit.SKU = B.SKU
					)
			AND HighLimit.SKU = LowLimit.SKU
			AND HighLimit.Week_No > LowLimit.Week_No
	), LowLimit.Week_No) ToWeek
FROM	@t LowLimit
WHERE	NOT EXISTS	(
				SELECT	B.SKU,
					B.Week_No
				FROM	@t B
				WHERE	LowLimit.SKU = B.SKU
					AND LowLimit.Week_No - 1 = B.Week_No
			)
) O2
   		WHERE	O2.sku = O1.sku
     			AND O2.fromweek < O1.fromweek
	) GroupID
FROM	(SELECT	LowLimit.SKU,
	LowLimit.Week_No FromWeek,
	ISNULL((
		SELECT	TOP 1 Week_No
		FROM	@t HighLimit
		WHERE	NOT EXISTS	(
						SELECT	B.Week_No
						FROM	@t B
						WHERE	HighLimit.Week_No + 1 = B.Week_No
							AND HighLimit.SKU = B.SKU
					)
			AND HighLimit.SKU = LowLimit.SKU
			AND HighLimit.Week_No > LowLimit.Week_No
	), LowLimit.Week_No) ToWeek
FROM	@t LowLimit
WHERE	NOT EXISTS	(
				SELECT	B.SKU,
					B.Week_No
				FROM	@t B
				WHERE	LowLimit.SKU = B.SKU
					AND LowLimit.Week_No - 1 = B.Week_No
			)
) O1
inner join @t t on t.sku = o1.sku and t.week_no between o1.fromweek and o1.toweek

Here is another, code-cleaner, way to solve it, with staging some data.
Trick is to save some data (island numbers) in a table variable as


DECLARE @r TABLE (SKU INT, FromWeek INT, ToWeek INT)

INSERT	@r
SELECT	LowLimit.SKU,
	LowLimit.Week_No,
	ISNULL(	(
			SELECT	TOP 1 Week_No
			FROM	@t HighLimit
			WHERE	NOT EXISTS	(
							SELECT	B.Week_No
							FROM	@t B
							WHERE	HighLimit.Week_No + 1 = B.Week_No
								AND HighLimit.SKU = B.SKU
						)
				AND HighLimit.SKU = LowLimit.SKU
				AND HighLimit.Week_No > LowLimit.Week_No
		), LowLimit.Week_No)
FROM	@t LowLimit
WHERE	NOT EXISTS	(
				SELECT	B.SKU,
					B.Week_No
				FROM	@t B
				WHERE	LowLimit.SKU = B.SKU
					AND LowLimit.Week_No - 1 = B.Week_No
			)

and then get the results with


SELECT		w.SKU,
		t.Week_No,
		t.Sale_Vol, 
 		(
			SELECT	1 + COUNT(*)
			FROM	@r z
   			WHERE	w.SKU = z.SKU
     				AND w.FromWeek > z.FromWeek
		) Group_No
FROM		@r w
INNER JOIN	@t t ON t.SKU = w.SKU and t.Week_No BETWEEN w.FromWeek AND w.ToWeek

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-09 : 09:09:50

Hi Peso, I tested it, it works perfectly. The first solution is magic and very clever. Thanks a lot!

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-09 : 22:17:38

Alright, here's my one. I still might use it in an article, as it's most definitely all my own work:

Here you go, one version with CTEs, one without. The idea is to identify those rows that mark the start of an island, and then get a cumulative count (or sum) of them to create an 'island number' per SKU. I use a CTE in the first example simply because I want to refer to this subquery multiple times. I figure the CTE makes it easier to read.

with sales_starts (sku, week, sale_vol, is_start) as (
select s.sku, s.week, s.sale_vol, case when s_prev.week is null then 1 end as is_start
from sales s
left join
sales s_prev
on s_prev.sku = s.sku
and s_prev.week = s.week - 1
)
select s.sku, s.week, s.sale_vol, (select count(s2.is_start) from sales_starts s2 where s2.sku = s.sku and s2.week <= s.week) as island_no
from sales_starts s;

--Or without CTEs (harder to read, of course)
select st.sku, st.week, st.sale_vol,
(select count(st2.is_start)
from (select s.sku, s.week, s.sale_vol, case when s_prev.week is null then 1 end as is_start
from sales s
left join
sales s_prev
on s_prev.sku = s.sku
and s_prev.week = s.week - 1
) st2
where st2.sku = st.sku and st2.week <= st.week) as island_no
from (select s.sku, s.week, s.sale_vol, case when s_prev.week is null then 1 end as is_start
from sales s
left join
sales s_prev
on s_prev.sku = s.sku
and s_prev.week = s.week - 1
) st;

And, I do realise that I could've done this with joins instead of having a subquery in the select clause. For completeness, I'm putting the CTE version of that here too.

with sales_starts (sku, week, sale_vol, is_start) as (
select s.sku, s.week, s.sale_vol, case when s_prev.week is null then 1 end as is_start
from sales s
left join
sales s_prev
on s_prev.sku = s.sku
and s_prev.week = s.week - 1
)
select s.sku, s.week, s.sale_vol, count(s_earlier.is_start) as island_no
from sales_starts s
join
sales_starts s_earlier
on s_earlier.sku = s.sku
and s_earlier.week <= s.week
group by s.sku, s.week, s.sale_vol;

Using joins like this is less intuitive, but it's equally as correct.

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-10 : 22:32:29

Stupid me - I used one too many copies of the sales table. This is better:

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-10 : 22:32:40

select s.sku, s.week, s.sale_vol,
(select count(*)
from
sales s_earlier
left join
sales s_prev
on s_prev.sku = s_earlier.sku
and s_prev.week = s_earlier.week - 1
where s_earlier.sku = s.sku
and s_earlier.week <= s.week
and s_prev.week is null
)
from sales s;

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources