Transact SQL :: Return Values That Are Equal To Both Fields ONLY

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


ADVERTISEMENT

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

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

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 View Related

Transact SQL :: Stored Procedure Return Values

Sep 23, 2015

If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer?  Is that simply the success flag?

View 5 Replies View Related

Transact SQL :: Return One Of Two Existing Values For A Record With Same ID

Apr 28, 2015

I have been researching BOL and other online resources but cannot seem to get a definitive answer.

Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest

Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest

Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,

[Code] ...

but cannot seem to be able to return one record with its corresponding value criteria.

View 21 Replies View Related

Transact SQL :: Query To Return Greater Than Zero Values To Sort Up And Zeros Go Down

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

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

May 12, 2015

I've to write a function to return a comma delimited values from a table columns

If a table has Tab1 ( Col1,Col2,Col3).

E.g. as below ( the columnName content I want to use as columns for my pivot table

CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;

I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....

View 13 Replies View Related

Transact SQL :: Return Preset Data Values Based On User Material ID

Jul 22, 2015

I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:

User Material = Wood-MDF
Preset Material = MDF Panel

I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?

View 2 Replies View Related

How Do You Find Not Equal Fields?

May 17, 2007

I have table 'A' containing Company information including the company zip code.  I have table 'B' which is a table of ZipCodes.  How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'?
Thank you

View 8 Replies View Related

Making Fields Equal Each Other...

Jun 28, 2005

Sorry if this is a total n00bie question but...


I have table A and table B

I want a field in table B to be equl to the primary key in table A, and i'm not sure how to do that.

Thanks

View 1 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Inner Join Matches Non-equal Fields??????

Jun 13, 2006

Hi group,

This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.

Let's assume the following values:
Table1:
hello
Hello

Table2:
hello

This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda


It returns:
hello
Hello

????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello


Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda

from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)

What if I wanted to return all EXACT matches? Then what? Change collation?

View 5 Replies View Related

Return Data Only If Time Is Equal By A Minute

Apr 9, 2015

Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.

As in the following should return the data:

'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'

And the following should not return the data:

'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'

The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.

So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.

View 4 Replies View Related

Transact SQL :: Query With NOT EQUAL TO VALUE

Oct 20, 2015

comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message

Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137
Incorrect syntax near '<'.

Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..

if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0

View 2 Replies View Related

Recognize NULL Values As Being Equal...

May 1, 2001

Hi,

I'm using SQL SERVER 7.0.


I'm driving myself crazy on this one. I have 2 tables that look like this:


T1T2
C1C2C3C1C2C3
JOE1OTTAWAJOE1TORONTO
MARC1OTTAWAMARC4OTTAWA
GAVINNULLHALIFAXGAVIN3HALIFAX
DARRINNULLHALIFAXDARRIN3HALIFAX
DENISENULLPITTSBURGHDENISENULLPITTSBURGH
LOUISENULLRUSSELLLOUISE2RUSSELL
ANDREA3STITTSVILLEANDREANULLSTITTSVILLE
MARIO66PITTSBURGHGEORGE6KINCARDINE
LARRY6KINCARDINE
MARIO66PITTSBURGH

What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:

SELECTT2.*
FROMT2 LEFT OUTER JOIN T1
ONT2.C1 = T1.C1
ANDT2.C2 = T1.C2
ANDT2.C3 = T1.C3
WHERET1.C1 IS NULL

But this statement returns the DENISE record when I do this (which has an EXACT match).
So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN).
So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?

This is the result set that I would like to have returned in this example:

JOE1TORONTO
MARC4OTTAWA
GAVIN3HALIFAX
DARRIN3HALIFAX
LOUISE2RUSSELL
ANDREANULLSTITTSVILLE
GEORGE6KINCARDINE
LARRY6KINCARDINE


I've included a script to build and populate the tables below.

Any help on this will be greatly appreciated.

Thanks in advance,
Darrin

------------------------------------------------
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T1'
)
DROP TABLE T1
GO

IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T2'
)
DROP TABLE T2
GO


CREATE TABLE [dbo].[T1] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T2] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO


INSERT T1 VALUES('JOE', '1', 'OTTAWA')
INSERT T1 VALUES('MARC', '1', 'OTTAWA')
INSERT T1 VALUES('GAVIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DARRIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T1 VALUES('LOUISE', NULL, 'RUSSELL')
INSERT T1 VALUES('ANDREA', '3', 'STITTSVILLE')
INSERT T1 VALUES('MARIO', '66', 'PITTSBURGH')
GO


INSERT T2 VALUES('JOE', '1', 'TORONTO')
INSERT T2 VALUES('MARC', '4', 'OTTAWA')
INSERT T2 VALUES('GAVIN', '3', 'HALIFAX')
INSERT T2 VALUES('DARRIN', '3', 'HALIFAX')
INSERT T2 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T2 VALUES('LOUISE', '2', 'RUSSELL')
INSERT T2 VALUES('ANDREA', NULL, 'STITTSVILLE')
INSERT T2 VALUES('GEORGE', NULL, 'KINCARDINE')
INSERT T2 VALUES('LARRY', NULL, 'KINCARDINE')
INSERT T2 VALUES('MARIO', '66', 'PITTSBURGH')
GO

View 3 Replies View Related

T-SQL (SS2K8) :: Joining Where Values Do Not Equal One Another

Apr 23, 2014

I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.

p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975

left side coming from p.item = right side coming from c.item.

Here are my statements.

--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num

[Code] ....

View 8 Replies View Related

Show Values For Temperatures Not Equal To Or Above Threshold?

Feb 10, 2014

The below stored procedure is used to create a vertical benchmark line on the X-Axis which has a hour scale. I use the stored procedure to find out which temperature crosses or equals the threshold temperature (340), then plot the vertical benchmark line at the hour the first temperature is equal to or greater than 340 degrees and less than 1000 degrees.

The logic below works if the temperature is equal to or greater than 340 degrees and less than 1000 degrees. THE ISSUE is I have 8 temperatures if they don't cross the threshold of 340 degrees I need to set a default value for my vertical line. In other words if the temperature is 180 and my threshold is 340 then set my vertical line on the highest temperature close to 340.

I tried removing my Where clause (but then it breaks the logic for those temperatures that are equal to or greater than 340). I tried using Case When but this didn't give me what I want either. I tried UNION as well. All giving me results I don't want.

Here is what I am looking for:

This first example is one where there was a temperature that was equal to or greater than the threshold of 340 degrees. This is CORRECT

Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-04 19:24:003.3500002014-02-04 16:02:13.0002014-02-05 05:02:13.000

If 8 temperatures did not equal or cross the threshold then give me the hour of the highest temperature close to the threshold but do not return 0.

For Example:

temp1 92
temp2 108
temp3 0
temp4 284 <<< this is the closest to the threshold so give me the hour when this occurred.
temp5 2192 *Remember I can only count temperatures less than 1000 degrees. Anything above 1000 degrees mean there is nothing in the oven. So it is false/positive.
temp6 102
temp7 0
temp8 12

Code:

first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-05 00:30:001.3000002014-02-05 02:00:13.0002014-02-05 02:00:13.000

Code:
CREATE PROCEDURE [dbo].[AgeScoreCardThreshold_JJ_12232013]
-- Add the parameters for the stored procedure here
@LicenseNumber int = NULL,
@Lot varchar(50) = NULL

[code].....

View 3 Replies View Related

SQL Server 2012 :: Combine One Value To Equal Two Values

Feb 13, 2015

I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.

The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.

View 1 Replies View Related

Distinct Record Equal To 2 Values From Same Column

Dec 6, 2013

Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:

name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1

View 2 Replies View Related

Sum Up Values In Table2 Where IDs Are Equal To Table1 And Then Update Value

Dec 17, 2013

I'm trying to update a value into a table a sum. The two tables have ID values. These ID values appear once in Table1 and multiple times in Table2. I'm currently trying to sum up the values in Table2 where the IDs are equal to Table1 and then update the value:

UPDATE [Table1] SET
[Total] =
(SELECT SUM([Table2].[QTY]) FROM [Table2],
[Table1] WHERE [Table1].[ID] = [Table2].[ID]
GROUP BY [Table1].[ID])
FROM [Table1], [Table2] WHERE [Table1].[ID] = [Table2].[ID]

View 2 Replies View Related

Transact SQL :: Get A List Of All Contracts Equal Or After To Their Maximum Contract

Jul 7, 2015

I have two tables and a CTE table.

-Shop : list of all shops with an ID_SHOP
-Contract : list of all contract with a FK to the shop FK_SHOP. A shop can have multiple contracts

I have a cte where I calculate for each FK_SHOP the maximum contract start date. I now want to join my shop table with the contract table but only for the contracts >= their maximum contract start date.

WITH cteContracts AS
(
SELECT
FK_SHOP
,ID_CONTRACT
,MAX(cont.DTT_START_DATE) DT_STARTDATE

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: When Invoice Contains Multiple Values Set Value Equal To A Value

May 18, 2015

All I have a table full of invoices and in that table there is a field named HCC. An invoice can contain multiple HCC's. I also have a table named hierarchical codes (below is an example)

(COL1)HCC...........(COL2)If any of the HCCs in this column exist on an invoice along with the HCC in column 1 then use HCC listed in column one
1
2
3.......................4
4
6
8.......................9 ,10 ,11 ,12 ,13

[code]...

I want to return invoice 1 with HCC set to 34 since the hierarchy for when any of these HCCs (35 ,36 ,37 ,38) exist on an invoice along with 34 is 34.

View 4 Replies View Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

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

How To Show Two Dataset With Equal &&amp; Non Equal Of Multiple Selection.

Jun 14, 2006

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.


Please advice. Thanks.

warm regards
Rakin
Singapore.

View 6 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

Return All Fields Of Database

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

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

Transact SQL :: CTE To Peg Char Values To Int Values In Radio-style Chart

Aug 26, 2015

I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements?  I've updated my query as below. Although this sample query works, it's not working for my real data.  Instead, I get an error.  At the bottom is the error part of my real query.I copied all of the tables from the first query block below.  But when I wrote the bottom query block, it underlined in red the words "answer" and "question."  It says "Invalid column name". 

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')

[code]...

View 13 Replies View Related

Expression Divide By 0 - How To Return N/A For Fields

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

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 View Related

Return Non-matching Records On Two Fields

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

Trying To Return Whole Records With Distinct Fields

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

SELECT - Dynamically Determine Fields To Return

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







Copyrights 2005-15 www.BigResource.com, All rights reserved