Help with TSQL - 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)

Help with TSQL

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-05-02 : 12:37:46

Hi All,

I am looking for 2 queries related to same table & fileds

I have a table Sales with the following fields.

SaleDate (datetime), ProductID (nvarchar), and Price (decimal=18,2)

This table hold the price information of each product per date. That is, a product may have different price on each date.

Problem 1:
I want the results for the most recent 3 weeks. Here's how I need my query results to be:

Saledate, ProductID,Price1stWeek,Price2ndWeek,Price2ndWeek
How can I achieve this?

Problem 2:
I want the most latest price (not null) availabe for a product in the sales table, that is I need the follwing fileds:

ProductID, Price

Again i need the most latest (non null) price available for each product.

I would really appreciate if anyone can help with those 2 queries.

Thanks,

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 13:36:19

[CODE]Select ProductID, SaleDate, Price from TABLE order by ProductID, SaleDate DESC[/CODE]Will get you a list of all of the products sorted by the ProductID and then by the Date in descening order so that you'll see the most recent sales date for the product first, the next next etc.

I'll post another query separately for you that resolves #2.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 13:46:04

The following sets up a test table and data, and then gives you the query you need for Problem #2:[CODE]create table #Table1 (SaleDate datetime, ProductID varchar(50), Price decimal(18,2))

insert into #table1 (SaleDate, ProductId, Price)
select '01/01/06','abc',123.50 UNION ALL
select '01/08/06','abc',125.50 UNION ALL
select '01/15/06','abc',126.50 UNION ALL
select '01/01/06','def',123.50 UNION ALL
select '01/08/06','def',123.50 UNION ALL
select '01/15/06','def',123.50 UNION ALL
select NULL,'def',893.49

select t.ProductId, t.Price, t.SaleDate
from #table1 t inner join (
select ProductId, Max(SaleDate) as MaxDate
from #table1
where SaleDate is not null
group by ProductId) jt on jt.ProductId = t.ProductId and jt.MaxDate = t.SaleDate
order by t.ProductId[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 13:50:18

I'd be happy to try and work on the actual query you need for #1, but I need some more information.

Are the SaleDates always 1 week apart? Or do I have to worry about 3 SaleDates in the same week, and then the next SalesDate is more than 1 week after the previous one? There is a pretty easy way to write the query if the dates are always at least 1 week apart, and a more elegant solution will be needed if you really need to determine the actual week of the data.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-05-02 : 14:07:42

druer,

Thanks for your time.
Yes the dates are always 1 week apart (eg 2006-04-02,2006-04-09,2006-04-16 etc)

Any help would be appreciated.

Thanks,

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 15:09:21

This seemed to work for me with the test I had posted before. [CODE]select t.ProductId, t.SaleDate, t.Price as CurrentPrice, (select price from #table1 where ProductId = t.ProductId and DateDiff(day, SaleDate, t.SaleDate) = 7) as PreviousPrice, (select price from #table1 where ProductId = t.ProductId and DateDiff(day, SaleDate, t.SaleDate) = 14) as Previous2Price
from #table1 t inner join (
select ProductId, Max(SaleDate) as MaxDate
from #table1
where SaleDate is not null
group by ProductId) jt on jt.ProductId = t.ProductId and jt.MaxDate = t.SaleDate
order by t.ProductId[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-03 : 05:47:46

Hi all,

Here are some simple alternatives which do what I understand you need. Just run the script below to see...

--data
set dateformat mdy
if object_id('tempdb.dbo.#Table1') is not null drop table #Table1
create table #Table1 (SaleDate datetime, ProductID  varchar(50), Price decimal(18,2))

insert into #table1 (SaleDate, ProductId, Price) 
select '01/01/06', 'abc', 1 UNION ALL
select '01/08/06', 'abc', 2 UNION ALL
select '01/15/06', 'abc', null UNION ALL
select '01/22/06', 'abc', 3 UNION ALL
select '01/01/06', 'def', null UNION ALL
select '01/08/06', 'def', 4 UNION ALL
select '01/15/06', 'def', 5 UNION ALL
select NULL, 'def', 6

--calculation
declare @SaleDate datetime
select @SaleDate = max(SaleDate) from #table1 --OR set @SaleDate = '01/22/06'

--1.
select
    ProductID,
    max(case when SaleDate = @SaleDate then price end) as Price,
    max(case when SaleDate = dateadd(d, -7, @SaleDate) then price end) as Price1WeekPrevious,
    max(case when SaleDate = dateadd(d, -14, @SaleDate) then price end) as Price2WeeksPrevious
from #table1 group by ProductID

--2.
select * from #table1 a where price is not null 
and (select count(*) from #table1 where ProductID = a.ProductID and SaleDate >= a.SaleDate and price is not null) = 1

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

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

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-03 : 08:44:33

I don't see how that solution will work as each productid would have a MAX sale date, and your algorithm only calculates a single MAX.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-04 : 04:36:52

Hi druer,

You might be right. The script does what I understand might be needed, but I may have misunderstood the requirement. It's hard to say for sure without the ddl, sample data and example results for that data (what we always ask for - and rarely get!) - or without feedback from zeeshan13.

zeeshan13?

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources