Merging Queries To Return Different Fields In Same
Oct 25, 2005
hi all,
can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.
1.number of imported imported animal type:
select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p
where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'
2. number of imported animal type that are licensed:
select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id
3.percentage: 2/1*100
select percentage=
((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100
4.local animal type:total pet records - 1
select number=
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)
5.local animal type that are licenced:total pet records-2
select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)
6.percentage: 5/4*100
select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100
thx,
vani
View 1 Replies
ADVERTISEMENT
Sep 6, 2007
I know how to get the results I want with two queries, but would like to use only one. The following is as abbreviated and concise as possible.
My first table [Items] contains two relevant fields:
ItemID (int) and GroupNum (varchar).
The second table [Call] contains three relevent fields:
ItemID (int), Started (smalldatetime), Voicemail (smalldatetime).
These are the results I would like:
SELECT GroupNum, Count(NOT Voicemail IS NULL), COUNT(DATEDIFF(n, Voicemail, Started) > 60)
OR
I would like the results grouped by by the group number, the total count where the voicemail is not null, and the total count where the date difference in minutes between started and voicemail is greater then 60.
I can join the table, and I can€™t get the group number and one OR the other of the two remaining fields, but not both.
Any help would be greatly appreciated
View 4 Replies
View Related
Sep 4, 2001
Help!!, Does anyone know of a way that I can merge three seperate fields into one field in my sql statement.
EG.
SELECT catnumber, catnumber2,catnumber3
FROM Categories
WHERE Customer = 'xxxx' and itemnumber = 1 or 2 or 4
I want the catnumber, catnumber2, catnumber3 all in one field so I can sort by it.
If anybody could help me it would be greatly appreciated.
Thanks
Mike
View 1 Replies
View Related
Feb 11, 2008
i have two columns in sql which is datetime datatype
fromdate & todate, now i want to display it like fromdate-todate in my front end
select fromdate +'-'+ todate as date from table.
when i use this query , the error is
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Thanks for any help
View 1 Replies
View Related
Mar 20, 2001
Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
Thanks - Sharon
View 5 Replies
View Related
Nov 6, 2007
I have an MS Access database that has 140 fields.
Every 4 fields are the same repeating pattern of types Memo, Number, Number, Currency. (e.g. Q1 - Memo, Q2 - Number, Q3 - Number, Q4 - Currency, Q5 - Memo ,etc. . . )
I need to query this and combine them into 4 columns. Do a group by on the 2nd field and calculate average on the fourth.
Is there any way to do this with queries? The fields were named in sequential order from Q1 - Q140.
This database isn't my fault, but unfortunately, I need to work with it.
View 2 Replies
View Related
Jan 25, 2008
how can I fill a data set with a multi parameter filter:
I want to create in my form few textboxes and create a query that filters the dataset to show only the specific data.
but the problem is if the user doesn't supply any words in some textbox the dataset is not filled.
it would be a good solution for me if I can either:
for example let's say my query is SELECT * FROM table WHERE column = @parameter,
i want to write before the query something like "if @parameter="" then replace the query syntax near WHERE with * so it looks SELECT * FROM table WHERE column = *.
or:
if there is some character or character series that sql reconizes in a parameter as an asterisk.
thank you for your time!
View 37 Replies
View Related
Oct 25, 2004
I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:
Example:
SELECT first_name + ' ' + last_name AS full_name
FROM names
It works - but it seems rather blah. Is there something that'd be more "proper"?
View 3 Replies
View Related
Sep 10, 2015
I have two tables. Status and Fourhistory tables.
Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.
Below i have given some possible sample scenarios which i face and the third table contains the expected output.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)
[Code] .....
View 10 Replies
View Related
Sep 10, 2015
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
View 4 Replies
View Related
Mar 8, 2004
Hello,
I am relativley new to SQL and have a question about identity fields.
I am creating a script to run everynight to insert records into a support table in a database. one of the fields is a identity field that is updated everytime a record is added locally or over the web.
Some records that are added into the database locally by users do not get added into theis support table, but so those new people entered in can use the website a entry must be added to this support table.
I am working on a script to take the records that where added by users and automatically put them in every night using a basic schedueled job.
The identy number is updated everynight in a table that collects all the important identiy numbers. I would like to use this table to alter the seed value and then increment by one every time a new record is added. This is my only sticking point so far.
Any help would be greatly appreciated.
Kenn Brown
View 13 Replies
View Related
May 29, 2008
Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
View 8 Replies
View Related
Dec 20, 1999
Help!
I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
Thanks.
View 1 Replies
View Related
Feb 7, 2007
Is there any way to create a chart using data fields from more that one query?
I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.
I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.
View 3 Replies
View Related
Apr 22, 2004
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.
View 5 Replies
View Related
Feb 16, 2007
Hello,
Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.
Thanks
View 6 Replies
View Related
Feb 20, 2013
Recently I had an application developer approach me and asked if I could provided him with a list of sprocs by returned row count. We had an issue where the application passed in a number of parameters which attempted to return 200k plus rows of data and the application was timing out. He changed the required parameters in the application and a reasonable number of rows were returned as expected. Short term solution to this one problem.
However there are always timeout issues with this particular application and we got to thinking that maybe other sprocs that were called using parameters would also fail at some point in time because too much data was being returned.
View 5 Replies
View Related
Jun 3, 2015
I am new to SQL. I added a calculated field to my dataset with and expression to calculate efficiency.
My expression is as follows: =Fields!EstTotLbrHrs.Value/Fields!ActTotLbrHrs.Value
In some cases my value for ActTotLbrHrs is 0, so it returns #Error
How do I return "N/A" for fields with ActTotLbrHrs = 0
View 1 Replies
View Related
Jan 18, 2008
Here is a very basic question that I have.
I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.
How can I pull back the records from table A that do not have a corresponding combination in B?
I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?
THANKS!
View 1 Replies
View Related
Oct 17, 2006
Hi
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Thanks for any help you can offer.
View 11 Replies
View Related
Dec 6, 2004
Hello,
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
5. Any other possible approaches ??
Looking forward to your responses,
Thanks much,
bmgun.
View 3 Replies
View Related
Sep 9, 2004
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough.
Does anyone have any solutions???
View 2 Replies
View Related
Jul 23, 2005
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
View 3 Replies
View Related
May 22, 2015
Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.
SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date
FROM tblVehicles
WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To])
AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));
View 4 Replies
View Related
Oct 16, 2015
I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below
select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version
[code]....
View 12 Replies
View Related
Dec 3, 2013
I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.
View 2 Replies
View Related
Oct 17, 2007
Hello,
I would like create in a table (A) a column with a formula's data.
In this formula I would like implement 2 fields from a Table (B)
So the formula can be :
[TABLE_B].[FIELD1] + [TABLE_B].[FIELD2]
Is it possible?
We can call 2 fields from a other table?
Thank you
View 1 Replies
View Related
Feb 19, 2014
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
View 2 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
