conditional summing across columns? -- sql server new users

sql server new users : conditional summing across columns?

R
2/13/2007 12:08:43 PM

I can't think of an efficient way to do this - assuming there is one.

The data I receive provides monthly sales values for each salesperson all in
one record:

SalesID PrevJan PrevFeb PrevMar ..... CurrJan CurrFeb
CurrMar .....

I need a view/query that shows the YTD totals for Prev and Curr, pulling the
current YTD month value from a single record in another table in the
database which is set as just a number from 1-12 (table is "siteinfo",
column is "currmo").

So if the latest data in the database was from February (siteinfo.currmo
value is 2), the following fields would be provided:

SalesID PrevYTD CurrYTD

where PrevYTD was the sum of only PrevJan and PrevFeb, and CurrYTD was the
sum of only CurrJan and CurrFeb.

....and if the "currmo" setting was 6 (June) each of PrevYTD and CurrYTD
would know to sum the Jan-Jun columns for each.

Hope that makes sense. I'm not even sure if this is doable....

I appreciate any suggestions.

Steve
2/13/2007 5:32:03 PM

[quoted text, click to view]

You can add columns.
SELECT SalesID
,PrevJan + PrevFeb AS PrevYTD
,CurrJan + CurrFeb AS CurrYTD
FROM dbo.Sales

You didn't include the DDL for your tables or "report". If each
"report" has columns added for the next Prev and Curr months the query
would have to be revised. Are there always 24 columns in a table with
the future months NULL?

You could create a stored procedure that adds 2 to 12 columns
depending on an input parameter.

R
2/14/2007 3:28:41 PM

Hi Steve,
Yes, the table always has 25 columns - 1 for the unique ID, 12 representing
previous month, 12 representing current month.

You wrote: "You could create a stored procedure that adds 2 to 12 columns
depending on an input parameter."

That sounds like what I need. Is there some automatic way SQL could
determine which are the "current months" based on the data itself (noting
the latest CURR month column automatically - it would the the one with
values >0) and use that in the stored procedure to calculate my YTDs?

I am such an SQL novice that I wouldn't know where to begin.

I appreciate your taking the time to respond!

[quoted text, click to view]

Steve
2/15/2007 9:48:46 AM

[quoted text, click to view]

I can't give you any detailed solutions that I know will work for sure
in your invironment but here are a couple of general options.

The report table is not normalized. If the table had four columns,
SalesId, Date, PrevMonth, CurrMonth you would only need to select a
range of records for a given SalesID. Depending on your data, the
number of years in the table or other factors, Date could be a number
from 1 to 12 or you could have Month and Year int columns
or you could have a datetime column.

If you can't normalize the table you could create a procedure with 12
blocks and an input parameter for the month you wanted to run the
report:

IF @MyParam = 1
BEGIN
SELECT SalesID
,PrevJan AS PrevYTD
,CurrJan AS CurrYTD
FROM dbo.Sales
END
ELSE IF @MyParam = 2
BEGIN
SELECT SalesID
,PrevJan + PrevFeb AS PrevYTD
,CurrJan + CurrFeb AS CurrYTD
FROM dbo.Sales
END

.....and so on to ELSE IF @MyParam = 12

Anthony Thomas
2/16/2007 12:00:00 AM

What you need is a CASE statement.

SELECT
a.SALESID

,CASE WHEN b.currmo >= 1 THEN a.PrevJan ELSE 0.0 END +
CASE WHEN b.currmo >= 2 THEN a.PrevFeb ELSE 0.0 END +
...
CASE WHEN b.currmo >= 12 THEN a.PrevDec ELSE 0.0 END
AS PrevYTD

,CASE WHEN b.currmo >= 1 THEN a.CurrJan ELSE 0.0 END +
CASE WHEN b.currmo >= 2 THEN a.CurrFeb ELSE 0.0 END +
...
CASE WHEN b.currmo >= 12 THEN a.CurrDec ELSE 0.0 END
AS CurrYTD

FROM
dbo.your_table AS a

CROSS JOIN
dbo.siteinfo AS b

There are a couple of different ways that you could write that case
statement. Steve showed you another way inside of batch programming using
the IF statement. I would prefer a set-base solution instead, but both
would be valid.

The CROSS JOIN is used when you want all combinations of one table matched
with all combinations of a second. Since the siteinfo table is only 1
record, this shouldn't be a problem. However, be careful. If you ever
cross two large tables, the number of results will be the number of rows
from the first times the number of rows in the second, which grows like N^2,
and is certainly NOT linear. This is, it could be a performance killer.

Lastly, the ellipses "..." are not part of the syntax above; I just didn't
want to type all of the individual lines, but I think you get the point.

One more piece of advice: spend some time on the Relational Theory, and
Database Normalization in particular.

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

R
2/28/2007 12:29:21 PM

Thanks for the help -- I'll have to try it out.

Unfortunately, this is the format we are getting from the client. Because
it's replaced monthly, I'd prefer to find a way to work with it in the
format they provide rather than run it through manipulation processes each
month.

[quoted text, click to view]

Anthony Thomas
3/1/2007 6:59:55 PM

Let us know how it works.

Anthony Thomas

--

[quoted text, click to view]

HX
3/9/2007 4:08:18 PM

Perfect!

Took a little bit to get it running (kind of brain fried and actually
overlooked a spelling error in the table name for 15 minutes - duh!), but it
works!

Don't know what I'd do without this group... Hopefully some day I'll know
this stuff well enough to help someone else.

Thanks, Anthony.

[quoted text, click to view]

Anthony Thomas
3/10/2007 12:52:59 PM

No problem. That's what we are hear for. Also, we were all in your shoes
at one time or another, even still, we each have to pose questions of our
own. Not one of knows everything. You'd be surprised at how much you can
learn trying to answer others questions. We all have had different
experiences.

Good luck.

Anthony Thomas

--

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn