How list only rows of newer dates?

by ·Comments Off on How list only rows of newer dates?

Product:
Microsoft SQL server

Issue:
How to in a view only list the data rows for the last 3 years?

Solution:

You need to have a column with the date in your fact table. If the date column in your fact table is a int, you have to join it with a date conversion table or use cast/convert.

We have used a date table (DM.dimdate).

The key_dimdate is a integer, and the date column is in the date format in SQL. The date format make it easy to compare it to a date question in SQL.

Create a SQL similar to this:

SELECT a.[Customer]
,a.[Account]
,a.[Order]
,a.[key_dimDate]
,a.[key_dimVersion]
,a.[Amount]
,a.[startDate] as [startDate]
,a.[endDate] as [endDate]
,a.[IsActive] as [IsActive]
FROM [DM].[facttable] a
inner JOIN [DM].[dimDate] f on a.[key_dimDate] = f.[key_dimDate]
where 1=1
and a.[IsActive] = 'Y'
-- and DATEADD(year, -3, GETDATE() ) < f.[Date] -- will list 3 years
and DATEADD(Quarter, -13, GETDATE() ) < f.[Date]  -- will list 39 months

 

If you get error like Column ‘id’ in field list is ambiguous, then you have missed to set the alias letter in front of all the columns references in the SQL query.

If you are getting an error: “Arithmetic overflow error converting expression to data type datetime.” Is that the F.Date in above SQL is a int, you have to convert it to a date somehow.

Only using CONVERT(DATETIME,[key_dimDate],103) can give overflow error.

Change the SQL to reflect your columns and tables.

 

More Information:

https://www.w3schools.com/sql/func_sqlserver_convert.asp 

https://www.sqlshack.com/sql-server-functions-for-converting-string-to-date/ 

https://www.w3schools.com/sql/func_sqlserver_dateadd.asp 

Let’s say you need to add five months to current date, use this:

SELECT * FROM YourTable
WHERE YourDate < DATEADD(month, 5, GETDATE())

I used function GETDATE() for getting current DateTime.

If you need to subtract some time, just add minus to second parameter:

SELECT * FROM YourTable
WHERE YourDate < DATEADD(month, -5, GETDATE())

https://koz.tv/sql-query-for-todays-date-minus-year-month-day-or-minute/ 

https://www.mssqltips.com/sqlservertip/2509/add-and-subtract-dates-using-dateadd-in-sql-server/ 

https://www.sqlshack.com/how-to-add-or-subtract-dates-in-sql-server/ 

 

To see the SQL job log for a time period, try this in SQL server (will not work in Azure SQL)

SELECT
@@SERVERNAME as ‘Server’, j.name as ‘Job Name’,
jh.run_date as ‘Run Date’, jh.run_status as ‘Job Status’
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (
SELECT ROW_NUMBER() OVER(PARTITION BY jh.job_id ORDER BY jh.run_date DESC) AS row_num, jh.*
FROM msdb.dbo.sysjobhistory jh
WHERE
jh.step_id = 0 AND
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112) AND
jh.run_date <= CONVERT(varchar(8), GETDATE() + 1, 112)
–ORDER BY jh.run_date DESC
) AS jh ON jh.job_id = j.job_id
WHERE j.enabled = 1 AND jh.run_status = 0
ORDER BY j.name, jh.run_date;

 

https://www.w3schools.com/sql/sql_join.asp 

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table