The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql
Showing posts with label sample. Show all posts
Showing posts with label sample. Show all posts
Wednesday, March 28, 2012
Wednesday, March 7, 2012
Hourly Average
Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:
lack of the FROM clause. I adapted it to a meaningess example using
an actual table:
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
There is a comma missing at the end of the first line.
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
>
>
There is a comma missing at the end of the first line.
it was just giving me what I asked for.
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT|||Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT
capabilities! I have the following sample fields coming from different
tables:
Location
TimeDate (timestamp)
Data
I need to return the average of Data per Location per HOUR.
Thanks.I believe this will do it.
SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM ....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)
Roy Harvey
Beacon Falls, CT
On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
Quote:
Originally Posted by
>Apologies for the simplicity of the question, but it reflects my
>capabilities! I have the following sample fields coming from different
>tables:
>
>Location
>TimeDate (timestamp)
>Data
>
>I need to return the average of Data per Location per HOUR.
>
>Thanks.
Thanks for the reply! I have run this and am getting a syntax error on
the second line which says there is incorrect syntax near the '(' . I
played with spacing but could not resolve. Do you have any tips?
Also, is it inappropriate
Roy Harvey wrote:
Quote:
Originally Posted by
I believe this will do it.
>
SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM ....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
>
Quote:
Originally Posted by
Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:
Location
TimeDate (timestamp)
Data
I need to return the average of Data per Location per HOUR.
Thanks.
lack of the FROM clause. I adapted it to a meaningess example using
an actual table:
SELECT id,
dateadd(hour,datediff(hour,0,crdate),0) as Hourly,
avg(schema_ver) as AvgData
FROM sysobjects
GROUP BY
id,
dateadd(hour,datediff(hour,0,crdate),0)
If you can't find the problem, past the exact code that is not
executing into your reply.
Roy Harvey
Beacon Falls, CT
On 17 Aug 2006 11:24:46 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
Quote:
Originally Posted by
>Roy:
>
>Thanks for the reply! I have run this and am getting a syntax error on
>the second line which says there is incorrect syntax near the '(' . I
>played with spacing but could not resolve. Do you have any tips?
>Also, is it inappropriate
>
>Roy Harvey wrote:
Quote:
Originally Posted by
>I believe this will do it.
>>
>SELECT Location,
> dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
> avg(Data) as AvgData
> FROM ....
> GROUP BY
> Location,
> dateadd(hour,datediff(hour,0,TimeDate),0)
>>
>Roy Harvey
>Beacon Falls, CT
>>
>On 17 Aug 2006 09:31:32 -0700, "Compliance" <comcontrol@.aol.com>
>wrote:
>>
Quote:
Originally Posted by
>Apologies for the simplicity of the question, but it reflects my
>capabilities! I have the following sample fields coming from different
>tables:
>
>Location
>TimeDate (timestamp)
>Data
>
>I need to return the average of Data per Location per HOUR.
>
>Thanks.
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT|||My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:
Quote:
Originally Posted by
>[Error] Script lines: 1-9 --------
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Quote:
Originally Posted by
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
Thanks again.
Roy Harvey wrote:
Quote:
Originally Posted by
On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
>
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
>
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
>
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT
it was just giving me what I asked for.
Thanks Very Much Roy.
Compliance wrote:
Quote:
Originally Posted by
My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:
>
Quote:
Originally Posted by
[Error] Script lines: 1-9 --------
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:
Quote:
Originally Posted by
On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT|||Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.
Thanks Very Much Roy.
Compliance wrote:
Quote:
Originally Posted by
My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:
>
Quote:
Originally Posted by
[Error] Script lines: 1-9 --------
Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:
>
DateTime Hourly AvgData
Name
------- -------
------ ----
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
>
>
Thanks again.
>
Roy Harvey wrote:
Quote:
Originally Posted by
On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <comcontrol@.aol.com>
wrote:
Quote:
Originally Posted by
SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT
Subscribe to:
Posts (Atom)