Count for TRUE cases

Below is a statement that will return both TRUE and FALSE cases:
select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and
month(mtdat)=5
How can I modify the statement above so that it returns only the count for
TRUE casesmtdat is obviously a smalldatetime or datetime that holds (for this
query) values in the month of May 2005. But what is mldat and what
values does it hold? Can you post some DDL & sample data that will
repro the problem?
Also, if you want to do some kind of datetime manipulation (in the
select list) you ought to use the datetime functions (like datediff(),
datepart(), dateadd(), etc.). Subtracting a value (I'm assuming it is
an int or a datetime) from a datetime value is not all that kosher.
I'm going to take a wild guess and say that mldat (or is it supposed to
be m1dat?) stores '1/1/2005'. If you're trying to find the number of
months between those two dates you would say DATEDIFF(mm, mldat,
mtdat). But then again, I just don't have a clue what you're trying to
do here.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
tslu69 wrote:

>Below is a statement that will return both TRUE and FALSE cases:
>select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and
>month(mtdat)=5
>How can I modify the statement above so that it returns only the count for
>TRUE cases
>
>|||mtdat and mldat are both datetime.
The statement below returns records which are either TRUE or FALSE, based on
whether the condition is met.
select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and m
onth(mtdat)=5
e.g. (british date format)
mtdat = '01/01/2005'
mldat = '02/01/2005'
so (mtdat-mldat<=4) returns TRUE
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:OtI
rWj9aFHA.740@.tk2msftngp13.phx.gbl...
mtdat is obviously a smalldatetime or datetime that holds (for this query) v
alues in the month of May 2005. But what is mldat and what values does it h
old? Can you post some DDL & sample data that will repro the problem?
Also, if you want to do some kind of datetime manipulation (in the select li
st) you ought to use the datetime functions (like datediff(), datepart(), da
teadd(), etc.). Subtracting a value (I'm assuming it is an int or a datetim
e) from a datetime value is not all that kosher.
I'm going to take a wild guess and say that mldat (or is it supposed to be m
1dat?) stores '1/1/2005'. If you're trying to find the number of months bet
ween those two dates you would say DATEDIFF(mm, mldat, mtdat). But then agai
n, I just don't have a clue what you're trying to do here.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
tslu69 wrote:
Below is a statement that will return both TRUE and FALSE cases:
select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and
month(mtdat)=5
How can I modify the statement above so that it returns only the count for
TRUE cases|||Are you using SQL Server? This looks like a query for some database
product that has a boolean type, (and that you forgot to actually
count anything). Maybe you want something like
select count(*) as PassId
from LpsMasF1
where year(mtdat)= 2005
and month(mtdat) = 5
and mtdat-mldat <= 4
or better yet,
select count(*) as PassId
from LpsMasF1
where mtdat >= '2005-05-01T00:00:00.000'
and mtdat < '2005-06-01T00:00:00.000'
and mtdat <= mldat + 4
Steve Kass
Drew University
tslu69 wrote:

>Below is a statement that will return both TRUE and FALSE cases:
>select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005 and
>month(mtdat)=5
>How can I modify the statement above so that it returns only the count for
>TRUE cases
>
>|||Did you see Steve's post? Your query is just not syntactically correct
for the T-SQL language. Maybe you mean something like:
select cast(case when (mtdat-mldat <= 4) then 1 else 0 end as bit)
as PassIt from LpsMasFl
where year(mtdat) = 2005
and month(mtdat) = 5
Although Steve's WHERE clause is better (with the normal date
comparisons, without the year() & month() functions). If you really
want the true/false type data then this query would be better (than what
you've got):
select cast(case when (datediff(dd,mtdat,mldat) <= 4) then 1 else 0
end as bit) as PassIt
from LpsMasFl
where mtdat >= '20050501 00:00:00.000'
and mtdat < '20050601 00:00:00.000'
I assume you want the PassIt value to be "true" for rows where the
number of days between mtdat & mldat is four or less and "not true" in
all other cases.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
tslu69 wrote:
> mtdat and mldat are both datetime.
> The statement below returns records which are either TRUE or FALSE,
> based on whether the condition is met.
> select (mtdat-mldat<=4) as PassIt from LpsMasFl where year(mtdat)=2005
> and month(mtdat)=5
> e.g. (british date format)
> mtdat = '01/01/2005'
> mldat = '02/01/2005'
> so (mtdat-mldat<=4) returns TRUE
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:OtIrWj9aFHA.740@.tk2msftngp13.phx.gbl...
> mtdat is obviously a smalldatetime or datetime that holds (for
> this query) values in the month of May 2005. But what is mldat
> and what values does it hold? Can you post some DDL & sample data
> that will repro the problem?
> Also, if you want to do some kind of datetime manipulation (in the
> select list) you ought to use the datetime functions (like
> datediff(), datepart(), dateadd(), etc.). Subtracting a value
> (I'm assuming it is an int or a datetime) from a datetime value is
> not all that kosher.
> I'm going to take a wild guess and say that mldat (or is it
> supposed to be m1dat?) stores '1/1/2005'. If you're trying to
> find the number of months between those two dates you would say
> DATEDIFF(mm, mldat, mtdat). But then again, I just don't have a
> clue what you're trying to do here.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> tslu69 wrote:
>|||Thanks all..
I've use the following statement:
select count(*) from LpsMasFl where year(mtdat)=2005 and month(mtdat)=5 and
mtdat-mldat<=4
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23gBM0f%23aFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Are you using SQL Server? This looks like a query for some database
> product that has a boolean type, (and that you forgot to actually
> count anything). Maybe you want something like
> select count(*) as PassId
> from LpsMasF1
> where year(mtdat)= 2005
> and month(mtdat) = 5
> and mtdat-mldat <= 4
> or better yet,
> select count(*) as PassId
> from LpsMasF1
> where mtdat >= '2005-05-01T00:00:00.000'
> and mtdat < '2005-06-01T00:00:00.000'
> and mtdat <= mldat + 4
> Steve Kass
> Drew University
> tslu69 wrote:
>