Sequence week number when year changed

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)

Sequence week number when year changed

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-09 : 09:43:36

Hi all,

I post a question regarding to SQL query help about 1 week ago, I got very helpful replies from you and I solve the problem eventually. Please refer the following link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67119

But yesterday I got 2 problems of my SQL raised from my users. Here is our table structure:

weekno Sales_Volume
200501 1,000
200502 1,200
200503 1,400
200506 2,000
200507 1,700
200508 1,500
200511 1,200
200512 1,300
200513 1,400
200514 1,600

I was hoping to get the output as below:

weekno Sales_Volume Sequence_No
200501 1,000 1
200502 1,200 2
200503 1,400 3
-----------------
200506 2,000 1
200507 1,700 2
200508 1,500 3
-----------------
200511 1,200 1
200512 1,300 2
200513 1,400 3
200514 1,600 4

Basically if the weeks number are not consecutive, then I would give a sequence number. I could achieve this by this 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

Q1: The SQL is perfect and I can get my desired output, but if the week number reaches to Week 53, I want my Sequence_No in the next week can still be consecutive. For instance:

Weekno Sales_Volume Sequence_No
200541 1,000 1
200542 1,200 2
200543 1,400 3
-----------------
200549 2,000 1
200550 1,700 2
200551 1,500 3
-----------------
200553 1,200 1
200551 1,300 2
200602 1,400 3
200603 1,600 4

Since x.weekno is not equal to t1.weekno -1 anymore, how can I achieve this?

Q2: My user said there is no sales on WEEK52 because of the Christmas, so in our system there is always no sales on that particular week. But is there a way I can make the Sequence_No still consecutive. Here is my final desired output.

Weekno Sales_Volume Sequence_No
200541 1,000 1
200542 1,200 2
200543 1,400 3
-----------------
200549 2,000 1
200550 1,700 2
200551 1,500 3
200553 1,200 4
200601 1,300 5
200602 1,400 6
200603 1,600 7

Thanks very much for your time and help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 10:05:46

Line "200551 1,300 5" would be "200601 1,300 5", right?

One way of fixing it could be to insert "dummy" rows for the weeks not used, ie
200552 -1
200553 -1

and discard weeks having sales less than zero. this way you can set an extra check for last years week 53, this years week 1 to treat them consecutive...

I think the only sane way to fix this is to have some kind of tally table


id  weekno           id  weekno  inuse
--  ------           --  ------  -----
 1  200501            1  200501      1
 2  200502            2  200502      1
...            or    ... 
51  200551           51  200551      1
52  200553           52  200552      0
53  200601           53  200553      1
                     54  200601      1

where all "used" weeks are stored. As you can see, week 200552 is not there but with the help of the tally table, it is still possible to calculate consecutiveness. All your other questions of week calculations could be more easily solved with this tally table.
Otherwise, how to tell if 200552 really is out of scope [no sale] or bad sale? Likewise 200504, is that a "no sale" week or a bad sale?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-09 : 10:14:53

"Q1: The SQL is perfect and I can get my desired output, but if the week number reaches to Week 53, I want my Sequence_No in the next week can still be consecutive"
Calculate the relative week no from a fixed date example 2000-01-01. so
200551 = n
200552 = n + 1
200553 = n + 2
200601 = n + 3

Or build your own calendar table and have your own running sequence no there. This option might be more feasible and will also solve your Q2.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-09 : 10:33:55

Reading through all this, you have never explained what your definition of week of year is. Different organizations have different definitions.

For example, the ISO standard is to have the first week of the year begin on the first Monday on or before Jan 4th of each year. This means the first week of the year can start anywhere from the 28th of December of the prior year, through January 4th.

It is likely that the date table created by the function in the link below could be used for what you want, but without knowing what your week is, it is hard to help you.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources