>Kevin,
>
>I also asked about execution plan for Martina's SQL. I agree with your observation re table scan for transformations on fields but I *think* Martina's will be OK since the transformation only targets parameters, not fields - so hopefully the SQL Server elves are smart enough to perform it only once, at the start.
>
>Bill: you're a seasoned campaigner so I'm interested that "efficiency as default position" isn't gospel. Most of we old-timers can't escape it, even in days when queries run in a blink of a second. Until they don't. ;-)
>
>It'll take only a few moments and will edify us too: how does that plan look?
>
Here's what I ran

 DECLARE @fromyear INT
  DECLARE @toyear INT
  DECLARE @frommonth INT
  DECLARE @tomonth INT
  SELECT @fromyear = 2016
  SELECT @toyear = 2016
  SELECT @frommonth = 1
  SELECT @tomonth = 12
   
  
  SELECT DISTINCT inv_number FROM view_expandedbilling   WHERE  inv_date BETWEEN CONVERT(DATETIME,CAST(@fromyear AS VARCHAR(4))+'-'+CAST(@frommonth AS VARCHAR(2))+'-01',120) AND 
            DATEADD(m,1,CONVERT(DATETIME,CAST(@toyear AS VARCHAR(4))+'-'+CAST(@tomonth AS VARCHAR(2))+'-01',120))-1     ORDER BY inv_number

It returned 6519 rows in this time 00:00:04
When I ran it the second time the time was 00:00:00

Don't know how to read one of these things, but it said that I was missing a nonclustered index on inv_date and there is one.
/*
Missing Index Details from SQLQuery1.sql - BILL-PC.safesignature (BILL-PC\Bill Fitzgerald (54))
The Query Processor estimates that implementing the following index could improve the query cost by 84.9048%.
*/

/*
USE [safesignature]
GO
CREATE NONCLUSTERED INDEX ON [dbo].[billing] ([inv_date])
INCLUDE ([inv_number],[storecode])
GO
*/

In practice, I run this query for one client - another parameter- and return about a hundred rows, and the time is miniscule.
I ommited that parameter to get the numbers to mean something




>One other issue: does the version you deployed for invoice date range include the whole last day of month, or only datetimes with 00:00:000 seconds on last day on month? IOW leaving out any other time of day than midnight? Maybe it doesn't matter since times are not stored - yet. ;-) This is the point I tried to make re the subtraction from first of following month, probably belonging in another dateadd. Actually I think that's recommended these days for any transformation on a datetime, not just this instance. Or is it using one of the date fields introduced in SQL2008?

It includes the time