August 24, 2018 at 11:24 am
I have 2 tables below. Table A contained the Spike value 2 different dates spike reading on the same AccountNum 2018-08-17 & 2018-08-21 . I need to take the Table A first Spike_Reading_Day 2018-08-17 and find the match on Table B Spike_Reading_Day 2018-08-17 and find all the dates till before the last Flag 'P' Readdate 2018-08-09 and calculate the number of days. Once I know there are 8 days between 2018-08-09 to 2018-08-17 and I need to take the Table A Spikeval 150 divide 8 days and get the calculated value apply on the 8 days. Example 150/ 8 days = 18.75 and apply it between 2018-08-09 to 2018-08-17 on each date
Same apply to Table A second Spike_Reading_Day 2018-08-21 there are 3 days between 2018-08-19 to 2018-08-21 till the before the last Flag 'P'. So I need to take the SpikeVal 180 / 3 days = 60 and apply it on the 2018-08-19,2018-08-20,and 2018-08-21. I have tried different option nothing works because I have 2 dates on the same account_number.Any one can champion it? Thanks for your help
Table A
FID AccountNum Spike_Reading_Day SpikeVal Flag
34166 TT0034166 2018-08-17 150 P
34166 TT0034166 2018-08-21 180 P
Table B
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 24.1021 A
34166 TT0034166 2018-08-11 24.1021 A
34166 TT0034166 2018-08-12 24.1021 A
34166 TT0034166 2018-08-13 24.1021 A
34166 TT0034166 2018-08-14 24.1021 A
34166 TT0034166 2018-08-15 24.1021 A
34166 TT0034166 2018-08-16 24.1021 A
34166 TT0034166 2018-08-17 24.1021 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 24.5550 A
34166 TT0034166 2018-08-20 24.5550 A
34166 TT0034166 2018-08-21 24.5550 A <---
My wanted Result
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 18.75 A
34166 TT0034166 2018-08-11 18.75 A
34166 TT0034166 2018-08-12 18.75 A
34166 TT0034166 2018-08-13 18.75 A
34166 TT0034166 2018-08-14 18.75 A
34166 TT0034166 2018-08-15 18.75 A
34166 TT0034166 2018-08-16 18.75 A
34166 TT0034166 2018-08-17 18.75 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 60 A
34166 TT0034166 2018-08-20 60 A
34166 TT0034166 2018-08-21 60 A <---
August 24, 2018 at 11:32 am
pcq0125 - Friday, August 24, 2018 11:24 AMI have 2 tables below. Table A contained the Spike value 2 different dates spike reading on the same AccountNum 2018-08-17 & 2018-08-21 . I need to take the Table A first Spike_Reading_Day 2018-08-17 and find the match on Table B Spike_Reading_Day 2018-08-17 and find all the dates till before the last Flag 'P' Readdate 2018-08-09 and calculate the number of days. Once I know there are 8 days between 2018-08-09 to 2018-08-17 and I need to take the Table A Spikeval 150 divide 8 days and get the calculated value apply on the 8 days. Example 150/ 8 days = 18.75 and apply it between 2018-08-09 to 2018-08-17 on each dateSame apply to Table A second Spike_Reading_Day 2018-08-21 there are 3 days between 2018-08-19 to 2018-08-21 till the before the last Flag 'P'. So I need to take the SpikeVal 180 / 3 days = 60 and apply it on the 2018-08-19,2018-08-20,and 2018-08-21. I have tried different option nothing works because I have 2 dates on the same account_number.Any one can champion it? Thanks for your help
Table A
FID AccountNum Spike_Reading_Day SpikeVal Flag
34166 TT0034166 2018-08-17 150 P
34166 TT0034166 2018-08-21 180 PTable B
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 24.1021 A
34166 TT0034166 2018-08-11 24.1021 A
34166 TT0034166 2018-08-12 24.1021 A
34166 TT0034166 2018-08-13 24.1021 A
34166 TT0034166 2018-08-14 24.1021 A
34166 TT0034166 2018-08-15 24.1021 A
34166 TT0034166 2018-08-16 24.1021 A
34166 TT0034166 2018-08-17 24.1021 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 24.5550 A
34166 TT0034166 2018-08-20 24.5550 A
34166 TT0034166 2018-08-21 24.5550 A <---My wanted Result
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 18.75 A
34166 TT0034166 2018-08-11 18.75 A
34166 TT0034166 2018-08-12 18.75 A
34166 TT0034166 2018-08-13 18.75 A
34166 TT0034166 2018-08-14 18.75 A
34166 TT0034166 2018-08-15 18.75 A
34166 TT0034166 2018-08-16 18.75 A
34166 TT0034166 2018-08-17 18.75 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 60 A
34166 TT0034166 2018-08-20 60 A
34166 TT0034166 2018-08-21 60 A <---
You've been here long enough! Please provide your data in consumable format.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2018 at 11:47 am
Hi SSC Guru,
Is this good enough? Thanks.
FID AccountNum Spike_Reading_Day SpikeVal Flag
34166 TT0034166 2018-08-17 150 P
34166 TT0034166 2018-08-21 180 P
Table B
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 24.1021 A
34166 TT0034166 2018-08-11 24.1021 A
34166 TT0034166 2018-08-12 24.1021 A
34166 TT0034166 2018-08-13 24.1021 A
34166 TT0034166 2018-08-14 24.1021 A
34166 TT0034166 2018-08-15 24.1021 A
34166 TT0034166 2018-08-16 24.1021 A
34166 TT0034166 2018-08-17 24.1021 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 24.5550 A
34166 TT0034166 2018-08-20 24.5550 A
34166 TT0034166 2018-08-21 24.5550 A <---
My wanted Result in bold
FID AccountNum ReadDate ReadVal Flag
34166 TT0034166 2018-08-09 23.3620 P
34166 TT0034166 2018-08-10 18.75 A
34166 TT0034166 2018-08-11 18.75 A
34166 TT0034166 2018-08-12 18.75 A
34166 TT0034166 2018-08-13 18.75 A
34166 TT0034166 2018-08-14 18.75 A
34166 TT0034166 2018-08-15 18.75 A
34166 TT0034166 2018-08-16 18.75 A
34166 TT0034166 2018-08-17 18.75 A <---
34166 TT0034166 2018-08-18 18.7660 P
34166 TT0034166 2018-08-19 60 A
34166 TT0034166 2018-08-20 60 A
34166 TT0034166 2018-08-21 60 A <---
August 24, 2018 at 11:51 am
Hi Phil,
I changed the format in aligned to the header with spaces and between fields. For somehow, SQLsevercentral trimmed the leading space on each row values.
August 24, 2018 at 12:09 pm
pcq0125 - Friday, August 24, 2018 11:51 AMHi Phil,
I changed the format in aligned to the header with spaces and between fields. For somehow, SQLsevercentral trimmed the leading space on each row values.
Not really.
Have a look at this post, which I created a while ago. Notice how I supply code which can be pasted into SSMS and executed by anyone else – they don't need access to my systems or data to do it. That is what I was suggesting.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 27, 2018 at 10:05 am
Declare @TableA as table
(
FID numeric(8,0),
AccountNum char(9),
Spike_Reading_Day date,
SpikeVal int,
Flag char(1)
);
Try this...
Declare @TableB as table
(
FID numeric(8,0),
AccountNum char(9),
ReadDate date,
ReadVal decimal(8,4),
Flag char(1)
);
insert @TableA
values
('34166','TT0034166','8/17/2018','150','P'),
('34166','TT0034166','8/21/2018','180','P');
insert @TableB
values
('34166','TT0034166','8/9/2018','23.362','P'),
('34166','TT0034166','8/10/2018','24.1021','A'),
('34166','TT0034166','8/11/2018','24.1021','A'),
('34166','TT0034166','8/12/2018','24.1021','A'),
('34166','TT0034166','8/13/2018','24.1021','A'),
('34166','TT0034166','8/14/2018','24.1021','A'),
('34166','TT0034166','8/15/2018','24.1021','A'),
('34166','TT0034166','8/16/2018','24.1021','A'),
('34166','TT0034166','8/17/2018','24.1021','A'),
('34166','TT0034166','8/18/2018','18.766','P'),
('34166','TT0034166','8/19/2018','24.555','A'),
('34166','TT0034166','8/20/2018','24.555','A'),
('34166','TT0034166','8/21/2018','24.555','A')
;
with cte
as
(
select a.FID, a.AccountNum, isnull(b.ReadDate , Spike_Reading_Day) ReadDate ,a.Spike_Reading_Day, a.SpikeVal
from @TableA a
outer apply
(
select top (1) b.ReadDate
from @TableB b
where
b.FID = a.FID
and b.AccountNum = a.AccountNum
and b.ReadDate < a.Spike_Reading_Day
and b.Flag = 'P'
order by b.ReadDate desc
) b
)
select a.FID
,a.AccountNum
,A.ReadDate
,case when a.Flag = 'A' then
SpikeVal/cast(count(a.Flag) over(partition by a.Fid, a.AccountNum, a.Flag , b.readDate order by a.FID) AS decimal(6,4))
else a.ReadVal
end [ReadVal]
,a.Flag
from @TableB a
left join cte b on a.FID = b.FID and a.AccountNum = b.AccountNum and a.ReadDate between b.ReadDate and b.Spike_Reading_Day
order by a.ReadDate;
August 27, 2018 at 2:43 pm
Hi femi,
Thanks for your help. I ran your query but ran into error on the select statement query below on the order by after the CTE. Any ideas?
SpikeVal/cast(count(a.Flag) over(partition by a.Fid, a.AccountNum, a.Flag , b.readDate order by a.FID) AS decimal(6,4))
Thanks,
August 27, 2018 at 3:10 pm
if you are getting an error message, can you post it?
August 27, 2018 at 6:13 pm
Hi femi,
See the error below and I bold it .Thanks.
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near 'order'.
select a.FID
,a.AccountNum
,A.ReadDate
,case when a.Flag = 'A' then
SpikeVal/cast(count(a.Flag) over(partition by a.Fid, a.AccountNum, a.Flag , b.readDate order by a.FID) AS decimal(6,4))
else a.ReadVal end [ReadVal]
,a.Flag
from @TableB a
left join cte b on a.FID = b.FID and a.AccountNum = b.AccountNum and a.ReadDate between b.ReadDate and b.Spike_Reading_Day
order by A.ReadDate;
August 28, 2018 at 7:47 am
Hmm..What happens when you remove the Order By clause within the Over clause?SpikeVal/cast(count(a.Flag) over(partition by a.Fid, a.AccountNum, a.Flag , b.readDate) AS decimal(6,4))
August 28, 2018 at 10:11 am
I removed the order by within the over clause and it worked. Thank you.very much. You are a champion.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply