April 12, 2007 at 4:49 pm
Hello,
I have a query in which the results express monthly revenue per company. The problem that I have is that for each month, a new line is created to express the revenue for a given company for that month. The thing that I want is for there to be one record for the company with each monthly revenue figure represented in the one record. The query will become a stored procedure with parameters. Don't be thrown by the syntax in the beginning of the query, where a UDF is used, look more closely at the SELECT statement:
**************************
DECLARE @Category varchar(8)
DECLARE @Item varchar(30)
DECLARE @Year varchar(4) --use split function in TxnRptg
DECLARE @Month varchar(5) -- use split, or GetAllMonths
DECLARE @MonthTable TABLE
( value varchar(100) Not Null)
SET @Category = 'Units'
SET @Item = 'LOG AUDITING BY DRIVER'
SET @Year = '2007'
SET @Month = 'all'
--Determine if the user wants all months, or just some months
IF NOT @Month = 'All'
BEGIN
INSERT INTO @MonthTable
(value)
SELECT
LTRIM(value)
FROM
TxnRptg.dbo.fn_split(@Month,',')
UPDATE @MonthTable
SET Value = TxnRptg.dbo.fn_ConvertMonth(value)
END
ELSE
BEGIN
INSERT INTO @MonthTable
(value)
SELECT
LTRIM(value)
FROM
TxnRptg.dbo.fn_GetAllMonths()
END
SELECT
lg.Customer AS CustNum,
lg.Company,
ls.Name,
ls.Addr1,
ls.Addr2,
ls.Addr3,
ls.Addr4,
ls.City,
ls.State,
ls.Zip,
ls.StartDate,
ls.ActiveStatus,
CASE
WHEN lg.TxnMonth = 1 THEN ISNULL(SUM(lg.qty),0)
ELSE 0
END AS JanUnits,
CASE
WHEN lg.TxnMonth = 1 THEN ISNULL(SUM(lg.Price),0)
ELSE 0
END AS JanRev,
CASE
WHEN lg.TxnMonth = 2 THEN ISNULL(SUM(lg.qty),0)
ELSE 0
END AS FebUnits,
CASE
WHEN lg.TxnMonth = 2 THEN ISNULL(SUM(lg.Price),0)
ELSE 0
END AS FebRev,
lg.Item
FROM
dbo.LOGS lg
INNER JOIN SalesReporting.dbo.LawsonCustomers ls
ON lg.Company = ls.Company
AND
lg.Customer = ls.Customer
WHERE
lg.Item = @Item
AND
lg.TxnMonth In (Select value From @MonthTable)
AND
lg.TxnYear = @Year
GROUP BY
lg.Customer, lg.Company, ls.Name, ls.Addr1, ls.Addr2, ls.Addr3,
ls.Addr4, ls.City, ls.State, ls.Zip, ls.StartDate, ls.ActiveStatus,
lg.Item, lg.TxnYear, lg.TxnMonth
ORDER BY
CAST(lg.Customer as Int), lg.Company
***********************************************
What do I need to change so that a company's revenue for each month will appear in one record? I tried nesting this witin an inner SELECT statement, but I received syntax errors when I attempted that.
Thank you for your help!
CSDunn
April 12, 2007 at 6:43 pm
Did you mean something like this as far as the revenue calculation?
SELECT
CustNum = lg.Customer
,TxnMonth = lg.TxnMonth
,Revenue = Sum(Isnull(lg.Price,0))
FROM
dbo.Logs AS lg
GROUP BY
lg.Customer
,lg.TxnMonth
ORDER BY
TxnMonth
,CustNum
April 13, 2007 at 8:15 am
The Revenue and Unit calculations have to be expressed in terms of a given month. Thus, the CASE statements that check for the month, then calculate Units and Revenue for that month.
April 13, 2007 at 8:17 am
Did you run the code I posted to see what it returns?
April 13, 2007 at 8:53 am
Sorry, I just ran the posted code, and the following is a sample of the results;
CustNum TxnMonth Revenue
1057 1 51.00
1057 2 68.00
1057 3 127.50
1057 12 93.50
Now I did just discover that the problem I was having with the nested SELECT statement I was trying earlier was due to the fact that I had the outer query expressed in the wrong place. I was trying to execute the outer query before the variable declarations and the test on @Month instead of afterwards. I have the outer query in the correct location now, and I'm getting back the results I expected.
Thank you again for your help!
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply