Multiple Database Query
May 17, 1999
Hello,
Can anyone tell me if SQL Server 6.5 has anything similiar to linked tables in MS Access? I need to query two tables simultaneously that reside in separate SQL databases. Is this possible, or must the tables reside in the same database?
For example, I have two databases db1 and db2. Table1 is in db1 and Table2 is in db2. I want to query something like this:
"SELECT * FROM table1 WHERE fieldname1 in (SELECT fieldname2 FROM db2.Table2 WHERE...)
Thanks,
Andrew
View 1 Replies
ADVERTISEMENT
Dec 4, 2000
Good afternoon one and all,
I want to write a query that will affect data across two databases (on the same server). How would I do this?
TIA for all help
Gurmi
View 1 Replies
View Related
Jul 20, 2005
I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 3, 2006
Sql2005??? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?
View 1 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Mar 17, 2006
Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.
I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:
CustID CustName JobOpen JobRush
----------------------------------------------------------------
22 John Deere 47 3
24 MCP 32 7
37 BlueON 6 0
In my noobness, I developed this:
SELECT CustID, MAX(CustName) AS CustName,
COUNT (*) AS JobOpen
FROM Customers
INNER JOIN Jobs ON JobCustID = CustID
WHERE (JobDone = 0)
GROUP BY CustID
As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.
But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need
WHERE (JobRush = 1)
So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?
I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.
Thanks for reading all this. Any input is greatly appreciated.
Matt
View 3 Replies
View Related
Oct 6, 2015
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Jun 9, 2008
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
View 1 Replies
View Related
Feb 12, 2008
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
View 2 Replies
View Related
Aug 22, 2005
Hi,New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM dbo.RentalControl_Main INNER JOIN dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND (dbo.RentalControl_Main.AgencyID = '2')
View 1 Replies
View Related
Sep 27, 2007
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
View 14 Replies
View Related
Apr 12, 2007
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
View 7 Replies
View Related
Nov 29, 2005
I want a query ro select the number of wins and the number of games played for a team. The pertinent columns are all in the same table and I was trying to get the information all in one query. Is something like this possible?SELECT COUNT(*) AS WinsFROM tbl_ScheduleWHERE Winner = 'IND'SELECT COUNT(*) AS GamesPlayedFROM tbl_ScheduleWHERE HomeID = 'IND' OR VisitorID = 'IND'How would I merge the two, or can I even do that?
View 2 Replies
View Related
Feb 7, 2006
Hi, how can I search using more than one field? This is a hard question to ask, I will see if I can explain it.
On a web form I have 7 various textboxes and dropdownlists. It is for a search. The user does not have to fill in all of them (he or she may want to use just one search field).
If I have 6 of the 7 fields blank, how can i get the result of just the field being used?
I have this example:sSQL.Append("and (table.fieldname= isnull(@parameter, table.fieldname) or table.fieldname IS null) ")but it doesn't seem to be doing the job.
Am I doing this right or is there another way?
Thank-you,Eric
View 5 Replies
View Related
Dec 29, 2006
hi guys,
is it possible to have multiple select subquery in select statement? eg :-
select d.name, d.gen, select (a,b from tblblabla where blabla), d.age
so it would produce 1 line of record :-
name gen a b age
qq 2 2 5 18
thanks in advance
is this possible?
View 4 Replies
View Related
Dec 11, 2007
Hi, I'm a bit stumped as to how to do this.I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.
E.g. Say I have two results in my string[], fred and bob.How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";
IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:
for(int a = 0; a < userArray.Length; a++){ conn.Open(); SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close()}
That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.
Any help is greatly appreciated.
View 4 Replies
View Related
May 20, 2008
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
View 2 Replies
View Related
Jun 3, 2008
hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)
View 2 Replies
View Related
Feb 11, 2005
Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.
I have 3 tables Clients <-oo ClientContacts oo-> Contacts
(the <-oo means one to may relation between the tables)
A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.
My goal is to run a query which will return
Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
WHERE (Clients.WorkOnHold = 0)
The result should return values for
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category,
and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.
I tryed an INNER JOIN but it will return juts the records having contact information.
Any solutions are appreciated.
Thanks.
Clients
CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100),
[MailingStreet] [varchar] (50),
[MailingCity] [varchar] (35),
[MailingState] [varchar] (35) ,
[MailingZip] [varchar] (10),
[WorkOnHold] [bit] NULL ,
[ClientNotes] [varchar] (500),
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Contacts
CREATE TABLE [Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[JobTitle] [varchar] (50),
[BusinessStreet] [varchar] (50),
[BusinessCity] [varchar] (35),
[BusinessState] [varchar] (35),
[BusinessPhone] [varchar] (20),
[BusinessFax] [varchar] (20),
[E-mailAddress] [varchar] (255),
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ClientContacts
CREATE TABLE [ClientContacts] (
[ClientID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[Category] [varchar] (50),
CONSTRAINT [FK_ClientContacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_ClientContacts_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ContactID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO
The INNER JOIN I tryed but is not good. It returns just clients having contacts attached.
SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName,
Contacts.[E-mailAddress]
FROM ClientContacts INNER JOIN
Clients ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)
View 3 Replies
View Related
Sep 7, 1999
Does anyone know of a tool or method that would allow me to join MS SQL and Oracle 7.3 tables for a query?
View 2 Replies
View Related
Dec 8, 1999
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1
And I need a table that will combine the isbns and give me sums
and counts like this:
Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2
Does anyone know of a query that can accomplish this? Thanks in advance.
View 1 Replies
View Related
Mar 16, 2006
Hi
I am new to Analysis services. and I have figure out the following things:
I have a cube with multiple dimensions. I want to display all the options in cube along with the count of the rows for that combination. I am unable to understand how to count the dimensions and apply to column.
For example, I have say dim1, dim2, dim3 dim4, name as dimensions and cnt as count of the rows. Now I want to write an mdx expression that returns me dim1, dim2, dim3, dim4 with count(name fields) for above combination so the output will be as follows:
name field1 name field2
dim1 dim2 dim3 dim4 200 100
how do I do it?
Also how do we accept input parameters for mdx expression. for example in the above thing, if I have to accept an input for dim1 and display the output values for dim1 how do I do it?
Any help is appreciated. Thank you.
View 1 Replies
View Related
May 25, 2007
I've run into an interesting challenge, and haven't figured out how to solve it yet. I'm fairly novice at MS SQL, and I would assume that there is a relatively simple/elegant solution. Here's what's going on:
I'm tracking projects that have a one to many relationship with sub-project type. There can be many entries for any given project and sub-project type. Each entry has a status.
EX: "Datalist"
ID Proj Sub-Proj Status
1 Alpha Review 1
2 Alpha Test 3
3 Alpha Review 2
4 Alpha Review 2
5 Alpha Test 4
In addition to the Datalist above, I've got a table which contains all valid combinations of Projects and Sub-Projects.
EX: "ValidCombos"
ID Proj Sub-Proj
1 Alpha Review
2 Alpha Test
3 Beta Review
4 Beta Rewrite
5 Beta Test
I need to make a query/table which contains the total number of IDs that have a given Project, Sub-Project, and Status.
EX using data from the tables above: "DesiredTable"
Proj Sub-Proj Status1 Status 2 Status3 Status4
Alpha Review 1 2 0 0
Alpha Test 0 0 1 1
Beta Review 0 0 0 0
Beta Rewrite 0 0 0 0
Beta Test 0 0 0 0
How do I do this? BIG thanks in advance!
View 3 Replies
View Related
Jun 12, 2015
My end game is to automate some of my monthly queries in a Job in SQL Server Agent. Right now I have two metric tables. One table is the name and comment with the PK. The secondary table is attributes/detail, such as reporting month, target and actuals.
I am currently running all different types of queries to get the aggregates. I'd like to get these into a job so it would run automatically and update the reporting table.
Would you recommend making one step or multiple steps for each query? I am trying to use an intelligent approach to begin to load the tables.
View 2 Replies
View Related
Apr 17, 2008
hi friends,
I m using pubs database. without using contains keyword i m getting result. if i use contains keyword i m not getting result.
select j.job_desc,e.fname,e.lname from jobs j,employee e where j.job_id=e.job_id and fname='Victoria'
This query displays as
desc fname lname
Managing EditorVictoriaAshworth
Below query displays as empty value
desc fname lname
select j.job_desc,e.fname,e.lname from jobs j,employee e where CONTAINS (e.fname, ' "Victoria" ')
View 4 Replies
View Related
Apr 21, 2015
I am trying to write a query which includes the following two tables.
Incidents
Column 1 = SourceContactNo
Contacts
Column 1 = ContactNo
Column 2 = Ref
The first columns in Incidents and Contacts both contain the same type of information (contact numbers, only the column names are different, some contact numbers appear in table 2 (contacts) which do not appear in incidents).What I really want to do is return the Contact Number & Ref from the table named contacts where the contact number is the same as SourceContactNo (in the first table incidents).Here is what I have so far:
SELECT Incidents.SourceContactNo, Contacts.ContactNo, Contacts.REF
FROM Contacts, Incidents
View 2 Replies
View Related
Apr 12, 2007
Hi, I have a query that gets the data for a specific week (starting Monday).
The below statement returns the data for the whole week mon-fri
What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.
How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1
SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1
FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed
Thanks in advance,
Tugsy
View 1 Replies
View Related
Apr 3, 2007
Assume that I got 10 line of insert query to be carry out, how can I run all these 10 insert query at on time instead of using sqlcommand.executenonquery for 10 time. Thanks.
View 8 Replies
View Related
Feb 5, 2007
I need to run a same query on 15 different databases and union the results.
Does any of the experts know how to do this with good performance?
Any help is appreciated.
Phil....the only reason i posted in T-Sql is because i will be using this query in reporting services :)
Thanks
View 9 Replies
View Related
