April 11, 2012 at 1:21 pm
Hi! I am needing some assistance with a date query.
I have data that looks like this:
Date column counts
2012-03-28 202
2012-03-21 202
2012-03-14 202
2012-03-07 200
2012-02-29 200
2012-02-22 200
2012-02-15 200
2012-02-08 200
2012-02-01 200
2012-01-25 200
2012-01-18 198
2012-01-11 198
2012-01-04 198
I am needing to extract the count for the last record of each month (something like the maximum date). In this example, I want to extract 200 for January, 200 for February, and 202 for March. Simply using Max(counts) does not work, because the count could lower. I really need the query to look at the last date record as the end of the month for each Period.
Does anybody know how to accomplish this task?
Thanks
April 11, 2012 at 1:39 pm
Please note how I set things up. This is what you really need to do when asking a question instead of relying on us to do it.
CREATE TABLE dbo.MyTable (
ADate DATETIME,
ACount INT
);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-03-28', 202);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-03-21', 202);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-03-14', 202);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-03-07', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-02-29', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-02-22', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-02-15', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-02-08', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-02-01', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-01-25', 200);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-01-18', 198);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-01-11', 198);
INSERT INTO dbo.[MyTable] (
[ADate],
[ACount]
) VALUES ('2012-01-04', 198);
WITH MyData (
RowNum,
DateVal,
Counts
) AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY DATEADD(mm, DATEDIFF(mm, 0, ADate), 0) ORDER BY Adate DESC),
ADate,
ACount
FROM
dbo.MyTable
)
SELECT
DateVal,
Counts
FROM
MyData
WHERE
RowNum = 1
ORDER BY
DateVal desc;
DROP TABLE dbo.MyTable;
April 11, 2012 at 1:52 pm
You'll want to partition the row_number by year as well if you are going to have dates with different years.
April 11, 2012 at 2:01 pm
Lynn,
Sorry, maybe I was a little unclear. I have a TABLE with the data already in it. I am not trying to create the table. I was just wanting to the appropriate query to extract the right counts for the last record of the month.
Thanks for your input
April 11, 2012 at 2:02 pm
roryp 96873 (4/11/2012)
You'll want to partition the row_number by year as well if you are going to have dates with different years.
Take a close look at the code in the partition by, it converts the dates to the first of the month there by partitioning by the year as well.
April 11, 2012 at 2:09 pm
Lynn Pettis (4/11/2012)
roryp 96873 (4/11/2012)
You'll want to partition the row_number by year as well if you are going to have dates with different years.Take a close look at the code in the partition by, it converts the dates to the first of the month there by partitioning by the year as well.
Oh shoot, I was just reading through it too fast and didn't even test it. :blush: I see what you are doing there now.
April 11, 2012 at 2:18 pm
Tammy Robinson (4/11/2012)
Lynn,Sorry, maybe I was a little unclear. I have a TABLE with the data already in it. I am not trying to create the table. I was just wanting to the appropriate query to extract the right counts for the last record of the month.
Thanks for your input
Understand, but I don't have your table or data, so I had to make a test environment and populate the table with data (used what you posted). The only part you need for your situation is the code with the CTE.
April 11, 2012 at 2:52 pm
Lynn,
Thanks for your response.
Worked out fine
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply