"Ant" <A**@discussions.microsoft.com> schrieb im Newsbeitrag
news:E9F635A9-8517-49F3-A7C3-318B9D82BC22@microsoft.com...
> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as
> a
> value.
>
> Hope somebody can help. Many thanks for your ideas.
> Ant
>
> SELECT  date,
>             payment
> FROM    table
> WHERE   "date is greater than beginning of this financial year"

"Ant" wrote:

> Hi, I'm new to SQL. I need to return a recordset which will include only
> records with a date after previous June. (Could be this year or last) It's
> for tax purposes. How can i specify that it's the last June, not 'June' as a
> value.
>
> Hope somebody can help. Many thanks for your ideas.
> Ant
>
> SELECT  date,
>              payment
> FROM    table
> WHERE   "date is greater than beginning of this financial year"

"Chandra" wrote:

> Hi Ant
>
> Probably this can solve the problem
>
>
> SELECT  date, payment
> FROM    table
> WHERE  
> date > (
> SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
>    ELSE datepart(yy,getdate())
>    END  as varchar(4)))
>
> Please let me know if there is any thing that u wanted to know.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---------------------------------------
>
>
>
> "Ant" wrote:
>
> > Hi, I'm new to SQL. I need to return a recordset which will include only
> > records with a date after previous June. (Could be this year or last) It's
> > for tax purposes. How can i specify that it's the last June, not 'June' as a
> > value.
> >
> > Hope somebody can help. Many thanks for your ideas.
> > Ant
> >
> > SELECT  date,
> >              payment
> > FROM    table
> > WHERE   "date is greater than beginning of this financial year"

"Ant" wrote:

> Hello Chandra,
> Thank you very much for your idea. It looks great. It almost works.
> Unfortunately it returns records from January 31-2004, however the earliest
> record should be no earlier than the previous June. I'll play around with it
> but if you have any ideas, I'd be most grateful.
>
> Many thanks
>
> Ant
>
> "Chandra" wrote:
>
> > Hi Ant
> >
> > Probably this can solve the problem
> >
> >
> > SELECT  date, payment
> > FROM    table
> > WHERE  
> > date > (
> > SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > datepart(yy,getdate())-1
> >    ELSE datepart(yy,getdate())
> >    END  as varchar(4)))
> >
> > Please let me know if there is any thing that u wanted to know.
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://groups.msn.com/SQLResource/
> > ---------------------------------------
> >
> >
> >
> > "Ant" wrote:
> >
> > > Hi, I'm new to SQL. I need to return a recordset which will include only
> > > records with a date after previous June. (Could be this year or last) It's
> > > for tax purposes. How can i specify that it's the last June, not 'June' as a
> > > value.
> > >
> > > Hope somebody can help. Many thanks for your ideas.
> > > Ant
> > >
> > > SELECT  date,
> > >              payment
> > > FROM    table
> > > WHERE   "date is greater than beginning of this financial year"

"Chandra" wrote:

> Hi Ant
> Then it might be with the date format.
>
> just try this way.
> FYI: i changed from 01/06 to 06/01
>
> SELECT  date, payment
> FROM    table
> WHERE  
> date > (
> SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> datepart(yy,getdate())-1
>    ELSE datepart(yy,getdate())
>    END  as varchar(4)))
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---------------------------------------
>
>
>
> "Ant" wrote:
>
> > Hello Chandra,
> > Thank you very much for your idea. It looks great. It almost works.
> > Unfortunately it returns records from January 31-2004, however the earliest
> > record should be no earlier than the previous June. I'll play around with it
> > but if you have any ideas, I'd be most grateful.
> >
> > Many thanks
> >
> > Ant
> >
> > "Chandra" wrote:
> >
> > > Hi Ant
> > >
> > > Probably this can solve the problem
> > >
> > >
> > > SELECT  date, payment
> > > FROM    table
> > > WHERE  
> > > date > (
> > > SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > > datepart(yy,getdate())-1
> > >    ELSE datepart(yy,getdate())
> > >    END  as varchar(4)))
> > >
> > > Please let me know if there is any thing that u wanted to know.
> > >
> > > --
> > > best Regards,
> > > Chandra
> > > http://chanduas.blogspot.com/
> > > http://groups.msn.com/SQLResource/
> > > ---------------------------------------
> > >
> > >
> > >
> > > "Ant" wrote:
> > >
> > > > Hi, I'm new to SQL. I need to return a recordset which will include only
> > > > records with a date after previous June. (Could be this year or last) It's
> > > > for tax purposes. How can i specify that it's the last June, not 'June' as a
> > > > value.
> > > >
> > > > Hope somebody can help. Many thanks for your ideas.
> > > > Ant
> > > >
> > > > SELECT  date,
> > > >              payment
> > > > FROM    table
> > > > WHERE   "date is greater than beginning of this financial year"

"Ant" wrote:

> Hi Chandra,
> Thanks very much for that help. It works. I was actually thinking on the way
> home, "I wonder if I change the date around..." But I would never have been
> able to work out that date string in the WHERE clause. I understand Transact
> SQL language but not good at working out queries like this one. Can you
> recommend any good books on useful business queries that I might gain more
> understanding by? Kind of techniques, rather than syntax. If anybody knows,
> it would be great.
>
> Chandra, Thanks heaps!
>
>
> "Chandra" wrote:
>
> > Hi Ant
> > Then it might be with the date format.
> >
> > just try this way.
> > FYI: i changed from 01/06 to 06/01
> >
> > SELECT  date, payment
> > FROM    table
> > WHERE  
> > date > (
> > SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > datepart(yy,getdate())-1
> >    ELSE datepart(yy,getdate())
> >    END  as varchar(4)))
> >
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://groups.msn.com/SQLResource/
> > ---------------------------------------
> >
> >
> >
> > "Ant" wrote:
> >
> > > Hello Chandra,
> > > Thank you very much for your idea. It looks great. It almost works.
> > > Unfortunately it returns records from January 31-2004, however the earliest
> > > record should be no earlier than the previous June. I'll play around with it
> > > but if you have any ideas, I'd be most grateful.
> > >
> > > Many thanks
> > >
> > > Ant
> > >
> > > "Chandra" wrote:
> > >
> > > > Hi Ant
> > > >
> > > > Probably this can solve the problem
> > > >
> > > >
> > > > SELECT  date, payment
> > > > FROM    table
> > > > WHERE  
> > > > date > (
> > > > SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > > > datepart(yy,getdate())-1
> > > >    ELSE datepart(yy,getdate())
> > > >    END  as varchar(4)))
> > > >
> > > > Please let me know if there is any thing that u wanted to know.
> > > >
> > > > --
> > > > best Regards,
> > > > Chandra
> > > > http://chanduas.blogspot.com/
> > > > http://groups.msn.com/SQLResource/
> > > > ---------------------------------------
> > > >
> > > >
> > > >
> > > > "Ant" wrote:
> > > >
> > > > > Hi, I'm new to SQL. I need to return a recordset which will include only
> > > > > records with a date after previous June. (Could be this year or last) It's
> > > > > for tax purposes. How can i specify that it's the last June, not 'June' as a
> > > > > value.
> > > > >
> > > > > Hope somebody can help. Many thanks for your ideas.
> > > > > Ant
> > > > >
> > > > > SELECT  date,
> > > > >              payment
> > > > > FROM    table
> > > > > WHERE   "date is greater than beginning of this financial year"

"Chandra" wrote:

> Hi Ant
> I can understand your curiosity. I never came across and book that can give
> you an idea on how to do it. Most of the books give u only the syntax.
> To get more ideas on how to build business logic, u need to practice. Just
> think of a situation and try to build the query for yourself. One place u can
> find to get a lot of ideas is newsgroups and user-groups.
> There are also a lot of websites that are developed by MVPs. Just go through
> them, u might pick up ideas and techniques.
>
> Please let me know if you would like to know anything else
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---------------------------------------
>
>
>
> "Ant" wrote:
>
> > Hi Chandra,
> > Thanks very much for that help. It works. I was actually thinking on the way
> > home, "I wonder if I change the date around..." But I would never have been
> > able to work out that date string in the WHERE clause. I understand Transact
> > SQL language but not good at working out queries like this one. Can you
> > recommend any good books on useful business queries that I might gain more
> > understanding by? Kind of techniques, rather than syntax. If anybody knows,
> > it would be great.
> >
> > Chandra, Thanks heaps!
> >
> >
> > "Chandra" wrote:
> >
> > > Hi Ant
> > > Then it might be with the date format.
> > >
> > > just try this way.
> > > FYI: i changed from 01/06 to 06/01
> > >
> > > SELECT  date, payment
> > > FROM    table
> > > WHERE  
> > > date > (
> > > SELECT '06/01/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > > datepart(yy,getdate())-1
> > >    ELSE datepart(yy,getdate())
> > >    END  as varchar(4)))
> > >
> > >
> > > --
> > > best Regards,
> > > Chandra
> > > http://chanduas.blogspot.com/
> > > http://groups.msn.com/SQLResource/
> > > ---------------------------------------
> > >
> > >
> > >
> > > "Ant" wrote:
> > >
> > > > Hello Chandra,
> > > > Thank you very much for your idea. It looks great. It almost works.
> > > > Unfortunately it returns records from January 31-2004, however the earliest
> > > > record should be no earlier than the previous June. I'll play around with it
> > > > but if you have any ideas, I'd be most grateful.
> > > >
> > > > Many thanks
> > > >
> > > > Ant
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > > Hi Ant
> > > > >
> > > > > Probably this can solve the problem
> > > > >
> > > > >
> > > > > SELECT  date, payment
> > > > > FROM    table
> > > > > WHERE  
> > > > > date > (
> > > > > SELECT '01/06/' + CAST(CASE WHEN datepart(mm,getdate()) < 6 THEN
> > > > > datepart(yy,getdate())-1
> > > > >    ELSE datepart(yy,getdate())
> > > > >    END  as varchar(4)))
> > > > >
> > > > > Please let me know if there is any thing that u wanted to know.
> > > > >
> > > > > --
> > > > > best Regards,
> > > > > Chandra
> > > > > http://chanduas.blogspot.com/
> > > > > http://groups.msn.com/SQLResource/
> > > > > ---------------------------------------
> > > > >
> > > > >
> > > > >
> > > > > "Ant" wrote:
> > > > >
> > > > > > Hi, I'm new to SQL. I need to return a recordset which will include only
> > > > > > records with a date after previous June. (Could be this year or last) It's
> > > > > > for tax purposes. How can i specify that it's the last June, not 'June' as a
> > > > > > value.
> > > > > >
> > > > > > Hope somebody can help. Many thanks for your ideas.
> > > > > > Ant
> > > > > >
> > > > > > SELECT  date,
> > > > > >              payment
> > > > > > FROM    table
> > > > > > WHERE   "date is greater than beginning of this financial year"