Apache OpenOffice Community Forum

dazedandconfused wrote:This is exactly what I was wondering and it worked, however it doesn't display in dollars even though the column in my table displays in dollars. Now that you mention it I would like to run the report at the beginning of the month to show sales only for the prior month. How would I do that?

Sliderule wrote:You said / asked:

dazedandconfused wrote:This is exactly what I was wondering and it worked, however it doesn't display in dollars even though the column in my table displays in dollars. Now that you mention it I would like to run the report at the beginning of the month to show sales only for the prior month. How would I do that?

Can you please post here the SQL ( Structured Query Language ) . . . that is . . . the SELECT statement . . . that you are using . . . AND . . . please include in your post . . . the TABLE and FIELD NAME that contains the DATE column.

Sliderule

SELECT "SALESMAN", SUM("Order Price") FROM "PO ITEMS" GROUP BY "SALESMAN"

1. Please try this Query . . . and . . . it will prompt user to input two numbers:
   
   1. A month number ( 1 to 12 ) . . . for example . . . 9
   2. A year number as a 4 digit number . . . for example . . . 2013

   Code: Select all

   SELECT 
      "PO ITEMS"."SALESMAN",
      TO_CHAR("PO INVOICE"."Date",'MM-YYYY') as "Month", 
      SUM("PO ITEMS"."Order Price") AS "Total Order Price"
   
   FROM "PO ITEMS",
        "PO INVOICE" 
   
   WHERE "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO"
     AND MONTH("PO INVOICE"."Date") = :Enter_Month_Number
     AND YEAR("PO INVOICE"."Date") = :Enter_Year_Number_as_YYYY
   
   GROUP BY "PO ITEMS"."SALESMAN",
             TO_CHAR("PO INVOICE"."Date",'MM-YYYY')

2. Alternate Query, that will always return data ONLY from the prior month ( prior month from CURRENT_DATE ) with NO required input ( entries ) from user:
   

   Code: Select all

   SELECT 
      "PO ITEMS"."SALESMAN", 
      TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
      SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 
   
   FROM "PO ITEMS", 
        "PO INVOICE" 
   
   WHERE "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
     AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
     AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) GROUP BY "PO ITEMS"."SALESMAN", TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Is there a way to change it so it shows the salesman name instead of their number?

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
  AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) 

GROUP BY "SALESMAN"."Salesman",
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND MONTH( "PO INVOICE"."Date" ) = :Enter_Month_Number 
  AND YEAR( "PO INVOICE"."Date" ) = :Enter_Year_Number_as_YYYY 

GROUP BY "SALESMAN"."Salesman", 
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Sliderule wrote:

Is there a way to change it so it shows the salesman name instead of their number?

I do NOT understand the 'relationship' you want / need . . . since . . . in the table "SALESMAN" . . . the Primary Key is "ID" . . . an INTEGER . . . but . . . in your other tables . . . for example . . . "PO INVOICE" and "PO ITEMS" . . . the column "Salesman" are VARCHAR . . . NOT, also INTEGER . . . therefore . . . you canNOT relate them.

I suspect . . . after you change either / both of those tables / column to an INTEGER, with the sample data . . . it should work as you want.

I strongly recommend you perform the above . . . but . . . as a TEST ( sample ) I coded the following Query . . . changing . . . in the CODE . . . the value of "PO ITEMS"."SALESMAN" to an INTEGER ( using the COALESCE function ) . . . and . . . when it ( in the table ) is NULL ( undefined ) defaulted it to an integer of 1.

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
  AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) 

GROUP BY "SALESMAN"."Salesman",
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Prompt for Month Number ( 1 to 12 ), and Year Number ( as 4 digit number ):

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND MONTH( "PO INVOICE"."Date" ) = :Enter_Month_Number 
  AND YEAR( "PO INVOICE"."Date" ) = :Enter_Year_Number_as_YYYY 

GROUP BY "SALESMAN"."Salesman", 
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Copy of database.odb

I do not understand your other question / comment . . . since I do NOT know where / how you entered the data. Also . . . it has nothing to do with this original post . . . so . . . if necessary . . . please start another forum topic.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

dazedandconfused wrote:All my primary keys are INTEGER'S. Salesman is a VARCHAR in all tables. I don't understand why I'd change it to an integer? I wouldn't be able to enter their name that way would I?

dazedandconfused wrote:Thanks. I can't figure out how to have it display USD instead of a number. Is there a way to do that?

Sliderule wrote:You have NOT told me what your output is . . . a Query, a Form, a Report, other?

Sliderule

Sliderule wrote:The way any Number ( prefix, thousands separator, number of decimal places, choice of character representing a decimal place, etc ), Date, Time, or Boolean field is displayed ( shows up on a screen ) depends on how you FORMAT the output data ( column ), whether a Result Set from a Query, a Form, or, a Report.

1. So, if it is a Query, after running the Query and you have a Result Set, format the column "Total Order Price" the way you want. For instance:
   1. Right click on the output column, "Total Order Price"
   2. From the pop-up, choose: Column Format...
   3. Click on the Format tab button
   4. Under Category, click on Currency
   5. Make any additional modifications, if needed
   6. If you desire to change the Alignment, Click on the Alignment tab button
   7. Make any modifications you desire
   8. Press the OK button
2. For a report, format the field in the report as you desire.

dazedandconfused wrote: What I want to do is have it set to us dollars by default every time I run the query or when I run the report. Is that possible?

Sliderule wrote:
For a report, format the field in the report as you desire.