Solved

# How to create an equation where it gives the same results when column A equals a certain value.

Posted on 2015-02-18
55 Views
I have created a case statement for when a value is within a certain timeframe.  For instance,
CASE
WHEN DATEDIFF(M, [DateA],[DateB]) <=3 THEN '<= 03 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=6 THEN '<= 06 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=9 THEN '<= 09 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=12 THEN '<= 12 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=24 THEN '<= 24 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=36 THEN '<= 36 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=48 THEN '<= 48 Mos.'
WHEN DATEDIFF(M, [DateA],[DateB]) <=60 THEN '<= 60 Mos.'
ELSE '>   60 Mos.'

For the values that are within each month segment, I want to be able to do a 'sum product' (as done in Excel) of ValuesC and ValuesD and divide it by the sum of the ValuesC.  Therefore, these results for a certain month bucket should be the same.  I've attached an example.  I want to be able to automate the results shown in Column F.

Thank you.
Case---Sum-Product-Example.xlsx
0
Question by:kristibigo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 7
• 6

LVL 24

Expert Comment

ID: 40616961
SUM(ValueC * ValueD) OVER(Partition by [Month Bucket] order by DateA ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) / SUM(ValueC) OVER(Partition by [Month Bucket] order by DateA ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
0

Author Comment

ID: 40617034
Are there any syntax I need to take into consideration?  For instance, when I apply the solution you created just below my case statement within my SQl, I'm receiving "Incorrect Syntax near..." "ROWS", the "[Month Bucket] (which I changed to the correct reference) and the second "Partition".  Is it missing a "Select", comma, parenthesis or something?  The "Over(Partition..." function is new to me.

Thank you.
0

LVL 24

Expert Comment

ID: 40617203
1. Can you post your current code.
2. Which version of SQL do you have?
0

Author Comment

ID: 40617338
I'm trying to incorporate your suggested code within the following:

USE SDM;
WITH
[ACCT_ID] AS
(
SELECT
[ACCT_KY],
[ACCT_NM],
[ACCT_HD],
[ACCT_LVL]
FROM
[ACCOUNT]),
RESULTS AS
(
SELECT
cm_SK,
b.ACCT_HD,
[CM_AOD] AOD,
CASE
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=3 THEN '<= 03 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=6 THEN '<= 06 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=9 THEN '<= 09 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=12 THEN '<= 12 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=24 THEN '<= 24 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=36 THEN '<= 36 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=48 THEN '<= 48 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=60 THEN '<= 60 Mos.'
ELSE '>   60 Mos.'
END MTRTY_BKT,
SUM(CM_BK_VL) as SUMBK
FROM
[LOAN].[dbo].[Loan] a
inner join [ACCOUNT] b on a.cm_SK = b.[ACCT_KY]
WHERE
[FLG] = '1'
GROUP BY
[CM_AOD],
CM_SK,
b.ACCT_HD,
CASE
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=3 THEN '<= 03 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=6 THEN '<= 06 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=9 THEN '<= 09 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=12 THEN '<= 12 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=24 THEN '<= 24 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=36 THEN '<= 36 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=48 THEN '<= 48 Mos.'
WHEN DATEDIFF(M,[CM_AOD],[CM_MTRTY]) <=60 THEN '<= 60 Mos.'
ELSE '>   60 Mos.'
END)
select
ACCT_KY,
ISNULL(SPACE((z.[ACCT_LVL]-1)*4) + z.[ACCT_NM], z.[ACCT_NM]) AS 'ACCT_NM',
sum(SUMBK) AS 'SUM_BK_BAL',
MTRTY_BKT,
AOD
from
[ACCT_ID] z LEFT JOIN
RESULTS on RESULTS.ACCT_HD.IsDescendantOf(z.ACCT_HD)=1
WHERE
z.ACCT_HD.IsDescendantOf(x)=1
GROUP BY
MTRTY_BKT,
ACCT_KY,
ISNULL(SPACE((z.[ACCT_LVL]-1)*4) + z.[ACCT_NM], z.[ACCT_NM]),
AOD
ORDER BY
ACCT_KY,
MTRTY_BKT
0

LVL 24

Expert Comment

ID: 40618396
This code works for me - can you test it on your computer:

``````create table #mytable
(DateA date,
DateB date,
ValueC decimal,
ValueD decimal,
[Month Bucket] varchar(20));
insert into #mytable
values ('20150101', '20150220', 0, 0,'<=3 Mos.'),
('20150131', '20150303', 1409389.65, 0.8158,'<=3 Mos.'),
('20150402', '20150602', 106742.33, 1.395,'<=3 Mos.'),
('20150802', '20151202', 34578.88, 1.3974,'<=6 Mos.'),
('20160402', '20161202', 13965.43, 1.4307,'<=9 Mos.'),
('20170803', '20181204', 669375, 1.8072,'<=24 Mos.'),
('20200405', '20221207', 38140.53, 2.1527,'<=36 Mos.'),
('20221228', '20250920', 10000, 4.153,'<=36 Mos.'),
('20251011', '20280725', 347949.43, 4.25,'<=36 Mos.'),
('20280815', '20310620', 120000, 4.45,'<=36 Mos.'),
('20310711', '20340605', 10000.5, 5.25,'<=36 Mos.'),
('20340626', '20370611', 0, 7,'<=36 Mos.'),
('20370702', '20400708', 7800, 9.25,'<=36 Mos.'),
('20400729', '20430826', 18391.75, 1.4281,'<=48 Mos.'),
('20430916', '20461103', 47467.68, 1.4321,'<=48 Mos.'),
('20461124', '20500201', 161472.58, 1.4326,'<=48 Mos.'),
('20500222', '20530523', 49334.83, 1.4594,'<=48 Mos.'),
('20530613', '20561002', 7500000, 1.91,'<=48 Mos.'),
('20561023', '20600304', 1050000, 1.99,'<=48 Mos.')
select *,
SUM(ValueC * ValueD) OVER(Partition by [Month Bucket] order by DateA ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
/ SUM(ValueC) OVER(Partition by [Month Bucket] order by DateA ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as myValue
from #mytable
order by [DateA]
``````
0

LVL 24

Expert Comment

ID: 40618399
Here's the output:

``````(19 row(s) affected)
DateA      DateB      ValueC                                  ValueD                                  Month Bucket         myValue
---------- ---------- --------------------------------------- --------------------------------------- -------------------- ---------------------------------------
2015-01-01 2015-02-20 0                                       0                                       <=3 Mos.             1.000000
2015-01-31 2015-03-03 1409390                                 1                                       <=3 Mos.             1.000000
2015-04-02 2015-06-02 106742                                  1                                       <=3 Mos.             1.000000
2015-08-02 2015-12-02 34579                                   1                                       <=6 Mos.             1.000000
2016-04-02 2016-12-02 13965                                   1                                       <=9 Mos.             1.000000
2017-08-03 2018-12-04 669375                                  2                                       <=24 Mos.            2.000000
2020-04-05 2022-12-07 38141                                   2                                       <=36 Mos.            3.948901
2022-12-28 2025-09-20 10000                                   4                                       <=36 Mos.            3.948901
2025-10-11 2028-07-25 347949                                  4                                       <=36 Mos.            3.948901
2028-08-15 2031-06-20 120000                                  4                                       <=36 Mos.            3.948901
2031-07-11 2034-06-05 10001                                   5                                       <=36 Mos.            3.948901
2034-06-26 2037-06-11 0                                       7                                       <=36 Mos.            3.948901
2037-07-02 2040-07-08 7800                                    9                                       <=36 Mos.            3.948901
2040-07-29 2043-08-26 18392                                   1                                       <=48 Mos.            1.968655
2043-09-16 2046-11-03 47468                                   1                                       <=48 Mos.            1.968655
2046-11-24 2050-02-01 161473                                  1                                       <=48 Mos.            1.968655
2050-02-22 2053-05-23 49335                                   1                                       <=48 Mos.            1.968655
2053-06-13 2056-10-02 7500000                                 2                                       <=48 Mos.            1.968655
2056-10-23 2060-03-04 1050000                                 2                                       <=48 Mos.            1.968655

(19 row(s) affected)
``````
0

LVL 24

Expert Comment

ID: 40618402
Incidentally, if you are wondering why the values are different to your spreadsheet, your formulas contain several errors. I believe the above are the correct figures.
0

Author Comment

ID: 40619105
I ran the query you created a couple boxes above.  I'm still receiving the same error message.  Is there some trigger that I need to do/turn on?  I'm providing you a snapshot of what I'm seeing.  Considering it's identical to what I received before, I think there is something else I need to do in order for the code to work.
0

Author Comment

ID: 40619111
I don't see where the attachment was uploaded above.  Attempting again.
0

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40619147
Different tack (maybe SUM() OVER() doesn't work on 2008R2). Try this instead, which should work:

``````create table #mytable
(DateA date,
DateB date,
ValueC decimal,
ValueD decimal,
[Month Bucket] varchar(20));
insert into #mytable
values ('20150101', '20150220', 0, 0,'<=3 Mos.'),
('20150131', '20150303', 1409389.65, 0.8158,'<=3 Mos.'),
('20150402', '20150602', 106742.33, 1.395,'<=3 Mos.'),
('20150802', '20151202', 34578.88, 1.3974,'<=6 Mos.'),
('20160402', '20161202', 13965.43, 1.4307,'<=9 Mos.'),
('20170803', '20181204', 669375, 1.8072,'<=24 Mos.'),
('20200405', '20221207', 38140.53, 2.1527,'<=36 Mos.'),
('20221228', '20250920', 10000, 4.153,'<=36 Mos.'),
('20251011', '20280725', 347949.43, 4.25,'<=36 Mos.'),
('20280815', '20310620', 120000, 4.45,'<=36 Mos.'),
('20310711', '20340605', 10000.5, 5.25,'<=36 Mos.'),
('20340626', '20370611', 0, 7,'<=36 Mos.'),
('20370702', '20400708', 7800, 9.25,'<=36 Mos.'),
('20400729', '20430826', 18391.75, 1.4281,'<=48 Mos.'),
('20430916', '20461103', 47467.68, 1.4321,'<=48 Mos.'),
('20461124', '20500201', 161472.58, 1.4326,'<=48 Mos.'),
('20500222', '20530523', 49334.83, 1.4594,'<=48 Mos.'),
('20530613', '20561002', 7500000, 1.91,'<=48 Mos.'),
('20561023', '20600304', 1050000, 1.99,'<=48 Mos.');

With mySummary as
(select [Month Bucket], SUM(ValueC * ValueD) / SUM(ValueC) as myValue
from #mytable
Group by [Month Bucket])

select M.*, L.MyValue
from #mytable M
left join mySummary L
on M.[Month Bucket] = L.[Month Bucket]
order by [DateA]
``````
0

Author Comment

ID: 40619206
Okay, that worked as you've designed above! (Thank you).  However, based on the code I provided seven boxes above, I'm uncertain where it should be placed/inserted. Is it to be placed at the top, within the "Results as" section, etc.?

I do appreciate your assistance and patience.
0

LVL 24

Assisted Solution

Phillip Burton earned 500 total points
ID: 40619213
Obviously, you don't do anything with lines 1-27

Well, you have got two WITH tables, so the third one goes after the first two.

Then you do a simple join between the third table and the second table as I did above.
0

Author Closing Comment

ID: 40619468
Whew!  After some rearranging my original code, it works!  Fantastic!  Really appreciate your time on this one, Phillip.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
###### Suggested Courses
Course of the Month4 days, 21 hours left to enroll

#### 739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.