sql server programming: [quoted text, click to view]

That is how we feel, too :) Please post DDL, so that people do not
have to guess what the keys, constraints, Declarative Referential
Integrity, data types, etc. in your schema are. Sample data is also a
good idea, along with clear specifications. It is very hard to debug
code when you do not let us see it.

You personal pseudo-code is wrong; you need a pair of date ranges
either in the table itself or encoding in another table if you ever
want your code to port and work.

I also see that yourmpersonal rules are that SUM() is in uppercase
because it is a reserved word and no other reserved words, that commas
go at the start of the punch card, we end names with an underscore to
make it hell to read. etc.

Here is a quick improvement on what you have. Look up the basic method
for a reporting table auxiliary table
SELECT M.reort_period_name,
SUM(H.vague_nameless_amt) AS gross_margin,
SUM(CASE WHEN H.somekind_acct IN ('31000', '31900', '36000',
'36100', '36600','36200') THEN vague_nameless_amt ELSE
0.00 END) AS tot_31000
FROM HBS_GLJX AS H, MonthReportPeriods AS M
WHERE H.post_date BETWEEN M.start_date AND M.end_date;
GROUP BY M.reort_period_name;

Never split temporal data into columns -- a duration in time is
alwaysaatomic.

select
SUM(hbs_gljx.amount_) AS GrossMargin
, SUM(CASE WHEN hbs_gljx.account_ = '31000' THEN hbs_gljx.amount_ ELSE 0
END) AS Sumof31000
, hbs_gljx.post_year_
, hbs_gljx.post_month_

from hbs_gljx
where
hbs_gljx.post_year_ = @yearpost --and hbs_gljx.post_month_ = @monthpost
AND hbs_gljx.account_ IN ('31000', '31900', '36000', '36100',
'36600','36200' )
Group By hbs_gljx.post_year_ , hbs_gljx.post_month_
Order By hbs_gljx.post_year_ , hbs_gljx.post_month_



[quoted text, click to view]

I am trying to accomplish this

GrossMargin Sumof31000 Year Month
733747.10 x 2006 1
745466.90 x 2006 2
792383.03 x 2006 3

Here is my qry
declare @yearpost int
set @yearpost = '2006'

select
SUM(hbs_gljx.amount_) AS GrossMargin, (SELECT SUM(hbs_gljx.amount_)
FROM hbs_gljx WHERE hbs_gljx.account_ = '31000' and hbs_gljx.post_year_ =
@yearpost )
, hbs_gljx.post_year_
, hbs_gljx.post_month_

from hbs_gljx
where
hbs_gljx.post_year_ = @yearpost --and hbs_gljx.post_month_ = @monthpost
AND hbs_gljx.account_ IN ('31000', '31900', '36000', '36100',
'36600','36200' )

GROUP by hbs_gljx.post_month_, hbs_gljx.post_year_

When I run the qry, I get the sum for the whole year for sumof31000 and NOT
for each month.

I am trying to get the sumof31000 acount for each month using one SQL
statement, which in the end I will divide the two and will give me Gross
Margin %.


Any help is appreciated.

Tom Cooper - That worked perfectly, thank you. Knew I was close.




[quoted text, click to view]