Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

Getting records based on the first and last days of a month

Have you ever had a situation where you needed to select rows from a table for a specific month?   This example demonstrates how to retrieve all of the records belonging to the previous month by setting the first and last days in the WHERE clause of a SELECT statement.   But with some tweaking, you can make this query retrieve data from any month you wish.

For this example, we’ll assume that we have a table in a data warehouse called DW.TRANSACTION with two columns (TRANSACTION_ID and EFFECTIVE_TS) that is populated and up to date.

To retrieve the previous month’s records, we would create a query of the form:

SELECT
       TRANSACTION_ID
      ,EFFECTIVE_TS
FROM
       DW.TRANSACTION
WHERE
       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS) - 1 MICROSECOND
;

If you would like to retrieve data from two months ago, the WHERE clause would be changed to:

       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 2 MONTHS)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH) - 1 MICROSECOND

At this point, you can keep increasing the values for the MONTHS to keep going into the past.

1 comment to Getting records based on the first and last days of a month

Leave a Reply

Buy Generic Viagra USA
Where TO Buy Viagra Online
Buy Viagra Where
Cheap Cheap Viagra
Line Viagra
Buy Discount Viagra
Cheapest viagra online Pills
Buy Viagra Sample
Cheap Viagra Online
Cheap Viagra FAST Shipping
Viagra for Sale
Buy Viagra New York
Cheap Websites For Viagra
Viagra FOR Sales Online
Cheapest Viagra Prices
Cheap Viagra Pills