Calculating Yearly values in SQL query

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 Programming

Calculating Yearly values in SQL query

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-11-03 : 16:36:08

I need to calculate lastweek,last6months,yearly values in a single query.

Can anyone help me out

SELECT *
FROM
(
SELECT AUDITL.PKGID,
AUDIT.PROCESSID,
EVENT.NAME,
AUDIT.AUDITSTAMP,
USERMASTER.USERNAME,
ROW_NUMBER() OVER(PARTITION BY AUDIT.PKGID ORDER BY AUDIT.AUDITSTAMP DESC) AS RowNum
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID AND AUDIT.RESULTEVENTID = EVENT.EVENTID INNER JOIN
USERMASTER ON AUDIT.USERID = USERMASTER.USERID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user') AND (AUDIT.PROCESSID IN (3, 5, 6))
AND (
({?TimePeriod} = 'LastWeek' and AUDITSTAMP >= dateadd(dd,0, datediff(dd,0,
dateadd(day,-1*datepart(weekday,getdate())+1,dateadd(week,-1,getdate()))))
and AUDITSTAMP < dateadd(dd,0, datediff(dd,0,
dateadd(day,7,dateadd(day,-1*datepart(weekday,getdate()),dateadd(week,-1,getdate())))
)))
or

({?TimePeriod}= 'Last6Months' and AUDITSTAMP > DATEADD(MONTH, -6, GETDATE())

or
({?TimePeriod}= 'Yearly' and AUDITSTAMP = ?

)
)
) AS T
WHERE
RowNum = 1
ORDER BY
AUDITSTAMP DESC

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-03 : 17:21:54

Please stop using the old Sybase getdate() and use the ANSI CURRENT_TIMESTAMP.
Please un-learn your last language -- writing -1*<expression> instead of -<expression> shows that you do not know SQL.

>> lastweek, last6months, yearly values <<

A good SQL programmer will create a reporting periods table that is shared for all reports. He will not try to build something on the fly with function calls. SQL is a declarative data language and you write it like 1950's COBOL procedural code. This is like driving nails with a sandwich instead of a hammer.

CREATE TABLE ReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date)),
etc);

You could also add more columns for the last week, last quarter, etc. ranges in each row.

Now the query is simple, portable and easy to read:

CRERATE PROCEDURE AuditReport
(@in_lastweek VARCHAR(15), @in_last6months VARCHAR(15), @in_last_year VARCHAR(15))
AS
SELECT A.pkg_id, A.process_id, E.name, A.audit_stamp, U.user_name,
R.report_period_name
FROM Audits AS A,
Events AS E,
User_Master AS U,
Report_Periods AS R
WHERE A.process_id = E.process_id
AND A.result_event_id = E.event_id
AND A.user_id = U.user_id
AND A.audit_type = 6
AND A.audit_msg = 'routed by user'
AND A.process_id IN (3, 5, 6)
AND A.audit_stamp BETWEEN R.period_start_date AND R.period_end_date
AND R.report_period_name IN (@in_lastweek, @in_last6months, @in_last_year);

You can make up your own names, but using ISO and MySQL conventions for time period names we would have
last year = 'yyyy-00-00'
a quarter as 'yyyy-Q[1-4]'
a week as 'yyyy-www' look up teh ISO week number; it is not the one Microsoft uses

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources