The text below is selected, press Ctrl+C to copy to your clipboard. (⌘+C on Mac) No line numbers will be copied.
Guest
SQL 2
By rajnish8987 on 30th January 2019 01:20:18 PM | Syntax: SQL | Views: 15



New paste | Download | Show/Hide line no. | Copy text to clipboard
  1. CREATE TABLE BAC1 (Billing_Account_Number INT, Billing_account_key INT, Parent_type VARCHAR(50), Active_flag CHAR(50), Deleted_flag CHAR(50),
  2. Created_date DATE, Edw_update_date DATE, Billing_status CHAR(20), Source_system CHAR(20), migrartion_date DATE);
  3.  
  4. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  5. Billing_status, Source_system, migrartion_date) VALUES(001, 111, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  6.  
  7. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  8. Billing_status, Source_system, migrartion_date) VALUES(002, 222, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  9.  
  10. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  11. Billing_status, Source_system, migrartion_date) VALUES(003, 333, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  12.  
  13. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  14. Billing_status, Source_system, migrartion_date) VALUES(004, 444, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  15.  
  16.  
  17. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  18. Billing_status, Source_system, migrartion_date) VALUES(005, 555, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  19.  
  20.  
  21. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  22. Billing_status, Source_system, migrartion_date) VALUES(006, 666, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  23.  
  24.  
  25. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  26. Billing_status, Source_system, migrartion_date) VALUES(007, 777, 'Bill', 'Yes', 'No', '28-jan-2019', '29-jan-2019', 'Completed', 'Laptop', '29-jan-2019');
  27.  
  28.  
  29. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  30. Billing_status, Source_system, migrartion_date) VALUES(008, 888, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  31.  
  32.  
  33. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  34. Billing_status, Source_system, migrartion_date) VALUES(009, 999, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  35.  
  36.  
  37. INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  38. Billing_status, Source_system, migrartion_date) VALUES(010, 1000, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  39.  
  40. SELECT * FROM BAC1;
  41.  
  42.  
  43. CREATE TABLE BAC_asset_relation (Billing_Account_Number INT, Asset_Number INT, Asset_Status CHAR(20));
  44.  
  45. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(001,00100,'Paid');
  46.  
  47. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(002,00200,'Paid');
  48.  
  49. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(003,00300,'Paid');
  50.  
  51. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(004,00400,'Paid');
  52.  
  53. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(005,00500,'Unpaid');
  54.  
  55. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(006,00600,'Paid');
  56.  
  57. INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(007,00700,'Unpaid');
  58.  
  59. SELECT * FROM BAC_asset_relation;
  60.  
  61.  
  62. SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  63. BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  64. BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status
  65. FROM BAC1 INNER JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number ASC;
  66.  
  67. SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  68. BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  69. BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status
  70. FROM BAC1 LEFT JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number DESC;
  71.  
  72.  
  73.  
  74.  
  75.  
  76.  
  77.  
  78.  
  79. SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  80. BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  81. BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status
  82. FROM BAC1 LEFT JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number DESC;
  83.  
  84. ALTER TABLE BAC1 ADD Asset_Status CHAR(20);
  85.  
  86. SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  87. BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  88. BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status, BAC1.Asset_Status
  89. FROM BAC1 INNER JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number ASC;
  90.  
  91.  
  92.  
  93.  
  94. UPDATE BAC1 (Asset_Status) SELECT BAC_asset_relation.Asset_Status FROM BAC_asset_relation WHERE BAC_asset_relation.Billing_Account_Number=Billing_Account_Number;
  95.  
  96. INSERT INTO  BAC1 (Asset_Status)
  97. SELECT Asset_Status FROM BAC_asset_relation
  98. WHERE Source_system='Laptop';
  99.  
  100.  
  101.  
  102. SELECT * FROM BAC1;
  103.  
  104. SELECT * FROM BAC_asset_relation;
  105.  
  106.  
  107.  DELETE FROM BAC1 WHERE BAC1.Asset_Status='Unpaid';



  • Recent Pastes