SQL - Hierarchy Node Tree With SUM Total At The Last Node

Each Object has a Parent Object, its Parent Object might have its another own Parent Object as well. Calculate the Sum Total of Time from the root to bottom. This may help you in calculating total time taken in project management that has a complex workflow design.  

CREATE TABLE WaitingTime ( OBJECT VARCHAR(10) , ParentObject VARCHAR(10) , WaitingTime INT )
INSERT INTO WaitingTime values ( 'A' ,NULL, 1)
INSERT INTO WaitingTime values ( 'B' ,'A', 2)
INSERT INTO WaitingTime values ( 'C' ,'A', 3)
INSERT INTO WaitingTime values ( 'D' ,'B', 4)
INSERT INTO WaitingTime values ( 'E' ,'B', 5)
INSERT INTO WaitingTime values ( 'F' ,'C', 6)
INSERT INTO WaitingTime values ( 'G' ,'C', 7)
 
 
;With cte AS (
SELECT *, WaitingTime as TotalWaitingTime FROM
waitingTime
UNION ALL
SELECT w.*, TotalWaitingTime+ w.WaitingTime 
FROM WaitingTime w
JOIN cte
ON w.ParentObject = cte.Object
)
SELECT OBJECT , ParentObject , WaitingTime , max(TotalWaitingTime)
FROM cte
GROUP BY OBJECT , ParentObject , WaitingTime

SQL - Select Statement With Conditions Of Different Table Like Price List Table

CREATE TABLE Price_Table_2020
(
 product Nvarchar(50),
 price INTEGER,
 product_description Nvarchar(12)
);
 
INSERT INTO Price_Table_2020
  (product, price, product_description)
VALUES
  ('ball', '100', 'plastic ball'),
  ('ink', '80', 'ink for pen'),
  ('pen', '1000', 'pen'),
  ('bucket', '200', 'bucket');
 
CREATE TABLE Price_Table_2019
(
  product VARCHAR(6),
  price INTEGER,
  product_description VARCHAR(12)
);
 
INSERT INTO Price_Table_2019
  (product, price, product_description)
VALUES
  ('ball', '90', 'plastic ball'),
  ('ink', '70', 'ink for pen'),
  ('pen', '900', 'pen'),
  ('bucket', '100', 'bucket');
 
CREATE TABLE Price_Table_2018
(
  product VARCHAR(6),
  price INTEGER,
  product_description VARCHAR(12)
);
 
INSERT INTO Price_Table_2018
  (product, price, product_description)
VALUES
  ('ball', '80', 'plastic ball'),
  ('ink', '60', 'ink for pen'),
  ('pen', '800', 'pen'),
  ('bucket', '300', 'bucket');
 
CREATE TABLE Product
(
  product VARCHAR(4),
  invoice_year INTEGER
);
 
INSERT INTO Product
  (product, invoice_year)
VALUES
  ('pen', '2019'),
  ('ball', '2020'),
  ('ink', '2020');
 
 
SELECT pr.product,
       pr.invoice_year,
       pc.price
FROM product pr
JOIN (
  SELECT *, 2020 AS year
  FROM price_table_2020
  UNION ALL
  SELECT *, 2019
  FROM price_table_2019
  UNION ALL
  SELECT *, 2018
  FROM price_table_2018
) pc ON pc.product = pr.product
    AND pc.year = pr.invoice_year
ORDER BY pr.invoice_year, pr.product
 

--Alternately

SELECT p.*, coalesce(p2018.price, p2019.price, p2020.price) AS invoice_price
FROM Product p
LEFT JOIN price_table_2018 p2018 ON p2018.product = p.product and p.invoice_year = 2018
LEFT JOIN price_table_2019 p2019 ON p2019.product = p.product and p.invoice_year = 2019
LEFT JOIN price_table_2020 p2020 ON p2020.product = p.product and p.invoice_year = 2020
ORDER BY p.product;
 
SELECT p.*, p2018.price, p2019.price, p2020.price
FROM Product p
LEFT JOIN price_table_2018 p2018 ON p2018.product = p.product and p.invoice_year = 2018
LEFT JOIN price_table_2019 p2019 ON p2019.product = p.product and p.invoice_year = 2019
LEFT JOIN price_table_2020 p2020 ON p2020.product = p.product and p.invoice_year = 2020
ORDER BY p.product;
 
Select * FROM Product
Select * from Price_Table_2020
Select * from Price_Table_2019
Select * from Price_Table_2018

 
  --Or
  --Restructuring Your Tables to put all the price data in one table, adding a year column to that table to indicate which year the price is applicable to.
 
SELECT pr.product,
       pr.invoice_year,
       pc.price
FROM product pr
JOIN price_table pc ON pc.product = pr.product
                   AND pc.year = pr.invoice_year
ORDER BY pr.invoice_year, pr.product

SQL - Alternate Value In the Column

Query The Table To Find Out the Column Values in the pattern alternately, Boy and Girl.

CREATE Table [Data]
(
  ID INT,
  [Col A]  NVARCHAR(10),
  [Col B]  NVARCHAR(10),
  [Col C] NVARCHAR(10)
);
--drop table [data]
INSERT INTO [data] (ID, [Col C]) VALUES
(1, 'Girl'),
(2, 'Boy'),
(3, 'Girl'),
(4, 'Boy'),
(5, 'Girl'),
(6, 'Boy'),
(7, 'Girl'),
(8, 'Boy'),
(9, 'Girl'),
(10,'Boy');
 
---OR
 
SELECT d.ID,
       d.[Col C],
       CASE WHEN ( d.[Col C] = coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '')
               and d.[Col C] = coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '') ) THEN 'FAIL'
            WHEN d.[Col C] <> coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '')     THEN 'Ok' -- First Line
            WHEN d.[Col C] <> coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '')    THEN 'Ok' -- Last Line
       END AS B
FROM [DATA] d
ORDER BY d.ID;