Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

SQL 2005 Query problem

I am using SQL 2005 and I am trying to get the total sales numbers by customer for the 2009 year. This information is contained in 3 different tables

ARMAST - the current Header table
ARYMST - the history Header table
ARCUST - the Customer table

My desired results is to have the out put list one line per customer with a total of their invoices for the year. In a perfect world I would be able to supply the date range at the time this query is run. or use it with something like Crystal Reports that would allow the input of the date ranges.

The Query I am using get the totals correct but I can not get one line per customer. And How can I supply the date range at the time the query is run?

Here is my Query.  Thank you for any help with this

select
armast.custno as custno,
arcust.company,
armast.invno,
armast.invdte,
armast.salesmn,
armast.invamt

from armast LEFT JOIN arcust ON arcust.custno = armast.custno

where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V'

Union All

select
arymst.custno as custno,
arcust.company,
arymst.invno,
arymst.invdte,
arymst.salesmn,
arymst.invamt

from arymst

LEFT JOIN arcust ON arcust.custno = arymst.custno
where invdte between '01/01/2009' and '12/31/2009' and artype <> 'R' and arstat <> 'V'
order by invamt desc



0
Wildone63
Asked:
Wildone63
  • 3
  • 2
2 Solutions
 
igni7eCommented:
SELECT
      armast.custno as custno,
      arcust.company,
      armast.invno,
      armast.invdte,
      armast.salesmn,
      SUM(armast.invamt)
FROM
      ARMAST
      LEFT OUTER JOIN ARYMST ON ARMAST.CUSTNO = ARYMST.CUSTNO
      LEFT OUTER JOIN ARCUST ON ARMAST.CUSTNO = ARCUST.CUSTNO
WHERE
      DATEADD(DAY,0,INVDTE) BETWEEN DATEADD(DAY,0,@STARTDTE) AND DATEADD(DAY,0,@ENDDTE)
      AND ARTYPE <> 'R'
      AND ARSTAT <> 'V'
GROUP BY
      armast.custno as custno,
      arcust.company,
      armast.invno,
      armast.invdte,
      armast.salesmn
0
 
igni7eCommented:
You can use SSRS (SQL Server Reporting Services) for this.
Just make this a stored prodcedure that takes in two parameters @STARTDTE and @ENDDTE.
Then use SSRS to call the stored procedure - it will automatically create the date calendar popups for you and generate your report.

Your SP will look like this:
CREATE PROCEDURE sp_GET_INVOICES
@STARTDTE DATETIME,
@ENDDTE DATETIME
AS
SELECT
      armast.custno as custno,
      arcust.company,
      armast.invno,
      armast.invdte,
      armast.salesmn,
      SUM(armast.invamt)
FROM
      ARMAST
      LEFT OUTER JOIN ARYMST ON ARMAST.CUSTNO = ARYMST.CUSTNO
      LEFT OUTER JOIN ARCUST ON ARMAST.CUSTNO = ARCUST.CUSTNO
WHERE
      DATEADD(DAY,0,INVDTE) BETWEEN DATEADD(DAY,0,@STARTDTE) AND DATEADD(DAY,0,@ENDDTE)
      AND ARTYPE <> 'R'
      AND ARSTAT <> 'V'
GROUP BY
      armast.custno as custno,
      arcust.company,
      armast.invno,
      armast.invdte,
      armast.salesmn
0
 
igni7eCommented:
Actually if you are getting the totals correctly this should work:

SELECT
      armast.custno as custno,
      arcust.company,
      armast.invno,
      armast.invdte,
      armast.salesmn,
      armast.invamt
FROM
      ARMAST
      LEFT OUTER JOIN ARYMST ON ARMAST.CUSTNO = ARYMST.CUSTNO
      LEFT OUTER JOIN ARCUST ON ARMAST.CUSTNO = ARCUST.CUSTNO
WHERE
      DATEADD(DAY,0,INVDTE) BETWEEN DATEADD(DAY,0,@STARTDTE) AND DATEADD(DAY,0,@ENDDTE)
      AND ARTYPE <> 'R'
      AND ARSTAT <> 'V'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jotheesh1Commented:
If you want the fields armast.invno, armast.invdte and armast.salesmn returned in the query, then you are not going to get 1 line of data for each customer because there will be many invoices (invno) many invoice dates (invdte) and possibly different salesmen (salesmn) duiring the date range.
Best thing I suggest it use your query that works or convert it into a stored procedure and use Sql Server Reporting Services - thereby you can use use date parameters for your query/SP instead of hard-coding it.
In SSRS reports you can create a group say CustomerGroup  which can have fields like Customer Number, Customer Name and Total Number of Invoices (which can be obtained using the SUM or COUNT SSRS functions) and Total Invoice Amount (using the SSRS =SUM() function).  At the Detail level you can have the rest of the fields like Invoice Number, Invoice Date, Sales Man and Invoice Amount.
------------------------------------------------------------------------------------------------------------------------
Cust#   Cust Name               Invoice                        Invoice Amount     Sales Person   Inv Dt
1           Customer1                110 invoices                   10,500.00
                                                   Invoice 225                                   10,000.00
                                                   Invoice 226                                          500.00
Also you may use the Inital hide feature on the Detail so that when you click on the + sign of the Customer Group, it expands and toggles to show the Detail.  
Hope this helps.
0
 
jotheesh1Commented:
Please correctly read as 2 invoices (instead of 110 invoices) - so that it is more meaningful for the example.
0
 
Wildone63Author Commented:
Thank you both for your answers. You have given me the detail I needed to move forward with this one. As you have shown I have several options and I am working now to decide which is the best plan for my environment.

Thanks Very Much...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now