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

x
?
Solved

how do I divide values in two tables MSSQL

Posted on 2010-09-06
6
Medium Priority
?
682 Views
Last Modified: 2012-06-27
How would I get a result table from the two table if the result is the tableA.dur/TableB.dur
the values that should be divided together are those that have a common
'year' field, I should have 13 results equal to the number of rows in the scond table
based on the date the first calculation would be 17.5/17.5
the second would be (17.5+28.5)/(17.5+28.5)
and the third would be   (17.5+28.5)/(17.5+28.5+23.5)
What is the sql query to perform the required calculations?


name          dur      year      date
aaa128      17.50      1      2007-01-01 10:00:00.000
aaa128      28.50      1      2007-01-01 04:00:00.000
aaa128      37.50      1      2007-01-01 18:00:00.000
aaa128      82.50      5      2007-05-10 06:00:00.000
aaa128      52.50      6      2007-06-11 08:00:00.000
aaa128      20.50      8      2007-08-14 12:00:00.000
aaa128      75.00      9      2007-09-15 14:00:00.000

here is the denom table
name           dur      year     date
aaa128      17.50      1      2007-01-01 10:00:00.000
aaa128      28.50      1      2007-01-01 04:00:00.000
aaa128      23.50      1      2007-01-01 08:00:00.000
aaa128      29.50      1      2007-01-01 20:00:00.000
aaa128      33.50      1      2007-01-01 20:00:00.000
aaa128      37.50      1      2007-01-01 18:00:00.000
aaa128      24.50      4      2007-04-08 04:00:00.000
aaa128      82.50      5      2007-05-10 06:00:00.000
aaa128      52.50      6      2007-06-11 08:00:00.000
aaa128      20.50      8      2007-08-14 12:00:00.000
aaa128      75.00      9      2007-09-15 14:00:00.000
aaa128      30.50      10      2007-10-17 16:00:00.000
aaa128      29.50      10      2007-10-17 16:00:00.000


Thank you
0
Comment
Question by:xav056
[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
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:james-ct16
ID: 33614833
Howdy

I have had a look at this but I dont fully understand from your description of how the date affects the calculation. Could you please provide a fully worked example for the calculations for year 1 please. I have included the code here to produce sample data to help anyone else.

Kind Regards

James
declare @tableA as table
(
name	varchar(20),
dur		decimal,
[year]	int,
[date]	datetime
)

declare @tableB as table
(
name	varchar(20),
dur		decimal,
[year]	int,
[date]	datetime
)

insert into @tableA values ('aaa128',17.50,1,'2007-01-01 10:00:00.000')
insert into @tableA values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableA values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableA values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableA values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableA values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableA values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')

insert into @tableB values ('aaa128',      17.50,      1,      '2007-01-01 10:00:00.000')
insert into @tableB values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableB values ('aaa128',      23.50,      1,      '2007-01-01 08:00:00.000')
insert into @tableB values ('aaa128',      29.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      33.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableB values ('aaa128',      24.50,      4,      '2007-04-08 04:00:00.000')
insert into @tableB values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableB values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableB values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableB values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')
insert into @tableB values ('aaa128',      30.50,      10,      '2007-10-17 16:00:00.000')
insert into @tableB values ('aaa128',      29.50,      10,      '2007-10-17 16:00:00.000')

Open in new window

0
 
LVL 9

Author Comment

by:xav056
ID: 33615419
Hello,
actually here how it should be
row should be sorted by date asc, year asc
then given the fact that table 2 has more results you take the first row which willl be
aaa128      28.50   1     2007-01-01 04:00:00.000 ( the earliest in time) find a time<= to this time in table A
in this case it would be a matching row in table A
the first calculation would be 28.5/28.5 from (year1 date 2007-01-01 04:00:00.000)(table A and Table B)
the second would be (28.5)/(28.5+23.5) from (year1 date 2007-01-01 08:00:00.000)(num from A denom from B)
third would be   (28.5+17.5)/(28.5+23.5+17.5)   from (year1 date 2007-01-01 10:00:00.000)
4th would be (17.5+28.5+37.5)/(17.5+28.5+23.5+37.5)  from(year 1 date 2007-01-01 18:00:00.000)
5th would be (17.5+28.5+37.5)/(17.5+28.5+23.5+37.5+29.5) from(year 1 date 2007-01-01 20:00:00.000)
6th would be (17.5+28.5+37.5)/(17.5+28.5+23.5+37.5+29.5+33.5) from(year 1 date 2007-01-01 20:00:00.000)
Hope that is clear enough
0
 
LVL 10

Expert Comment

by:james-ct16
ID: 33618456
Good evening

I believe the attached code to be pretty close however there are a few more scenarios i think need looking at.
what happens if a year / date appears in table a but not table b
what happens when there are multiple records for the same date year and name? the attached solution currently ignores this and adds them together.

So the attached solution works by using a pretty standard technique for creating a running total by utilising a cross join back to itself. Note these are the 2 commented queries. Then we simply apply you join logic to the query by using the two running total columns as sub selects.

I hope that helps
declare @tableA as table
(
name	varchar(20),
dur		decimal(10,2),
[year]	int,
[date]	datetime
)

declare @tableB as table
(
name	varchar(20),
dur		decimal(10,2),
[year]	int,
[date]	datetime
)

insert into @tableA values ('aaa128',		17.50,		1,		'2007-01-01 10:00:00.000')
insert into @tableA values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableA values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableA values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableA values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableA values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableA values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')

insert into @tableB values ('aaa128',      17.50,      1,      '2007-01-01 10:00:00.000')
insert into @tableB values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableB values ('aaa128',      23.50,      1,      '2007-01-01 08:00:00.000')
insert into @tableB values ('aaa128',      29.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      33.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableB values ('aaa128',      24.50,      4,      '2007-04-08 04:00:00.000')
insert into @tableB values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableB values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableB values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableB values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')
insert into @tableB values ('aaa128',      30.50,      10,      '2007-10-17 16:00:00.000')
insert into @tableB values ('aaa128',      29.50,      10,      '2007-10-17 16:00:00.000')

---- create running totals for table a
--select a.[YEAR],a.date, cast(SUM(aa.dur) as decimal(10,2)) [RunningTotalA] from @tableA a
--cross join @tableA aa 
--where (a.year = aa.year and aa.date <= a.date)
--group by a.[year], a.date
--order by a.date asc

---- create running totals for table b
--select b.[YEAR],b.date, cast(SUM(bb.dur) as decimal(10,2)) [RunningTotalB] from @tableB b
--cross join @tableB bb 
--where (b.year = bb.year and bb.date <= b.date)
--group by b.[year], b.date
--order by b.date asc


-- put the above together and perform the calculation
select b.year,b.date, isnull(MAX(a.RunningTotalA),0)/ MAX(b.RunningTotalB) [Result] from
(
	select b.[YEAR],b.date, cast(SUM(bb.dur) as decimal(10,2)) [RunningTotalB] from @tableB b
	cross join @tableB bb 
	where (b.year = bb.year and bb.date <= b.date)
	group by b.[year], b.date
) B
left join 
(
	select a.[YEAR],a.date, cast(SUM(aa.dur) as decimal(10,2)) [RunningTotalA] from @tableA a
	cross join @tableA aa 
	where (a.year = aa.year and aa.date <= a.date)
	group by a.[year], a.date
)A on b.year=a.year and a.date<=b.date
group by b.date, b.year
order by b.year,b.date asc

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 9

Author Comment

by:xav056
ID: 33618803
Hello james-ct16
what happens if a year / date appears in table a but not table b
--This would never happen as any record in A should be in B

what happens when there are multiple records for the same date year and name?
--if there were multiple records for the same name , year and date then it should be handled so the first record would have its value
the second records would have its value+ the previous value

Thank you
0
 
LVL 10

Accepted Solution

by:
james-ct16 earned 2000 total points
ID: 33619033
Howdy
With the clarification of those rules the attached should be your final solution.

Regards
James
declare @tableA as table
(
name	varchar(20),
dur		decimal(10,2),
[year]	int,
[date]	datetime
)

declare @tableB as table
(
name	varchar(20),
dur		decimal(10,2),
[year]	int,
[date]	datetime
)

insert into @tableA values ('aaa128',		17.50,		1,		'2007-01-01 10:00:00.000')
insert into @tableA values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableA values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableA values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableA values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableA values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableA values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')

insert into @tableB values ('aaa128',      17.50,      1,      '2007-01-01 10:00:00.000')
insert into @tableB values ('aaa128',      28.50,      1,      '2007-01-01 04:00:00.000')
insert into @tableB values ('aaa128',      23.50,      1,      '2007-01-01 08:00:00.000')
insert into @tableB values ('aaa128',      29.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      33.50,      1,      '2007-01-01 20:00:00.000')
insert into @tableB values ('aaa128',      37.50,      1,      '2007-01-01 18:00:00.000')
insert into @tableB values ('aaa128',      24.50,      4,      '2007-04-08 04:00:00.000')
insert into @tableB values ('aaa128',      82.50,      5,      '2007-05-10 06:00:00.000')
insert into @tableB values ('aaa128',      52.50,      6,      '2007-06-11 08:00:00.000')
insert into @tableB values ('aaa128',      20.50,      8,      '2007-08-14 12:00:00.000')
insert into @tableB values ('aaa128',      75.00,      9,      '2007-09-15 14:00:00.000')
insert into @tableB values ('aaa128',      30.50,      10,      '2007-10-17 16:00:00.000')
insert into @tableB values ('aaa128',      29.50,      10,      '2007-10-17 16:00:00.000')

---- create running totals for table a
--select a.[YEAR],a.date, cast(SUM(aa.dur) as decimal(10,2)) [RunningTotalA] from @tableA a
--cross join @tableA aa 
--where (a.year = aa.year and aa.date <= a.date)
--group by a.[year], a.date
--order by a.date asc

-- create running totals for table b
select b.Record, b.[YEAR],b.date, cast(SUM(bb.dur) as decimal(10,2)) [RunningTotalB] from (select ROW_NUMBER() over (order by [year], [date]asc) [Record],  name,dur,YEAR,date from @tableb)b
cross join (select ROW_NUMBER() over (order by [year], [date]asc) [Record],  name,dur,YEAR,date from @tableb) bb 
where (b.year = bb.year and bb.Record <= b.Record)
group by b.Record, b.[year], b.date
order by b.date asc

-- put the above together and perform the calculation
select b.year,b.date, isnull(MAX(a.RunningTotalA),0)/ (b.RunningTotalB) [Result] from
(
	select b.Record, b.[YEAR],b.date, cast(SUM(bb.dur) as decimal(10,2)) [RunningTotalB] from (select ROW_NUMBER() over (order by [year], [date]asc) [Record],  name,dur,YEAR,date from @tableb)b
	cross join (select ROW_NUMBER() over (order by [year], [date]asc) [Record],  name,dur,YEAR,date from @tableb) bb 
	where (b.year = bb.year and bb.Record <= b.Record)
	group by b.Record, b.[year], b.date
) B
left join 
(
	select a.[YEAR],a.date, cast(SUM(aa.dur) as decimal(10,2)) [RunningTotalA] from @tableA a
	cross join @tableA aa 
	where (a.year = aa.year and aa.date <= a.date)
	group by a.[year], a.date
)A on b.year=a.year and a.date<=b.date
group by b.year,b.date, b.RunningTotalB
order by b.year,b.date asc

Open in new window

0
 
LVL 9

Author Closing Comment

by:xav056
ID: 33623845
Thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

730 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