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
59 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

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 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

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 explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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