Solved

SQL SP Get Last 30 days

Posted on 2010-09-22
6
444 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 26

Expert Comment

by:tigin44
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now