Transact SQL :: Query To Return As Part Of The Fields
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
ADVERTISEMENT
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
Aug 3, 2015
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
[code]...
View 5 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
Oct 9, 2015
I have a table which contains a 2 columns "LoanID" and "LoanStatus" and the values in "LoanStatus" are either 0 or 1 in it. If I run a select statement; I get:
LoanID LoanStatus
1 0
2 1
3 1
4 1
5 0
I need a query to return the following from the LoanStatus (distinct Values):
1 NA
0 Current
-1 Late
View 8 Replies
View Related
Jul 21, 2015
What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100 and therefore the TSQL would look like
SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc
Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.
View 6 Replies
View Related
Aug 3, 2015
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
View 5 Replies
View Related
Sep 16, 2015
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
View 14 Replies
View Related
Oct 1, 2015
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
View 4 Replies
View Related
Jul 22, 2015
Error below was returned from an agent job:
OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]
A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?
View 3 Replies
View Related
Sep 17, 2015
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.
I am using sql server 2008.
View 14 Replies
View Related
Nov 25, 2015
Is it possible to find out available free space from a mounted / network drive using tsql query? I am using sql server 2008 R2.
View 5 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 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
Mar 21, 2007
how can you return part of a string and convert it into an integer value? Maybe like this:
convert(left(column, 3) as int)
does that work?
The Yak Village Idiot
View 2 Replies
View Related
Nov 20, 2012
I have a table with 2 columns ItemID and ReturnDesc. A sample of the data is:
ItemId ReturnDesc
1244 Did not want
1244 Wrong color
3426 Return came with ticket 426
3571 Ticket 584 was not included
The only information I want to have returned is the number following the text 'ticket'. How do I do this?
View 3 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Nov 10, 2007
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
View 1 Replies
View Related
Jul 14, 2015
I have a table like this :
reg.no | description | start_date | end_date | load_date
I want to join this table with itself on reg. no. But not all the rows in table must be joined.
But for example, rows with load_date 01-07-2015 to be joined with rows with load_date 02-07-2015. And the rest of the rows should not used in join (for example, rows having other load_dates)
Is this possible ? and how?
View 4 Replies
View Related
Jul 17, 2015
I am using SQL Server 2008. I have strings like this:
AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)
I want to split the string with delimiter '-' and store as first part and second part.
I saw few sample functions to split a string but these return table of values.
I simply want first part and second part.
In above examples context:
@First_Part = 'AB'
@Second_Part = '123'
Any simple way to do this?
View 6 Replies
View Related
May 13, 2015
I have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>> and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies
View Related
Jun 10, 2015
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString
1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString
1 Extracts^This^String1
2 Extracts^This^String2
3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
View 4 Replies
View Related
Apr 20, 2015
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">
I would like to replace this part of the string with a different piece of text.
I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.
View 4 Replies
View Related
Sep 18, 2015
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
View 4 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
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 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
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
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
Aug 31, 2015
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
View 2 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
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
