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
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?
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 RelatedI 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 ?
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
TABLE DEFINITION
Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)
CUSTOMER TABLE
CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
OUTPUT
CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
Records should be filtered by
1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
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!
I have two table in two different database. I have to found count for both table and put into other table.
example : Database --> A , table is T1
Database --> B , table is T2
I want count of both table and put into T3 table in database A or B .
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
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.
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
[Code] ....
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedLooking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS
[Code] .....
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
View 11 Replies View RelatedI have write two query but its only work one at a time need your expertise what i am doing wrong.
[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID
writing a cross join query with one table:
Cities(City_name, X_coordinate, Y_coordinate)
the result should be all combinations without reverse column returns
SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???
for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
I have one table with text column(varchar50) and 2 value columns(INT)
it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4
I have tried to use CASE with no luck.
edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4
I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.
I have this
DECLARE @BackupLoc nvarchar (100)
DECLARE @DayOfWeek nvarchar (100)
set @BackupLoc = N'D:Backup'
set @DayOfWeek = (SELECT DATENAME(dw,GETDATE()))
set @BackupLoc = @backuploc + @DayOfWeek
[Code] ....
But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
View 2 Replies View RelatedI have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
I would like to know how to split the phone number into two columns based on - symbol Dynamically.
for example
Phone Number
123-12323
1234-1222
so output should be
code number
123 12323
1234 1222
We are using SQL2005.
I have a stored proc that runs a Select query based on a complex view.
The sproc has two input date parameters (StartDate and EndDate).
We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates.
We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration).
We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables.
Unfortunatly SQL Server Management does not make any index recommendations.
I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job.
How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes?
Any help appreciated.
Reagrds,
Paul.
Hello,
I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
Any help would be much appreciated.
Thanks
Karl
Hi,
These are my tables
auth:
id, level, base
info:
aid, name
store:
aid, itemID
aid is the ID that comes from the auth table.
The final output that I plan to get is
Name Number of Items
where name comes from the info table, and number of items comes from the store table.
Query something like:
show the total number of items for all those users (in the fashion Name, Number of items)from the store table, for all those whose level=something and base=something in the auth table.
For example,
if there are 10 users whom match level=something and base=something in the auth table,
What I want displayed is:
------------------------
Name Number of Items
------------------------
user1 10
user2 15
user3 20
....
user10 30
-----------------------
which if broken up is:
select name from info where aid=X
select count(*) from store where aid=X ..
for all those whose base=something and level=something from auth.
Thanks,
Shashi
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
Two tables:
table #1 = timestamp, servername1(never changes), value(int)
Table #2 = timestamp, servername2(never changes), value(int)
What I need to do is merge the two tables into a single table based on the matching timestamp:
timestamp, servername1, servername2 as headers then values under
"1/1/1-1:10:1" , "10" , "10" - as an example
"1/1/1-1:20:1" , "20" , "21" - as an example
"1/1/1-1:30:1" , "1" , "5" - as an example
Powershell or MSSQL 2008 queries work.
I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.
The table is for some performance stat collection app.
Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object.
Table #2 is the same collection but for a different server.
The layout in the database is terrible but this is what I get to work with.