SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Query To Consolidate Data From Tables

Feb 12, 2014

I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.

So for example: A source table with two developers

-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------

I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------

I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.

My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.

This is how far I have come now:

IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),

[Code] ....

View 4 Replies View Related

SQL Server 2014 :: Query To Combine Two Tables Based Third Table

Feb 18, 2015

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:

Table A:

Table B:

Table C:

So what query do I need write to have table like below?

Table D

View 7 Replies View Related

How To Combine Two Tables (not Query)

Apr 6, 2005

I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.

I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.

First, the new flat file is missing two columns (not critical data).

Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).

I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.

Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!

View 3 Replies View Related

How To Combine 2 Tables Data

Oct 6, 2015

I have 2 tables. first table contains name and age and the second table contains class and roll num.  I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.

View 8 Replies View Related

Query To Combine Two Tables Based On Third Table

Feb 18, 2015

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:

Table A:

Table B:

Table C:

So what query do I need write to have table like below?

View 3 Replies View Related

Combine Tables From 2 SQL Servers With Different Schemas And Update As New Data Is Entered

Mar 28, 2008

I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.

View 10 Replies View Related

SQL Server 2012 :: Combine One Value To Equal Two Values

Feb 13, 2015

I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.

The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.

View 1 Replies View Related

SQL Server 2012 :: Combine Functionality Of IN And LIKE In WHERE Clause

May 8, 2015

I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'

What I would like to do is something like this, which doesn't work:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')

I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.

View 7 Replies View Related

SQL Server 2012 :: Increment Query Based On Two Tables?

Feb 17, 2014

I have two tables with this info:

TABLE 1

COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB

TABLE 2

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347

I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352

I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.

View 5 Replies View Related

SQL Server 2012 :: Merge Data From Two Tables

Feb 3, 2014

Merging table :

--------Dummy TABLE

create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200

[Code] .....

I want Result like this :

date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600

View 2 Replies View Related

SQL Server 2012 :: Copying Data From One Set Of Tables To Another

Mar 26, 2014

1. Take a subset of data from about 100 tables that have multiple references to other tables in this group of 100 from a first DB.
2. Insert the above data into a second DB, a database that already has data in the 100 tables, while maintaining the correct references.

As a general approach, the best way I can think of doing this is as follows:

1. Create mapping tables for every ID that is referenced in a different table (OldID NewID)
2. Insert the old data into the new table and output the OldID and NewID into the mapping table.
3. Use that mapping data to make sure all tables that use those IDs have the new IDs in DB2.

This approach is extremely labor intensive both on initial implementation and would require a fairly substantial amount of work to maintain going forward.

View 7 Replies View Related

SQL Server 2012 :: Query To Return Top 10 Tables On Each Database On Server?

Nov 21, 2013

i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.

View 3 Replies View Related

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

Mar 11, 2015

I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -

SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml

[code]...

View 2 Replies View Related

SQL Server 2012 :: How To Perform Update Query That Involves Multiple Tables

Aug 31, 2015

I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:

SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an

[Code] ....

I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?

The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.

View 9 Replies View Related

SQL Server 2012 :: Open EDI File And Insert Data Into Tables

Sep 27, 2012

Is there a way to open EDI file in SQL Server and insert the data into tables?

View 9 Replies View Related

SQL Server 2012 :: Select Large Data From Multiple Tables

May 10, 2014

In a Library Management database we have these tables

1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc

In document table I have 500,000 records.

I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?

View 3 Replies View Related

SQL Server 2012 :: Purging Data From Master And 5 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.

Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample

dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

View 9 Replies View Related

SQL Server 2012 :: XML Import Into Multiple Tables With Data Translation

Jul 17, 2014

I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....

I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.

The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.

The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.

It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.

View 5 Replies View Related

SQL Server 2012 :: How To Loop Unmatched Rows Data From Two Tables

Jan 12, 2015

We are having folder table and team table as like below structure.

Folderlist (F)Table: (
==============

id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203

Teamlist table (T)
============

team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208

If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)

If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id

Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache

like phone/tab/b (this is for F.parent_id=202)

View 1 Replies View Related

SQL Server 2012 :: Unable To Link Data After Import From XML Into Tables

Aug 13, 2015

I'm pulling data from XML into tables, but I'm unsure how to link the data after it's imported. This example has names and tasks, and I can pull the data into two tables, but I can't find any way to link the task to the appropriate person. My person and task tables populate without issue, but there's nothing I can find to link the rows together. So in this example Test 1 would go to the first two Tasks and Test 2 would go to the second two work items.

DECLARE @XML TABLE (XMLData XML);
DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));
DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));
INSERT INTO @XML SELECT '
<process>
<header>
<Person><Name>Test1</Name><Address>123 main street</Address></Person>

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server 2012 :: How To Merge Two Tables Data On Behalf Of ID In Comma Separated Manner

Jul 2, 2015

i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.

This is the image path :

[URL]

View 3 Replies View Related

SQL Server 2012 :: Extracting Data From XML Query

Sep 28, 2015

I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.

create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Query To Get The Month Wise Data?

Sep 13, 2015

I have the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1

In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1

View 6 Replies View Related

SQL Server 2012 :: Query Get Data In Double Codes In String

Feb 9, 2014

I have data like this

"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}

I want to get only data in double codes in using sql query.

View 9 Replies View Related

SQL Server 2012 :: Making Query For Presenting Data In Different Format?

Jun 18, 2015

I have below table:

IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints

--===== Create the test table with

create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))

--===== All Inserts into the IDENTITY column

INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)

and i want to have a table in below format: (query for it)

Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5

I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.

View 9 Replies View Related

SQL Server 2012 :: Query On Grouping Data On Weekly Basis

Oct 6, 2015

I have query on grouping data on weekly basis..

1. Week should start from Monday to Sunday

2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).

3. I want output like below

Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12

View 1 Replies View Related

SQL Server 2014 :: Combine Data From Historical Table?

Aug 13, 2014

Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.

View 9 Replies View Related

SQL Server 2012 :: Query All Columns In A Database That Have The Data Type As Integer?

Feb 20, 2014

Is there a way to query all the columns in a database that have the data type as Integer.

View 9 Replies View Related

SQL Server 2012 :: Query Pulling Latest Info Data From Table

Aug 29, 2014

Let's say I have a table of data as per the below..

I'm trying to extract only the green highlighted items..

The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.

As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).

I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.

View 7 Replies View Related

SQL 2012 :: Query With Three Tables?

Jul 22, 2015

I currently have a query that uses three tables [Table1, Table2, Table3]. Each table only has two columns: Group and something else. I want a query that gives me the group and those three something else.

Table1: Group and Info1
Table2: Group and Info2
Table3: Group and Info3
Query: Group, Info1, Info2, Info3

The groups never repeat in a single table.

However, the groups are not identical in every table. I want the groups to never repeat twice: All the groups should appear if they are there at least once in one of the table, and provide Info1, Info2, Info3 (blank if it's not there in its table, filled if it is).

That should be a fairly simple query, I just can't seem to make it work so groups don't repeat.

Little visual example.

Table1: Group - Info1
1 - a
2 - b
4 - c
5 - d

Table2: Group - Info2
1- aa
3- bb
4- cc

Table3: Group - Info3

5- aaa

The query would give me this:

1 - a - aa - ""
2 - b - "" - ""
3 - "" - bb - ""
4 - c - cc - ""
5 - d - "" - aaa

View 3 Replies View Related

Combine 2 Tables

Sep 20, 2007

I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved