Date Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Date Function

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-07 : 16:25:03

I need to calculate a date which is 7 days prior to today how would I do that? and 7 days prior from a specific date?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 16:27:57

Use DATEADD:

DATEADD(d, -7, GETDATE())

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-07 : 16:28:04

Look in Books Online at the DATEADD() function.

What she said.

aravindt77
Posting Yak Master

120 Posts

Posted - 2010-10-08 : 02:10:35

CONVERT(VARCHAR(10) , DATEADD(d , -7 ,GETDATE() ) , 110)...to be more specific in date by avoiding the time factor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 02:12:56

No, actually use this to remove the time:

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 7, 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-08 : 10:29:04

This is the query I have. Where the hard coded dates are I need to changes those to T-7 days
will the date add work?

USE [cSupport]
GO

/****** Object: View [dbo].[Weekly Stats] Script Date: 10/08/2010 10:25:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[Weekly Stats]
AS
SELECT TOP (100) PERCENT
GROUP_NAME, SUM(CREATED) AS CREATED, SUM(CLOSED) AS CLOSED, SUM([OPEN]) AS [OPEN], SUM([OPEN30]) AS [OPEN30], SUM([OPEN90]) AS [OPEN90]
FROM (
SELECT
GROUPS.GROUP_NAME, COUNT(*) AS CREATED, 0 AS CLOSED, 0 AS [OPEN], 0 AS [OPEN30], 0 AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
and (DT_CREATED>='2010-09-30 23:59:59.000')
GROUP BY GROUPS.GROUP_NAME

UNION ALL

SELECT
GROUPS.GROUP_NAME, 0 AS CREATED, COUNT(*) AS CLOSED, 0 AS [OPEN], 0 AS [OPEN30], 0 AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
and (DT_CLOSED>='2010-09-30 23:59:59.000')
GROUP BY GROUPS.GROUP_NAME

UNION ALL

SELECT
GROUPS.GROUP_NAME, 0 AS CREATED, 0 AS CLOSED, COUNT(*) AS [OPEN], 0 AS [OPEN30], 0 AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
and INCIDENTS.DT_CLOSED IS NULL
GROUP BY GROUPS.GROUP_NAME

UNION ALL

SELECT
GROUPS.GROUP_NAME, 0 AS CREATED, 0 AS CLOSED, 0 AS [OPEN], COUNT(*) AS [OPEN30], 0 AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
and INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<='2010-08-30 23:59:59.000'
GROUP BY GROUPS.GROUP_NAME

UNION ALL

SELECT
GROUPS.GROUP_NAME, 0 AS CREATED, 0 AS CLOSED, 0 AS [OPEN], 0 AS [OPEN30], COUNT(*) AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
and INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<='2010-06-30 23:59:59.000'
GROUP BY GROUPS.GROUP_NAME

) AS W

GROUP BY GROUP_NAME
ORDER BY GROUP_NAME

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 10:35:17

You have 3 distinct hard-coded dates in 4 different places, which ones need to be changed?

I assume you'll also want a DateDiff for OPEN30 and OPEN90?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 10:48:35

I've got this so far:

SELECT GROUPS.GROUP_NAME, 
SUM(CASE WHEN DT_CREATED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CREATED, 
SUM(CASE WHEN DT_CLOSED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CLOSED, 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN], 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(month, DATEDIFF(month, 0, getdate())-1, 0) AS [OPEN30], 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(month, DATEDIFF(month, 0, getdate())-3, 0) AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
GROUP BY GROUP_NAME ORDER BY GROUP_NAME

I removed the UNIONs and replaced them with CASE expressions. I also modified the hard-coded dates with DATEADD expressions. I can't tell if that is correct but it should be close.

edit: forgot the WHERE clause.

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-08 : 10:52:45

I acutally need it changed for all of them
we pull this query each friday to report how many ticket were opened last week, how many where closed, how many are currently open, and tickets that are open >30days, and open >90 days
now let's say if we closed a ticket this week which was in the open >90days column that column should show that diffrence this week....
does tat make sense?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 11:27:16

Just to clarify, when you say "open > 30 days", is it actually 30 days prior to the current date, or earlier than the 1st of the prior month? The example you posted suggested the latter. In that case, the query I posted should suffice.

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-08 : 11:28:18

30 days prior to the current day. The query you posted doesn't work

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 11:54:22

Try this:

SELECT GROUPS.GROUP_NAME, 
SUM(CASE WHEN DT_CREATED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CREATED, 
SUM(CASE WHEN DT_CLOSED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CLOSED, 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN], 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, DATEDIFF(day, 0, getdate())-30, 0) AS [OPEN30], 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, DATEDIFF(day, 0, getdate())-90, 0) AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
GROUP BY GROUP_NAME ORDER BY GROUP_NAME

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-08 : 14:05:18

i'm getting a syntax error

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 14:15:07

I think I got it:

SELECT GROUPS.GROUP_NAME, 
SUM(CASE WHEN DT_CREATED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CREATED, 
SUM(CASE WHEN DT_CLOSED>=DATEADD(day, DATEDIFF(day, 0, getdate())-7, 0) THEN 1 ELSE 0 END) AS CLOSED, 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN], 
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, DATEDIFF(day, 0, getdate())-30, 0) THEN 1 ELSE 0 END) AS [OPEN30],
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, DATEDIFF(day, 0, getdate())-90, 0) THEN 1 ELSE 0 END) AS [OPEN90]
FROM INCIDENTS
INNER JOIN GROUPS ON INCIDENTS.ID_GROUP=GROUPS.ID
INNER JOIN REPS ON INCIDENTS.ID_ASSIGNEE=REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC RENT%'
GROUP BY GROUP_NAME ORDER BY GROUP_NAME

Sorry about that.

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-08 : 14:30:36

yeah that worked thanks alot now I have to work on the latter part :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources