Need help to filter based on From (Month and Year) and To (Month and Year)

• Qira

  SSCommitted

  Points: 1930

  More actions

  December 7, 2014 at 2:27 am

  #299221

  This is my table and data,

  CREATE TABLE [dbo].[paymentTrnx](

  [idx] [int] IDENTITY(1,1) NOT NULL,

  [paymentDte] [date] NULL,

  [officialReceiptNo] [nvarchar](16) NULL,

  [sysReceiptNo] [nvarchar](16) NULL

  ) ON [PRIMARY]

  GO

  SET IDENTITY_INSERT [dbo].[paymentTrnx] ON

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (1, CAST(0x60350B00 AS Date), N'018567810', N'2012-01-000005')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (2, CAST(0x60350B00 AS Date), N'018567810', N'2012-01-000005')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (3, CAST(0x60350B00 AS Date), N'018567810', N'2012-01-000005')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (4, CAST(0x60350B00 AS Date), N'018567911', N'2012-01-000006')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (5, CAST(0x60350B00 AS Date), N'018567911', N'2012-01-000006')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (6, CAST(0x60350B00 AS Date), N'018567911', N'2012-01-000006')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (7, CAST(0x62350B00 AS Date), N'018568113', N'2012-01-000008')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (8, CAST(0x62350B00 AS Date), N'018568214', N'2012-01-000009')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (9, CAST(0x68350B00 AS Date), N'018568315', N'2012-01-000010')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (10, CAST(0x96310B00 AS Date), N'0140423', N'2009-01-03959')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (11, CAST(0x96310B00 AS Date), N'0140424', N'2009-01-03960')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (12, CAST(0x96310B00 AS Date), N'0140425', N'2009-01-03961')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (13, CAST(0x96310B00 AS Date), N'0140428', N'2009-01-03964')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (14, CAST(0x96310B00 AS Date), N'0140429', N'2009-01-03965')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (15, CAST(0x96310B00 AS Date), N'0140432', N'2009-01-03968')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (16, CAST(0x96310B00 AS Date), N'0140434', N'2009-01-03970')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (17, CAST(0x96310B00 AS Date), N'0140436', N'2009-01-03972')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (18, CAST(0x96310B00 AS Date), N'0140439', N'2009-01-03975')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (19, CAST(0x96310B00 AS Date), N'0140441', N'2009-01-03977')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (20, CAST(0x96310B00 AS Date), N'0138152', N'2009-04-00666')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (21, CAST(0x96310B00 AS Date), N'0140443', N'2009-01-03979')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (22, CAST(0x96310B00 AS Date), N'0141726', N'2009-01-03981')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (23, CAST(0x96310B00 AS Date), N'0140445', N'2009-01-03982')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (24, CAST(0x96310B00 AS Date), N'0133944', N'2009-03-00623')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (25, CAST(0x96310B00 AS Date), N'0140447', N'2009-01-03984')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (26, CAST(0x96310B00 AS Date), N'0140449', N'2009-01-03986')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (27, CAST(0x96310B00 AS Date), N'0141727', N'2009-01-03987')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (28, CAST(0x96310B00 AS Date), N'0140451', N'2009-01-03989')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (29, CAST(0x96310B00 AS Date), N'0140452', N'2009-01-03991')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (30, CAST(0x96310B00 AS Date), N'0140453', N'2009-01-03992')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (31, CAST(0x96310B00 AS Date), N'0140455', N'2009-01-03994')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (32, CAST(0x96310B00 AS Date), N'0140456', N'2009-01-03995')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (33, CAST(0x96310B00 AS Date), N'0141729', N'2009-01-03998')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (34, CAST(0x96310B00 AS Date), N'0140461', N'2009-01-04001')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (35, CAST(0x96310B00 AS Date), N'0140462', N'2009-01-04002')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (36, CAST(0x96310B00 AS Date), N'0135652', N'2009-06-00505')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (37, CAST(0x96310B00 AS Date), N'0136669', N'2009-05-00354')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (38, CAST(0x96310B00 AS Date), N'0141731', N'2009-01-04004')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (39, CAST(0x96310B00 AS Date), N'0139065', N'2009-10-00746')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (40, CAST(0x96310B00 AS Date), N'0138154', N'2009-04-00668')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (41, CAST(0x96310B00 AS Date), N'0138155', N'2009-04-00669')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (42, CAST(0x96310B00 AS Date), N'0139067', N'2009-10-00748')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (43, CAST(0x99310B00 AS Date), N'0141735', N'2009-01-04008')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (44, CAST(0x99310B00 AS Date), N'0125883', N'2009-09-00358')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (45, CAST(0x99310B00 AS Date), N'0133945', N'2009-03-00624')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (46, CAST(0x99310B00 AS Date), N'0136670', N'2009-05-00355')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (47, CAST(0x99310B00 AS Date), N'0126988', N'2009-06-00506')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (48, CAST(0x99310B00 AS Date), N'0136671', N'2009-05-00356')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (49, CAST(0x99310B00 AS Date), N'0141744', N'2009-01-04018')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (50, CAST(0x99310B00 AS Date), N'0133949', N'2009-03-00629')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (51, CAST(0x99310B00 AS Date), N'0125884', N'2009-09-00359')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (52, CAST(0x99310B00 AS Date), N'0141745', N'2009-01-04019')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (53, CAST(0x99310B00 AS Date), N'0139070', N'2009-10-00751')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (54, CAST(0x99310B00 AS Date), N'0136672', N'2009-05-00357')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (55, CAST(0x99310B00 AS Date), N'0126990', N'2009-06-00508')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (56, CAST(0x99310B00 AS Date), N'0123697', N'2009-07-00400')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (57, CAST(0x99310B00 AS Date), N'0141751', N'2009-01-04025')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (58, CAST(0x99310B00 AS Date), N'0139072', N'2009-10-00753')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (59, CAST(0x9A310B00 AS Date), N'0136674', N'2009-05-00359')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (60, CAST(0x9A310B00 AS Date), N'0138158', N'2009-04-00672')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (61, CAST(0x9A310B00 AS Date), N'0125885', N'2009-09-00360')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (62, CAST(0x9A310B00 AS Date), N'0141756', N'2009-01-04031')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (63, CAST(0x9A310B00 AS Date), N'0141757', N'2009-01-04032')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (64, CAST(0x9B310B00 AS Date), N'0131941', N'2009-07-00403')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (65, CAST(0x9B310B00 AS Date), N'0141767', N'2009-01-04046')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (66, CAST(0x9B310B00 AS Date), N'0123412', N'2009-03-00630')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (67, CAST(0x99310B00 AS Date), N'0139075', N'2009-10-00756')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (68, CAST(0x9B310B00 AS Date), N'0141768', N'2009-01-04047')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (69, CAST(0x9B310B00 AS Date), N'0136675', N'2009-05-00360')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (70, CAST(0x99310B00 AS Date), N'0139076', N'2009-10-00757')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (71, CAST(0x9B310B00 AS Date), N'0142008', N'2009-10-00758')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (72, CAST(0x9B310B00 AS Date), N'0141778', N'2009-01-04058')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (73, CAST(0x9B310B00 AS Date), N'0141779', N'2009-01-04059')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (74, CAST(0x9A310B00 AS Date), N'0138160', N'2009-04-00674')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (75, CAST(0x9B310B00 AS Date), N'0139077', N'2009-10-00759')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (76, CAST(0xD2310B00 AS Date), N'0143280', N'2009-01-05017')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (77, CAST(0xD2310B00 AS Date), N'0135722', N'2009-06-00605')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (78, CAST(0xD2310B00 AS Date), N'0143282', N'2009-01-05019')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (79, CAST(0xD2310B00 AS Date), N'0125922', N'2009-09-00425')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (80, CAST(0xD2310B00 AS Date), N'0143283', N'2009-01-05020')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (81, CAST(0xD2310B00 AS Date), N'0143286', N'2009-01-05023')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (82, CAST(0xD2310B00 AS Date), N'0139156', N'2009-10-00946')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (83, CAST(0xD2310B00 AS Date), N'0143287', N'2009-01-05024')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (84, CAST(0xD2310B00 AS Date), N'0143288', N'2009-01-05025')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (85, CAST(0xD2310B00 AS Date), N'0144730', N'2009-01-05034')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (86, CAST(0xD2310B00 AS Date), N'0135724', N'2009-06-00607')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (87, CAST(0xD2310B00 AS Date), N'0143553', N'2009-01-05035')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (88, CAST(0xD2310B00 AS Date), N'0145544', N'2009-03-00784')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (89, CAST(0xD2310B00 AS Date), N'0136757', N'2009-05-00447')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (90, CAST(0xD2310B00 AS Date), N'0143554', N'2009-01-05036')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (91, CAST(0xD2310B00 AS Date), N'0144731', N'2009-01-05037')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (92, CAST(0xD2310B00 AS Date), N'0143555', N'2009-01-05038')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (93, CAST(0xD2310B00 AS Date), N'0136761', N'2009-05-00451')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (94, CAST(0xD2310B00 AS Date), N'0123472', N'2009-03-00785')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (95, CAST(0xD2310B00 AS Date), N'0143556', N'2009-01-05040')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (96, CAST(0xD2310B00 AS Date), N'0143557', N'2009-01-05041')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (97, CAST(0xD2310B00 AS Date), N'0139160', N'2009-10-00951')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (98, CAST(0xD2310B00 AS Date), N'0142119', N'2009-10-00954')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (99, CAST(0xD2310B00 AS Date), N'0143559', N'2009-01-05045')

  INSERT [dbo].[paymentTrnx] ([idx], [paymentDte], [officialReceiptNo], [sysReceiptNo]) VALUES (100, CAST(0xD2310B00 AS Date), N'0142120', N'2009-10-00955')

  GO

  print 'Processed 100 total records'

  SET IDENTITY_INSERT [dbo].[paymentTrnx] OFF

  How to filter data based on From (Month and Year) and To (Month and Year)? No need to define the day in the date.

  Let's say, user parameter is

  From: Jan-2013

  To: Dec 2013

  From: Feb-2014

  To: July-2014

  From: Mar-2014

  To: Sept-2014

  Need help. As a result, my resultset is accurate

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 7, 2014 at 11:14 am

  #1763167

  Quick suggestion

  😎

  SELECT

  PT.idx

  ,PT.paymentDte

  ,PT.officialReceiptNo

  ,PT.sysReceiptNo

  FROM dbo.paymentTrnx PT

  WHERE (YEAR(PT.paymentDte) * 100) + MONTH(PT.paymentDte) BETWEEN 201201 AND 201203;

• Jeff Moden

  SSC Guru

  Points: 1004341

  More actions

  December 7, 2014 at 1:15 pm

  #1763172

  Eirikur Eiriksson (12/7/2014)

  ---

  Quick suggestion

  😎

  SELECT

  PT.idx

  ,PT.paymentDte

  ,PT.officialReceiptNo

  ,PT.sysReceiptNo

  FROM dbo.paymentTrnx PT

  WHERE (YEAR(PT.paymentDte) * 100) + MONTH(PT.paymentDte) BETWEEN 201201 AND 201203;

  Careful now... that won't be SARGable.

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

• Jeff Moden

  SSC Guru

  Points: 1004341

  More actions

  December 7, 2014 at 1:37 pm

  #1763175

  This should do it and it will be SARGable...

  --===== These would be parameters for a stored procedure.

  DECLARE @pStartMonthDate DATETIME --Can be any date in the starting month

  ,@pEndMonthDate DATETIME --Can be any date in the starting month

  ;

  --===== Just filling in the parameters with on of the examples.

  -- Feel free to change it. Can be just about any legal format.

  -- This snippet would normally not be included in the proc.

  -- We're just testing here.

  SELECT @pStartMonthDate = 'Feb 2012'

  ,@pEndMonthDate = 'March 2012'

  ;

  --===== Setup the start and end date to make a SARGable query.

  -- Finds first of next month after the desired end month.

  SELECT @pStartMonthDate = DATEADD(mm,DATEDIFF(mm, 0,@pStartMonthDate),0)

  ,@pEndMonthDate = DATEADD(mm,DATEDIFF(mm,-1,@pEndMonthDate) ,0)

  ;

  --===== Get the rows according to the parameters in a SARGable manner.

  SELECT idx, paymentDte, officialReceiptNo, sysReceiptNo

  FROM dbo.paymentTrnx

  WHERE paymentDte >= @pStartMonthDate --This could use an index on paymentDte

  AND paymentDte < @pEndMonthDate --This could use an index on paymentDte

  ;

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

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 7, 2014 at 1:50 pm

  #1763176

  Eirikur Eiriksson (12/7/2014)

  ---

  Quick suggestion

  😎

  SELECT

  PT.idx

  ,PT.paymentDte

  ,PT.officialReceiptNo

  ,PT.sysReceiptNo

  FROM dbo.paymentTrnx PT

  WHERE (YEAR(PT.paymentDte) * 100) + MONTH(PT.paymentDte) BETWEEN 201201 AND 201203;

  Bullseye Jeff, and point taken, should have mentioned the resulting scan, more appropriate would be "quick brute force suggestion"

  😎

  Table 'paymentTrnx'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Edit: hit by the reply bug:w00t:

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 7, 2014 at 2:00 pm

  #1763177

  BTW the 2014 cardinality estimator goes on a hike on this one, with only handful of rows in the set it estimates 16.4317 rows for both queries, in fact the optimizer produces exactly the same plan for both.

  😎