[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

sql query select last entry for month

Posted on 2006-06-06
11
Medium Priority
?
1,002 Views
Last Modified: 2012-05-05
need to select from a table containing weekly data. Written out it would be this:


select the last date in may 05 and june 05 given that it is now june 06

using now() select this months and the previous month's last weekly data point from the previous year

there are four rows for may and june 05 I want the last row for each month
0
Comment
Question by:pwdavismd
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16842448
Hi pwdavismd,
> there are four rows for may and june 05 I want the last row for each month

How is this table structured?

Regards,

Patrick
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16842689
You need to post table schema and preferably some sample data and the desired output schema.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16843076
Select *
   from YourTable as A
  , (select  convert(datetime,convert(char(8),DateAdd(m,mth,Dateadd(d,-1,Dateadd(m,1,Convert(char(6),Getdate(),112)+'01'))),112)+' 23:59:59.997'))  as LastDate
         from (select  -12 as MTH select -13) as M
    ) as L
Where YourDate = (Select max(yourdate) from YourTable as X
                                 where yourdate <= L.lastDate)
     
order by yourdate
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 1

Author Comment

by:pwdavismd
ID: 16845592
Lowfatspread: from cause error is returned when I tried your statement; Syntax error in FROM clause.

right now I'm running this against an Access DB but it will be converted to SQL server soon

structure is:

SOURCEKEY      DATE      VALUE      FREQUENCY      date2      val
DDR001      19940321                 110.6      W      3/21/1994      110.6
DDR001      19940328                 110.7      W      3/28/1994      110.7
DDR001      19940404                 110.9      W      4/4/1994      110.9
DDR001      19940411                 110.8      W      4/11/1994      110.8
DDR001      19940418                 110.5      W      4/18/1994      110.5
DDR001      19940425                 110.6      W      4/25/1994      110.6
DDR001      19940502                 110.4      W      5/2/1994      110.4
DDR001      19940509                 110.1      W      5/9/1994      110.1
DDR001      19940516                 109.9      W      5/16/1994      109.9
DDR001      19940523                 109.9      W      5/23/1994      109.9
DDR001      19940530                 109.8      W      5/30/1994      109.8
DDR001      19940606                 110.1      W      6/6/1994      110.1
DDR001      19940613                 109.8      W      6/13/1994      109.8

all I want returned from the query is two numbers (val). Since it's June 2006 I want June 2005 last value (by date2) for DDR001 and May 2005 last value (date2)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16847051
CREATE PROCEDURE procHistoricVal
   @SourceKey char(10)  --change to appropriate datatype
AS
DECLARE @val1 decimal(10,2),
   @val2 decimal(10,2)

SELECT TOP 1 @val1 = val
FROM myTable
WHERE SOURCEKEY = @SourceKey
   AND YEAR(date2) = YEAR(GetDate()) - 1
   AND MONTH(date2) = YEAR(GetDAte()) - 1
ORDER BY date2 DESC

SELECT TOP 1 @val2 = val
FROM myTable
WHERE SOURCEKEY = @SourceKey
   AND YEAR(date2) = YEAR(GetDate()) - 1
   AND MONTH(date2) = YEAR(GetDAte())
ORDER BY date2 DESC

SELECT @SourceKey, YEAR(GetDate) AS ReportYear, MONTH(GetDate()) AS ReportMonth, @val1, @val2
GO
0
 
LVL 4

Accepted Solution

by:
cavehop earned 600 total points
ID: 16848103
I think there is a small typo in BriCrowe's solution: The lines that have
    MONTH(date2) = YEAR(GetDate...
should be
    MONTH(date2) = MONTH(GetDate...

Here is another possible solution in a single query if you would like to have the data returned as a set of (two) records:

SELECT date2, val
FROM myTable
WHERE SOURCEKEY = 'DDR001' AND
    (date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE()) - 1)) OR
    (date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE())))
ORDER BY date2

Either way, I hope you have an index on the date2 field if this table is of any size at all.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16851499
>I'm running this against an Access DB but it will be converted to SQL server soon
this is the SQL Server Topic area...

it should work in sql server...
all you need to do is change yourdate to date2..

hth
0
 
LVL 1

Author Comment

by:pwdavismd
ID: 16852262
cavehop i like the compact sql statement but query is returning all values for the last date in june in 2005. seems like it's ignoring the WHERE SOURCEKEY = 'DDR001' part. Any thoughts?
0
 
LVL 4

Expert Comment

by:cavehop
ID: 16853190
Whoops, you are right.  I misplaced one of the close parentheses.  That's what I get for forgetting to test on a table with a SOURCEKEY <> 'DDR001'...

The query should be:
SELECT date2, val
FROM myTable
WHERE SOURCEKEY = 'DDR001' AND
    (date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE()) - 1) OR
    (date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE()))))
ORDER BY date2
0
 
LVL 1

Author Comment

by:pwdavismd
ID: 16853373
figured it out cavehop needed one more set of () around the entire statement
0
 
LVL 4

Expert Comment

by:cavehop
ID: 16853389
Or a slightly cleaner version that shows the parentheses nesting better...

SELECT date2, val
FROM myTable
WHERE SOURCEKEY = 'DDR001' AND
    (date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE()) - 1
              ) OR
     date2 = (SELECT MAX(date2) FROM myTable
        WHERE SOURCEKEY = 'DDR001' AND
        YEAR(date2) = YEAR(GETDATE()) - 1 AND
        MONTH(date2) = MONTH(GETDATE())
              )
    )
ORDER BY date2
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

640 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