Solved

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

Posted on 2015-02-18
Medium Priority
70 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
• 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 2000 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 2000 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

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as welâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll