Hi all,
Apologies if this is the incorrect group. I couldn't find a group that
specifically handled queries in SQL Server. I originally posted this in the
Access Queries group, but since my back-end database is actually SQL Server
and I am just using Access as my front-end I need suggestions that are
specific to SQL Server.
Let's say I have a table with three fields in it: effective_date, entry_date
and amount. Is there a way that I could contruct a query such that I could
return all the records from the table where the latter of the effective_date
and entry_date fields falls within some target date range?
So for example, let's say the following data is in my table:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
5/1/2004 6/1/2005 $150
7/1/2004 4/1/2004 $170
2/1/2006 8/1/2004 $130
I want all the records where the *latter* of the two date fields falls
between 1/1/2004 and 12/31/2004. What I'd need to see in my result set based
on the data above is:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
7/1/2004 4/1/2004 $170
The third data item dropped out because even though the effective_date for
that record falls within our range, the latter of the two dates, the entry
date in this case, falls outside of our interested date range. The fifth
data item similarly falls out because its latest date, the effective date,
also falls outside our range.
How do I write a query to accomplish this?
Thanks for any help!
-Jeff VanBishlerThis is a good candidate for the CASE expression in SQL:
SELECT effective_date, entry_date, amount
FROM [Your Table]
WHERE
CASE
WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
ELSE entry_date
END
BETWEEN '1/1/2004' AND '12/31/2004'
The DATEDIFF function above is an easy way to find out which date is later
than the other. If effective_date is prior to entry_date, the DATEDIFF
function will return a number greater than one. If entry_date is prior to
effective_date, it will return a number less than one.
"Jeff VanBishler" wrote:
> Hi all,
> Apologies if this is the incorrect group. I couldn't find a group that
> specifically handled queries in SQL Server. I originally posted this in th
e
> Access Queries group, but since my back-end database is actually SQL Serve
r
> and I am just using Access as my front-end I need suggestions that are
> specific to SQL Server.
> Let's say I have a table with three fields in it: effective_date, entry_da
te
> and amount. Is there a way that I could contruct a query such that I could
> return all the records from the table where the latter of the effective_da
te
> and entry_date fields falls within some target date range?
> So for example, let's say the following data is in my table:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 5/1/2004 6/1/2005 $150
> 7/1/2004 4/1/2004 $170
> 2/1/2006 8/1/2004 $130
> I want all the records where the *latter* of the two date fields falls
> between 1/1/2004 and 12/31/2004. What I'd need to see in my result set bas
ed
> on the data above is:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 7/1/2004 4/1/2004 $170
> The third data item dropped out because even though the effective_date for
> that record falls within our range, the latter of the two dates, the entry
> date in this case, falls outside of our interested date range. The fifth
> data item similarly falls out because its latest date, the effective date,
> also falls outside our range.
> How do I write a query to accomplish this?
> Thanks for any help!
>
> -Jeff VanBishler
>
>|||Worked like a dream!
Thanks Mark!
-Jeff VanBishler
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:E5710BB0-9319-43C2-88A8-2E6307B9A5CD@.microsoft.com...
> This is a good candidate for the CASE expression in SQL:
> SELECT effective_date, entry_date, amount
> FROM [Your Table]
> WHERE
> CASE
> WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
> ELSE entry_date
> END
> BETWEEN '1/1/2004' AND '12/31/2004'
> The DATEDIFF function above is an easy way to find out which date is later
> than the other. If effective_date is prior to entry_date, the DATEDIFF
> function will return a number greater than one. If entry_date is prior to
> effective_date, it will return a number less than one.
> "Jeff VanBishler" wrote:
>
Showing posts with label criteria. Show all posts
Showing posts with label criteria. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Query to return latest record, multiple join fields
Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql
Monday, February 20, 2012
Query Syntax
I am looking to find records that share the same Policy_Desc but have
different Policy_ids. I know there are records that fit this criteria but fo
r
some reason this query returns zero rows.
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE Inside.Policy_Desc = Outside.Policy_Desc
AND Inside.Policy_id <> Outside.Policy_id
)Maybe this will do it
SELECT t1.Policy_Desc, t1.Policy_id
FROM Policy_Table as t1 INNER JOIN Policy_Table as t2
ON t1.Policy_Desc = t2.Policy_Desc AND t1.Policy_ID=t2.Policy_id <>
t1.Policy_id
Adi|||A few things:
Check if the database collation is case sensitive. If so, then convert the
desc to upper case in both sides and check.
If there are leading blank spaces, you may want to use LTRIM.
If none of the above works, then we can break our heads.|||Also post ddl and sample data.|||Can Policy_Desc be NULL? If so try this
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE (Inside.Policy_Desc = Outside.Policy_Desc
OR (Inside.Policy_Desc is null and Outside.Policy_Desc is
null))
AND Inside.Policy_id <> Outside.Policy_id
)
ORDER BY Policy_Desc, Policy_id|||And try if you are getting any result for this.
SELECT Policy_Desc, count(*)
FROM Policy_Table
group by policy_desc
having count(*) > 1|||> I know there are records that fit this criteria but for
> some reason this query returns zero rows
There is nothing wrong with your query, barring the NULLs and case
differences mentioned by the others. I would expect the query below to
return data if you actually have duplicates.
SELECT Policy_Desc, COUNT(*)
FROM Policy_Table
GROUP BY Policy_Desc
HAVING COUNT(*) > 1
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B0A11403-3790-44FD-9C7F-976F72846942@.microsoft.com...
>I am looking to find records that share the same Policy_Desc but have
> different Policy_ids. I know there are records that fit this criteria but
> for
> some reason this query returns zero rows.
> SELECT Policy_Desc, Policy_id
> FROM Policy_Table AS Outside
> WHERE EXISTS
> (
> SELECT 1 FROM Policy_Table AS Inside
> WHERE Inside.Policy_Desc = Outside.Policy_Desc
> AND Inside.Policy_id <> Outside.Policy_id
> )
different Policy_ids. I know there are records that fit this criteria but fo
r
some reason this query returns zero rows.
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE Inside.Policy_Desc = Outside.Policy_Desc
AND Inside.Policy_id <> Outside.Policy_id
)Maybe this will do it
SELECT t1.Policy_Desc, t1.Policy_id
FROM Policy_Table as t1 INNER JOIN Policy_Table as t2
ON t1.Policy_Desc = t2.Policy_Desc AND t1.Policy_ID=t2.Policy_id <>
t1.Policy_id
Adi|||A few things:
Check if the database collation is case sensitive. If so, then convert the
desc to upper case in both sides and check.
If there are leading blank spaces, you may want to use LTRIM.
If none of the above works, then we can break our heads.|||Also post ddl and sample data.|||Can Policy_Desc be NULL? If so try this
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE (Inside.Policy_Desc = Outside.Policy_Desc
OR (Inside.Policy_Desc is null and Outside.Policy_Desc is
null))
AND Inside.Policy_id <> Outside.Policy_id
)
ORDER BY Policy_Desc, Policy_id|||And try if you are getting any result for this.
SELECT Policy_Desc, count(*)
FROM Policy_Table
group by policy_desc
having count(*) > 1|||> I know there are records that fit this criteria but for
> some reason this query returns zero rows
There is nothing wrong with your query, barring the NULLs and case
differences mentioned by the others. I would expect the query below to
return data if you actually have duplicates.
SELECT Policy_Desc, COUNT(*)
FROM Policy_Table
GROUP BY Policy_Desc
HAVING COUNT(*) > 1
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B0A11403-3790-44FD-9C7F-976F72846942@.microsoft.com...
>I am looking to find records that share the same Policy_Desc but have
> different Policy_ids. I know there are records that fit this criteria but
> for
> some reason this query returns zero rows.
> SELECT Policy_Desc, Policy_id
> FROM Policy_Table AS Outside
> WHERE EXISTS
> (
> SELECT 1 FROM Policy_Table AS Inside
> WHERE Inside.Policy_Desc = Outside.Policy_Desc
> AND Inside.Policy_id <> Outside.Policy_id
> )
Labels:
criteria,
database,
fit,
havedifferent,
microsoft,
mysql,
oracle,
policy_desc,
policy_ids,
query,
records,
server,
share,
sql,
syntax
Subscribe to:
Posts (Atom)