I need to create a View with select in my database. It must be view can't use stored procedure so it needs to be created in just one query without temp tales.

I create a simple example:

CREATE TABLE #TemporaryTable          -- Local temporary table - starts with single #
(
    id int,
    date_order date,
    order_status varchar(50)
);


insert into #TemporaryTable
VALUES
('1','2022-01-01','Completed'),
('2','2022-01-01','Cancelled'),
('3','2022-01-01','Completed'),
('4','2022-01-01','Completed'),
('5','2022-01-02','Cancelled'),
('6','2022-01-02','Cancelled'),
('7','2022-01-02','Completed'),
('8','2022-01-02','Completed'),
('9','2022-01-02','Completed'),
('10','2022-01-03','Cancelled'),
('11','2022-01-04','Completed')



select * from #TemporaryTable


SELECT COUNT(crm1.date_order) AS [count_all_orders], crm1.date_order AS [date_order],COUNT(crm2.date_order) AS [[count_cancelled_orders]
FROM            #TemporaryTable crm1 WITH (nolock)
left outer join #TemporaryTable crm2 WITH (nolock) on crm2.id = crm1.id and crm2.order_status ='Cancelled'
GROUP BY crm1.date_order,crm2.date_order

Now the date is doubled and giving bad values. Please check that in SQL fiddle and help me. Thank you!

CodePudding user response：

There is no need to join, you can simply use conditional aggregation:

COUNT(someNonNullValue) and COUNT(*) are the same thing

SELECT
  COUNT(*) AS count_all_orders,
  crm1.date_order,
  COUNT(CASE WHEN crm2.order_status = 'Cancelled' THEN 1 END) AS count_cancelled_orders
FROM #TemporaryTable crm
GROUP BY
  crm.date_order;

NOLOCK is a very bad idea. Only use it in exceptional circumstances.