December 13, 2011 at 2:20 pm
I want to sum columns based on paramters selected.
The problem is that I have a table that collects funds spend by month in columns....
Example:
april may june july august sept oct nov dec jan
ldr_amt_1ldr_amt_2ldr_amt_3ldr_amt_0ldr_amt_4ldr_amt_5ldr_amt_6ldr_amt_7ldr_amt_8ldr_amt_9
2633.711677.264275.980.00 1982.213172.252766.633150.253767.873020.01
Now I want to sum the columns based on what the user selects...
for example a user needs to know how much is spent between june and july
or how much is spend between april to november, etc....
Regards
December 13, 2011 at 2:42 pm
between
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 2:45 pm
Looking at this again I don't think between is your answer. The confusion lies in the way you posted this. It is very unclear what your table looks like, the data looks like and what you are trying to get as output. Can you explain clearly, exactly what you are trying to do? It would probably help if you posted ddl (create table script), sample data (insert statements) in addition. Take a look at the first link in my signature for examples and help in collecting and posting this info.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 3:58 pm
Hello Guys,
Below is the Information
CREATE TABLE [dbo].[fund_balance](
[ldr_entity_id] [char](5) NOT NULL,
[processing_yr] [smallint] NOT NULL,
[rollup_structure_id] [char](10) NOT NULL,
[cost_centre] [char](5) NOT NULL,
[item] [char](5) NOT NULL,
[programme] [char](3) NOT NULL,
[activity] [char](4) NOT NULL,
[btl_sof] [char](8) NOT NULL,
[fund_amt_class] [char](2) NOT NULL,
[udak_surr_key] [numeric](18, 0) NOT NULL,
[curr_code] [char](3) NOT NULL,
[budget_surr_key] [numeric](18, 0) NOT NULL,
[annual_amt] [money] NOT NULL,
[ldr_amt_0] [money] NOT NULL,
[ldr_amt_1] [money] NOT NULL,
[ldr_amt_2] [money] NOT NULL,
[ldr_amt_3] [money] NOT NULL,
[ldr_amt_4] [money] NOT NULL,
[ldr_amt_5] [money] NOT NULL,
[ldr_amt_6] [money] NOT NULL,
[ldr_amt_7] [money] NOT NULL,
[ldr_amt_8] [money] NOT NULL,
[ldr_amt_9] [money] NOT NULL,
[ldr_amt_10] [money] NOT NULL,
[ldr_amt_11] [money] NOT NULL,
[ldr_amt_12] [money] NOT NULL,
[ldr_amt_13] [money] NOT NULL,
[ldr_amt_14] [money] NOT NULL,
[chgstamp] [int] NOT NULL
INSERT INTO [DBSfund].[dbo].[fund_balance]
([ldr_entity_id],[processing_yr],[rollup_structure_id],[cost_centre]
,[item],[programme],[activity],[btl_sof],[fund_amt_class]
,[udak_surr_key],[curr_code],[budget_surr_key],[annual_amt]
,[ldr_amt_0],[ldr_amt_1],[ldr_amt_2] ,[ldr_amt_3],[ldr_amt_4]
,[ldr_amt_5],[ldr_amt_6],[ldr_amt_7],[ldr_amt_8],[ldr_amt_9]
,[ldr_amt_10],[ldr_amt_11],[ldr_amt_12],[ldr_amt_13],[ldr_amt_14]
,[chgstamp])
VALUES
('GOB1',
2012,
'FUND12',
19017,340,'ALL','ALL','REC','EX',354922766,'BZE',850732716,
26446.17,0.00,2633.71,1677.26,4275.98,1982.21,3172.25,2766.63,3150.25,
3767.87,3020.01,0.00,0.00,0.00,0.00,0.00,249)
GO
The information provided illustration how much is spent by month for a particular accounting combination...
Current I have a report that tell them how much is spend already.... That data is current information...
What the users want now is to be able to say , give me a report on how much is spent between april and June or
for some between april and november .. etc...
My problem is how will I get the query to SUM the period selected...
I just need some hints on how columns get passed as parameters and how to SUM a range of colums
Hope this helps to understand the problem better..
Regards
Bol
December 14, 2011 at 7:14 am
Thanks for the ddl and sample data. It is totally unclear what your data is and what you are trying to do.
My problem is how will I get the query to SUM the period selected...
I just need some hints on how columns get passed as parameters and how to SUM a range of colums
Hope this helps to understand the problem better..
I don't see anything in your data that has anything to do with dates. In your first post it sounded like you have a column of ldr_amtx for each month but you have 15 of them. You can't actually pass a column as a parameter. You can only pass valid sql data types and a column is not a datatype. You could pass a varchar that is the name of the column and build dynamic sql. I have a feeling this is going to get ugly because your data is not normalized.
To help with your explanation maybe you could explain the logic as it relates to your columns...
for example a user needs to know how much is spent between june and july
or how much is spend between april to november, etc....
There is no way for myself or anybody else to know from your data structures and ambiguous names what might the dates are.
how to SUM a range of colums
I can try to answer that generically but am not able to help in your situation due to unclear requirements. Let's say you know that you want the sum of ldr_amt2 - ldr_amt5.
select sum(ldr_amt2 + ldr_amt3 + ldr_amt4 + ldr_amt5) as MyTotal from fund_balance
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2011 at 10:38 am
Hello Sean.
I believe you answer my question that a column cannot be passed as a parameter...
The informationI included is the entire table for fund_balance in the database... It does not have any dates.
note that ldr_amt_1 is april,
ldr_amt_2 is may
ldr_amt_3 is june
and so on....
It's very tricky on how I will get this information....
Regards
Bol
December 14, 2011 at 10:44 am
Yes it can. You just need to separate the logic of monthname & monthvalue.
This code assumes that you always need the same amount of months. This also works with or without data for any of the months in the period.
Use name logic as in cross apply to fetch the month's name in a different dataset. You can return it in the same as this one but it duplicates a lot of data for nothing.
SELECT
S.NoArticle
, S.Description
, S.ParametreReportProduit
, SUM(CASE WHEN dtYM.ixPivot = 01 THEN S.Quantity
ELSE 0
END) AS Qty_Month01
, SUM(CASE WHEN dtYM.ixPivot = 02 THEN S.Quantity
ELSE 0
END) AS Qty_Month02
, SUM(CASE WHEN dtYM.ixPivot = 03 THEN S.Quantity
ELSE 0
END) AS Qty_Month03
, SUM(CASE WHEN dtYM.ixPivot = 04 THEN S.Quantity
ELSE 0
END) AS Qty_Month04
, SUM(CASE WHEN dtYM.ixPivot = 05 THEN S.Quantity
ELSE 0
END) AS Qty_Month05
, SUM(CASE WHEN dtYM.ixPivot = 06 THEN S.Quantity
ELSE 0
END) AS Qty_Month06
, SUM(CASE WHEN dtYM.ixPivot = 07 THEN S.Quantity
ELSE 0
END) AS Qty_Month07
, SUM(CASE WHEN dtYM.ixPivot = 08 THEN S.Quantity
ELSE 0
END) AS Qty_Month08
, SUM(CASE WHEN dtYM.ixPivot = 09 THEN S.Quantity
ELSE 0
END) AS Qty_Month09
, SUM(CASE WHEN dtYM.ixPivot = 10 THEN S.Quantity
ELSE 0
END) AS Qty_Month10
, SUM(CASE WHEN dtYM.ixPivot = 11 THEN S.Quantity
ELSE 0
END) AS Qty_Month11
, SUM(CASE WHEN dtYM.ixPivot = 12 THEN S.Quantity
ELSE 0
END) AS Qty_Month12
, SUM(S.Quantity) AS Qty_Total
, S.[Unit Cost]
, dbo.FnNavMoneyRounding(SUM(S.Quantity * S.[Unit Cost])) AS Total_Cost
, SUM(S.Quantity) / 12 AS Qty_AVG
, S.DaysLeadTime
INTO
#pivot
FROM
#Sales S
INNER JOIN (
SELECT
Y
, M
, ROW_NUMBER() OVER ( ORDER BY Y, M ) AS ixPivot
FROM
(
SELECT DISTINCT
Y
, M
FROM
dbo.Calendar C
WHERE
C.dt BETWEEN @BeginDate AND @EndDate
) dtYearMonths
) dtYM
ON S.Annee = dtYM.Y
AND S.Mois = dtYM.M
GROUP BY
S.NoArticle
, S.Description
, S.ParametreReportProduit
, S.[Unit Cost]
, S.DaysLeadTime
December 14, 2011 at 10:52 am
Thanks very much Ninja's_RGR'us
I will analyse the sql script...
regards
Bol
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply