Moving data from one table to another using variable percentages and business days

Question

Post reply

Moving data from one table to another using variable percentages and business days

ecmpo

Grasshopper

Points: 21
More actions
October 20, 2013 at 1:24 pm

#395692

I need to move data from one table to another using variable percentages and current business day minus 1.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
The syntax was killing me, nothing seemed to work...It is very important that I get this done quickly so as a near last resort I'm asking here for assistance- any of which will be sincerely appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

Something like this?

DECLARE @varPercentage INT = 50;

INSERT INTO @varTable2 (myColumns)

SELECT TOP (@varPercentage) PERCENT myColumns

FROM @varTable1;

I assume @varTable1 and @varTable2 are table variables?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ecmpo Grasshopper Points: 21 More actions · Answer 2

Yes - varTable1 and varTable2 are table variables.

I want to be able to set the table names outside of the INSERT statement.

I also want to be able to use the previous business day as part of the WHERE clause.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

ecmpo (10/20/2013)
I want to be able to set the table names outside of the INSERT statement.

You'll need to explain this a bit more. Probably isn't possible.

ecmpo (10/20/2013)
I also want to be able to use the previous business day as part of the WHERE clause.

Right, I forgot about the WHERE clause. How do you define a "business day"? Got a time table somewhere where you can join to?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ecmpo Grasshopper Points: 21 More actions · Answer 4

I guess I want to do something like this:

Set varTable1 = "tblCompanyNames"

Set VarTable2 = "tblCompanyNames2"

Set varPercentage = "10"

Set varDate = In VB this would be If Date = Monday, Friday, If Date = Tuesday, Monday, and so on...

INSERT INTO @varTable2 (myColumns)

SELECT TOP (@varPercentage) PERCENT myColumns

FROM @varTable1

Where

Or do I need a reference table specifically showing BusinessDays (Mon-Friday) for the calendar year?

Table name: tblCalendar

ColumnA ColumnB ColumnC

01-01-13 Monday YesABusinessDay

so on and so forth.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Storing the table names will work only if you use dynamic SQL.

You create your INSERT SELECT statement in a string variable and you execute it with sp_executesql.

Regarding the business date: you could create a scalar function that searches the previous business day. Store the result in a variable and use that one in your WHERE clause.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP