Query Syntax: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Query/Report Help needed

Hello all,

I need to do the following task repeatedly. I have been doing it
manually until now and I am thinking if there is any way I could
automate the whole process.

Here is the task:
For a selected group of tables, I need to create an Excel file with one
worksheet per table. The worksheet contains information about the
table.

Here is an Example of Person table with fields PersonId, LastName,
FirstName ...

Column Name -> PersonId LastName FirstName ...
Type -> char char char ...
Length -> 6 50 50
Data -> P00001 Smith John ...
. P00002 Smith Joan ...You can do this from within an ActiveX Script in DTS. Select the
necessary information from INFORMATION_SCHEMA.COLUMNS, then use the
Excel automation objects to put the data into the proper cells. You can
then do the same for the actual data, using the column information that
you selected to generate a SQL statement if your tables are constantly
changing.

You should be able to find information on using the Excel automation
objects on the Microsoft website.

HTH,
-Tom.sql

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)

when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))

This is not a bug. This is due to the order in which certain expressions are evaluated. The query optimizer is free to move expressions around to determine the optimum plan. You could get this error in SQL Server 2000 also but it is more easy to hit in SQL Server 2005 due to newer optimizations to match computed column indexes/indexed views. This is documented in the behavior changes topic in BOL. See the link below and search for "Expressions in queries":

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

Basically, you need to use CASE expression in all the places where you are doing the CONVERT(float) with ISNUMERIC check in the WHEN clause. You will of course get slightly bad performance due to the complexity of the expressions. The correct approach however is to normalize the table and not mix different data domains in the same column.

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

sql

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_ Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?
Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_ Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_ Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.sql

Wednesday, March 28, 2012

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me],
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
as FeederName,
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2].[Program_Web]
as FeederWeb FROM
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me]
= 'Dental Assistant (Cert)' and
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
<> '' order by
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog_2
'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name],
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> as FeederName,
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2].[Program_Web]
> as FeederWeb FROM
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name]
> = 'Dental Assistant (Cert)' and
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> <> '' order by
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column pref
ix
> 'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog
_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or m
ore tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to
access it. Or the view may be in another
schema and you will have to preface that view name with that.

query works in 2005 but not in 2000

Hi,
I have the following query, it works fine in sql server 2005, but in
sql server 2000 it errors out:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104),
104)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
in sql 2000 i get the following error:
Msg 8120, Level 16, State 1, Line 7
Column 'test.DATE_CREATED' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
In 2005 it returns the data fine.
I need to have it return sorted by date and grouped by date, but the
returned data needs to not display the time, thats why I was doing that
conversion in the select. Any reason why it would work in one and not
the other. Or anyway around this?
Thanks.Put the right stuff in a subquery and make the final conversion in the
outside query. Using subqueries is usually the best way of dealing with
Group By problems.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164831064.173961.83960@.16g2000cwy.googlegroups.com...
> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
>
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
>|||phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
The error occurs because the SQL Server 2000 compiler sees a difference
between the SELECT list and the GROUP BY, due to the extra CONVERT you have
in the SELECT. The GROUP BY must match the SELECT with the summaried columns
removed. If you use this it should work in 2000:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
Dan|||Hi,
I see that the SELECT and GROUPBY need to match, but the problem is I
need to return the datetime without the date, thats why i was
converting to varchar. And I can't do the Order by as varchar as then
it's not ordering it anymore by datetime, which it needs to. If I try
to make the SELECT and GROUPBY the same, it complains about the ORDERBY
also needing to be the same, thats the part where i'm stuck, I need no
time in the return but it needs to be ordered by datetime.
Sylvain is there anyway you can give me a sample of how to put it in a
subquery and then make the final conversion outside?
Thanks.
Daniel Crichton wrote:
> phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> > Hi,
> > I have the following query, it works fine in sql server 2005, but in
> > sql server 2000 it errors out:
> >
> > DECLARE @.datestart datetime
> > DECLARE @.dateend datetime
> >
> > set @.datestart = '2004-11-29'
> > set @.dateend = '2006-11-29'
> >
> > select convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > from testtable1 test, testtable2 od
> > where test.code=od.code2
> > and (test.method<>3)
> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > '+@.dateend
> > group by convert(datetime, convert(varchar, test.date_created, 104),
> > 104)
> > order by convert(datetime, convert(varchar, test.date_created, 104),
> > 104) desc
> >
> > in sql 2000 i get the following error:
> > Msg 8120, Level 16, State 1, Line 7
> > Column 'test.DATE_CREATED' is invalid in the select list because it is
> > not contained in either an aggregate function or the GROUP BY clause.
> >
> > In 2005 it returns the data fine.
> >
> > I need to have it return sorted by date and grouped by date, but the
> > returned data needs to not display the time, thats why I was doing that
> > conversion in the select. Any reason why it would work in one and not
> > the other. Or anyway around this?
> >
> > Thanks.
>
> The error occurs because the SQL Server 2000 compiler sees a difference
> between the SELECT list and the GROUP BY, due to the extra CONVERT you have
> in the SELECT. The GROUP BY must match the SELECT with the summaried columns
> removed. If you use this it should work in 2000:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
>
> Dan|||Actually I got the subquery select working, used a select into
statement, to put it into a temp table, and then had the temp table
data return the first column as varchar, removing the time.
Thank you for the help.
phil2phil wrote:
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
> Thanks.
>
> Daniel Crichton wrote:
> > phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> >
> > > Hi,
> > > I have the following query, it works fine in sql server 2005, but in
> > > sql server 2000 it errors out:
> > >
> > > DECLARE @.datestart datetime
> > > DECLARE @.dateend datetime
> > >
> > > set @.datestart = '2004-11-29'
> > > set @.dateend = '2006-11-29'
> > >
> > > select convert(varchar, (convert(datetime, convert(varchar,
> > > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > > from testtable1 test, testtable2 od
> > > where test.code=od.code2
> > > and (test.method<>3)
> > > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > > '+@.dateend
> > > group by convert(datetime, convert(varchar, test.date_created, 104),
> > > 104)
> > > order by convert(datetime, convert(varchar, test.date_created, 104),
> > > 104) desc
> > >
> > > in sql 2000 i get the following error:
> > > Msg 8120, Level 16, State 1, Line 7
> > > Column 'test.DATE_CREATED' is invalid in the select list because it is
> > > not contained in either an aggregate function or the GROUP BY clause.
> > >
> > > In 2005 it returns the data fine.
> > >
> > > I need to have it return sorted by date and grouped by date, but the
> > > returned data needs to not display the time, thats why I was doing that
> > > conversion in the select. Any reason why it would work in one and not
> > > the other. Or anyway around this?
> > >
> > > Thanks.
> >
> >
> > The error occurs because the SQL Server 2000 compiler sees a difference
> > between the SELECT list and the GROUP BY, due to the extra CONVERT you have
> > in the SELECT. The GROUP BY must match the SELECT with the summaried columns
> > removed. If you use this it should work in 2000:
> >
> > DECLARE @.datestart datetime
> > DECLARE @.dateend datetime
> >
> > set @.datestart = '2004-11-29'
> > set @.dateend = '2006-11-29'
> >
> > select convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > from testtable1 test, testtable2 od
> > where test.code=od.code2
> > and (test.method<>3)
> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > '+@.dateend
> > group by convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1)
> > order by convert(datetime, convert(varchar, test.date_created, 104),
> > 104) desc
> >
> >
> >
> > Dan|||phil2phil wrote:
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
>
Instead of converting, use this to "strip off" the time portion of a
date/time:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Easy: to create a subquery, you put the query between parenthesis and you
give it an name (or alias). In your case, this should look like:
Select convert(varchar, SQ.dt, 1) as dt, fruits
From (
select convert(datetime, convert(varchar, test.date_created,104), 104) dt,
count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2 and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104), 104)
) as SQ
order by SQ.dt desc
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164899356.494835.194380@.j72g2000cwa.googlegroups.com...
> Actually I got the subquery select working, used a select into
> statement, to put it into a temp table, and then had the temp table
> data return the first column as varchar, removing the time.
> Thank you for the help.
> phil2phil wrote:
>> Hi,
>> I see that the SELECT and GROUPBY need to match, but the problem is I
>> need to return the datetime without the date, thats why i was
>> converting to varchar. And I can't do the Order by as varchar as then
>> it's not ordering it anymore by datetime, which it needs to. If I try
>> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
>> also needing to be the same, thats the part where i'm stuck, I need no
>> time in the return but it needs to be ordered by datetime.
>> Sylvain is there anyway you can give me a sample of how to put it in a
>> subquery and then make the final conversion outside?
>> Thanks.
>>
>> Daniel Crichton wrote:
>> > phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
>> >
>> > > Hi,
>> > > I have the following query, it works fine in sql server 2005, but in
>> > > sql server 2000 it errors out:
>> > >
>> > > DECLARE @.datestart datetime
>> > > DECLARE @.dateend datetime
>> > >
>> > > set @.datestart = '2004-11-29'
>> > > set @.dateend = '2006-11-29'
>> > >
>> > > select convert(varchar, (convert(datetime, convert(varchar,
>> > > test.date_created,104), 104)), 1) dt, count(apples) fruits
>> > > from testtable1 test, testtable2 od
>> > > where test.code=od.code2
>> > > and (test.method<>3)
>> > > and test.date_created between '00:00 '+@.datestart AND '23:59:59
>> > > '+@.dateend
>> > > group by convert(datetime, convert(varchar, test.date_created, 104),
>> > > 104)
>> > > order by convert(datetime, convert(varchar, test.date_created, 104),
>> > > 104) desc
>> > >
>> > > in sql 2000 i get the following error:
>> > > Msg 8120, Level 16, State 1, Line 7
>> > > Column 'test.DATE_CREATED' is invalid in the select list because it
>> > > is
>> > > not contained in either an aggregate function or the GROUP BY clause.
>> > >
>> > > In 2005 it returns the data fine.
>> > >
>> > > I need to have it return sorted by date and grouped by date, but the
>> > > returned data needs to not display the time, thats why I was doing
>> > > that
>> > > conversion in the select. Any reason why it would work in one and
>> > > not
>> > > the other. Or anyway around this?
>> > >
>> > > Thanks.
>> >
>> >
>> > The error occurs because the SQL Server 2000 compiler sees a difference
>> > between the SELECT list and the GROUP BY, due to the extra CONVERT you
>> > have
>> > in the SELECT. The GROUP BY must match the SELECT with the summaried
>> > columns
>> > removed. If you use this it should work in 2000:
>> >
>> > DECLARE @.datestart datetime
>> > DECLARE @.dateend datetime
>> >
>> > set @.datestart = '2004-11-29'
>> > set @.dateend = '2006-11-29'
>> >
>> > select convert(varchar, (convert(datetime, convert(varchar,
>> > test.date_created,104), 104)), 1) dt, count(apples) fruits
>> > from testtable1 test, testtable2 od
>> > where test.code=od.code2
>> > and (test.method<>3)
>> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
>> > '+@.dateend
>> > group by convert(varchar, (convert(datetime, convert(varchar,
>> > test.date_created,104), 104)), 1)
>> > order by convert(datetime, convert(varchar, test.date_created, 104),
>> > 104) desc
>> >
>> >
>> >
>> > Dan
>

query works fine outside union, but doesnt work .. .

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??

select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc

i've included the union query here for completeness of the question

begin
declare @.current_date datetime
set @.current_date = GETDATE()

select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @.current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

endTry using UNION ALL.

-PatP

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquettXe.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also
).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use a
n
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below al
so).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))sql

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquettÂe.asp?id=5006
John
"Rossco" wrote:
> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Monday, March 26, 2012

Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

Query with embedded SELECT

This is a request for help with a SELECT within SELECT. The following return
s
open occurances (no END_DATE) and selects the associated appointment with th
e
highest APPT_ID. I want to imbed another SELECT that will 1) read back to th
e
Appointment table (joining back on the APPT_ID) and return the APPT_DATE for
this appointment. 2) I want to JOIN to a table called Resource AS r on a
column in the Appointment table called a.RESOURCE_ID and get r. RESOURCE_NAM
E
from the resource table. Here is the original SELECT:
SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone, RTRIM(p.first_name
+ ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name) +
' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
Thank you very muchrichardb
Please post DDL+ sample data + expected result. It's hard to suggest
something.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> This is a request for help with a SELECT within SELECT. The following
returns
> open occurances (no END_DATE) and selects the associated appointment with
the
> highest APPT_ID. I want to imbed another SELECT that will 1) read back to
the
> Appointment table (joining back on the APPT_ID) and return the APPT_DATE
for
> this appointment. 2) I want to JOIN to a table called Resource AS r on a
> column in the Appointment table called a.RESOURCE_ID and get r.
RESOURCE_NAME
> from the resource table. Here is the original SELECT:
> SELECT o.OCCURANCE_DESC AS CaseID, p.home_phone AS phone,
RTRIM(p.first_name
> + ' ' + p.middle_name) + ' ' + p.last_name AS Patient, o.NOTE AS Comment,
> MAX(a.APPT_ID) AS LastAppt FROM Occurance o INNER JOIN Patient p ON
> o.CLIENT_ID = p.PatUniqueID JOIN Appointment a ON o.OCCURANCE_ID =
> a.OCCURANCE_ID WHERE ISDATE(END_DATE) = 0 AND a.Status < 200 GROUP BY
> o.OCCURANCE_DESC, p.home_phone, RTRIM(p.first_name + ' ' + p.middle_name)
+
> ' ' + p.last_name, o.NOTE, o.OCCURANCE_ID ;
> Thank you very much|||I don't know what "post DDL+ sample data" means? Let me try to simplify the
request. Let's say I am selecting from a table of appointments the latest
appointment of each type for each patient as follows
SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
This would give:
PATIENT_NAME APPT_TYPE LastestAppt
-- -- --
JONES 90800 43
SMITH 81000 35
etc.
Each appointment row includes a code for the doctor. However, I did not want
to group on the doctor initially, because I only want one most recent
appointment for each patient. Now I want to know who is the doctor for that
appointment, so I think I need a second imbeded SELECT to go back into the
Appointment file and pick off the doctor's ID, resulting in:
PATIENT_NAME APPT_TYPE LastestAppt Doctor
-- -- -- --
JONES 90800 43 RMB
SMITH 81000 35 JDB
etc.
I cannot figure out how to do that and would appreciate help with an example
.
"Uri Dimant" wrote:

> richardb
> Please post DDL+ sample data + expected result. It's hard to suggest
> something.
>
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:4D7B74D9-AD1C-40BB-94FF-EC18FF764920@.microsoft.com...
> returns
> the
> the
> for
> RESOURCE_NAME
> RTRIM(p.first_name
> +
>
>|||Richardb
DDL means to post actual a table structure with some data.
CREATE TABLE xxx
(
col1 INT,
col2 INT,
blala...
)
INSERT INTO xxx VALUES ('data',data'.....)
Based on your narrotive I guess you need the following
SELECT p.PATIENT_NAME, a.APPT_TYPE,
a.MAX(APPT_ID) AS LastestAppt,D.Doctorid
FROM Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID JOIN
(SELECT max(doctoreID) Doctorid FROM
FROM 'SomeTable' GROUP BY Somthing) D ON
D.doctoreID=AnotherTable.Doctorid
WHERE STATUS <
200 GROUP BY PATIENT_NAME, APPT_TYPE.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:8A2292C7-A997-47BA-A0A0-5173DDA61C26@.microsoft.com...
> I don't know what "post DDL+ sample data" means? Let me try to simplify
the
> request. Let's say I am selecting from a table of appointments the latest
> appointment of each type for each patient as follows
> SELECT p.PATIENT_NAME, a.APPT_TYPE, a.MAX(APPT_ID) AS LastestAppt FROM
> Appointments JOIN Patients p ON a.PATIENT_ID = p.PATIENT_ID WHERE STATUS <
> 200 GROUP BY PATIENT_NAME, APPT_TYPE.
> This would give:
> PATIENT_NAME APPT_TYPE LastestAppt
> -- -- --
> JONES 90800 43
> SMITH 81000 35
> etc.
> Each appointment row includes a code for the doctor. However, I did not
want
> to group on the doctor initially, because I only want one most recent
> appointment for each patient. Now I want to know who is the doctor for
that
> appointment, so I think I need a second imbeded SELECT to go back into the
> Appointment file and pick off the doctor's ID, resulting in:
> PATIENT_NAME APPT_TYPE LastestAppt Doctor
> -- -- -- --
> JONES 90800 43 RMB
> SMITH 81000 35 JDB
> etc.
> I cannot figure out how to do that and would appreciate help with an
example.
>
> "Uri Dimant" wrote:
>
with
to
APPT_DATE
a
Comment,
p.middle_name)sql

Friday, March 23, 2012

Query using IN and LIKE at the same time

The following query does not compile:
SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
OK, so it might it been a silly attempt, but seriously... I need to return
all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
including words like 'Rootbeer'. The reason why I am trying to use the IN is
because the ('%Beer%', '%apple%') will end up being replaced with a sub
query.
Thanks.SELECT * FROM Food AS f
WHERE EXISTS(
SELECT * FROM [sub_query_table] AS t
WHERE CHARINDEX(t.[field], f.[field]) > 0
)
Probably not very efficient, but will get what you want.
"Gecko" wrote:

> The following query does not compile:
> SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
> OK, so it might it been a silly attempt, but seriously... I need to return
> all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
> including words like 'Rootbeer'. The reason why I am trying to use the IN
is
> because the ('%Beer%', '%apple%') will end up being replaced with a sub
> query.
> Thanks.
>
>|||There are several ways of doing this:
SELECT *
FROM Food
WHERE FoodName LIKE '%Beer%'
OR FoodName LIKE '%apple%' ;
-- or
SELECT *
FROM Food
INNER JOIN ( SELECT 'Beer' UNION
SELECT 'apple' ) D ( p )
ON FoodName LIKE '%' + p + '%' ;
Anith|||Thanks KH,
Just curios, would there be a way of doing the same things but instead of
returning a record if the record contains either '%Beer%' OR '%Apple%', I
would like to return the record only if it contains both '%Beer%' AND
'%Apple%' (notice how the first one is OR and the other AND)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
> SELECT * FROM Food AS f
> WHERE EXISTS(
> SELECT * FROM [sub_query_table] AS t
> WHERE CHARINDEX(t.[field], f.[field]) > 0
> )
> Probably not very efficient, but will get what you want.
>
> "Gecko" wrote:
>|||These might give you what you're looking for:
select * from Food as f
where 0 < all (
select charindex(t.[field],f.[field])
from subquerytable as t
)
or
select * from Food as f
where not exists (
select * from subquerytable
where charindex(t.[field],f.[field]) = 0
)
You might also look into SQL Server's full-text indexing.
Steve Kass
Drew University
Gecko wrote:

>Thanks KH,
>Just curios, would there be a way of doing the same things but instead of
>returning a record if the record contains either '%Beer%' OR '%Apple%', I
>would like to return the record only if it contains both '%Beer%' AND
>'%Apple%' (notice how the first one is OR and the other AND)
>
>"KH" <KH@.discussions.microsoft.com> wrote in message
>news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
>
>
>|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relatioanl division.

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows with the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table for only columns listed in the TEMPLATE table. I want to do this using only one select statement. (I know how to do this with stored procedure but thats not what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HK
If all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
...
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
|||Thanks for your help David. But this will not work for me. The example I gave was to explain my problem. But in reality, I will not know all column names in the DATA table, only TEMPLATE table is the guide which has list of fields to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider building
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows wi
th the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table fo
r only columns listed in the TEMPLATE table. I want to do this using only on
e select statement. (I know how to do this with stored procedure but thats n
ot what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HKIf all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
..
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
--|||Thanks for your help David. But this will not work for me. The example I gav
e was to explain my problem. But in reality, I will not know all column name
s in the DATA table, only TEMPLATE table is the guide which has list of fiel
ds to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGE
R
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider buildi
ng
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Friday, March 30, 2012

Query/Report Help needed

Query Works in SQL 2000 but not in SQL 2005

Query Works in SQL 2000 but not in SQL 2005

Query works in access, fails in new SQL Server db

Query works in access, fails in new SQL Server db

Wednesday, March 28, 2012

Query works in access, fails in new SQL Server db

query works in 2005 but not in 2000

query works in 2005 but not in 2000

query works in 2005 but not in 2000

query works fine outside union, but doesnt work .. .

Query with sub-query still returns unexpected results

Query with sub-query still returns unexpected results

Query with sub-query still returns unexpected results

Monday, March 26, 2012

Query with MAX Date

Query with embedded SELECT

Friday, March 23, 2012

Query using IN and LIKE at the same time

Query using dynamic column names

Query using dynamic column names

Query Syntax

Blog Archive

About Me