Can I achieve this with SQL? - 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)

Can I achieve this with SQL?

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-02 : 05:39:37

Hi all,

I am facing a pretty challenging task on SQL. We have a table, the structure is like below:

Date Sales_Volume
Week01 1,000
Week02 1,200
Week03 1,400
Week06 2,000
Week07 1,700
Week08 1,500
Week11 1,200
Week12 1,300
Week13 1,400
Week14 1,600

From above table you would see the week numbers are not consecutive. What I am trying to do is separating all those weeks are not consecutive and at the mean time, giving a sequence number to those consecutive weeks. Here is my desired output.

Date Sales_Volume Sequence_No
Week01 1,000 1
Week02 1,200 2
Week03 1,400 3
Week06 2,000 1
Week07 1,700 2
Week08 1,500 3
Week11 1,200 1
Week12 1,300 2
Week13 1,400 3
Week14 1,600 4

I am not sure is there a way I can achieve this with SQL, or I need PL/SQL to do this. Our database environment is MS SQL2000. I appreciate any thought you have on it. Many thanks for your time and help.

Pete

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-02 : 06:07:48

What makes you think you can do something in pl/sql but not t-sql. Usually you can do anything you need in either. The problem is usually knowing what you want to do.

Not efficient but my first thought is (this would be a lot simpler in v2005 with a common table expression I feel)

To get the week number
convert(int,right(date,2))

everything that follows I've used d for the above expression

To get the start of sequences
select *
from tbl t1
where not exists (select * from tbl t2 where t2.d+1 = t1.d)

so to get the result
select t.*, (select count(*) from tbl t2 where t2.d <= t.d and t2.d >= a.strtseq)
from
tbl t
join
(select t.d, strtseq = max(t1.d) from tbl t, tbl t1 where not exists (select * from tbl t2 where t2.d+1 = t1.d) and t1.d <= t.d group by t.d) a
on t.d = a.d

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 06:17:34

declare @table table
(
	weekno	int
)

insert into @table
select 1 union
select 2 union
select 3 union
select 6 union
select 7 union
select 8 union
select 11 union
select 12 union
select 13 union
select 14

select	t.weekno, t.weekno - (
	select 	max(weekno) as max_weekno
	from
	(	
		select	weekno,
			(select count(*) from @table x where x.weekno = t1.weekno -1) as flag
		from	@table t1
	) a
	where	flag	= 0
	and	weekno	<= t.weekno) + 1
from	@table t

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 06:30:29

And another option...

--data
declare @t table (Date varchar(10) primary key, Sales_Volume int, Sequence_No int)
insert @t (Date, Sales_Volume)
          select 'Week01', 1000
union all select 'Week02', 1200
union all select 'Week03', 1400
union all select 'Week06', 2000
union all select 'Week07', 1700
union all select 'Week08', 1500
union all select 'Week11', 1200
union all select 'Week12', 1300
union all select 'Week13', 1400
union all select 'Week14', 1600

--calculation - note that the order 'can not be guaranteed', though I've never yet seen an example of this not working.
declare @i int, @PreviousDateNumber int

update @t set 
    @i = case when cast(right(Date, 2) as int) = @PreviousDateNumber + 1 then @i + 1 else 1 end,
    Sequence_No = @i,
    @PreviousDateNumber = right(Date, 2)

select * from @t

/*results
Date       Sales_Volume Sequence_No 
---------- ------------ ----------- 
Week01     1000         1
Week02     1200         2
Week03     1400         3
Week06     2000         1
Week07     1700         2
Week08     1500         3
Week11     1200         1
Week12     1300         2
Week13     1400         3
Week14     1600         4
*/

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-02 : 08:08:12

Thanks very much for your help, that is what I was looking for. But our case is slightly different. Instead of week no in the first column, it's actually the Week Commencing Date. For instance I want it to display as:

Date Sales_Volumn Sequence_No
02/01/2005 1,000 1
09/01/2005 1,000 2
16/01/2005 1,200 3
23/01/2005 1,150 4
-------
11/12/2005 1,300 1
18/12/2005 1,200 2
---------
01/01/2006 1,000 1
08/01/2006 1,000 2
15/01/2006 1,200 3
22/01/2006 1,300 4

How I can apply the SQL to these as I can’t use MAX function and do the calculations.

Thanks very much for your time and help!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 08:28:57

Ah, we have a case of moving goal posts

1. Are you absolutely certain that you last post specifies what you want as your final output?
2. Is your actual data structure what you originally posted (i.e. Date with actual values of 'Week01' etc...)?

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-02 : 08:38:25

Hi Ryan Randall,

I am sorry if I comfused you. Yes, my last post is actually what I want exactly. I put Week No instead just wanted to make things easier to understand. Thanks very much for your help.

Pete

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 08:44:23

And my 2nd question?

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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-02 : 08:55:27

You can use datediff(ww,'20050101',Date)
to get the week number to use in the sequence.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-02 : 09:46:07

2.Is your actual data structure what you originally posted (i.e. Date with actual values of 'Week01' etc...)?

No, I put Week Number instead just wanted to make things easier to understand, it's actually Week Commencing Date.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-02 : 09:51:34

You can use datediff(ww,'20050101',Date)
to get the week number to use in the sequence.

I am afraid I can't, because I have more than 3 years history data, I can't use Week Number to identify them. If it's week number, it has to be like: 2005 Week01, 2006 Week2 etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 09:55:01

then use YYYYWW

select year(date) * 100 + datepart(week, date)

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-02 : 10:23:42

That really helps, thanks a lot!

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-07 : 05:57:12

Hi all, I got a new problem with the SQL. Can I change the output a little bit, it's like below now:

Week_No_Volumn Sequence_No
01 1,000 1
02 1,000 2
03 1,200 3
04 1,150 4
-------
07 1,300 1
08 1,200 2
---------
11 1,000 1
12 1,000 2
13 1,200 3
14 1,300 4

How can I make it as:

Week_No Sales_Volumn Sequence_No
01 1,000 4
02 1,000 3
03 1,200 2
04 1,150 1
-------
07 1,300 2
08 1,200 1
---------
11 1,000 4
12 1,000 3
13 1,200 2
14 1,300 1

Basically what I want is instead of ascend, can I order the SQ_No descend?

How should I change the SQL:

select t.weekno, t.weekno - (
select max(weekno) as max_weekno
from
(
select weekno,
(select count(*) from @table x where x.weekno = t1.weekno -1) as flag
from @table t1
) a
where flag = 0
and weekno <= t.weekno) + 1
from @table t

Thanks very much for your help.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-07 : 06:02:18

I found the solution, the SQL should change as:

select t.weekno, (
select min(weekno) as max_weekno
from
(
select weekno,
(select count(*) from @table x where x.weekno = t1.weekno +1) as flag
from @table t1
) a
where flag = 0
and weekno >= t.weekno) - t.weekno + 1
from @table t

Thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources