Sql-server – Date variable to filter result for all previous years

datequerysql server

I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year. I don't want to hard code this years' date into WHERE or HAVING, but instead am interested to somehow have this dynamically pass the correct variable so that it also works in the future without changing the code. I appreciate any suggestions. Hope this is clear enough.

Best Answer

There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():

DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);

SELECT ... FROM dbo.Customers AS c
WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.Orders 
    WHERE CustomerID = c.CustomerID
    AND OrderDate >= @BeginningOfThisYear
);

Less intuitive ways include:

DECLARE @BeginningOfThisYear date = DATEADD(DAY, 
  1-DATEPART(DAYOFYEAR, SYSUTCDATETIME()), 
  SYSUTCDATETIME()
);

And downright terrible ways include:

DECLARE @BeginningOfThisYear date = CONVERT(date,
  CONCAT(YEAR(SYSUTCDATETIME()),'0101'));

Related Solutions

Sql-server – Refactoring sql for tables with similar columns but different table names

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter 
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0 
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0 
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0 
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0 
BREAK
END
RETURN
END


GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'


DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function 
FROM    dbo.fnSplit(@varyears, ',')


EXEC ( @combinedString )

Sql-server – Store the resulting list of a SELECT in a variable to count AND use the result

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course.

DECLARE @Value int, @Label varchar(25)  
DECLARE @Tmp table (value int, label varchar(25)) 

INSERT INTO @Tmp (value, label) 
    SELECT 1, 'One' 
UNION SELECT 2, 'Two' 
UNION SELECT 3, 'Three' 

WHILE (SELECT count(1) FROM @Tmp) > 0 
BEGIN 
    SELECT TOP 1 @Value = value, @Label = label FROM @Tmp ORDER BY value 
    DELETE FROM @Tmp WHERE value = @Value 
    SELECT @Value 'value', @Label 'label' 
END

Best Answer

Related Solutions

Sql-server – Refactoring sql for tables with similar columns but different table names

Sql-server – Store the resulting list of a SELECT in a variable to count AND use the result

Related Question