Select statement that lists details but also subtotals p

Plateforme Level Extreme

Abonnement

Profil corporatif

Produits & Services

Support

Légal

English

Select statement that lists details but also subtotals p

Message

29/09/1999 21:33:48

Cindy Winegarden
Duke University Medical Center
Durham, Caroline du Nord, États-Unis

29/09/1999 19:43:12

Peter Brama
West Pointe Enterprises
Detroit, Michigan, États-Unis

Information générale

Forum:

Visual FoxPro

Catégorie:

Base de données, Tables, Vues, Index et syntaxe SQL

Titre:

Re: Select statement that lists details but also subtotals p

Divers

Thread ID:

00269748

Message ID:

00270837

Vues:

Pete,

What I meant was

SELECT Client, SUM() AS BegBal ;
FROM ... ;
INTO CURSOR BeginningBalances

SELECT .*, BeginningBalances.BegBal ;
FROM ..., BeginningBalances ;
INTO CURSOR ReportCursor ;
ORDER BY ...

Not a one-step process or normalized data, but easy to follow.

>Cindy,
>
>Thats what I figured but I was under the assumption that a SELECT UNION SELECT needed the same number of fields and types in both selects. If that is the case, how do I "place hold" the unused fields in the first select?
>
>Also, if I just try to do it with a single select (the sum first to a field and then all the others), I only get one record and lose all the detail.
>
>Pete
>
>
>>Peter,
>>
>>I was thinking at first that you needed a separate record for the Balance forward, but you don't. Put the name and the balance forward in the group header and then the detail lines and then the total.
>>
>>To get the balance forward (in each record) you could SLECT SUM(...) for each client and then select it and all of the stuff you detail below, joining on the client number.
>>
>>Your project is making progress!
>>
>>
>>
>>
>>>Cindy,
>>>
>>>I think I understand the UNION but not sure how the records are combined into one cursor.... that and I wish my SELECTS were THAT simple.... here is what I have right now....
>>>
>>>Select client.clientnum, client.clientname, 0.0 as begbal,;
>>> code.codetype,AllTrim(str(code.codenum))+" "+code.codedesc as code, ;
>>> Trim(employee.lastname)+", "+employee.firstname as employee, ;
>>> log.logdate, ;
>>> log.time, ;
>>> log.rate, ;
>>> iif(code.codetype="Time" or code.codetype="Administrative",log.time*log.rate,log.rate) as amount ;
>>> from Timepro!log left join timepro!employee on log.employeeid=employee.employeeid ;
>>> left join timepro!client on log.clientid=client.clientid ;
>>> left join timepro!code on log.codeid=code.codeid ;
>>> where log.firmid=gFirmid and log.logdate<=gEnddate and log.employeeid<>0 and log.clientid<>0 ;
>>> into cursor curswip ;
>>> order by client.clientname, code.codenum,employee
>>>
>>>
>>>Here is the sum statement I need to add for BEGBAL
>>>
>>> sum(iif(code.codetype="Time" and log.logdate>>> iif((code.codetype="Expense" or code.codetype="WIP") and ;
>>> log.logdate>>> iif(code.codetype=="Billings" and ;
>>> log.logdate>>>
>>>
>>>What I need for the end result is all the detail (log), sorted by clientnum and if their is a beginning balance, that amount to be listed at least on the first record (if not all records) for that client so that the report GROUPING section can print it before the detail......
>>>
>>> Mr Smith
>>> Code 103 09/27/99 $50.00
>>> Code 105 09/27/99 $10.00
>>> Total.......... $60.00
>>>
>>> Mr Jones Beg Bal $15.00
>>> Code 99 09/21/99 $10.00
>>> Total.......... $25.00
>>>
>>>
>>>Any suggestions?
>>>
>>>
>>>
>>>
>>>
>>>>>I have a select statement that I use to build a report. It reads records in several files based on a main files entrydate field. Detail is listed for everything from a start to an end date.
>>>>>
>>>>>I need to also summarize (beg bal) everything PRIOR to the start date and that needs to be listed in the client header as a BEGINNING BALANCE.
>>>>>
>>>>>I have the main detail select statement working well. When I add a SUM for records prior to the start date and store that in a field, my 50+ records turns into 1 record. The sum seems to lose all the detail.
>>>>>
>>>>>Any suggestions on how to do this? Do I have to somehow run a seperate select statement to get the beginning balances than combine the two into one cursor for the report? Am I overlooking something (most likely) that would correct this problem?
>>>>>
>>>>>Thanks...
>>>>
>>>>Peter,
>>>>
>>>>I think you need a type field in your report cursor, where the types are BeginningBalance, and CurrentDetail. Then you can populate your cursor with the following:
>>>>
>>>>

>>>>SELECT [BeginningBalance] AS cType, ;
>>>>    SUM(yCost) AS yCost, ;
>>>>    dBeginningOfPeriod - 1 AS dDateField ;
>>>>    FROM MasterTable ;
>>>>    Where dDateField < dBeginningOfPeriod ;
>>>>UNION ALL ;
>>>>    SELECT [CurrentDetail] AS cType, ;
>>>>    yCost, ;
>>>>    dDateField ;
>>>>    FROM MasterTable
>>>>    WHERE BETWEEN(dDateField, dBeginningOfPeriod, dEndOfPeriod) ;
>>>>    ORDER BY 3    && dDateField in 3rd position
>>>>

>>>>
>>>>Note that SUM adds everything in the group, in this case all records which meet the WHERE clause in the first part. Please read about UNION to see if you need the "ALL". Notice that in a UNION query the ORDER columns can only be referred to by number.

Répondre

Fil

Voir

Click here to load this message in the networking platform