asked on

t-sql filter by date perio

I have a sql server table that looks like this

The script to create table looks like this:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesTest1](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[OrderDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
 CONSTRAINT [PK_EmployeesTest1] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[EmployeesTest1] ON 

INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(N'1968-12-08T00:00:00.000' AS DateTime), CAST(N'2014-05-01T00:00:00.000' AS DateTime), N'507 - 20th Ave. E.
Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales', N'Dr.', CAST(N'1972-02-19T00:00:00.000' AS DateTime), CAST(N'2016-08-14T00:00:00.000' AS DateTime), N'908 W. Capital Way', N'Tacoma', N'WA', N'98401', N'USA', N'(206) 555-9482')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative', N'Ms.', CAST(N'1963-08-30T00:00:00.000' AS DateTime), CAST(N'2017-04-01T00:00:00.000' AS DateTime), N'722 Moss Bay Blvd.', N'Kirkland', N'WA', N'98033', N'USA', N'(206) 555-3412')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative', N'Mrs.', CAST(N'1967-09-19T00:00:00.000' AS DateTime), CAST(N'2017-05-03T00:00:00.000' AS DateTime), N'4110 Old Redmond Rd.', N'Redmond', N'WA', N'98052', N'USA', N'(206) 555-8122')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager', N'Mr.', CAST(N'1985-03-04T00:00:00.000' AS DateTime), CAST(N'2016-10-17T00:00:00.000' AS DateTime), N'14 Garrett Hill', N'London', NULL, N'SW1 8JR', N'UK', N'(71) 555-4848')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (6, N'Suyama', N'Michael', N'Sales Representative', N'Mr.', CAST(N'1993-07-02T00:00:00.000' AS DateTime), CAST(N'2015-10-12T00:00:00.000' AS DateTime), N'Coventry House
Miner Rd.', N'London', NULL, N'EC2 7JR', N'UK', N'(71) 555-7773')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (7, N'King', N'Robert', N'Sales Representative', N'Mr.', CAST(N'1970-05-29T00:00:00.000' AS DateTime), CAST(N'2017-01-02T00:00:00.000' AS DateTime), N'Edgeham Hollow
Winchester Way', N'London', NULL, N'RG1 9SP', N'UK', N'(71) 555-5598')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (8, N'Callahan', N'Laura', N'Inside Sales Coordinator', N'Ms.', CAST(N'1988-01-09T00:00:00.000' AS DateTime), CAST(N'2017-03-05T00:00:00.000' AS DateTime), N'4726 - 11th Ave. N.E.', N'Seattle', N'WA', N'98105', N'USA', N'(206) 555-1189')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (9, N'Dodsworth', N'Anne', N'Sales Representative', N'Ms.', CAST(N'1988-01-27T00:00:00.000' AS DateTime), CAST(N'2016-11-15T00:00:00.000' AS DateTime), N'7 Houndstooth Rd.', N'London', NULL, N'WG2 7LT', N'UK', N'(71) 555-4444')
SET IDENTITY_INSERT [dbo].[EmployeesTest1] OFF

Open in new window

The FiscalYear period runs from July 1 to June 30.

So for example

FiscalYear 2018 = 07/01/2017 to 06/30/2018
FiscalYear 2017 = 07/01/2016 to 06/30/2017
FiscalYear 2016 = 07/01/2015 to 06/30/2016

Todays date is 8/10/2017 so that means we are currently in Fiscal Year 2018.

How do I set the where clause in this query so that it filters the OrderDate column by the following criteria:?

Only show records with an OrderDate that falls in FiscalYears prior to the current FiscalYear

SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[Title]
,[TitleOfCourtesy]
,[BirthDate]
,[OrderDate]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
FROM [Northwind].[dbo].[EmployeesTest1]
WHERE [OrderDate]

HainKurt

here

declare @dts as date;
declare @dte as date;
declare @fy as int;

set @fy=case when month(getdate())<7 then year(getdate()) else year(getdate())+1 end;
--set @fy=2017;
set @dts=cast(@fy-1 as varchar)+'0701';
set @dte=cast(@fy as varchar)+'0630';

SELECT *, @fy, @dts, @dte
  FROM [EmployeesTest1]
WHERE [OrderDate] between @dts and @dte;

Open in new window

you can uncomment line 6 and hardcode year to check the query...
if you want, you can combine all logic into a single query...
or you can create 2 functions that returns fiscal start and end date and use it in your query...

* for previous fiscal year just use this, @line 6

set @fy=@fy-1;

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arnold

You can use day_of_year to see where you are in the term, before or after..then adjusting the range to either July 1 of this year or the prior.

You can limit your query by where that only has your fiscal year
Using dateadd, datediff, ...

Got to think the right clause will post back

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

ignoring between is a good thing for date ranges:

WHERE [OrderDate] >= @dts and [OrderDate] < @dte

but if @dte is set to "06-30" then you would be ignoring ALL of June 30

Way easier to only use "07-01" as the cutoff point, just adjusting for the year as needed.

arnold

If you want to partition the data or aggregate, group them, you can use the same logic to group based on the convertion that will also sort the rows into the fiscal years to which they belong when calculating

arnold

what information do you want if you run your query on july 1st?
the prior years data or no data?
the above conversion will return the prior year data in non leap years and current fiscal year in leap years for the july 1 run query