SQL Gurus Welcome here.

well from this forum i am learning SQL, and really dont know why i never used
it before. but i have a Question. the following code will create get the
correct Data. it returns a number of records. in those records i could have
10 agency 1 dept 1 records, and 5 agency 2 dept 2 etc etc...

the query has them listed, i would like a report to show only Agency 1 Dept
1 10 recs not a list of 10 but a summary.

USE F:\foxpro25\lopfi\dbases\checkhdr
SELECT 0
USE F:\lopvfp\newmem\newmem.DBF
SELECT AGENCY,DEPT,;
FROM newmem;
WHERE empdate between checkhdr.procdate and GOMONTH(checkhdr.procdate,1) AND
(INLIST(dept,24,34,35,38)) ;
INTO CURSOR TOM
BROWSE
*report form f:\lopvfp\newmem\listing.frx preview
CLOSE DATA

Dan
Tue Jan 31 15:26:13 CST 2006

You want something like this:

SELECT AGENCY,DEPT, Count(*);
FROM newmem;
WHERE empdate between checkhdr.procdate and
GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
Group by 1,2 ;
INTO CURSOR TOM

Dan

Tom wrote:
> well from this forum i am learning SQL, and really dont know why i
> never used it before. but i have a Question. the following code will
> create get the correct Data. it returns a number of records. in those
> records i could have 10 agency 1 dept 1 records, and 5 agency 2 dept
> 2 etc etc...
>
> the query has them listed, i would like a report to show only Agency
> 1 Dept 1 10 recs not a list of 10 but a summary.
>
> USE F:\foxpro25\lopfi\dbases\checkhdr
> SELECT 0
> USE F:\lopvfp\newmem\newmem.DBF
> SELECT AGENCY,DEPT,;
> FROM newmem;
> WHERE empdate between checkhdr.procdate and
> GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
> INTO CURSOR TOM
> BROWSE
> *report form f:\lopvfp\newmem\listing.frx preview
> CLOSE DATA

Cindy
Tue Jan 31 15:29:28 CST 2006

Hi Tom,

First, there is no need to open the tables - VFP will do that for you if
they are in the search path.

I'm not sure what you mean by "...not a list of 10 but a summary" but I
think you mean that you want an aggregate column and a Group By clause.
Something like:

Select Agency, Dept, ;
Count(*) As NumRecs, Sum(Amount) As TotalOwed ;
From NewMem ;
Where ..... ;
Group By Agency, Dept

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:78B05274-7708-4E92-B7C6-B2E5DC68DEA4@microsoft.com...
> ...the following code will create get the
> correct Data. it returns a number of records. in those records i could
> have
> 10 agency 1 dept 1 records, and 5 agency 2 dept 2 etc etc...
>
> the query has them listed, i would like a report to show only Agency 1
> Dept
> 1 10 recs not a list of 10 but a summary.

> SELECT AGENCY,DEPT,;
> FROM newmem;
> WHERE empdate between checkhdr.procdate and GOMONTH(checkhdr.procdate,1)
> AND
> (INLIST(dept,24,34,35,38)) ;
> INTO CURSOR TOM

Tom
Tue Jan 31 15:40:32 CST 2006

Thank you Dan. Thats Super!! i knew the Group by might be what i needed to
use but wasnt sure how.

Tom

"Dan Freeman" wrote:

> You want something like this:
>
> SELECT AGENCY,DEPT, Count(*);
> FROM newmem;
> WHERE empdate between checkhdr.procdate and
> GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
> Group by 1,2 ;
> INTO CURSOR TOM
>
> Dan
>
> Tom wrote:
> > well from this forum i am learning SQL, and really dont know why i
> > never used it before. but i have a Question. the following code will
> > create get the correct Data. it returns a number of records. in those
> > records i could have 10 agency 1 dept 1 records, and 5 agency 2 dept
> > 2 etc etc...
> >
> > the query has them listed, i would like a report to show only Agency
> > 1 Dept 1 10 recs not a list of 10 but a summary.
> >
> > USE F:\foxpro25\lopfi\dbases\checkhdr
> > SELECT 0
> > USE F:\lopvfp\newmem\newmem.DBF
> > SELECT AGENCY,DEPT,;
> > FROM newmem;
> > WHERE empdate between checkhdr.procdate and
> > GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
> > INTO CURSOR TOM
> > BROWSE
> > *report form f:\lopvfp\newmem\listing.frx preview
> > CLOSE DATA
>
>
>

Tom
Tue Jan 31 16:14:29 CST 2006

Can you have 2 "WHERE" statements in the same select?
WHERE empdate <= GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38))
and
WHERE empdate <= GOMONTH(checkhdr.procdate,2) AND (INLIST(dept,24,34,35,38))
"Dan Freeman" wrote:

> You want something like this:
>
> SELECT AGENCY,DEPT, Count(*);
> FROM newmem;
> WHERE empdate between checkhdr.procdate and
> GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
> Group by 1,2 ;
> INTO CURSOR TOM
>
> Dan
>
> Tom wrote:
> > well from this forum i am learning SQL, and really dont know why i
> > never used it before. but i have a Question. the following code will
> > create get the correct Data. it returns a number of records. in those
> > records i could have 10 agency 1 dept 1 records, and 5 agency 2 dept
> > 2 etc etc...
> >
> > the query has them listed, i would like a report to show only Agency
> > 1 Dept 1 10 recs not a list of 10 but a summary.
> >
> > USE F:\foxpro25\lopfi\dbases\checkhdr
> > SELECT 0
> > USE F:\lopvfp\newmem\newmem.DBF
> > SELECT AGENCY,DEPT,;
> > FROM newmem;
> > WHERE empdate between checkhdr.procdate and
> > GOMONTH(checkhdr.procdate,1) AND (INLIST(dept,24,34,35,38)) ;
> > INTO CURSOR TOM
> > BROWSE
> > *report form f:\lopvfp\newmem\listing.frx preview
> > CLOSE DATA
>
>
>

Cindy
Tue Jan 31 16:25:39 CST 2006

Hi Tom,

You can use And and Or in a Where clause. However, what you've described is
a little confusing since any dates that are less than two months ahead will
also be less than one month ahead. Maybe you want something like:

Where ;
(empdate <= GOMONTH(checkhdr.procdate,1) AND INLIST(dept,<some list
here>)) ;
Or ;
(empdate <= GOMONTH(checkhdr.procdate,2) AND INLIST(dept,<a different
list here>))




--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:6D8E4098-7749-4B9C-986B-70105274A99E@microsoft.com...
> Can you have 2 "WHERE" statements in the same select?
> WHERE empdate <= GOMONTH(checkhdr.procdate,1) AND
> (INLIST(dept,24,34,35,38))
> and
> WHERE empdate <= GOMONTH(checkhdr.procdate,2) AND
> (INLIST(dept,24,34,35,38))

Cindy
Tue Jan 31 17:05:53 CST 2006

Hi Tom,

Select ;
SomeStuffHere1, SomeStuffHere2, ;
Sum(Iif(Between(ProcDate, Date1, Date2), 1, 0)) As Column1, ;
Sum(Iif(Between(ProcDate, Date3, Date4), 1, 0)) As Column2 ;
From ....... ;
Group By SomeStuffHere1, SomeStuffHere2

Get as far as you can and post the SQL you come up with if it still doesn't
work.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:B4C9DCB4-AB75-4631-AF15-4005A211C1FB@microsoft.com...
> Cindy, you have good reason to be confused because my Client is to, and
> its
> made me confused.
>
> we have Closing dates (procdate), what i am trying to list in 2 columns is
> the number of records of people that the hiredate(empdate) falls between
> the
> last procdate and current procdate, then the second list, empdate falls
> between the current procdate and the future procdate. i think. lol, now im
> confused again.
>
> "Cindy Winegarden" wrote:
>
>> Hi Tom,
>>
>> You can use And and Or in a Where clause. However, what you've described
>> is
>> a little confusing since any dates that are less than two months ahead
>> will
>> also be less than one month ahead. Maybe you want something like:
>>
>> Where ;
>> (empdate <= GOMONTH(checkhdr.procdate,1) AND INLIST(dept,<some list
>> here>)) ;
>> Or ;
>> (empdate <= GOMONTH(checkhdr.procdate,2) AND INLIST(dept,<a different
>> list here>))
>>
>>
>>
>>
>> --
>> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
>> cindy_winegarden@msn.com www.cindywinegarden.com
>>
>>
>> "Tom" <Tom@discussions.microsoft.com> wrote in message
>> news:6D8E4098-7749-4B9C-986B-70105274A99E@microsoft.com...
>> > Can you have 2 "WHERE" statements in the same select?
>> > WHERE empdate <= GOMONTH(checkhdr.procdate,1) AND
>> > (INLIST(dept,24,34,35,38))
>> > and
>> > WHERE empdate <= GOMONTH(checkhdr.procdate,2) AND
>> > (INLIST(dept,24,34,35,38))
>>
>>
>>

Tom
Tue Jan 31 16:46:30 CST 2006

Cindy, you have good reason to be confused because my Client is to, and its
made me confused.

we have Closing dates (procdate), what i am trying to list in 2 columns is
the number of records of people that the hiredate(empdate) falls between the
last procdate and current procdate, then the second list, empdate falls
between the current procdate and the future procdate. i think. lol, now im
confused again.

"Cindy Winegarden" wrote:

> Hi Tom,
>
> You can use And and Or in a Where clause. However, what you've described is
> a little confusing since any dates that are less than two months ahead will
> also be less than one month ahead. Maybe you want something like:
>
> Where ;
> (empdate <= GOMONTH(checkhdr.procdate,1) AND INLIST(dept,<some list
> here>)) ;
> Or ;
> (empdate <= GOMONTH(checkhdr.procdate,2) AND INLIST(dept,<a different
> list here>))
>
>
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Tom" <Tom@discussions.microsoft.com> wrote in message
> news:6D8E4098-7749-4B9C-986B-70105274A99E@microsoft.com...
> > Can you have 2 "WHERE" statements in the same select?
> > WHERE empdate <= GOMONTH(checkhdr.procdate,1) AND
> > (INLIST(dept,24,34,35,38))
> > and
> > WHERE empdate <= GOMONTH(checkhdr.procdate,2) AND
> > (INLIST(dept,24,34,35,38))
>
>
>

Bernhard
Wed Feb 01 05:54:48 CST 2006

Hi Tom

> Cindy, you have good reason to be confused because my Client is to, and its
> made me confused.
>
> we have Closing dates (procdate), what i am trying to list in 2 columns is
> the number of records of people that the hiredate(empdate) falls between the
> last procdate and current procdate, then the second list, empdate falls
> between the current procdate and the future procdate. i think. lol, now im
> confused again.

Do you mean something like that:

SELECT AGENCY,DEPT, ;
sum(iif(between(emptdate,lastProcDate,currentProcDate), 1, 0) AS prevCount,;
sum(iif(between(emptdate,currentProcDate,futureProcDate), 1, 0) AS nextCount,;
FROM newmem;
WHERE between(emptdate, lastProcDate, futureProcDate) ;
AND (INLIST(dept,24,34,35,38)) ;
Group by 1,2 ;
INTO CURSOR TOM

Regards
Bernhard Sander

Tom
Wed Feb 01 08:31:16 CST 2006

Bernhard, Cindy; thats close i think but look at this code. when i throw it
to the Frx the data is correct. let me explain a little, New people are
entered in the table with Hire Dates. we give the option to enter a person 1
month in the future as well as the current processing month. my code needs to
show, how many employees were entered in between the last procdate and the
current. then the current and 1 month ahead. i feel i am just missing one
"WHERE" statement in the code.
my report looks like this;
agency Dept New Rec for dt1 New rec for dt2
123 1 3 in question
234 2 4
345 3 5
Total records for dt1 12
i need to add to this "NEW REC FOR DT2" because they could have entered
someone in the future but dont want to charge the agency for it until next
month. this will keep accounting straight for current month. make sense? sort
of!
SELECT AGENCY,DEPT, Count(*);
FROM newmem ;
WHERE empdate between gomonth(checkhdr.procdate,-1)and checkhdr.procdate AND
(INLIST(dept,24,34,35,38));
Group by 1,2;
INTO CURSOR TOM
SELECT TOM
brow
report form f:\lopvfp\newmem\listing.frx preview

"Bernhard Sander" wrote:

> Hi Tom
>
> > Cindy, you have good reason to be confused because my Client is to, and its
> > made me confused.
> >
> > we have Closing dates (procdate), what i am trying to list in 2 columns is
> > the number of records of people that the hiredate(empdate) falls between the
> > last procdate and current procdate, then the second list, empdate falls
> > between the current procdate and the future procdate. i think. lol, now im
> > confused again.
>
> Do you mean something like that:
>
> SELECT AGENCY,DEPT, ;
> sum(iif(between(emptdate,lastProcDate,currentProcDate), 1, 0) AS prevCount,;
> sum(iif(between(emptdate,currentProcDate,futureProcDate), 1, 0) AS nextCount,;
> FROM newmem;
> WHERE between(emptdate, lastProcDate, futureProcDate) ;
> AND (INLIST(dept,24,34,35,38)) ;
> Group by 1,2 ;
> INTO CURSOR TOM
>
> Regards
> Bernhard Sander
>

Tom
Wed Feb 01 10:55:05 CST 2006

cindy, this code is closer to what i want to return in the query, However, if
i could get the 2 dates ranges in different colums it would be perfect, right
now it combines the count in one, i have to have the WHERE and OR statment
on their own.

SELECT AGENCY,DEPT,COUNT(*);
FROM newmem ;
where (empdate between gomonth(checkhdr.procdate,-1) and checkhdr.procdate
AND INLIST(dept,24,34,35,38));
or (empdate between checkhdr.procdate and gomonth(checkhdr.procdate,1) AND
INLIST(dept,24,34,35,38));
Group by 1,2;
INTO cursor TOM
SELECT TOM
brow

"Cindy Winegarden" wrote:

> Hi Tom,
>
> Select ;
> SomeStuffHere1, SomeStuffHere2, ;
> Sum(Iif(Between(ProcDate, Date1, Date2), 1, 0)) As Column1, ;
> Sum(Iif(Between(ProcDate, Date3, Date4), 1, 0)) As Column2 ;
> From ....... ;
> Group By SomeStuffHere1, SomeStuffHere2
>
> Get as far as you can and post the SQL you come up with if it still doesn't
> work.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Tom" <Tom@discussions.microsoft.com> wrote in message
> news:B4C9DCB4-AB75-4631-AF15-4005A211C1FB@microsoft.com...
> > Cindy, you have good reason to be confused because my Client is to, and
> > its
> > made me confused.
> >
> > we have Closing dates (procdate), what i am trying to list in 2 columns is
> > the number of records of people that the hiredate(empdate) falls between
> > the
> > last procdate and current procdate, then the second list, empdate falls
> > between the current procdate and the future procdate. i think. lol, now im
> > confused again.
> >
> > "Cindy Winegarden" wrote:
> >
> >> Hi Tom,
> >>
> >> You can use And and Or in a Where clause. However, what you've described
> >> is
> >> a little confusing since any dates that are less than two months ahead
> >> will
> >> also be less than one month ahead. Maybe you want something like:
> >>
> >> Where ;
> >> (empdate <= GOMONTH(checkhdr.procdate,1) AND INLIST(dept,<some list
> >> here>)) ;
> >> Or ;
> >> (empdate <= GOMONTH(checkhdr.procdate,2) AND INLIST(dept,<a different
> >> list here>))
> >>
> >>
> >>
> >>
> >> --
> >> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> >> cindy_winegarden@msn.com www.cindywinegarden.com
> >>
> >>
> >> "Tom" <Tom@discussions.microsoft.com> wrote in message
> >> news:6D8E4098-7749-4B9C-986B-70105274A99E@microsoft.com...
> >> > Can you have 2 "WHERE" statements in the same select?
> >> > WHERE empdate <= GOMONTH(checkhdr.procdate,1) AND
> >> > (INLIST(dept,24,34,35,38))
> >> > and
> >> > WHERE empdate <= GOMONTH(checkhdr.procdate,2) AND
> >> > (INLIST(dept,24,34,35,38))
> >>
> >>
> >>
>
>
>

Cindy
Thu Feb 02 12:28:18 CST 2006

Hi Tom,

See my post to your other thread. You'll notice that I asked some questions
about what you were trying to do and the answers are described here. You
really should continue posting back to the original thread since your
problem is not with the report but with the SQL code to compile the data.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:DAD334B6-04F8-4D91-BF7D-97F556B1BE19@microsoft.com...
> Bernhard, Cindy; thats close i think but look at this code. ....