CODEWITHSUNDEEP
sql
Kamran Pervaiz
Kamran Pervaiz

Reputation: 1931

one to many sql query with good performance

Could someone please help me? I am trying to write a SQL query and having trouble with it for 2-3 days. let me define the problem first.

I have 2 tables

  1. Payment_Schedule_Master
    [PAYMENT_SCHEDULE_MASTER_ID] [int] NOT NULL, Primary key
    [FPI_ID] [varchar](9) NOT NULL,
    [DELETE_FLAG] [char](1) NOT NULL,
    [CREATED_BY] [varchar](30) NOT NULL,
    [CREATED_DATE] [datetime] NOT NULL,
    [MODIFY_BY] [varchar](30) NOT NULL,
    [MODIFY_DATE] [datetime] NOT NULL
  1. Payment_Schedule_Detail
    [PAYMENT_SCHEDULE_DETAIL_ID] [int] IDENTITY(1,1) NOT NULL, Primary key
    [PAYMENT_SCHEDULE_MASTER_ID] [int] NOT NULL, Foreign key to master table
    [PAY_YEAR] [int] NOT NULL,
    [PAY_MONTH] [int] NOT NULL,
    [ACTUAL] [money] NULL,
    [FORECAST] [money] NULL,
    [DELETE_FLAG] [char](1) NOT NULL,
    [CREATED_BY] [varchar](30) NOT NULL,
    [CREATED_DATE] [datetime] NOT NULL,
    [MODIFY_BY] [varchar](30) NOT NULL,
    [MODIFY_DATE] [datetime] NOT NULL

There is a one-to-many relationship between the two: Master has one entry and detail has many. Payment_Schedule_Detail has an id, foreign key, actual, forecast and many column. Actual and forecast will have numerical values in it.

Problem:
I want to get those Payment_Schedule_Master rows which have Actual and ForeCast equal to 0.

My Query:

I tried this query

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualSum, t.ForecastSum
from 
    (Select 
        SUM(Actual) As ActualSum, 
        SUM (forecast) AS ForecastSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
where  
    t.ActualSum = t.ForecastSum
and t.ActualSum = 0

The problem with this query is that if Actual has 200 in Jan and -200 in Dec it will pick that title as well because SUM (Actual) will be 0 which is wrong.

I am not sure how to modify the query that it should only get those titles which has actual 0 and forecast 0.

Testing:
and also if anyone will let me know how to test the method?

Update: tried this query but it is taking 8 seconds.

Select 
    t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
    t.ActualSum, t.ForecastSum, psd.ACTUAL, psd.FORECAST
from 
    (Select 
         SUM(Actual) As ActualSum, SUM (forecast) AS ForecastSum, 
         PAYMENT_SCHEDULE_MASTER_ID
     from 
          [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
          PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
     dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
Inner Join 
     [dbo].[PAYMENT_SCHEDULE_DETAIL] psd on psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
 where  
     t.ActualSum = t.ForecastSum
     and t.ActualSum = 0
     and psd.ACTUAL = 0 
order by 
     psm.FPI_ID

Data And Output:

psm_id  Actual      ForeCast  [other columns]
900     10000.00    0.00
900     -10000.00   0.00
900     0.00        0.00
912     0.00        0.00
912     0.00        0.00
912     0.00        0.00

psm_id = Payment_Schedule_Master_ID

Payment_Schedule_Master_Id 900 Sum of actual will be 0, It should not appear on the result. but 912 will appear in the result because all records were 0. I hope this helps.

Upvotes: 0

Views: 448

Answers (8)

Kamran Pervaiz
Kamran Pervaiz

Reputation: 1931

thank you for the answers but it was not working at all.

In my database there was one special case which I didnt see. It was that if both actual and forecast is 0 for a specific Payment_Schedule_Id it will have only one row.

Select PAYMENT_SCHEDULE_MASTER_ID from (
Select COUNT(*)  As IdCount, PAYMENT_SCHEDULE_MASTER_ID, ACTUAL , FORECAST
from PAYMENT_SCHEDULE_DETAIL
group by PAYMENT_SCHEDULE_MASTER_ID, ACTUAL, FORECAST) t
where t.IdCount = 1 
and t.ACTUAL = 0.00
and t.FORECAST = 0.00

Above was the solution.

Upvotes: 0

Wasim
Wasim

Reputation: 1174

SELECT psm_id, ActualSum, ForecastSum FROM
(SELECT 
    PAYMENT_SCHEDULE_MASTER_ID AS psm_id, SUM(psd.Actual) As ActualSum, SUM (psd.forecast) AS ForecastSum
FROM 
    [dbo].[PAYMENT_SCHEDULE_DETAIL] psd, dbo.PAYMENT_SCHEDULE_MASTER psm
GROUP BY PAYMENT_SCHEDULE_MASTER_ID
) ts, dbo.PAYMENT_SCHEDULE_MASTER psm1
WHERE psm1.PAYMENT_SCHEDULE_MASTER_ID = ts.psm_id
AND ts.ActualSum = 0 AND ts.ForecastSum
ORDER BY 
     psm1.FPI_ID

Upvotes: 0

Bernhard Barker
Bernhard Barker

Reputation: 55619

I believe this is what you're looking for:

SELECT M.*
FROM Payment_Schedule_Detail D
JOIN Payment_Schedule_Master M
ON M.PAYMENT_SCHEDULE_MASTER_ID = D.PAYMENT_SCHEDULE_MASTER_ID
GROUP BY D.PAYMENT_SCHEDULE_MASTER_ID
HAVING SUM(D.Actual) = 0 AND SUM(D.Forecast) = 0

or this:

SELECT M.*
FROM (SELECT PAYMENT_SCHEDULE_MASTER_ID,
             SUM(Actual) ActualSum, SUM(Forecast) ForecastSum
      FROM Payment_Schedule_Detail
      GROUP BY PAYMENT_SCHEDULE_MASTER_ID) D
JOIN Payment_Schedule_Master M
ON M.PAYMENT_SCHEDULE_MASTER_ID = D.PAYMENT_SCHEDULE_MASTER_ID
WHERE ActualSum = 0 AND ForecastSum = 0

Not sure which one's better in terms of performance. I think the second one would create a temporary table and is thus slower, but it probably depends a little on the optimiser.

Depending on what type of SQL database you're using, the foreign keys may or may not automatically get indexed, if it doesn't you should index your foreign key.

Upvotes: 0

boisvert
boisvert

Reputation: 3739

Looks to me like you need to use where on your 'actual' and 'forecast' columns before you sum:

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualSum, t.ForecastSum
from 
    (Select 
        SUM(Actual) As ActualSum, 
        SUM (forecast) AS ForecastSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     where Actual=0 and forecast=0
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID

Having said, if performance is an issue, you don't need to SUM actual and forecast, because you're summing zeros. Then we can do more simply:

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 0 as ActualSum, 0 as ForecastSum
from
    (Select distinct
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        dbo.PAYMENT_SCHEDULE_DETAIL
     where Actual=0 and forecast=0 ) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm
On psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID

I'm not too clever at performance optimisation - if your subquery produces many rows, picking the distinct ones before joining, as above, may be quicker. But you could try this:

Select distinct
   psd.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 0 as ActualSum, 0 as ForecastSum
from
   dbo.PAYMENT_SCHEDULE_DETAIL psd
Inner Join 
   dbo.PAYMENT_SCHEDULE_MASTER psm
On
   psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
Where
   Actual=0 and forecast=0

Which is simpler, but may be slower as it joins the rows before filtering the distinct ones. And, don't forget to index you FK, unless you're doing a lot of data writes.

Upvotes: 0

sgmoore
sgmoore

Reputation: 16077

What about -

select * from Payment_Schedule_Master
where NOT EXISTS
 (
     SELECT  null FROM  Payment_Schedule_Detail 
        where (forecast != 0 or actual != 0) and 
        Payment_Schedule_Master.id = PAYMENT_SCHEDULE_MASTER_ID
 )

Note, this will also find Payment_Schedule_Master which do not have any related payment_schedule_details lines.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Your original query is pretty close to what you need. You are looking for cases where the actual and forecast are always zero. Well, if they are always 0, then the sum of the absolute value is 0. So try this:

Select 
   t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
   t.ActualAbsSum, t.ForecastAbsSum
from 
    (Select 
        SUM(abs(Actual)) As ActualAbsSum, 
        SUM(abs(forecast)) AS ForecastAbsSum, 
        PAYMENT_SCHEDULE_MASTER_ID
     from 
        [dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
        PAYMENT_SCHEDULE_MASTER_ID) t
Inner Join 
    dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
where  
    t.ForecastAbsSum = 0 and t.ActualAbsSum = 0

Are you trying to get any example where they are 0, as in:

Select psm.*
from dbo.PAYMENT_SCHEDULE_MASTER psm 
where psm.PAYMENT_SCHEDULE_MASTER_ID in (select PAYMENT_SCHEDULE_MASTER_ID
                                         from [PAYMENT_SCHEDULE_DETAIL]
                                         where actual = 0 and forecast = 0
                                        )

This uses the in instead of a join just for clarity (both are implemented in about the same say in the SQL Server engine).

Upvotes: 0

halit
halit

Reputation: 1128

Select 
    t.PAYMENT_SCHEDULE_MASTER_ID, psm.FPI_ID, 
    t.ActualSum, t.ForecastSum, psd.ACTUAL, psd.FORECAST
from 
    (Select t1.ActualSum,t1.ForecastSum,t1.PAYMENT_SCHEDULE_MASTER_ID
    from
    (Select 
         SUM(Actual) As ActualSum, SUM (forecast) AS ForecastSum, PAYMENT_SCHEDULE_MASTER_ID
     from 
          [CILS].[dbo].[PAYMENT_SCHEDULE_DETAIL]
     group by 
          PAYMENT_SCHEDULE_MASTER_ID) t1
          where  
     t1.ActualSum = 0 and t1.ForecastSum=0)t    

Inner Join 
     [Cils].dbo.PAYMENT_SCHEDULE_MASTER psm on psm.PAYMENT_SCHEDULE_MASTER_ID = t.PAYMENT_SCHEDULE_MASTER_ID
Inner Join 
     [CILS].[dbo].[PAYMENT_SCHEDULE_DETAIL] psd on psm.PAYMENT_SCHEDULE_MASTER_ID = psd.PAYMENT_SCHEDULE_MASTER_ID
order by 
     psm.FPI_ID

Upvotes: 0

bAN
bAN

Reputation: 13835

To test you can use something like SQL Fiddle.

Try this

I try the following SQL

select *
from Payment_Schedule_Master mast
where ((select sum(actual) from Payment_Schedule_Detail where main = mast.id) = 0)
and ((select sum(forecast) from Payment_Schedule_Detail where main = mast.id) = 0)

It is simpler and I think it makes what you want

Upvotes: 0

Related Questions