Help with a subquery... thanks

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a subquery... thanks

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-29 : 22:20:00

I have the following two sets of code that work independantly, and I guess I have the nesting incorrect, still learning. They are;
SELECT SUM(TotalSeconds) .....to ) AS TotalSecondsTable
and
SELECT StartDateRange .....to ) AS DateTable
However I am having nothing but trouble putting the two together. The idea is that the last set of code returns dates for the previous codes case statements where the time is limited if outside the range.

ALTER PROCEDURE dbo.Dates

@DateRange varchar(30) = NULL,
@SiteName char(50) = NULL

AS
BEGIN
/* SET NOCOUNT ON */

SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds
FROM (SELECT Duration.EventID, DATEDIFF(second, Duration.StartTime, Duration.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < DateTable.StartDateRange THEN DateTable.StartDateRange ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > DateTable.EndDateRange THEN DateTable.EndDateRange ELSE EventDetails.EventEndTime
END AS EndTime
FROM EventDetails) AS Duration INNER JOIN EventDetails AS EventDetails_1 ON Duration.EventID = EventDetails_1.EventID INNER JOIN SiteDetails ON EventDetails_1.SiteID = SiteDetails.SiteID
WHERE (NOT (EventDetails_1.EventEndTime < DateTable.StartDateRange)) AND (NOT (EventDetails_1.EventStartTime > DateTable.EndDateRange)) AND (SiteDetails.SiteName IN (@SiteName))
) AS TotalSecondsTable
FROM(SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT CAST(@DateRange AS varchar(30))AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE()
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange)) AS DateTable
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 01:43:08

Not quite clear from your query, can you just explain what you want with some sample data from your queries and expected output?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-30 : 02:55:13

Sure and thanks for your help, you guys are a life saver especially for us that are just starting out.

I have two queries as listed below with output data, each run successfully individually. I am tring to add the second query as a subquery to the first query so I can replace @SourceStartDate and @SourceEndDate of the first query with StartDateRange and EndDateRange of the second query. Hopefully this makes sense:)

ALTER PROCEDURE dbo.TotalDuration

@SiteName char(50) = NULL,
@SourceStartDate DateTime = NULL,
@SourceEndDate DateTime = NULL

AS
BEGIN
/* SET NOCOUNT ON */

SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds
FROM (SELECT Duration.EventID, DATEDIFF(second, Duration.StartTime, Duration.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < @SourceStartDate THEN @SourceStartDate ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > @SourceEndDate THEN @SourceEndDate ELSE EventDetails.EventEndTime
END AS EndTime
FROM EventDetails) AS Duration
INNER JOIN EventDetails AS EventDetails_1 ON Duration.EventID = EventDetails_1.EventID INNER JOIN SiteDetails ON EventDetails_1.SiteID = SiteDetails.SiteID
WHERE (NOT (EventDetails_1.EventEndTime < @SourceStartDate)) AND (NOT (EventDetails_1.EventStartTime > @SourceEndDate)) AND (SiteDetails.SiteName IN (@SiteName))) AS TotalSecondsTable
END

Running [dbo].[TotalDuration] ( @SiteName = Ben, @SourceStartDate = 1/1/2008, @SourceEndDate = 7/29/2008 ).

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
0 1 0 0
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[TotalDuration].

ALTER PROCEDURE dbo.DateRange

@DateRange varchar(30) = NULL

AS
BEGIN
/* SET NOCOUNT ON */
SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT CAST(@DateRange AS varchar(30))AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE()
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange)AS DateTable
END

Running [dbo].[DateRange] ( @DateRange = Today ).

StartDateRange EndDateRange DateRangeSelection
----------------------- ----------------------- ------------------------------
30/07/2008 30/07/2008 4:16:15 PM Today
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[DateRange].

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-03 : 21:43:33

Final Solution

ALTER PROCEDURE dbo.TotalSepDuration

@SiteName char(50) ,
@DateRange varchar(30)

AS
BEGIN
/* SET NOCOUNT ON */
SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, SiteName, TransmitterType, StartDateRange, EndDateRange, DateRangeSelection, COUNT(EventID) AS EventCount
FROM (SELECT EventID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds, SiteName, TransmitterType, StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT EventDetails.EventID, SiteName, TransmitterType, StartDateRange, EndDateRange, DateRangeSelection,
CASE
WHEN EventDetails.EventStartTime < StartDateRange THEN StartDateRange ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > EndDateRange THEN EndDateRange ELSE EventDetails.EventEndTime
END AS EndTime
FROM (SELECT DateRangeSel.StartDateRange, DateRangeSel.EndDateRange, DateRangeSel.DateRangeSelection
FROM (SELECT CAST(@DateRange AS varchar(30)) AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE() WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)) WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange)
AS DateRangeSel)
AS DateLimit, EventDetails INNER JOIN SiteDetails ON EventDetails.SiteID = SiteDetails.SiteID WHERE (NOT (EventDetails.EventEndTime < StartDateRange)) AND (NOT (EventDetails.EventStartTime > EndDateRange)) AND (SiteDetails.SiteName IN (@SiteName)))
AS DateDif)
AS TotalSum
GROUP BY SiteName, TransmitterType, StartDateRange, EndDateRange, DateRangeSelection
ORDER BY SiteName
END

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-03 : 21:47:25

PS I used this to create a query from a temp table/field in the database to be used for a report parameter so the customer can select option:

ALTER PROCEDURE dbo.DateRangeSelection

AS
BEGIN
CREATE TABLE #DateRangeSelection (DateRange varchar(30));
INSERT INTO #DateRangeSelection (DateRange)
SELECT 'Current Hour'
UNION ALL
SELECT 'Last Hour'
UNION ALL
SELECT 'Last 12 Hours'
UNION ALL
SELECT 'Last 24 Hours'
UNION ALL
SELECT 'Today'
UNION ALL
SELECT 'Yesterday'
UNION ALL
SELECT 'Last Weekend'
UNION ALL
SELECT 'Current Week'
UNION ALL
SELECT 'Last 7 Days'
UNION ALL
SELECT 'Last 5 Week Days'
UNION ALL
SELECT 'Last Week'
UNION ALL
SELECT 'Last 2 Weeks'
UNION ALL
SELECT 'Current Month'
UNION ALL
SELECT 'Last 30 days'
UNION ALL
SELECT 'Last 90 days'
UNION ALL
SELECT 'Last Month'
UNION ALL
SELECT 'Last 3 Months'
UNION ALL
SELECT 'Current Quarter'
UNION ALL
SELECT 'Last Quarter'
UNION ALL
SELECT 'Current Year'
UNION ALL
SELECT 'Last Year'
SELECT * FROM #DateRangeSelection;
IF OBJECT_ID(N'tempdb..#DateRangeSelection', N'U') IS NOT NULL
DROP TABLE #DateRangeSelection;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources