self join problem

19 Jul 2006 4:30 PM

MJ

SQL2K. Here is the table:
M         Q1       Q2
1/06     10        100
2/06     -5        150
3/06     15        50
4/06     20        10
5/06     -3        500
6/06    -10       24
.....
if Q1 of last month < 0, then
     (Q1 + Q2)
else
     (Q2)
end

i used a self join to compare the month, but ended up with duplicates. It
was not a very efficient query either. Could anyone give me some help? Thanks
much.

19 Jul 2006 4:41 PM

Arnie Rowland

I'm not really clear you your question. Please confirm that your desired results looks somewhat like this:

M    Value
1/06    100 (12/05 assumed 0 since missing)
2/06    150
3/06    65
4/06    10
5/06    500
6/06    14

Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"MJ" <M*@discussions.microsoft.com> wrote in message news:6F0E9602-02B7-408D-8D6A-D68C7F339337@microsoft.com...
> SQL2K. Here is the table:
> M         Q1       Q2
> 1/06     10        100
> 2/06     -5        150
> 3/06     15        50
> 4/06     20        10
> 5/06     -3        500
> 6/06    -10       24
> ....
> if Q1 of last month < 0, then
>     (Q1 + Q2)
> else
>     (Q2)
> end
>
> i used a self join to compare the month, but ended up with duplicates. It
> was not a very efficient query either. Could anyone give me some help? Thanks
> much.

19 Jul 2006 7:12 PM

MJ

M    Value
1/06    100 (12/05 assumed 0 since missing)
2/06    150
3/06    65
4/06    10
5/06    500
6/06    14

Yes. That's what I was looking for. I got the query to work though:

select B.Q1 + B.Q2
from #table as B
inner join #table as A
on B.id = A.id
and A.date = ("B.date - 1 month")
and A.Q1 < 0

union

select B.Q2
......
......
and A.Q1 >=0

Show quote

"Arnie Rowland" wrote:

> I'm not really clear you your question. Please confirm that your desired results looks somewhat like this:
>
> M    Value
> 1/06    100 (12/05 assumed 0 since missing)
> 2/06    150
> 3/06    65
> 4/06    10
> 5/06    500
> 6/06    14
>
>
>
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "MJ" <M*@discussions.microsoft.com> wrote in message news:6F0E9602-02B7-408D-8D6A-D68C7F339337@microsoft.com...
> > SQL2K. Here is the table:
> > M         Q1       Q2
> > 1/06     10        100
> > 2/06     -5        150
> > 3/06     15        50
> > 4/06     20        10
> > 5/06     -3        500
> > 6/06    -10       24
> > ....
> > if Q1 of last month < 0, then
> >     (Q1 + Q2)
> > else
> >     (Q2)
> > end
> >
> > i used a self join to compare the month, but ended up with duplicates. It
> > was not a very efficient query either. Could anyone give me some help? Thanks
> > much

20 Jul 2006 5:27 AM

Omnibuzz

you don't need a Union for this.. Since the dateformat you had given threw an
error by the parser in my head, I assumed it to be an int (and since you have
a workable query to get this done "and A.date = ("B.date - 1 month")"
I guess you can write it to your way...

create table #quarter(M int        ,Q1      int, Q2 int)
insert into #quarter values(1 ,    10   ,     100 )
insert into #quarter values(2 ,    -5   ,     150 )
insert into #quarter values(3 ,    15   ,     50 )
insert into #quarter values(4 ,    20   ,     10 )
insert into #quarter values(5 ,    -3   ,     500 )
insert into #quarter values(6 ,   -10   ,    24    )

select a.m, case when isnull(b.Q1,0) >=0 then a.q2 else a.q1 + a.q2 end as
result from #quarter a left outer join #quarter b
on a.m = b.m + 1

Hope this helps

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

20 Jul 2006 6:41 AM

jsfromynr

Hi There,

Omnibuzz's Solution will work indeed

You may like to try this.

Create view tmp1
as
Select '20060101' M,     10       Q1, 100 Q2
Union All
Select '20060201',     -5        ,150
Union All
Select '20060301',     15        ,50
Union All
Select '20060401',     20        ,10
Union All
Select '20060501',     -3        ,500
Union All
Select '20060601',    -10       ,24

Select *,T1.M, case when isnull(t2.Q1,0)>=0 then T1.Q2 Else T1.Q1+T1.Q2
End

>From tmp1 T1

Left Join tmp1 T2
On datediff(m,convert(datetime,T2.M,112), convert(datetime,T1.M,112))=1

drop view tmp1

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

20 Jul 2006 3:57 PM

MJ

Thanks for the help. The Left Outer Join would work perfectly. In the case, I
would think that the left outer join is more efficient than the union?

Show quote

"jsfromynr" wrote:

>
> Hi There,
>
> Omnibuzz's Solution will work indeed
>
> You may like to try this.
>
> Create view tmp1
> as
> Select '20060101' M,     10       Q1, 100 Q2
> Union All
> Select '20060201',     -5        ,150
> Union All
> Select '20060301',     15        ,50
> Union All
> Select '20060401',     20        ,10
> Union All
> Select '20060501',     -3        ,500
> Union All
> Select '20060601',    -10       ,24
>
>
> Select *,T1.M, case when isnull(t2.Q1,0)>=0 then T1.Q2 Else T1.Q1+T1.Q2
> End
> >From tmp1 T1
> Left Join tmp1 T2
> On datediff(m,convert(datetime,T2.M,112), convert(datetime,T1.M,112))=1
>
>
> drop view tmp1
>
> With Warm regards
> Jatinder Singh
> http://jatindersingh.blogspot.com
> http://sqloracle.tripod.com
>
>

19 Jul 2006 5:31 PM

--CELKO--

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You have posted before; why are you so rude?

CREATE TABLE StinkingDirtyFoobar -- you gave no name, so I picked one!
..
(improper_month_encoding CHAR (5) NOT NULL
CHECK (improper_month_encoding LIKE <<complicated Regular
Expression>>),
q1 INTEGER NOT NULL,
q2 INTEGER NOT NULL);

>> if Q1 of last month < 0, then (Q1 + Q2) else (Q2) endif <<

>> i used a self join to compare the month, but ended up with duplicates. It was not a very efficient query either. <<

Want to share it with us? Or you can change the DDL to use temporal
darta types and do math on them.