Query Problem - 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

General SQL Server Forums

New to SQL Server Programming

Query Problem

Author

Topic

IndySandhu
Starting Member

3 Posts

Posted - 2008-06-19 : 16:55:04

Hello,

I have two tables called TableA and TableB:

TableA:
-------
TableA_id int (primary key),
site_name varchar(20)

TableB
-------
TableB_id int (primary key),
TableA_id int
status int,
sales money,
date (smalldatetime)

For every record in TableA I need to display as columns : site_name and sales. However I need to display the lastest record where status = 1 and the lastest 2 records where status = 2.

I'm not quite sure how to do this.

Indy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-19 : 16:59:36

Post sample data that illustrates your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

IndySandhu
Starting Member

3 Posts

Posted - 2008-06-19 : 17:30:15

TableA
------
TableA_Id Site_Name
1 site1
2 site2
3 site3

TableB
------

TableB_id Status Sales TableA_Id Date
1 1 1000 1 2008-01-01
2 1 80 1 2008-01-02
3 2 10 1 2008-02-02
4 2 0 1 2008-02-03
5 3 12 1 2008-02-04
6 3 14 1 2008-02-05
7 3 16 1 2008-03-01

status 1 = Estimate
status 2 = Forecast
Status 3 = Budget

I want to return sales figures for the last estimate, the last budget and the last 2 forecasts

eg, the results would be as follows:

Site_name sales(Est) sales(Budget) sales(Forecast1) sales(forcast2)
--------------------------------------------------------------------
site1 80 16 10 0

Indy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 01:50:33

[code]DECLARE @TableA table
(
TableA_Id int,Site_Name varchar(100))
insert into @TableA
select 1, 'site1' union all
select 2, 'site2' union all
select 3, 'site3'

DECLARE @TableB table
(TableB_id int,
[Status] int,
Sales int,
TableA_Id int,
Date datetime
)
INSERT INTO @TableB
SELECT 1, 1, 1000, 1, '2008-01-01' union all
SELECT 2, 1, 80, 1, '2008-01-02' union all
SELECT 3, 2, 10, 1, '2008-02-02' union all
SELECT 4, 2, 0, 1, '2008-02-03' union all
SELECT 5, 3, 12, 1, '2008-02-04' union all
SELECT 6, 3, 14, 1, '2008-02-05' union all
SELECT 7, 3, 16, 1, '2008-03-01'

SELECT a.Site_Name,
MAX(CASE WHEN b.status=1 and b.RowNo=1 THEN b.sales else NULL END) AS [Sales(est)],
MAX(CASE WHEN b.status=3 and b.RowNo=1 THEN b.sales ELSE NULL END) AS [Sales(budget)],
MAX(CASE WHEN b.status=2 ANd b.rowno=2 then b.sales else null end) as [Sales(Forecast1)],
MAX(CASE WHEN b.status=2 and b.rowno=1 then b.sales else null end) AS [Sales{Forecast2}]
FROM @TableA a
INNER JOIN
(SELECT ROW_NUMBER() OVER(PARTITION BY TableA_id,status ORDER BY date desc) as RowNo,*
FROM @TableB)b
ON b.TableA_id=a.TableA_id
GROUP BY a.Site_Name
Site_Name Sales(est) Sales(budget) Sales(Forecast1) Sales{Forecast2}
---------------------------------------------------------------------------------------------------- ----------- ------------- ---------------- ----------------
site1 80 16 10 0[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 02:17:56

Add a

WHERE b.RowNo <= 2 to speed up things.

E 12°55'05.25"
N 56°04'39.16"

IndySandhu
Starting Member

3 Posts

Posted - 2008-06-20 : 04:25:08

I'm using sql server 2000 and row_number() is not supported

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources