Select Into Help - 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

SQL Server Development (2000)

Select Into Help

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2012-06-01 : 11:04:42

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

52326 Posts

Posted - 2012-06-01 : 12:19:00

how are two tables related? are there some common set of fields using which you can join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nhaas
Yak Posting Veteran

90 Posts

Posted - 2012-06-01 : 13:32:10

[bnumber] is the relationship.

nhaas
Yak Posting Veteran

90 Posts

Posted - 2012-06-01 : 13:46:18

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:53:34

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/

nhaas
Yak Posting Veteran

90 Posts

Posted - 2012-06-04 : 12:31:10

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 12:37:16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources