?
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
?
70 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

609 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