December 13, 2018 at 5:04 pm
All,
I have the following DDL/DML:
/****** Object: Table [dbo].[tabusrVoucher] Script Date: 13/12/2018 23:44:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tabusrVoucher](
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[amount] [int] NULL,
[Code] [varchar](50) NOT NULL,
[ConfirmedAt] [datetime] NULL,
[ValueRef] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tabusrVoucher] ([uniqueref], [amount], [Code], [ConfirmedAt], [ValueRef]) VALUES (N'fd1cf53b-fa40-4864-b302-6963d37f4220', 80, N'abcd', CAST(N'2018-01-01T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[tabusrVoucher] ([uniqueref], [amount], [Code], [ConfirmedAt], [ValueRef]) VALUES (N'a812661c-339c-43ba-aa51-ffdd332b6faf', 80, N'abcde', CAST(N'2017-01-12T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[tabusrVoucher] ([uniqueref], [amount], [Code], [ConfirmedAt], [ValueRef]) VALUES (N'f1972d66-e89f-4e77-8966-65feaaeac502', 80, N'abcdef', NULL, NULL)
GO
INSERT [dbo].[tabusrVoucher] ([uniqueref], [amount], [Code], [ConfirmedAt], [ValueRef]) VALUES (N'a9ea8c38-1991-4998-9816-4963f5520e38', 80, N'abcdefg', NULL, NULL)
GO
ALTER TABLE [dbo].[tabusrVoucher] ADD CONSTRAINT [DF_tabusrVoucher_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
My current query is:
select
v1.amount, count(v1.code),
(
select count (code) from tabusrvoucher
where tabusrvoucher.amount=v1.amount
and datediff(d,confirmedat,getdate())<365
group by amount)
from
tabusrvoucher v1
where v1.ConfirmedAt is null
group by v1.amount
having count(v1.code)<5
Is it possible to avoid the use of the subquery? I wondered about something using the over by or partition but I don't think I can because it won't allow me to set a constraint? I also wondered about a left outer join but couldn't make that work without getting a Cartesian result.
Thanks
December 13, 2018 at 6:45 pm
I think this is the same:select v1.amount,
count(v1.code) Count1,
Count2
from tabusrvoucher v1
outer apply(select count(code)
from tabusrvoucher
where tabusrvoucher.amoun t= v1.amount
and datediff(d,confirmedat,getdate()) < 365
group by amount) T(Count2)
where v1.ConfirmedAt is null
group by v1.amount,T.Count2
having count(v1.code)<5
December 14, 2018 at 1:22 pm
select v1.amount,
sum(case when v1.ConfirmedAt is null then 1 else 0 end) as ConfirmNullCount,
sum(case when datediff(day,v1.confirmedat,getdate())<365 then 1 else 0 end) as ConfirmDateCount
from tabusrvoucher v1
group by v1.amount
having count(v1.code)<5
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 14, 2018 at 2:12 pm
Both,
Thank you for your help.
Thanks
December 15, 2018 at 5:38 am
My suggestion is to add a covering no-clustered index and use Scott's query, that will reduce the effort to a single scan of the index without any sorting.
😎
CREATE NONCLUSTERED INDEX NCLIDX_DBO_tabusrVoucher_AMOUNT_INCL_CODE_CONFIRMEDAT ON dbo.tabusrVoucher(amount ASC) INCLUDE ([Code], [ConfirmedAt]);
December 16, 2018 at 8:17 am
Since no one specifically identified it and with the idea of teaching a man to fish and how to unsnarl his reel, the other thing that Scott's code does is it gets rid of the following non-Sargable predicate:
and datediff(d,confirmedat,getdate())<365
In case you don't know, in most cases, having a formula in the "Search ARGument" (which is where the term "SARGability" comes from) on a column in an ON or WHERE clause will destroy the ability to ever be able to do an index seek. The entire index will need to be scanned, instead, because the results of the formula is what is being compared and so it must be applied to every row in the index.
If you were to keep that search criteria in the WHERE clause, it should be rewritten to keep the d.confirmedat column from being a part of a formula, as follows...
AND d.ConfirmedAt > DATEADD(dd,-365,GETDATE())
If d.ConfirmedAt is of the DATETIME datatype, it can be even simpler and does follow ANSI standards (which DATE and DATETIME2 do not)....
AND d.ConfirmedAt > GETDATE()-365
Remember, don't use columns in formulas for search criteria. Always rewrite the formula so that it's not included. There's an exception for doing "Whole Date" comparisons but I'd avoid even that for three reasons... 1) although it will produce a seek, it's still slower, 2) a lot of people don't know and will never know the exception and so it confuses them when troubleshooting or they may thing it's OK to do in other places, and 3) I hate having to remember exceptions and have two different methods for doing precisely the same thing. That latter one is also one of the big reasons I don't use the DATETIME2 datatype and almost always avoid the DATE datatype even though the latter has some good savings in space. In fact, that's the only reason why I might use the DATE datatype... to save space.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2018 at 8:25 am
All,
Thank you for the advice. Sorry for taking a while to reply to some posts.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply