SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select Into Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nhaas
Yak Posting Veteran

USA
90 Posts
Posted - 06/01/2012 :  11:04:42  Show Profile  Visit nhaas's Homepage  Reply with Quote
I currently perform a Monthly SQL for billing of:

"SELECT * INTO VoipNumbersMonthly
FROM VOIPNumbers
WHERE (TYPE <> ' 'AND (STATUS <> 'HOLD')AND (STATUS <> 'NOT')) and phquantity<>'0'"

Works fine:
But I removed one of the Fields of "ORG" and put this into a new Table for better trackability:
[bnumber]
,[ORG]
,[activedate]
,[aduser]
,[setvenderid]
,[reference]
FROM [Tesisdev].[dbo].[BillingORG]

Question is, how do I run a query to update the ORG field on VoipNumbersMonthly from the BILLINGORG table with the newest "Activedate"

I can get the newest Activedate for the month via:

SELECT [bnumber]
,[ORG]
,[activedate]
,[aduser]
,[setvenderid]
,[reference]
FROM [Tesisdev].[dbo].[BillingORG]
WHERE
CAST(FLOOR(CAST(activedate AS FLOAT))AS DATETIME) >= '2012-05-01'

I know that I need to get all of the Numbers from VoipNumbersMonly, them update those numbers with the HighestDateTime for that number.

Anyhelp would be approicated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
47057 Posts
Posted - 06/01/2012 :  12:19:00  Show Profile  Reply with Quote
how are two tables related? are there some common set of fields using which you can join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

nhaas
Yak Posting Veteran

USA
90 Posts
Posted - 06/01/2012 :  13:32:10  Show Profile  Visit nhaas's Homepage  Reply with Quote
[bnumber] is the relationship.
Go to Top of Page

nhaas
Yak Posting Veteran

USA
90 Posts
Posted - 06/01/2012 :  13:46:18  Show Profile  Visit nhaas's Homepage  Reply with Quote
My major issue is I dont know how to do a join with a MAX date from the BillingORG table.

I know how to get the MAX date, just cant figure out how to plug it into a "SELECT INTO" statement.

SELECT bnumber, MAX([activedate]) AS Date
FROM [Tesisdev].[dbo].[BillingORG]
group by bnumber
Edited by - nhaas on 06/01/2012 14:21:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47057 Posts
Posted - 06/01/2012 :  23:53:34  Show Profile  Reply with Quote
sounds like below. if this is what we want please post some sample data from tables and explain what you want with required output


UPDATE v
SET v.ORG = b1.ORG 
FROM VoipNumbersMonthly v
INNER JOIN [Tesisdev].[dbo].[BillingORG] b1
ON b1.[bnumber] = v.[bnumber]
INNER JOIN 
(SELECT bnumber, MAX([activedate]) AS Date
FROM [Tesisdev].[dbo].[BillingORG]
group by bnumber)b2
ON b2.bnumber = b1.bnumber
AND b2.[Date] = b1.[activedate]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

nhaas
Yak Posting Veteran

USA
90 Posts
Posted - 06/04/2012 :  12:31:10  Show Profile  Visit nhaas's Homepage  Reply with Quote
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47057 Posts
Posted - 06/04/2012 :  12:37:16  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000