select sum, ... margin for each month

[codrutza]

Hello

SQL Server 2000
Maybe you help me with this.

I have to write a stored procedure.
I have to print:

2010 until last full month (May)
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
jobs
kg
sales
cost
profit
margin
2009 all
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
jobs
kg
sales
cost
profit
margin

I have this:

CREATE procedure [dbo].[Z_comp] @Year int as
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @c_result INT

SET @DateStart = cast(@Year-1 as varchar(4))+'0101'
-- Set date start to be 01/01/year-1 00:00:00

SET @DateEnd = cast(@Year+1 as varchar(4))+'0101'
-- Set date end to be 01/01/year+1 00:00:00

IF @Year= YEAR(GETDATE())
-- Current year, must get last full month
BEGIN
SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1
END


SELECT …..


Select brings in the report every record; after that I make the sum, profit, ….., margin (if sum sales<>0 then sum profit/sum sales*100 for each month), for each year

I want to obtain the sums, …., margin from the stored procedure. How shall I write this?
Thank you

 

[markros]

You can write it using case statements, e.g.

;with cte as (select Month(DateFld) as [Mnth], Year(DateFld) as [Year], sum(Sales) as Sales, count(JoibID) as Jobs, ... from myTable where DateFld >=@StartDate and DateFld <@EndDate group by year(DateFld), Month(DateFld))

select sum(case when Year = @Year and Mnth = 1 then Sales end)
as Jan, ...

from cte

PluralSight Learning Library

 

[codrutza]

Thank you for reply.
What's cte? I am very new in SP. Can you recommend a book?
Where do I write your SELECT if in my SELECT I have to retrieve fields (to have some conditions on them), not sums, something like that:
SELECT
Customers.AccNo,
....
JobA.Cargo,
JobA.CompanyJob,
JobA.CompanyPartner,
JobA.TransportA,

...
WHERE
JobA.Cargo IN ('F/F', 'L/F')
And Customers.AccNo=”111”
And JobA.TransportA=”tttt”
….
I know that if I use group by. I have to put all the fields in aggregates.

 

[markros]

Can you post DDL of your table(s), some input data and desired output?

CTE is a short term for Common Table Expression - they were introduced in SQL Server 2005 and it's one of my favorite T-SQL language enhancements.

Take a look at this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

that provides some information about typical applications of CTE

PluralSight Learning Library

 

[codrutza]

I have SQL Server 2000 and I have to develop a report with Crystal Reports, based on SP. It's working fine when retrives every record and I do the calculation, everything in the report, (after the data is retrieved). But I wanted to bring the sums, etc all calculated in the report, because the report would be faster.
I don't know how do I write in a SP:

CREATE procedure [dbo].[Z_comp] @Year int asDECLARE @DateStart datetime DECLARE @DateEnd datetimeDECLARE @c_result INT SET @DateStart = cast(@Year-1 as varchar(4))+'0101' -- Set date start to be 01/01/year-1 00:00:00 SET @DateEnd = cast(@Year+1 as varchar(4))+'0101' -- Set date end to be 01/01/year+1 00:00:00 IF @Year= YEAR(GETDATE()) -- Current year, must get last full month BEGIN SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1 END
SELECT
Customers.AccNo,
....
JobA.Cargo,
JobA.CompanyJob,
JobA.CompanyPartner,
JobA.TransportA,

...

Sum(JobA.sales) on Jan on previous year,
Sum(JobA.sales) on Feb on previous year,
….
Sum(JobA.sales) on Dec on previous year,
…..
Sum(JobA.sales) on Jan on current year,
……

WHERE
JobA.Cargo IN ('F/F', 'L/F')
And Customers.AccNo="111"
And JobA.TransportA="tttt"


I do just the reports in a program developed by programers. I am working just part time. Now I am at home.

 

[markros]

SELECT 
Customers.AccNo, 
....
JobA.Cargo, 
JobA.CompanyJob, 
JobA.CompanyPartner, 
JobA.TransportA, 

...

Sum(case when Year(DateFld) = @PrevYear and month(DateFld) = 1 then JobA.sales end) as [Jan on previous year],
Sum(case when Year(DateFld) = @PrevYear 
and month(DateFld) = 2 then JobA.sales end) as [Feb on previous year],
......

WHERE
JobA.Cargo IN ('F/F', 'L/F')
And Customers.AccNo="111"
And JobA.TransportA="tttt"

------------------
In other words, you can include case expressions to calculate sum for each month and year.

[url=http://www.pluralsight.com/main/olt/?ac=1905603345]PluralSight Learning Library[/url]

 

[codrutza]

Thank you for your time. I'll try this when I am at work.