SQL JOIN QUERY - RESULT

[Abhineet_SISL]

I am trying to join the below two queries but not getting the accurate result.

select
bi.CONSUMERNO,
bi.UNITCONSUMED, -- billed unit
bi.BILLDATE
from
tbl_billinfo bi
where bi.CONSUMERNO = '120400824271'

AND

select [TBL_PAYMENTSETTLEMENT].CONSUMERNO, [dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME from [TBL_PAYMENTSETTLEMENT] where
[TBL_PAYMENTSETTLEMENT].CONSUMERNO = '120400824271'

RESULT I NEED : unique bill date , billed unit, current_ec and paymentdatetime of this consumer

==========

QUERY I TRIED USING JOIN :

===================

select
[dbo].[TBL_BILLINFO].CONSUMERNO,
[dbo].[TBL_BILLINFO].UNITCONSUMED, -- billed unit
[dbo].[TBL_BILLINFO].BILLDATE,
[dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME
from
tbl_billinfo
right join
[TBL_PAYMENTSETTLEMENT]
on
tbl_billinfo.CONSUMERNO = [dbo].[TBL_PAYMENTSETTLEMENT].CONSUMERNO
where
[TBL_BILLINFO].CONSUMERNO = '120400824271'
group by
[dbo].[TBL_BILLINFO].CONSUMERNO,
[dbo].[TBL_BILLINFO].UNITCONSUMED, -- billed unit
[dbo].[TBL_BILLINFO].BILLDATE,
[dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME;

BELOW ARE THE OUTPUT OF BOTH QUERIES:

===============================

select
bi.CONSUMERNO,
bi.UNITCONSUMED, -- billed unit
bi.BILLDATE
from
tbl_billinfo bi
where bi.CONSUMERNO = '120400824271'

120400824271 93.00 2014-12-15
120400824271 94.00 2015-01-15
120400824271 95.00 2015-02-11
120400824271 94.00 2015-03-09
120400824271 94.00 2015-04-14
120400824271 94.00 2015-05-15
120400824271 94.00 2015-06-08
120400824271 94.00 2015-07-09
120400824271 94.00 2015-08-08
120400824271 94.00 2015-09-09
120400824271 94.00 2015-10-08
120400824271 94.00 2015-11-10
120400824271 94.00 2015-12-11
120400824271 94.00 2016-01-11
120400824271 94.00 2016-02-07
120400824271 94.00 2016-03-08
120400824271 94.00 2016-04-10
120400824271 94.00 2016-05-08
120400824271 94.00 2016-06-10
120400824271 94.00 2016-07-11
120400824271 94.00 2016-08-09
120400824271 94.00 2016-09-10
120400824271 94.00 2016-10-09
120400824271 94.00 2016-11-08
120400824271 94.00 2016-12-07
120400824271 94.00 2017-01-10
120400824271 94.00 2017-02-09
120400824271 94.00 2017-03-06
120400824271 94.00 2017-04-12
120400824271 94.00 2017-05-09
120400824271 130.00 2017-06-13
120400824271 130.00 2017-07-10
120400824271 130.00 2017-08-14
120400824271 130.00 2017-09-15
120400824271 130.00 2017-10-17
120400824271 130.00 2017-11-17
120400824271 130.00 2017-12-16
120400824271 130.00 2018-01-15
120400824271 130.00 2018-02-12
120400824271 130.00 2018-03-09
120400824271 130.00 2018-04-20
120400824271 142.00 2018-05-12
120400824271 142.00 2018-06-12
120400824271 142.00 2018-07-12
120400824271 142.00 2018-08-11
120400824271 142.00 2018-09-09
120400824271 142.00 2018-10-11
120400824271 142.00 2018-11-09
120400824271 142.00 2018-12-11
120400824271 142.00 2019-01-07
120400824271 142.00 2019-02-05
120400824271 142.00 2019-03-03
120400824271 142.00 2019-04-17
120400824271 142.00 2019-05-16
120400824271 48.00 2019-06-21
120400824271 142.00 2019-07-15
120400824271 142.00 2019-08-10
120400824271 142.00 2019-09-15

select [TBL_PAYMENTSETTLEMENT].CONSUMERNO, [dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME from [TBL_PAYMENTSETTLEMENT] where
[TBL_PAYMENTSETTLEMENT].CONSUMERNO = '120400824271'

120400824271 291.74 2014-12-23 00:00:00.000
120400824271 311.00 2015-01-20 00:00:00.000
120400824271 266.27 2015-07-30 00:00:00.000
120400824271 300.67 2015-08-28 00:00:00.000
120400824271 329.80 2015-09-24 00:00:00.000
120400824271 329.80 2015-10-26 00:00:00.000
120400824271 329.80 2015-11-17 00:00:00.000
120400824271 329.80 2016-01-21 00:00:00.000
120400824271 329.80 2016-02-29 00:00:00.000
120400824271 329.80 2016-03-16 00:00:00.000
120400824271 329.80 2016-05-30 00:00:00.000
120400824271 329.80 2016-06-19 00:00:00.000
120400824271 273.53 2016-07-20 00:00:00.000
120400824271 329.80 2016-08-31 00:00:00.000
120400824271 329.80 2016-10-23 00:00:00.000
120400824271 329.80 2016-12-29 00:00:00.000
120400824271 329.80 2017-01-31 00:00:00.000
120400824271 329.80 2017-02-17 00:00:00.000
120400824271 329.80 2017-03-14 00:00:00.000
120400824271 0.00 2017-03-29 00:00:00.000
120400824271 289.78 2017-04-30 00:00:00.000
120400824271 334.20 2017-05-19 00:00:00.000
120400824271 482.68 2017-06-30 00:00:00.000
120400824271 337.58 2017-08-28 00:00:00.000
120400824271 489.00 2017-09-27 00:00:00.000
120400824271 489.00 2017-10-26 00:00:00.000
120400824271 489.00 2017-11-25 00:00:00.000
120400824271 489.00 2017-12-27 00:00:00.000
120400824271 481.80 2018-01-31 00:00:00.000
120400824271 481.78 2018-02-28 00:00:00.000
120400824271 489.00 2018-03-17 00:00:00.000
120400824271 489.00 2018-04-26 00:00:00.000
120400824271 481.56 2018-05-26 00:00:00.000
120400824271 481.52 2018-09-22 00:00:00.000
120400824271 481.51 2018-10-26 00:00:00.000
120400824271 540.60 2018-12-22 00:00:00.000
120400824271 540.60 2019-02-15 00:00:00.000
120400824271 540.60 2019-03-29 00:00:00.000
120400824271 140.00 2019-06-26 00:00:00.000
120400824271 385.18 2019-07-27 00:00:00.000
120400824271 96.30 2019-07-27 00:00:00.000
120400824271 96.25 2019-08-19 00:00:00.000
120400824271 96.26 2019-09-27 00:00:00.000

[#2]

Hi @Abhineet_SISL , 

I read your question many times but couldn’t find out the problem you meet clearly.
1. Would you please let us know what the expected result is? 

ex. 
customer   unique bill date    Unit    Paymentdatetime

————————————————————————

1234567    2018-01-01.          93.      2018-01-30 00:00:00

1234567.   2018-01-12.         100.     2018-01-30 00:00:00

etc.

2. Would you please let us know the result you get with the SQL script you tried? I didn’t find out the output of your first query.

If you can clarify those 2 issues above, we may help you figure out the answer, as long as your explanations are sufficient for analyzing. 

Regards,

Sten