Compare values of records in same table

microsoft.public.sqlserver.programming

30 Jun 2005 2:05 PM

Chesster

CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int)

INSERT INTO test values('aaa','6/5/2005',0)
INSERT INTO test values('aaa','6/16/2005',75)
INSERT INTO test values('aaa','6/20/2005',159)
INSERT INTO test values('bbb','6/8/2005',7)
INSERT INTO test values('bbb','6/15/2005',10)
INSERT INTO test values('bbb','6/18/2005',11)
INSERT INTO test values('ccc','6/10/2005',0)
INSERT INTO test values('ccc','6/17/2005',40)
INSERT INTO test values('ccc','6/19/2005',0)
INSERT INTO test values('ddd','6/4/2005',2)
INSERT INTO test values('ddd','6/16/2005',2)
INSERT INTO test values('ddd','6/20/2005',10)
INSERT INTO test values('eee','6/15/2005',111)
INSERT INTO test values('eee','6/19/2005',23)

I need a query that returns the records where the change in qty's of the
same materials is >= 20% within a specific date range. For example: using the
above data the date range I need is from 6/15/2005 to 6/20/2005. Now for the
records in between that date range I have to compare the qty columns where
the materials match and return only the records where the change between
those values is >= 20%. So I need the query to return the two 'aaa' records
because the change in their qty's (75, 159) is >= 20%. I don't need the 'bbb'
records because the change is < 20% but I need the 'ccc', 'ddd' and 'eee'
records because the qty's within the date range have a change of >= 20%

Any help would be greatly appreciated.

30 Jun 2005 3:22 PM

Alejandro Mesa

Chesster,

Thanks for the ddl and sample data. See if this helps:

use northwind
go

CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int)
go

INSERT INTO test values('aaa','6/5/2005',0)
INSERT INTO test values('aaa','6/16/2005',75)
INSERT INTO test values('aaa','6/20/2005',159)
INSERT INTO test values('bbb','6/8/2005',7)
INSERT INTO test values('bbb','6/15/2005',10)
INSERT INTO test values('bbb','6/18/2005',11)
INSERT INTO test values('ccc','6/10/2005',0)
INSERT INTO test values('ccc','6/17/2005',40)
INSERT INTO test values('ccc','6/19/2005',0)
INSERT INTO test values('ddd','6/4/2005',2)
INSERT INTO test values('ddd','6/16/2005',2)
INSERT INTO test values('ddd','6/20/2005',10)
INSERT INTO test values('eee','6/15/2005',111)
INSERT INTO test values('eee','6/19/2005',23)
go

create function dbo.ufn_v1 (
@sd datetime,
@ed datetime
)
returns table
as
return (
select
    material,
    import_dt,
    qty
from
    test
where
    import_dt >= @sd and import_dt < dateadd(day, 1, @ed)
)
go

declare @sd datetime, @ed datetime

set @sd = '20050615'
set @ed = '20050620'

select
    material,
    import_dt,
    previous_qty,
    qty,
    qty_percent_change
from
    (
    select
        t1.material,
        t1.import_dt,
        t2.qty as previous_qty,
        t1.qty,
        (abs(t1.qty - t2.qty) * 100.00) / isnull(nullif(t2.qty, 0), 1.00) as
qty_percent_change
    from
        ufn_v1(@sd, @ed) as t1
        inner join
        ufn_v1(@sd, @ed) as t2
        on t1.material = t2.material
        and t2.import_dt = (select max(t3.import_dt) from ufn_v1(@sd, @ed) as t3
where t3.material = t1.material
        and t3.import_dt < t1.import_dt
        )
    ) as t
where
    qty_percent_change > 20.00
order by
    material,
    import_dt
go

drop function dbo.ufn_v1
go

drop table test
go

AMB

Show quote

"Chesster" wrote:

> CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int)
>
> INSERT INTO test values('aaa','6/5/2005',0)
> INSERT INTO test values('aaa','6/16/2005',75)
> INSERT INTO test values('aaa','6/20/2005',159)
> INSERT INTO test values('bbb','6/8/2005',7)
> INSERT INTO test values('bbb','6/15/2005',10)
> INSERT INTO test values('bbb','6/18/2005',11)
> INSERT INTO test values('ccc','6/10/2005',0)
> INSERT INTO test values('ccc','6/17/2005',40)
> INSERT INTO test values('ccc','6/19/2005',0)
> INSERT INTO test values('ddd','6/4/2005',2)
> INSERT INTO test values('ddd','6/16/2005',2)
> INSERT INTO test values('ddd','6/20/2005',10)
> INSERT INTO test values('eee','6/15/2005',111)
> INSERT INTO test values('eee','6/19/2005',23)
>
>
> I need a query that returns the records where the change in qty's of the
> same materials is >= 20% within a specific date range. For example: using the
> above data the date range I need is from 6/15/2005 to 6/20/2005. Now for the
> records in between that date range I have to compare the qty columns where
> the materials match and return only the records where the change between
> those values is >= 20%. So I need the query to return the two 'aaa' records
> because the change in their qty's (75, 159) is >= 20%. I don't need the 'bbb'
> records because the change is < 20% but I need the 'ccc', 'ddd' and 'eee'
> records because the qty's within the date range have a change of >= 20%
>
> Any help would be greatly appreciated.
>
>

30 Jun 2005 4:18 PM

Chesster

Thanks-a-million!

Show quote

"Alejandro Mesa" wrote:

> Chesster,
>
> Thanks for the ddl and sample data. See if this helps:
>
> use northwind
> go
>
> CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int)
> go
>
> INSERT INTO test values('aaa','6/5/2005',0)
> INSERT INTO test values('aaa','6/16/2005',75)
> INSERT INTO test values('aaa','6/20/2005',159)
> INSERT INTO test values('bbb','6/8/2005',7)
> INSERT INTO test values('bbb','6/15/2005',10)
> INSERT INTO test values('bbb','6/18/2005',11)
> INSERT INTO test values('ccc','6/10/2005',0)
> INSERT INTO test values('ccc','6/17/2005',40)
> INSERT INTO test values('ccc','6/19/2005',0)
> INSERT INTO test values('ddd','6/4/2005',2)
> INSERT INTO test values('ddd','6/16/2005',2)
> INSERT INTO test values('ddd','6/20/2005',10)
> INSERT INTO test values('eee','6/15/2005',111)
> INSERT INTO test values('eee','6/19/2005',23)
> go
>
> create function dbo.ufn_v1 (
> @sd datetime,
> @ed datetime
> )
> returns table
> as
> return (
> select
>     material,
>     import_dt,
>     qty
> from
>     test
> where
>     import_dt >= @sd and import_dt < dateadd(day, 1, @ed)
> )
> go
>
> declare @sd datetime, @ed datetime
>
> set @sd = '20050615'
> set @ed = '20050620'
>
> select
>     material,
>     import_dt,
>     previous_qty,
>     qty,
>     qty_percent_change
> from
>     (
>     select
>         t1.material,
>         t1.import_dt,
>         t2.qty as previous_qty,
>         t1.qty,
>         (abs(t1.qty - t2.qty) * 100.00) / isnull(nullif(t2.qty, 0), 1.00) as
> qty_percent_change
>     from
>         ufn_v1(@sd, @ed) as t1
>         inner join
>         ufn_v1(@sd, @ed) as t2
>         on t1.material = t2.material
>         and t2.import_dt = (select max(t3.import_dt) from ufn_v1(@sd, @ed) as t3
> where t3.material = t1.material
>         and t3.import_dt < t1.import_dt
>         )
>     ) as t
> where
>     qty_percent_change > 20.00
> order by
>     material,
>     import_dt
> go
>
> drop function dbo.ufn_v1
> go
>
> drop table test
> go
>
>
> AMB
>
>
>
> "Chesster" wrote:
>
> > CREATE TABLE test (material varchar(3), import_dt smalldatetime, qty int)
> >
> > INSERT INTO test values('aaa','6/5/2005',0)
> > INSERT INTO test values('aaa','6/16/2005',75)
> > INSERT INTO test values('aaa','6/20/2005',159)
> > INSERT INTO test values('bbb','6/8/2005',7)
> > INSERT INTO test values('bbb','6/15/2005',10)
> > INSERT INTO test values('bbb','6/18/2005',11)
> > INSERT INTO test values('ccc','6/10/2005',0)
> > INSERT INTO test values('ccc','6/17/2005',40)
> > INSERT INTO test values('ccc','6/19/2005',0)
> > INSERT INTO test values('ddd','6/4/2005',2)
> > INSERT INTO test values('ddd','6/16/2005',2)
> > INSERT INTO test values('ddd','6/20/2005',10)
> > INSERT INTO test values('eee','6/15/2005',111)
> > INSERT INTO test values('eee','6/19/2005',23)
> >
> >
> > I need a query that returns the records where the change in qty's of the
> > same materials is >= 20% within a specific date range. For example: using the
> > above data the date range I need is from 6/15/2005 to 6/20/2005. Now for the
> > records in between that date range I have to compare the qty columns where
> > the materials match and return only the records where the change between
> > those values is >= 20%. So I need the query to return the two 'aaa' records
> > because the change in their qty's (75, 159) is >= 20%. I don't need the 'bbb'
> > records because the change is < 20% but I need the 'ccc', 'ddd' and 'eee'
> > records because the qty's within the date range have a change of >= 20%
> >
> > Any help would be greatly appreciated.
> >
> >

30 Jun 2005 6:02 PM

--CELKO--

Can I assume this is what you meant? Otherwise we need more specs and
you need a key for the table.

CREATE TABLE Test
(material CHAR(3) NOT NULL,
import_dt DATETIME NOT NULL,
PRIMARY KEY (material, import_dt),
qty INTEGER NOT NULL
CHECK (qty > 0));

>> query that returns the records [sic] where the change in qty's of the same materials is >= 20% within a specific date range. <<

SELECT material, MIN(qty), MAX(qty)
FROM Test AS T1
WHERE import_dt BETWEEN @my_start_date AND @my_end_date
GROUP BY material
HAVING 0.80 * MAX(qty) >= MIN(qty);