Precedence of MAX and WHERE

Hi
I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :
SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username
Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?
Thanks
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||You could compare these queries and see which one yields the results you
want. Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 . Also, "date" is a really bad name
for a column. Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT? Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings. The safest approach here is to use
YYYYMMDD format, then this can't be by software or humans.
CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO
DROP TABLE dbo.myDataTable
GO
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||Andrew,

> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:

> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>
>

Possible to do this report?

Hi,
Is it possible to get reporting services to create a report with a repeating
grouping structure with a total at the bottom of each group? I would want to
start with a report header that is only output once. Then for each repeating
group, I need a header, the body, and a footer with the total. Here is an
example of the structure (hope it makes sense!!!)...
>>Report Header Begin<<
Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
Title=Hello
>>Group Header End<<
>> Group Body Begin<<
Code Customer Quantity
XYZ Smith 10
ABC Jones 10
123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
Total = 30
>>Group Footer End<<
>>Group Header Begin<<
Title=OK
>>Group Header End<<
>> Group Body Begin<<
Code Customer Quantity
XYZ Smith 5
ABC Jones 5
123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
Total = 15
>>Group Footer End<<
--
McGeeky
http://mcgeeky.blogspot.comThis is totally supported and easy to do. You need to add groups to your
table. Each group can have header and footers. You can then add an
expression that totals the current field. Search Books Online for the word
grouping
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to get reporting services to create a report with a
> repeating grouping structure with a total at the bottom of each group? I
> would want to start with a report header that is only output once. Then
> for each repeating group, I need a header, the body, and a footer with the
> total. Here is an example of the structure (hope it makes sense!!!)...
>>Report Header Begin<<
> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
> Code Customer Quantity
> XYZ Smith 10
> ABC Jones 10
> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
> Title=OK
>>Group Header End<<
>> Group Body Begin<<
> Code Customer Quantity
> XYZ Smith 5
> ABC Jones 5
> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
> Total = 15
>>Group Footer End<<
> --
> McGeeky
> http://mcgeeky.blogspot.com
>|||Thanks Bruce. I have managed to get a group header and group body working
but cannot make a group footer, only a table footer (which I don't need).
How do I make a group footer?
Thanks!
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> This is totally supported and easy to do. You need to add groups to your
> table. Each group can have header and footers. You can then add an
> expression that totals the current field. Search Books Online for the word
> grouping
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group? I
>> would want to start with a report header that is only output once. Then
>> for each repeating group, I need a header, the body, and a footer with
>> the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>|||Right click the group and there should be a dialog box, where you can select
display footer...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"McGeeky" <anon@.anon.com> wrote in message
news:uuaheqkfFHA.1044@.tk2msftngp13.phx.gbl...
> Thanks Bruce. I have managed to get a group header and group body working
> but cannot make a group footer, only a table footer (which I don't need).
> How do I make a group footer?
> Thanks!
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
>> This is totally supported and easy to do. You need to add groups to your
>> table. Each group can have header and footers. You can then add an
>> expression that totals the current field. Search Books Online for the
>> word grouping
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group? I
>> would want to start with a report header that is only output once. Then
>> for each repeating group, I need a header, the body, and a footer with
>> the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>|||Doh! So simple, but so easily missed.
--
McGeeky
http://mcgeeky.blogspot.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OzsLHvkfFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Right click the group and there should be a dialog box, where you can
> select display footer...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "McGeeky" <anon@.anon.com> wrote in message
> news:uuaheqkfFHA.1044@.tk2msftngp13.phx.gbl...
>> Thanks Bruce. I have managed to get a group header and group body working
>> but cannot make a group footer, only a table footer (which I don't need).
>> How do I make a group footer?
>> Thanks!
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23yu%23nEkfFHA.1248@.TK2MSFTNGP12.phx.gbl...
>> This is totally supported and easy to do. You need to add groups to your
>> table. Each group can have header and footers. You can then add an
>> expression that totals the current field. Search Books Online for the
>> word grouping
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:eL6gP$jfFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Is it possible to get reporting services to create a report with a
>> repeating grouping structure with a total at the bottom of each group?
>> I would want to start with a report header that is only output once.
>> Then for each repeating group, I need a header, the body, and a footer
>> with the total. Here is an example of the structure (hope it makes
>> sense!!!)...
>>Report Header Begin<<
>> Branch=14 Date=2005-07-01
>>Report Header End<<
>>Group Header Begin<<
>> Title=Hello
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 10
>> ABC Jones 10
>> 123 Zippy 10
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 30
>>Group Footer End<<
>>Group Header Begin<<
>> Title=OK
>>Group Header End<<
>> Group Body Begin<<
>> Code Customer Quantity
>> XYZ Smith 5
>> ABC Jones 5
>> 123 Zippy 5
>>Group Body End<<
>>Group Footer Begin<<
>> Total = 15
>>Group Footer End<<
>> --
>> McGeeky
>> http://mcgeeky.blogspot.com
>>
>>
>