Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values. 2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))) Here is the formula for the percent: =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
I have two tables (teachers, students) each table has 3 fields (FirstName, LastName, EMail). I'm trying to combine the data from both tables into one so I have a "master list" to send e-mails to all teachers and students via an ASP script/CDO.
I've been reading up on Joins but I can't figure out the SQL to combine data from both tables. Any ideas?
Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.
I have the following 2 tables:
I'm trying to get the below output:
Here's the SQL I've been using: SELECT a.PERSON_ID, sum(a.AMOUNT), CASE WHEN b.JOB_CODE = 10 then "Level 2 Seller" WHEN b.JOB_CODE = 15 then "Level 3 Seller" ELSE 'Level 1 Seller' END as "Job_Code" FROM tbl_SALES a, tbl_JOBFUNCTION b WHERE a.PERSON_ID = b.PERSON_ID GROUP BY a.PERSON_ID, b.JOB_CODE
Here's what my results are with the query I've been using:
Each person could have many job codes, but when they have 10 then they're consider "level 2". - If they have 15 then they're "level 3". - If they don't have either 10 or 15 then they're "level 1". - If they have both 10 and 15 they're still "level 3".
I have database with two different tables BaseData and QualityDetails as given below with ID as the Primary key BaseData.
Sent_to_Client Name ID 1/2/2015 ABC1 1000000 2/24/2015 ABC2 1000001 5/24/2015 ABC3 1000002 3/24/2015 ABC4 1000003
QualityDetails
ID Error 1000000 1 1000001 2 1000002 0 1000003 3
I want to write an SQL Query to get the data by combining both the tables by dates from 2/1/2015 to 5/30/2015 in BaseData table. My table after executing the query should be
ID Name Error 1000001 ABC2 2 1000002 ABC3 0 1000003 ABC4 3
I have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama). My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:movie_id movie_title comments actor_first actor_last genre_name1 Casino blah blah Robert DeNiro Action1 Casino blah blah Robert DeNiro Drama1 Casino blah blah Joe Pesci Action1 Casino blah blah Joe Pesci Drama And here's the query that produced that:1 SELECT movies.movie_title, movies.comments, actors.actor_first, 2 actors.actor_last, genres.genre_name 3 FROM movies INNER JOIN movies_actors ON movies.movie_id = movies_actors.movie_id 4 INNER JOIN actors ON movies_actors.actor_id = actors.actor_id 5 INNER JOIN movies_genres ON movies_genres.movie_id = movies.movie_id 6 INNER JOIN genres ON movies_genres.genre_id = genres.genre_id So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?
i have such a error in my sql server db i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters . i am using nchar and nvarchar and ntext
Declare @SQuery nvarchar(3000) set @TblName1 = '[' + @TblName1 + ']' set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description, sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1 where a.stat_month <=' + @Month + ' and a.district=' + @District +' Group by a.commodity1, b.descrip_1 order by [Amount] desc'
Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)... SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantID, QuestionID, Answer FROM [3_Temp]
WHERE SurveyID=1) AS preData PIVOT
( MAX(Answer) FOR QuestionID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )
1. Fact table with sales by customer, Territory ID etc 2. Table with targets per Territory ID
I want two combine them in one pivot table.
Pivot: Territory;Sales;Target
What I'm getting is the correct column with sales, but the overall sum of all territories in column for targets.Relationship is established between two tables via Territory ID
how to combine measures and fields coming from an analytical model (tabular) along with some Excel calculations. Basically I want to provide users with a simple report (to be displayed in SharePoint Excel services) containing charts and slicers. The data comes from a tabular model, and most of the calculations are in the model as well.However there is some little tweaking that must be done. For example I might need additional calculated columns, but I don't feel the need to modify the tabular model for that. I was wondering if I could do this within Excel as well -- but without having to bring all the data through a pivot table, then manipulate it and then show it on the report. So to be clear I do not want any pivot tables lying around, even if on a hidden sheet.
I noticed that when selecting a pivot chart in Excel, at the ribbon menu under "PIVOTCHART TOOLS"/"ANALYSE" there is a group of buttons named "Calculations". One of them is named OLAP Tools.Is it fair to assume that these options will allow me to create new measures at the Excel side, without affecting my tabular model?
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer AS DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest ( Zip, UpsRegion, UspsRegion ) SELECT CMRC_ZipRegionUps.Zip, CMRC_ZipRegionUps.UpsRegion, CMRC_ZipRegionUsps.UspsRegion FROM CMRC_ZipRegion, CMRC_ZipRegionUsps GO
Is there any way to do this? Or do I have to manually enter all the entries?
Hello, I have two tables that I would like to combine but with some rules. Table 1 has two columns with Actual Time and Actual People Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex. Planned Time has 20:00,23:00 Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15 Actual People has 5,2,5,3
Output should be like Planned_Time,Actual_Time,Planned_People,Actual_People ,19:00,,5 20:00,19:30,2,2 ,19:45,,5 ,21:15,,3 23:00,,5,
I can do this in asp but if this can be done in sql it would be better. Any ideas appreciated. Thanks.
this is my query: tabel1: select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1' if i run this query i will get output like this: userid user_name password role_code expiry_date created_date 1 karthik karthik AD 01/17/20082007-01-24 active 0:00:00.000 1
i have another table which has the following records
tabel 2: select * from code master
codename codedescription AD admin sp supervisor
so in the first query i need the output as like tithe following:
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b 15 _ a ____________ ny _____ arena 25 _ d ____________ fg_____ metus 35 _ f ____________ 45 _ f ____________ these two tables above will become table-c like below;
table-c 15__arena 25__metus 35__null 45__null but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
Hi all, I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS. When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works.. SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS, MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P WHERE FA.QUESTIONID=QS.ID AND FA.INCIDENTID=I.INCIDENTID AND I.INCIDENTID=D.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND QS.QUESTIONNAIREID=2 AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P, MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS, MAT_ANSWERS A, MAT_INCIDENTANSWERS IA WHERE I.INCIDENTID=D.INCIDENTID AND I.INCIDENTID=QI.INCIDENTID AND D.INCIDENTID=IA.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND Q.ID=QI.QUESTIONNAIREID AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID AND QS.ID=A.QUESTIONID AND A.ID=IA.ANSWERID AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND Q.ID=2 AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
When the value in the second column is null, I need to check the value in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project from issuerequest as C left outer join ProductCustomer on (C.customer = D.customerID) and (C.product=D.produktID) where actuellproduct='1' group by C.issueID
this gives a result as: 100 | P002 100 | P003 101 | P004 102 | P002 103 | null ...
What I actually want is just 2 columns with in the first IssueID and in the second Project and no duplicate data. Everything (except null) is more important then the same value in column 3.
100 | P002 101 | P003 102 | P002 103 | null
I've tried with except, but can't get all duplicate data out.
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50 database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
I am trying to do a really weird combination of an Append/Union query..I'm doing most of my commands through macros and queries.how to do a normal Append/Union/Create queries but I am trying to do something kind of weird. If table X has rows A, B, C, and D, and table Y has a column with rows 1, 2, 3, and 4... I need the values in table Y to be assigned to each individual row in table X. Is there a way to create a new table where the columns merge such that values can be assigned like:
I need to combine data from two tables located in the same database.
The column GID in Table 1 and RealEstateID in Table 2 has a common id
I want to specify a date interval (AcquistitionDate in Table 2) eg 20131001 to 20131030 and get Name from table 1, and combine these to a result table.
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT a.DATE, a.STATE, b.CITY FROM TABLE_A a LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem: When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
I have two tables - T_Assets and T_Inspections. I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL: SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection) FROM T_Assets INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this. Jagdip
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are : setup_zipcode, setup_category, and listing
and inside setup_zipcode it has the columns: zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns: category_id, category_parent, category_path and category_name
and the final table for listing has the columns: listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT * FROM setup_zipcode, setup_category, listing WHERE listing_category LIKE '%-$cat-%' AND listing_zip = zip_code AND category_id = '$cat' ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
