Databases
StackOverflow2013
Postsdbo
POone to many sql query with good performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POone to many sql query with good performance
    primarykey
    data
    text
    <p>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. </p> <p>I have 2 tables</p> <ol> <li><code>Payment_Schedule_Master</code></li> </ol> <pre> [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</pre> <ol> <li><code>Payment_Schedule_Detail</code></li> </ol> <pre> [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</pre> <p>There is a one-to-many relationship between the two: <code>Master</code> has one entry and <code>detail</code> has many. <code>Payment_Schedule_Detail</code> has an <code>id</code>, foreign key, actual, forecast and many column. Actual and forecast will have numerical values in it.</p> <p><strong>Problem:</strong><br> I want to get those <code>Payment_Schedule_Master</code> rows which have <code>Actual</code> and <code>ForeCast</code> equal to 0.</p> <p><strong>My Query:</strong> </p> <p>I tried this query</p> <pre><code>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 </code></pre> <p>The problem with this query is that if <code>Actual</code> has 200 in Jan and -200 in Dec it will pick that title as well because <code>SUM (Actual)</code> will be 0 which is wrong.</p> <p>I am not sure how to modify the query that it should only get those titles which has actual 0 and forecast 0.</p> <p><strong>Testing:</strong><br> and also if anyone will let me know how to test the method?</p> <p><strong>Update:</strong> tried this query but it is taking 8 seconds.</p> <pre><code>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 </code></pre> <p><strong>Data And Output:</strong></p> <pre><code>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 </code></pre> <p>psm_id = Payment_Schedule_Master_ID</p> <p>Payment_Schedule_Master_Id <code>900</code> Sum of actual will be 0, It should not appear on the result. but <code>912</code> will appear in the result because all records were 0. I hope this helps.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZaffy
    1. USKamran Pervaiz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POone to many sql query with good performance
      1. USKamran Pervaiz
      1. VTBountyStart
    2. VO
      singulars
      1. POone to many sql query with good performance
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POone to many sql query with good performance
      1. This table or related slice is empty.
      1. VTBountyClose
    1. COCan you define "good" performance? What is the explain plan of your query and the structure of your tables?
      singulars
      1. POone to many sql query with good performance
      1. USBen
    2. COgood performance is with in 1 second.
      singulars
      1. POone to many sql query with good performance
      1. USKamran Pervaiz
    3. COI have updated the question with schema.
      singulars
      1. POone to many sql query with good performance
      1. USKamran Pervaiz
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload