Home Optimise T-sql query
Reply: 0

Optimise T-sql query

user1529
1#
user1529 Published in July 17, 2018, 7:55 am

The query I need to develop is to list out sites which have no records (retail transactions) for a given date. I've been able to accomplish this for a specific site, but I know the query can be written more efficiently using joins but my foray into inners and outers hasn't been much fun.

Here's what I have so far:

DECLARE @StartDate DATE = '2017-11-01',
    @EndDate DATE = '2017-11-30';

SELECT tx.Txndate, count(*) as txCount
FROM [Report].[dbo].[FactTransactions] tx 
where tx.Site = 2
and tx.TxnDate between @StartDate and @EndDate
group by tx.TxnDate
union all
select db.daybookdate, 0 as txCount
from DimDaybook db
where db.daybookdate between @StartDate and @EndDate
and NOT EXISTS (SELECT 1 FROM [Report].[dbo].[FactTransactions] AS t WHERE t.TxnDate = db.daybookdate and t.txndate between @StartDate and @EndDate and t.site = 2) 
order by tx.Txndate

This returns me the result set for site 2

Txndate txCount
2017-11-01  1691
2017-11-02  1657
2017-11-03  1835
2017-11-04  1587
2017-11-05  1489
2017-11-06  1544
2017-11-07  1525
2017-11-08  1782
2017-11-09  1848
2017-11-10  1990
2017-11-11  0
2017-11-12  0

What I'd really like to have is a result set that looks like this and runs much much quicker than my hack (currently around 2mins - for context the transactions table has 83,486,412 records)

Site    Date    Transactions
2   11/11/17    0
2   12/11/27    0
3   12/11/17    0
22  1/11/17     0
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.630859 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO