Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-18
13
Medium Priority
?
64 Views
Last Modified: 2015-02-19
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
Comment
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
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

by:kristibigo
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

by:Phillip Burton
ID: 40617203
1. Can you post your current code.
2. Which version of SQL do you have?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:kristibigo
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

by:Phillip Burton
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]

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
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)

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
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

by:kristibigo
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

by:kristibigo
ID: 40619111
I don't see where the attachment was uploaded above.  Attempting again. Snapshot-of-Incorrect-syntax.gif
0
 
LVL 24

Accepted Solution

by:
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]

Open in new window

0
 

Author Comment

by:kristibigo
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

by:Phillip Burton
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

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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

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

Join & Ask a Question