Selecting Max date from 2 different views

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

Selecting Max date from 2 different views

Author

Topic

jonathan.crockett
Starting Member

4 Posts

Posted - 2009-01-16 : 06:27:54

I have two seperate views (vw_dm_last_bill_date and vm_inv_last_bill_date) which show the max bill date from two other queries, both contain only two fields; mmatter and last_bill_date.

I want to create a third view which gives me the max last_bill_date for each mmatter.

Any help would be much appreciated.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 06:32:01

[code]
Create View as NewView as
select mmatter,max(vw_dm_last_bill_date) as maxbilldate
from
(
select mmatter,vw_dm_last_bill_date from vw_dm_last_bill_date
union all
select mmatter,vw_dm_last_bill_date from vm_inv_last_bill_date
)t
group by mmatter

[/code]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-16 : 06:32:52

Umh...my personal recommendation would be to stay away from nested views but I guess that wasn't the question... :

SELECT MAX(a.last_bill_date)
FROM (
  SELECT last_bill_date FROM vw_dm_last_bill_date 
  UNION ALL 
  SELECT last_bill_date FROM vm_inv_last_bill_date
  ) as a

EDIT: just changed column name...

- Lumbago

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 06:40:56

Union all The Data With Both Of Two Views and then find the max from this....

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 06:46:18

quote:
Originally posted by Lumbago

Umh...my personal recommendation would be to stay away from nested views but I guess that wasn't the question... :
SELECT MAX(a.last_bill_date)
FROM (
SELECT last_bill_date FROM vw_dm_last_bill_date
UNION ALL
SELECT last_bill_date FROM vm_inv_last_bill_date
) as a
EDIT: just changed column name...

- Lumbago

That'll return you one row (max date of all nmatters) and not max for every nmatter.

jonathan.crockett
Starting Member

4 Posts

Posted - 2009-01-16 : 06:47:10

Well I'd be more than happy to do it from the actual tables, but that was getting my head in an even bigger mess. So if you could help with that. The two original views are

*** 1st View ***

ALTER VIEW [dbo].[vw_dm_last_bill_date]
AS
SELECT son_db.dbo.matter.mmatter, MAX(son_db.dbo.ledger.ltradat) AS last_bill_date
FROM son_db.dbo.matter LEFT OUTER JOIN
son_db.dbo.ledger ON son_db.dbo.ledger.lmatter = son_db.dbo.matter.mmatter AND son_db.dbo.ledger.llcode IN ('FEES')
GROUP BY son_db.dbo.matter.mmatter

*** 2nd View ***

ALTER VIEW [dbo].[vw_inv_last_bill_date]
AS
SELECT [Matter Number] as mmatter, MAX([Invoice Date]) AS last_bill_Date
FROM dbo.INV_INVOICES
GROUP BY [Matter Number]

I essentially need a view that will tell me the last Bill date for each mmatter.

If someone has a view that would tidy this up that would be awesome

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 06:54:17

[code]
Create YourNewView as
select mmatter,max(last_bill_date) as max_last_bill_date
from
(
SELECT son_db.dbo.matter.mmatter, (son_db.dbo.ledger.ltradat) AS last_bill_date
FROM son_db.dbo.matter LEFT OUTER JOIN
son_db.dbo.ledger ON son_db.dbo.ledger.lmatter = son_db.dbo.matter.mmatter AND son_db.dbo.ledger.llcode IN ('FEES')
union all
SELECT [Matter Number] as mmatter, ([Invoice Date]) AS last_bill_Date
FROM dbo.INV_INVOICES
)t
group by mmatter[/code]

jonathan.crockett
Starting Member

4 Posts

Posted - 2009-01-16 : 07:30:55

Thanks so much for that, works a treat.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 07:34:22

May Be Like This,......

with ashishashish as
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)
where RN=1

Thanks,,

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 08:03:49

A Little modification Is required In the last post,,,,,

select * from
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)A
where A.RN=1

Try This ....
Thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:02:58

quote:
Originally posted by ashishashish

May Be Like This,......

with ashishashish as
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)
where RN=1

Thanks,,

ashishashish please test your queries before posting. above query doesnt have select part at all (you're just creating a cte alone)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 09:48:50

quote:
Originally posted by visakh16

quote:
Originally posted by ashishashish

May Be Like This,......

with ashishashish as
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)
where RN=1

Thanks,,

ashishashish please test your queries before posting. above query doesnt have select part at all (you're just creating a cte alone)

yea so i do post the modified query ,,,
well thanks for our suggestion and let me know if further correction is required,,,
Thanks,,,

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 10:05:09

quote:
Originally posted by ashishashish

May Be Like This,......

with ashishashish as
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)
where RN=1

Thanks,,

So Sorry for replying wrong query this query should be like that,,,,,

with ashishashish as
(select mmatter,max(last_bill_date),row_number() over (partition by mmatter order by mmatter)as RN from
(
select * from vw_dm_last_bill_date
UNION ALL
select * from vm_inv_last_bill_date
)t)
select * from ashishashish where RN=1

May Be This Is the Right One,,,,,
Thanks,,,,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources