# Query Applying Standard Deviation Function

Jul 27, 2015

I have below table structure

DECLARE @table Customer (Date DATE, CustomerID VARCHAR(5))
INSERT INTO @Customer (Date, CustomerID) VALUES
('2/6/2015','AKXLV'),
('2/7/2015','CPDYA'),
('2/7/2015','ZXDQW'),
('2/8/2015','LK1MP'),
etc, till 3836 Records for '2/8/2015' and 4869 Records for '2/8/2015' etc...

Now, I want to take the records which are meeting the standard deviation value of count of CustomerIDs in the group.

Date Count of Customers Avg. Count STD DEV
2/6/2015 2 3944.07 1849.61
2/7/2015 2 3944.07 1849.61
2/8/2015 3836 3944.07 1849.61
2/9/2015 4869 3944.07 1849.61

[Code] ....

Here I want to filter out +/- STDDEV of Average - So I need to filter out rows with dates 2/6/2015, 2/7/2015 and 2/16/2015 since these values not in the range of +/- STDDEV of Average.

Here CustomerID is Varchar type, So we need to convert them to Count first partitioning the Date Column and take the average and STDDEV of all without any partition...

## Mean, Minimum, Maximum, Standard Deviation Of Time

May 7, 2007

I have a report that calculates mean, min, max, stddev of somer exercises for a class (pushups, situps, trunk lifts, etc). I also have to calculate those for the 1-mile run time (ex: data -- 7:56, 6:35, 9:45 ( in minuteseconds). Obviously the standard Avg(), Min(), Max(), StdDev() functions won't work for time. So I put in some custom code to convert the time to seconds so I can use the standard functions on the seconds and also code to convert that answer back to minuteseconds. Max() works, but for example when I try to calculate the min(), it includes nulls from the dataset as zeros (not every student has to do the mile run). So the min is always 0. How can I exclude nulls from being calculated. The min() function excludes nulls so it returns the correct min() on integer data. What else can I do?

## ANSI SQL Deviation

Aug 7, 2001

My question here is whether or not MS SQL is processing joins according to the ANSI SQL standard. The situation is this, a friend of mine has the following query (The definition for the tables and the data in them are at the end of the message):

SELECT t.*, v.*
FROM Table3 T LEFT OUTER JOIN
TableView V ON T.tx = V.tx
WHERE V.tx IS NULL and V.part IN (1,2)

My friend is implying that the OUTER join takes precedence over the WHERE clause and should return ALL rows from table3 that don't exist in the view. The view, as you will see below, is a partitioned view. My contention is that the proper join processing is to join the tables first and then apply the WHERE clause to that result set (regardless of the join type). In my friend's case, he expects this query to return four rows. My expectation is that it will return zero rows, because in the result set after the join is performed there are no rows that have a NULL in the v.tx column AND 1 or 2 in the v.part column. The actual ouput agrees with my premise and my friend is sure that this is not proper ANSI SQL join processing. Is this proper ANSI join processing or is MS SQL deviating from the standard?

/************************************************** **********************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

CREATE TABLE [dbo].[Table1] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD
CONSTRAINT [CK_Table1] CHECK ([Part] = 1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO

CREATE TABLE [dbo].[Table2] (
[Part] [tinyint] NOT NULL ,
[id] [int] NOT NULL ,
[tx] [INT] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Part],
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table2] WITH CHECK ADD
CONSTRAINT [CK_Table2] CHECK ([Part] = 2)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table3]
GO

CREATE TABLE [dbo].[Table3] (
[tx] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table3] WITH CHECK ADD
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[tx]
) ON [PRIMARY]
GO
--DROP VIEW TableView
CREATE VIEW TableView AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
GO

INSERT TableView (Part,Id,Tx) VALUES (1,1,1)
INSERT TableView (Part,Id,Tx) VALUES (1,2,2)
INSERT TableView (Part,Id,Tx) VALUES (1,3,NULL)
INSERT TableView (Part,Id,Tx) VALUES (2,4,4)
INSERT TableView (Part,Id,Tx) VALUES (2,5,5)
INSERT TableView (Part,Id,Tx) VALUES (2,6,NULL)
INSERT Table3 (tx) VALUES (1)
INSERT Table3 (tx) VALUES (2)
INSERT Table3 (tx) VALUES (3)
INSERT Table3 (tx) VALUES (4)
INSERT Table3 (tx) VALUES (5)
INSERT Table3 (tx) VALUES (6)
INSERT Table3 (tx) VALUES (7)
INSERT Table3 (tx) VALUES (8)
GO

## GMT Deviation Hours

Dec 14, 2007

Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.

Thanks

## How To Calculate Deviation In Matrix?

Jun 21, 2007

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product
X1
100
0
110
10
130
20
100
-30
90
-10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me???

Thank You.

Balwant Patel.

## Converting Basic EffDt Query To SQL-92 Standard

Jan 28, 2005

How would I convert this query to ANSI 92 standard?

select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)

## Compare Standard Evalulation To Standard License Version

May 30, 2007

Hi All,

Good Evening.

I need a comparision between Evaluation copy and Standard license version of SQL Server 2005 /SSIS.

I'm assigned a task to evaluate SSIS and migrate a project to SSIS.

Now i'm being posed questions for the complete functionality of the tool...

- Whether it has a Bulk load trasformation ?

- Whether the evaluation version contain all the features of a standard license version ?

I need to submit a consolidated report for SSIS in comparision to the current ETL tool features.

Can you please let me is there any considerable features not given with a evaluation copy ?

Suresh N

## Cannot Run Datamining Query Task In Standard Edition Of Integration Services

Nov 13, 2006

Hi

We are using SQL Server 2005 Standard Edition. I have SSIS package which uses 'Data Mining Query Task'. I am using this task to run the predictions using the mining model I have deployed. When I run this SSIS package from IDE , everything is fine. But I deployed this SSIS to msdb, and when I right click on the package and run it from MSDB I get error message saying ,

'Cannot run this task in this edition of Integration Services. It requires higher level edition'.

Altimately we want to execute this SSIS from an API which is written in C#. Can you please suggest any workaround for doing so?

Thanks,

Vikas

## Query To Retrieve Max Price Without Using Max Function And Sub Query

Mar 5, 2014

ItemIDItemNamePrice

1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00

the above is the existing table and i need a query to retrieve max price with out using max function and sub query

## How Do SQL 2000 Service Packs Play A Role In Upgrading?i.e.Can SQL 2000 Standard With No SP Be Upgraded To SQL 2005 Standard

Aug 2, 2006

How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??

## Applying SP3

Sep 15, 2004

Hi

I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.

Could anybody tell me what and where is the impact when we install SP3..??

I would appreciate any kinda help.

Rgds

Wilson

## Apply SP2 After Applying CTP?

Feb 20, 2007

Should I apply SP2 even though I have already installed the community preview?

## Applying Rules

Apr 18, 2008

In my package i have column called "optinout" coming from source file which has the value "start" and "stop", now in my destination table i have a column called "contact permission code" to which i need to apply a business rule based on the value in column "optinout" the rule is as stated "Contact Permission Code" is OptOut if "OptInOut" value is "STOP" else OptIn.

## Query Function

Jan 16, 2008

Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.
Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String)   Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType)   Dim Connection As New SqlConnection(ConnectionString)   Dim Command As New SqlCommand(QueryString, Connection)   Command.Connection.Open()      Return Command.ExecuteReader()End Function
It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).
Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!

## Function Vs. Sub-Query

Oct 19, 2004

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?

Bjorn

## To Use Function In A Query

Feb 15, 2006

hi
i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..
chakri

## Function Query

May 8, 2008

i have function:

can anybody help me to figure this issue? how can i get that.
Thank you.

## Query With Function Won't Run

May 29, 2008

Hi...

I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Incorrect syntax near '.'.

------------------------------

Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)

SELECT dv.product ,
dv.itemname ,
dv.u_vlgx_plc,
dv.shorted ,
dv.onhand ,
dv.po_num ,
t10.docduedate
FROM
(SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname AS t6_cardname,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
) dv

OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10

GROUP BY dv.product ,
dv.itemname ,
dv.u_vlgx_plc ,
dv.shorted ,
dv.onhand ,
t10.docduedate,
dv.po_num ,
dv.t6_cardname
ORDER BY dv.u_vlgx_plc,
dv.t6_cardname,
dv.product

I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.

Any idea how I can fix this? Thanks!

## -UseInprocLoader When Applying The SNAPSHOT

Oct 19, 2003

Hi guys,
ive search high and low for info regarding the "-UseInprocLoader" (in-process BULK INSERT command ) though to no avail. Their are only brief overviews on this property.

does anyone know or have detailed info on the "-UseInprocLoader" Property

1M thanks

## Replication And Applying Patches

Oct 25, 2003

Hi all

I just want to make sure I have the proper understanding of how to apply MS security patches in a replicated SQL2k environment.

Any comment or suggestion would be highly appreciated :-)

## Applying Service Pack 3a

Feb 20, 2004

Hi,

I am trying to install the eval version of Reporting Services. One of the requirements for installing this is to have SQL Server 2000 with service pack 3a. I've downloaded the service pack and followed the instructions documented by Microsoft (applying SQL2KSP3.exe, then SQL2KASP3.exe).

It appears that the application of the service packs did nothing. No errors where returned after the executable was run. But, after having applied these service packs, the version has not changed. I have restarted the service, and the server with no success. The version that is returned is
'Microsoft SQL Server 2000 - 8.00.194'.

I am running MSSQL server Developer Edition, running on Windows 2000 Professional (service pack 4).

Any suggestions. I have even uninstalled the SQL server and re-applied the service packs, so I really am having trouble trying to figure out what to do next.

Thanks,

Jim

## Applying Service Pack 5a

May 26, 1999

When I try to run SQL Server 6.5 Service Pack 5a, I consistently receive the error message

"Setup initialization could not be successfully completed.
cfgchar.exe could not be executed. Please check the relevant
out file."

I am currently at Service Pack 3.

1) I have insured that the PATH environment variable does not contain quotes.
2) I have no other applications running at the same time as the service pack is running.

The contents of the cfgchar.out file appear as follows:

C:MSSQLcharsets - created
C:MSSQLcharsetscp1250 - created
C:MSSQLcharsetscp1250DICTION.250 - copied
C:MSSQLcharsetscp1250NOCASE.250 - copied
C:MSSQLcharsetscp1250CSYDIC.250 - copied
C:MSSQLcharsetscp1250CSYNC.250 - copied
C:MSSQLcharsetscp1250HUNDIC.250 - copied
C:MSSQLcharsetscp1250HUNNC.250 - copied
C:MSSQLcharsetscp1250PLKDIC.250 - copied
C:MSSQLcharsetscp1250PLKNC.250 - copied
C:MSSQLcharsetscp1250ROMDIC.250 - copied
C:MSSQLcharsetscp1250ROMNC.250 - copied
C:MSSQLcharsetscp1250SHLDIC.250 - copied
C:MSSQLcharsetscp1250SHLNC.250 - copied
C:MSSQLcharsetscp1250SKYDIC.250 - copied
C:MSSQLcharsetscp1250SKYNC.250 - copied
C:MSSQLcharsetscp1250SLVDIC.250 - copied
C:MSSQLcharsetscp1250SLVNC.250 - copied
C:MSSQLcharsetscp1251 - created
C:MSSQLcharsetscp1251DICTION.251 - copied
C:MSSQLcharsetscp1251NOCASE.251 - copied
C:MSSQLcharsetscp1251UKRDIC.251 - copied
C:MSSQLcharsetscp1251UKRNC.251 - copied
C:MSSQLcharsetscp1253 - created
C:MSSQLcharsetscp1253DICTION.253 - copied
C:MSSQLcharsetscp1253NOCASE.253 - copied
C:MSSQLcharsetscp1253GRALTDCT.253 - copied
C:MSSQLcharsetscp1253GRMXTDCT.253 - copied
C:MSSQLcharsetscp1253GRNOACCE.253 - copied
C:MSSQLcharsetscp1254 - created
C:MSSQLcharsetscp1254DICTION.254 - copied
C:MSSQLcharsetscp1254NOCASE.254 - copied
C:MSSQLcharsetscp1255 - created
C:MSSQLcharsetscp1255DICTION.255 - copied
C:MSSQLcharsetscp1255NOCASE.255 - copied
C:MSSQLcharsetscp1257 - created
C:MSSQLcharsetscp1257DICTION.257 - copied
C:MSSQLcharsetscp1257NOCASE.257 - copied
C:MSSQLcharsetscp1257ETIDIC.257 - copied
C:MSSQLcharsetscp1257ETINC.257 - copied
C:MSSQLcharsetscp1257LTHDIC.257 - copied
C:MSSQLcharsetscp1257LTHNC.257 - copied
C:MSSQLcharsetscp1257LVIDIC.257 - copied
C:MSSQLcharsetscp1257LVINC.257 - copied
isql failed - Exit Code: 1

Any ideas?

Peter Townsend
Litens Automotive Group

## Applying Service Packs

Sep 27, 2007

Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?

## Applying And/or In Stored Procedure

Mar 28, 2008

Hi,

I need to modify an already existing stored procedure, which takes three parameters. Currently it uses exclusively "or" logic to apply the parameters to a select statement. e.g.

and (adid = @adID or (editorID = @editorID and aa.editorTypeID = 1))

What I need it to do is to apply a different "and/or" logic depending on what is supplied. If two or three parameters are supplied then it needs to use "and" logic to join them. Howver, if just one parameter is supplied then it should search on that parameter alone - which it won't do if I just join them all together using "and" by default.

The work has to be done inside the T-SQL, not in the calling code. I only know two ways of doing this. Firstly to create different stored procedures and use the calling code to pick one or the other depending on supplied parameters. The other is to do if/or login inside the SP itself on the values of the parameters. Either way presents something of a maintenance nightmare. Is there any other way to do this?

Cheers,
Matt

## Applying SSIS In Application

Dec 13, 2006

Is there anyway I can integrate the ssis in Web applications or Windows applications?

Thanks

## Error When Applying Snapshot

Jun 27, 2006

Hello, I have setup web sync from wm 5.0 and it seems to crash when applying the snapshot. Looking at the column definitions it seems fine. Am I missing something? Thanks in advance.

John

The identity column must be either an integer or big integer data type and cannot be NULL.
HRESULT 0x80004005 (25551)

The SQL statement failed to execute. [ SQL statement = CREATE TABLE "macros" ( "peopleid" numeric ( 18 , 0 ) NOT NULL , "macroid" numeric ( 18 , 0 ) IDENTITY ( 1 , 1 ) NOT NULL , "description" nvarchar ( 255 ) NULL , "rowguid" uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT "MSmerge_df_rowguid_8E4B26C6706844BC984B039BA07845B5" DEFAULT ( NEWID ( ) ) ) ]
HRESULT 0x80004005 (28560)

The operation could not be completed.

## Applying SP4. Do You Have To Provide Sa Password?

May 14, 2007

I have never applied a service pack to sql server 2000. Someone tells me that you have an option of supplying the SA password but do not have to and the service pack is applied just as if you did supply the SA password. This sounds odd to me. So, is it true? I am asking because our server instance shows "SP4" but a fix that was supposed to be included in SP4 was apparently not as the problem persists (link from sql server 2005 to 2000 fails when referenced in sql2005). I was thinking that whoever ran the service pack may not have provided the SA password so some of the SP4 was not applied???

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.

Thanks.

## Applying ASP.NET 2.0 To Custom Pages

May 18, 2006

database layed out with several tables and relationships set up, and i
went ahead and built a bunch of pages that I was planning on using.
While i was doing this I ran into 2 problems, the first was i wrote
some SQL statements to insert data from input boxes into some tables i
have in my database, i used a catch exception statement as well as a
if/else statement to handle the transfer to diffirent pages like a
signup succeded or failed, the problem im having is that the data isnt
saving into the database. (I was modeling my code after the Video 8 in the
Learning SQL server 2005 Express Edition for Beginners Video Series....
I actually got them to work......sorta....) When i test it no
exceptions are thrown and it continues on like nothing went wrong. The
other Problem i ran into was i realized i wanted to use the memberships
and roles provided by ASP.NET 2.0, and since i already created the pages for the site, i was
wondering if there was a way to add them in. This site isnt anything
fancy at all, i wanted to try and create a app where users sign up and
create an account on the site, but i also wanted to have the
flexability to insert and retrieve data from the database with out
having to be stuck in the templates. Sorry for the long story im
trying to give all the info possible. Any help on this would be
great! Thanks!

## Applying Colors To Reports

Sep 25, 2006

Is there a way to apply .css or Skin files to all reports. Changing colors in each and every page is a little cumbersome.

## Failure Applying Hot Fix KB934458

Dec 26, 2007

Our update of KB 934458 to SQL Server 2005 SP2 has failed on a Windows 2003 SP2 server. This is a standard version of SQL Server.

The error reported is:

Error Number : 29506
Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file e:MSQLMSSQLData for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.

I have verified that Administrator has full control permissions for the folder indicated in the error message. The SQL Server Service account also has full control permissions on the folder.

I have run the update as administrator without any access restriction.

Thanks

## Dropping/Applying Constraints

Jul 28, 2006

I have some C# code written a little while that imports data nightly
into a database. Most of the data is typically deleted from the tables
and imported again. There are some tables where data is never deleted
and references other tables. Because there are constraints between
these tables, the code processes the import in the following sequence.

get constraints from database->drop constraints->delete data->apply
constaints->import data

I was curious how I can accomplish similiar results in SSIS. I know I
can execute a process task to manage the constraints, but I was
wondering if there is any other way.

Thanks,
PJ

## Re-applying Format String

Feb 6, 2008

Hi,

I'm retrieving data from a cube using a datareader, all the measures have format string, but when retrieve from Integration services it's lost.

I know there is a way to get the format string as columns using extended properties of the conection. I haven't tried this yet, but is there any way of reapplying this format to the columns using this format strings??

thanks

## Aggregate Function In Sub-query

Jan 15, 2014

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.

SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);

My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.