SQL 2012 :: Query Based On Column Name / Setting Up Database Table
Oct 30, 2015
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.
I need 3 partitions
First partition has to hold present data second partition need to hold the previous year data (SAS storage) Third partition need to hold all the old data and need to be in the archive database
I understand that first we need to disable the constraints (Indexes PK & FK) Then create partition function and partition schema Then Create the Constraints again
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter 001 NY 2005Q1 003 WI 2006Q2 etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id) FROM data_table WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True. If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null App1 ||| Linux |||UDB App1 ||| AIX |||UDB App1 ||| Linux |||Sql
where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.
I have a view saved on server - mhsvi-datawarehousedatawarehouse.This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008ainstance1.When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)
The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.I have access to the database where the view is saved and the database that the TSQL calls.
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...
I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?
Example--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3
I have tables in my database, tblNames1, tblNames2, tblNames3, and a main addresses table (currently empty). Once I've imported the address data I need to match the addressIDs in the names tables to the Primary ID in the address table based on the values of a field CompanyName (which is common to all the tables) My issue is that I have a huge CSV file with the master address information but obviously SQL server needs to assign foreign keys so the names tables can linked to corresponding rows in address table. It's a a many to 1 relationship as their will be one address with multiple name entries. All the names are normalized so everything can be matched up...
CREATE TABLE EDI_data_proc_log( ID int IDENTITY(1,1), comment VARCHAR(3000), time_recorded DATETIME DEFAULT GETDATE(), run_by varchar(100), duration int );
When a record is inserted I like the duration column to be computed.This should happen only after the first record to the table has gotten inserted.You might say a trigger would be the best.. Ok then, show me the syntax.
Or I am thinking can we write a user defined function that will compute the value for the duration column.
--By default, I would like to update the duration column as follows:
--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )
INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500) INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200) INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125) INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300) INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)
I want to have an other employee table named employee_modified
Empno empname salary commission derived_column1(salary+commission) derived_column2(derived_column1 + xxxx) and so on derive other columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
with actividades_secundarias as ( select a.*, r.Antigo, r.Novo, rn = row_number() over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo) from ACT_SECUNDARIAS a inner join ((select
[Code] ....
I want to make a delete statement like this:
select * into #table1 from actvidades_secundarias where rn>1 Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae
I have about 50 databases that are only accessed once a month and on a predictable schedule. Would it free up resources on the server if they were kept offline and brought online only when needed ?
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt] FROM [dbo].[SF_Affiliate_Customer] C WHERE ( [afflt_intrnl_cust_ind] = 'N' AND [afflt_empl_ind] = 'N' AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA') )AND
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price from tblCustomerPurchases as a join tblProduct as b on a.ID=b.ID
I have a SSRS report with 6 columns each column containing count of total# of applicants meeting certain criteria. User want to click on each column and see the basic information and also want to get the ability to export the data into excel.
I know that I can create 6 drillthrough reports with basic information of applicants and link it to the count from each column respectively but I was wondering if it is possible to write a Stored procedure with all 6 select queries and execute only 1 select query based on the column that user clicks on main report ?
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp -------- --------- 11 * 12 *
ProdGrp ProdGrpDesc --------- --------------- A Prod Group A B Prod Group B C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp -------- --------- 11 A 11 B 11 C 12 A 12 B 12 C
I'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
tbl_results IDresult (primary) IDquestion (foreign, to tbl_question) result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example: tbl_questions IDquestion_question 1_________Which OS do you use? 2_________Which databases do you use? tbl_answers IDanswer_IDquestion_answer 1________1_________Windows 2________1_________Linux 3________1_________Mac OS 4________2_________SQL Server 5________2_________MS Access 6________2_________MySQL 7________2_________Oracle 8________2_________Other 9________2_________No, I do not use databases tbl_results IDresult_IDquestion_results ........ 23_______1________1,2 24_______2________4,6,7 ........ Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be: IDquestion_IDanswer_answer 1_________1________Windows 1_________1________Linux 2_________4________SQL Server 2_________6________MySQL 2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.
SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber", dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType", dbo.vw_tphillip_Customer_Inv.Date AS "Date", dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate", dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
I have a stored procedure which selects results based on some date calculations.
In my table I have a status column and two date fields (Approval Date and Signature Date) If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.
If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ï?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
I need to create a derived column for each row in a SQL dataset.
This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).
One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?
I have been using database mail for quite a few years now. I started using it with SQL Server 2005 and then it worked fine. Then we went over to SQL 2012 and the problems started. At first I could not get any e-mails out and read a post by Microsoft that a patch needs to come out but not available yet. That was in 2012. Presumably the patch came out with windows updates and my DB Mail started working. Now, however, I have the problem that some e-mails do get sent but the majority failed.
I use this so that whenever a user captures certain data to update a person's status on the database then an automatic e-mail will be sent to that person to inform them of their status at this company.
