Subquery - another way to achieve the same result?

Question

Subquery - another way to achieve the same result?

as_1234

SSCrazy

Points: 2849
More actions
December 13, 2018 at 5:04 pm

#369370

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 100955 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

as_1234 SSCrazy Points: 2849 More actions · Answer 3

as_1234

SSCrazy

Points: 2849

December 14, 2018 at 2:12 pm

#2016363

Both,

Thank you for your help.

Thanks

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

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]);

Jeff Moden SSC Guru Points: 1003903 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2849 More actions · Answer 6

All,

Thank you for the advice. Sorry for taking a while to reply to some posts.

Thanks