CODEWITHSUNDEEP
sqlpostgresql
K Pekosh
K Pekosh

Reputation: 693

Check for differences without having to create a dummy table first

I am building a script that checks for inaccurate data entry amongst four new columns added to a table. Three of these checks are relatively straight forward, but the fourth field to check is a flag column (t/f) whose value is determined on two criterion: Start Date in the table itself less than today's date, and a variable in another table having a specific value. To do this check, I have created a dummy table that essentially recreates the flag column with a unique ID, and then I join that dummy table on the table I want to check and scan for differences. What I am asking is if there is a way to do the check directly without having to create a dummy table. I have tried out a few things, but my SQL skills are not terribly advanced.

The code I am currently using is below:

CREATE TABLE check_is_billable
AS
SELECT csv.service_line_items.id AS service_line_items_id,
    csv.media_plans.id AS media_plan_id,
    cast(csv.service_line_items.start_date AS DATE) < CURRENT_DATE AND csv.media_plans.aasm_state = 'approved' AS is_billable_check
FROM csv.service_line_items
INNER JOIN csv.media_plans ON csv.service_line_items.media_plan_id = csv.media_plans.id
ORDER BY service_line_items_id;

SELECT
    'Is Billable Check' AS test,
    COUNT(*) AS error_count
FROM csv.check_is_billable
INNER JOIN csv.service_line_items ON (csv.service_line_items.id = csv.check_is_billable.service_line_items_id
    AND csv.check_is_billable.media_plan_id = csv.service_line_items.media_plan_id
    AND csv.check_is_billable.is_billable_check != cast(csv.service_line_items.is_billable AS BOOLEAN));

DROP TABLE check_is_billable;

Basically, I am wondering if there is a way that I can modify the middle step so that I do not have to create and drop a dummy table.

Upvotes: 0

Views: 55

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

you can do direcly without using create and drop but using a dinamic table 

SELECT '
  Is Billable Check' AS test
  , COUNT(*) AS error_count 
  FROM (
    SELECT 
      csv.service_line_items.id AS service_line_items_id
      , csv.media_plans.id AS media_plan_id
      , cast(csv.service_line_items.start_date AS DATE) < CURRENT_DATE 
                AND csv.media_plans.aasm_state = 'approved' AS is_billable_check
      FROM csv.service_line_items 
     INNER JOIN csv.media_plans ON csv.service_line_items.media_plan_id = csv.media_plans.id 
  ) T  INNER JOIN csv.service_line_items ON
       (csv.service_line_items.id = t.service_line_items_id 
            AND T.media_plan_id = csv.service_line_items.media_plan_id 
                AND T.is_billable_check != cast(csv.service_line_items.is_billable AS BOOLEAN));

Upvotes: 1

Related Questions