date computation from weekno and a column value - SQL Help

UDA
Enthusiast

date computation from weekno and a column value - SQL Help

Hi,

I have a table with the following structure
a
b
c
wkno
sun_1
mon_2
tue_3
wed_4
thu_5
fri_6
sat_7

I need to select abc for a particular date but don't have column that would specify the date. It just a weekno and split up of days. Based on the column value for sun_1,mon_2,tue_3,wed_4,thu_5,fri_6, sat_7 i need to frmulate the date.

For example if i have a value >0 for any of the days then the weekno+the day(sun_1 for ex) will give a date.

For example weekno 10000 indicates 1 week of 2009
then if Sat_7 has a value >0 the date would be 3-Jan-2009.

How do i write a sql to do this? Also i would have tod o this for a 200 million rows so the performance is also a major consideration. Is there any function that could do this???
6 REPLIES
Senior Apprentice

Re: date computation from weekno and a column value - SQL Help

???
How do you know that "weekno 10000 indicates 1 week of 2009"?

"if Sat_7 has a value >0 the date would be 3-Jan-2009."
And if it's <=0? What range of values is possible in Sat_7?

How are weeks defined? ISO-weeks?

It's probably better to calculate weekno and Sat_7 from that "particular date".

Dieter
Enthusiast

Re: date computation from weekno and a column value - SQL Help

We have a look up calendar table that has the data like
week no
day of week
gregorian date
julian date

The any value (positive or negative) other than zero holds significance.
a b c weekid
Enthusiast

Re: date computation from weekno and a column value - SQL Help

We have a look up calendar table that has the data like
week no
day of week
gregorian date
julian date

Any value (positive or negative) other than zero holds significance. For Ex
a b c weekid sat_qty sun_qty Mon_Qty Tue_qty Wed_qty Thu_Qty Fri_Qty
1 2 3 100000 1 0 0 0 0 0 0
2 3 4 100000 0 -5 0 0 0 0 0
3 4 5 100000 0 0 0 0 0 0 5

Calendar Table
weekid Day of week gregorian_date
100000 Saturday 3-Jan-2009
100000 Sunday 4-Jan-2009
100000 Monday 5-Jan-2009
100000 Tuesday 6-Jan-2009
100000 Wednesday 7-Jan-2009
100000 Thursday 8-Jan-2009
100000 Friday 9-Jan-2009
100001 Saturday 10-Jan-2009
100001 Sunday 11-Jan-2009
100001 Monday 12-Jan-2009

So whenever i see a qty<>0 take that column as day of week and the given weekid to calculate the date.
Senior Apprentice

Re: date computation from weekno and a column value - SQL Help

Who can be blamed for that **** data model?

Is there only a single value <> 0 for each row or multiple?

single value <> 0:
select ....
from tab join cal
on tab.weekid = cal.weekid
and
case
when sat_qty <> 0 then 'Saturday'
when sun_qty <> 0 then 'Sunday'
...
end = cal.day_of_week

multiple values <> 0:
select ....
from tab join cal
on tab.weekid = cal.weekid
and
(
case when sat_qty <> 0 then 'Saturday' end = cal.day_of_week
or
case when sun_qty <> 0 then 'Sunday' end = cal.day_of_week
or ...
)

Horrible data model horrible query

Dieter
Enthusiast

Re: date computation from weekno and a column value - SQL Help

I know its crude... but i still have to work with it...:(

The real problem is not selecting day...
i need to have the date..

The query should look like

select a,b,

(
case
when sat_qty<> 0 then (select b.normal_date from calendar_table b
where a.weekid=b.weekid and day_of_week = 1)
when sun_qty<> 0 then (select b.normal_date from calendar_table b
where a.weekid=b.weekid and day_of_week = 2)
when mon_qty<> 0 then (select b.normal_date from calendar_table b
where a.weekid=b.weekid and day_of_week = 3)
.... and so on

end
) As normal_date
,sum(c)
from table a where a.weekid = b.week_id
Senior Apprentice

Re: date computation from weekno and a column value - SQL Help

Your example uses Scalar Subqueries, in most cases it's easy to re-write them using (Outer) Joins, this is what i did:

select a,b,
b.normal_date
,sum(c)
from table a join cal b
on a.weekid = b.week_id
and (
case
when sat_qty<> 0 then 1
when sun_qty<> 0 then 2
.... and so on
end
) = b.day_of_week
group by ...

Btw, another typical way to solve that kind of problem:
select normal_date, sum(qty)
from
(
select normal_date, sat_qty as qty
from table a join cal b
on a.weekid = b.week_id
where sat_qty <> 0 and b.day_of_week = 1
union all
select normal_date, sun_qty as qty
from table a join cal b
on a.weekid = b.week_id
where sun_qty <> 0 and b.day_of_week = 2
union all
...
) dt
group by ...

Dieter