"ricky" wrote:

> Hi
>
> This is somewhat a trivial question, but I have query with some Date Params
> in the WHERE clause, to filter on a certain period, or up to a certain
> period (for historical analysis).
>
> e.g
>
> SELECT
> SUM(Payment) AS Payment,
> SUM(Recovery) AS Recovery,
> MONTH(TransDate) AS TransMonth,
> YEAR(TransDate) AS TransYear)
>
> FROM AccountLedger
>
> GROUP BY
> MONTH(TransDate),
> YEAR(TransDate)
>
> HAVING
>
> MONTH(TransDate) <= 6
> YEAR(TransDate)  <= 2006
>
> I was hoping this would retrieve all transactions up to and including end of
> June 2006.  However, this does not seem to be the case, I think it is only
> bringing back transactions for months 1-6 for all years?
>
> It appears that it is excluding months 7-12, why is this so?
>
> Kind Regards
>
> Ricky
>
>
>

"Baj-SGC818" <BajSGC***@discussions.microsoft.com> wrote in message
news:B25F1ADA-F9B9-4333-8D1A-3A5C7DE345AE@microsoft.com...
> Hi Ricky
>
> It looks like you have an AND missing in your HAVING Clause so only the
> first line is executing
>
> All the best
>
>
>
> "ricky" wrote:
>
> > Hi
> >
> > This is somewhat a trivial question, but I have query with some Date
Params
> > in the WHERE clause, to filter on a certain period, or up to a certain
> > period (for historical analysis).
> >
> > e.g
> >
> > SELECT
> > SUM(Payment) AS Payment,
> > SUM(Recovery) AS Recovery,
> > MONTH(TransDate) AS TransMonth,
> > YEAR(TransDate) AS TransYear)
> >
> > FROM AccountLedger
> >
> > GROUP BY
> > MONTH(TransDate),
> > YEAR(TransDate)
> >
> > HAVING
> >
> > MONTH(TransDate) <= 6
> > YEAR(TransDate)  <= 2006
> >
> > I was hoping this would retrieve all transactions up to and including
end of
> > June 2006.  However, this does not seem to be the case, I think it is
only
> > bringing back transactions for months 1-6 for all years?
> >
> > It appears that it is excluding months 7-12, why is this so?
> >
> > Kind Regards
> >
> > Ricky
> >
> >
> >

"Chris Lim" <blackca***@hotmail.com> wrote in message
news:1154083766.376363.242070@p79g2000cwp.googlegroups.com...
> ricky wrote:
> > I was hoping this would retrieve all transactions up to and including
end of
> > June 2006.  However, this does not seem to be the case, I think it is
only
> > bringing back transactions for months 1-6 for all years?
> >
> > It appears that it is excluding months 7-12, why is this so?
>
> Take out the HAVING clause and use a WHERE clause instead. The former
> gets applied after grouping has been done, whereas the latter gets
> applied before grouping (which is what you want).
>
> e.g
>
>  SELECT
>  SUM(Payment) AS Payment,
>  SUM(Recovery) AS Recovery,
>  MONTH(TransDate) AS TransMonth,
>  YEAR(TransDate) AS TransYear)
>
>  FROM AccountLedger
>  WHERE TransDate < 1 Jul 2006'
>  GROUP BY
>  MONTH(TransDate),
>  YEAR(TransDate)
>
> The reason you were only getting months 1-6 in all years is because you
> were treating the month and year separately (i.e. months 1 - 6, years
> <= 2006). You just need to say < '1 Jul 2006' to get everything up to
> end of June.
>
> Chris
>

> Hi
>
> This is somewhat a trivial question, but I have query with some Date Params
> in the WHERE clause, to filter on a certain period, or up to a certain
> period (for historical analysis).
>
> e.g
>
> SELECT
> SUM(Payment) AS Payment,
> SUM(Recovery) AS Recovery,
> MONTH(TransDate) AS TransMonth,
> YEAR(TransDate) AS TransYear)
>
> FROM AccountLedger
>
> GROUP BY
> MONTH(TransDate),
> YEAR(TransDate)
>
> HAVING
>
> MONTH(TransDate) <= 6
> YEAR(TransDate)  <= 2006
>
> I was hoping this would retrieve all transactions up to and including end of
> June 2006.  However, this does not seem to be the case, I think it is only
> bringing back transactions for months 1-6 for all years?
>
> It appears that it is excluding months 7-12, why is this so?
>
> Kind Regards
>
> Ricky
>
>

"Tracy McKibben" <tr***@realsqlguy.com> wrote in message
news:uUEztfksGHA.4752@TK2MSFTNGP02.phx.gbl...
> ricky wrote:
> > Hi
> >
> > This is somewhat a trivial question, but I have query with some Date
Params
> > in the WHERE clause, to filter on a certain period, or up to a certain
> > period (for historical analysis).
> >
> > e.g
> >
> > SELECT
> > SUM(Payment) AS Payment,
> > SUM(Recovery) AS Recovery,
> > MONTH(TransDate) AS TransMonth,
> > YEAR(TransDate) AS TransYear)
> >
> > FROM AccountLedger
> >
> > GROUP BY
> > MONTH(TransDate),
> > YEAR(TransDate)
> >
> > HAVING
> >
> > MONTH(TransDate) <= 6
> > YEAR(TransDate)  <= 2006
> >
> > I was hoping this would retrieve all transactions up to and including
end of
> > June 2006.  However, this does not seem to be the case, I think it is
only
> > bringing back transactions for months 1-6 for all years?
> >
> > It appears that it is excluding months 7-12, why is this so?
> >
> > Kind Regards
> >
> > Ricky
> >
> >
>
> This won't work because you're telling it you want records that have a
> month <= 6 AND a year <= 2006.  August/2005 is excluded because the
> month is not <= 6.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com