Solved

SQL SP Get Last 30 days

Posted on 2010-09-22
6
475 Views
Last Modified: 2012-05-10
I have a sp that returns the top 30 results but I need the last 30 days even if the result is 0 for any of those days.  Today is brain fart day:)

BEGIN
     SELECT TOP 30
        SUM([TotalGallonsSinceLastEventReport]) As DailyTotal,
        CONVERT(VARCHAR(8), [ReceivedDate], 1) As Date            
     FROM
         [PumpHourMeters]
     WHERE
         [ModuleID] = @ModuleID
     GROUP BY CONVERT(VARCHAR(8), [ReceivedDate], 1)
     ORDER BY CONVERT(VARCHAR(8), [ReceivedDate], 1)ASC
            
END
0
Comment
Question by:MBoy
  • 3
  • 3
6 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 33735505

BEGIN
     SELECT SUM([TotalGallonsSinceLastEventReport]) As DailyTotal,
        CONVERT(VARCHAR(8), [ReceivedDate], 1) As Date            
     FROM
         [PumpHourMeters]
     WHERE ReceivedDate > gatdate() -30
         [ModuleID] = @ModuleID
     GROUP BY CONVERT(VARCHAR(8), [ReceivedDate], 1)
     ORDER BY CONVERT(VARCHAR(8), [ReceivedDate], 1)ASC
            
END

Open in new window

0
 
LVL 3

Author Comment

by:MBoy
ID: 33735954
I get the same results as before... 5 days where there is data and no return results for the other 25 days??
0
 
LVL 3

Author Comment

by:MBoy
ID: 33735969
Sorry - This table is refined data that doesn't show dates other than the days with pumping data.  Sorry, I knew today was brain fart day.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:tigin44
ID: 33735986
if you dont have data related to that days than you cant get that days with the above query. If you want to list the last 30 days even if those days have corresponding records the above query have to be modified.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 33736046
try this
DECLARE @DATES (salesDate	datetime);
DECLARE @tmpDate	datetime;
SET @tmpDate = GETDATE() -30;

WHILE (@tmpDate <= GETDATE())
BEGIN
	INSERT INTO @DATES (salesDate)
	VALUES(@tmpDate)

	SET @tmpDate = DATEADD(DAY, 1, @tmpDate);
END


	SELECT  
		SUM([TotalGallonsSinceLastEventReport]) As DailyTotal,
        CONVERT(VARCHAR(8), D.salesDate, 1) As Date            
     FROM
		@DATES D
	LEFT OUTER JOIN [PumpHourMeters] Y ON Y.ReceivedDate = D.salesDate
     WHERE
         [ModuleID] = @ModuleID
     GROUP BY CONVERT(VARCHAR(8), [ReceivedDate], 1)
     ORDER BY CONVERT(VARCHAR(8), [ReceivedDate], 1)ASC

Open in new window

0
 
LVL 3

Author Comment

by:MBoy
ID: 33738135
I'm having trouble getting this into a valid SP. Any help would be appreciated.

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Pump_GetChartPumpingSummaryLastThirtyDays]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Pump_GetChartPumpingSummaryLastThirtyDays];
GO
CREATE PROCEDURE [dbo].[Pump_GetChartPumpingSummaryLastThirtyDays]
(
      @ModuleID INT
)
AS
BEGIN
     SELECT TOP 30
        SUM([TotalGallonsSinceLastEventReport]) As DailyTotal,
        CONVERT(VARCHAR(8), [ReceivedDate], 1) As Date            
     FROM
         [PumpHourMeters]
     WHERE
         [ModuleID] = @ModuleID
     GROUP BY CONVERT(VARCHAR(8), [ReceivedDate], 1)
     ORDER BY CONVERT(VARCHAR(8), [ReceivedDate], 1)ASC
            
END
GO
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question