user code amount
user1 A 10
user1 B 100
user1 C 10
user2 B 50
user2 D 10
user3 A 10
user3 C 20
what i want in my report is the following as result
Sum of code A and C Sum of code B and D
user1 20 100
user2 60
user3 30
so can you make variables on the scope user that do a selection on the 'code'
in words like a variable that give me the sum of the amount where code in A and C in the scope user
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
Being new to this forum and to SQL, I would to apologise upfront for my clumsy code and explanations as I try and get to grips with this subject.
I have created this query which does work:
SELECT T1.U_Serial, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'NA' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'NA' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'A' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'NA' else 'DN' end As Available, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'Late' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'Busy' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'Done' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'Waiting' else 'DN' end As Progress,
FROM [dbo].[@IDH_JOBENTR] T0 , [dbo].[@IDH_JOBSHD] T1
WHERE T0.Code = T1.U_JobNr AND T1.U_Serial not like ''
ORDER BY T1.U_Serial, T1.U_AEDate DESC, T1.U_JobNr DESC ,T1.Code DESC,Today, T1.U_RDate, Available, Progress, T1.U_Status,T1.U_JobTp,T0.U_CardNM, T0.U_ZpCd, T1.U_ASDate, T1.U_ItemCd, T1.U_ItemDsc
The tables I’m using are IDH_JOBENTR which is the header records for my jobs and IDH_JOBSHD, which holds the row details for the jobs.
I’m trying to extract from the IDH_JOBSHD table the last (row) detail record for the (header) job.
The way the query works now, the first row for each unique serial number is the record I need, but this is all the info I would like to display I don’t need the other lines, and I don't know how to strip out the rest of the data
Hello, I wonder if someone has come across a similar problem I am having. I am migrating a database from Access to MS SQL 2000. The tables imported fine, but the queries had to be rewritten. I placed them under "Views". At present I am able to run a query directly on the SQL server, and it populates all fields nicely. When I do "SELECT * FROM EQCustomerReport" where EQCustomerReport is a name of a View on SQL, it only returns part of the fields. The other fields are blanked out for some reason. Please tell me if I am making a common mistake or something. Should I be using Stored Procedures instead of Views. Or would I be better of passing a full text of a query from my .NET application? Thanks very much in advance. Martyn
Hello, I have a requirement to select millions of rows from table and need do some parsing each row. I have identity column on each table. Here is the query logic I'm following. Logic A:Uisng While loop processing data row by row Logic B: Using Cursor Processing row by row
Here is the perormance on the above ran against .5 millions rows of data.
I add a new table and I want to see it in my list, I can go to EM and refresh there but it appears I have to close QA in order to see the new table. Is there a refresh option in QA to see my new object?
I have a SSRS report with 6 columns each column containing count of total# of applicants meeting certain criteria. User want to click on each column and see the basic information and also want to get the ability to export the data into excel.
I know that I can create 6 drillthrough reports with basic information of applicants and link it to the count from each column respectively but I was wondering if it is possible to write a Stored procedure with all 6 select queries and execute only 1 select query based on the column that user clicks on main report ?
Hello,I have 2 tables:- Customers with columns:customerID(prim_key),customerName(with customer's names)- Deliveries with columns:deliveryID(primKey),sender(ref_key to CustomerID from Customers),receiver(also ref_key to CustomerID from Customers);I need to select all data about deliveries, but instead of havingsender's ID and receiver's ID, I need to have their Names.I tried to do:SELECTdeliveries.deliveryID,Customers.customerName AS sender,Customers.customerName AS receiverFROMcustomers, deliveriesWHERECustomers.customerID=Deliveries.sender ANDCustomers.customerID=Deliveries.receiver;But this only works if sender=receiver, which is obvious ;)I'd like to know if there is any other way for obtaining those datawithin one queryThank you very much for your helpChris
I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is
select Hue from AvailableColors whereColor = @ColorParam.
For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....
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!
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Hi guys, I have a car_race table which has these fields
car_id int race_id int b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 2 b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 3 b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 3 b_car_won 'YES'
car_id 1 race_id 4 b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
select contractnbr,affcontractnbr,tdd,affrtramount,affturn, pt=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate),ratio=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate)/afftrun from #targetfinishedaff
datediff(d,afffirstfundingdate,affRTRBalanceZeroDate) appears two times in this code, Is there a way to make it appear only once? Thx
I have two SQL query that I would like to make in to one, if possibleHere is the first one: (Query4)SELECT rooms.DESCRIPTIO, rooms.ID, bookings1.DATEFROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms.IDWHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));And the second one:SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMIDFROM rooms LEFT JOIN Query4 ON rooms.ID = Query4.ROOMIDWHERE (((Query4.ROOMID) Is Null));Is it possible to make one query of this two?With the same result.
I havea textbox for searching by patient name but now and though I am searching with LIKE% i can only search for exact string, for example if user typed JASSIM it will search for field full_name LIKE '%JASSIM%" ad if user typed JASSIM RAHMA it will search for full_name LIKE '%JASSIM RAHMA%' but i want to search if the field CONTAINS '%JASSIM% AND %RAHMA% when the user types JASSIM RAHAM in the textbox
Hi all. I have tried making this query for a while and i didn't manage to. I know it is something related to JOIN, but i didn't manage to make it work : I have two tables. Table1- db_stockprices Table2- db_stockSymbols
and so on for all the stocks. ID is Primary Key SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information.
What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID.
Example: If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted.
Is this possible within 1 SQL Query? I would really appreciate a good query example for this example..
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Hi, I have a query thatI need to make into one query for the sake of an application reading only one cursor.
Here's the syntax: select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
The error message i'm recieving is the following:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have table 'OrderTab' in Sql Server database which have Company Number,ContactId,Quantity fields and data in tables are in following format. Company ContactId QuantityNumber100001 101 1100001 102 2100001 103 9100001 104 8100001 105 4100001 106 3100001 107 2100001 108 1100001 109 3
I need to create the query and want the result in following format. CompanyNumber |MaxQuantityContactId |MaxQuantity| MinQuantityContactId |MinQuantity100001 103 9 101 1 Where MaxQuatityContactId = ContactId which has order max quantityMaxQuantity = max quantity order by single company(100001) MinQuatityContactId = ContactId which has order min quantityMinQuantity = min quantity order by single company (100001) Any idea how to do that. Thanks in AdvanceArvind
I'm a bit new to MS-SQL so i thought maybe you could help me.
This is my prob. I have an Access application that i have upsized to a MS-SQL server. I have a query based on 3 tables: Customers, Companys and Payreminders. When i run this query, i can't add new records or make any changes. If i only run the query with the tables: companys and payreminders i can add new records and edit them. If i run a query that's based on companys and customers, i can edit and add new records, but when i run the query based on companys and customers i can't add or edit records.
companys, payreminders: Editable payreminders, companys: Editable Companys, customers: Non editable Companys, payreminders, customers: Non editable
Something must go wrong when i use the companys and customers table. Can anybody help or give a suggestion what to do.
I need help makeing the following query run more efficently.
Code:
SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip FROM Landlord_tbl t1 left outer join Mail_tbl t2 ON t2.potentialSitesID = t1.potentialSitesID WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip ORDER BY t1.firstName, t1.lastName, t1.city, t1.state
Where, Opening column is 0 when there is no previous closing and Value column would be the daily total. The closing would be the Opening value + Daily Total and this value would Opening value for next line. Please help me how to write the SQL statement to get this result from above table.
I have encountered some problems creating MDX query. There are two input parameters on report, both dropdown list and from query. The first parameter will check "State", and the second parameter "Store Name" depends on previous' result. If there's no "State" found, I manage to show a "No Data" in the "State" droplist. But with "No Data" in first parameter, the second parameter simply is not enabled. I want the second parameter shows " No Data" as well but not succeed.
Here's code
...
SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS ,
[Store].[Store Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube]) ... The query need to look at dataset that contains [store name] on rows, and some measures on columns. It also restricted by parameter @State.
Store Name that satisfies @State will be displayed. But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result. I'd rather like show something rather than nothing. I try codes like ... MEMBER [Store].[Store].[NA] AS '"N/A"' IIF(<Parameter empty>, (SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...), (SELECT ...<Original statement >) ) ... It shows "Subselect support only Column axis".
Any one has an idea? Please help me out Many thanks!
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
