CODEWITHSUNDEEP
sql-servert-sql
Kyle
Kyle

Reputation: 5537

In T-SQL, how to get records belonging to previous month based on condition of today?

If the day component of today is less than or equal to 19, I need records from the 20th of previous month, into the future. For example:

dbo.Invoices

Date        InvoiceNumber
10/20/2012  x
11/13/2012  y
11/20/2012  z
12/19/2012  aa
12/21/2012  bb

Today (11/13), I need x, y, z, aa, bb.
On 11/20, I need z, aa, bb.
On 12/19, I need z, aa, bb.
On 12/21, I need bb.

This is what I have so far:

SELECT [omitted]
,CASE
    WHEN DAY(GETDATE()) <= 19 THEN

FROM QB_INVOICES_HEADER a
INNER JOIN CI_INVOICEADJS b
ON a.InvoiceNumber = b.InvoiceNumber
WHERE DATEDIFF(day, a.InvoiceDt, b.EffectiveCheckingDt) <= 60
ORDER BY b.EffectiveCheckingDt ASC

Upvotes: 0

Views: 212

Answers (3)

Justin
Justin

Reputation: 9724

Just SQL Query :

SQLFIDDLEExample

SELECT
InvoiceNumber
FROM Invoices
WHERE Date >= CASE WHEN DAY(GETDATE())<=19 
                    THEN CAST(MONTH(DATEADD (mm , -1 , GETDATE() )) as varchar(2))+
                         '/20/'+CAST(YEAR(DATEADD (mm , -1 , GETDATE() )) as varchar(4))
                    ELSE CONVERT(VARCHAR(10), GETDATE(), 101) 
              END

If you want differrent date just replace GETDATE() to '10/19/2012'

SELECT
InvoiceNumber
FROM Invoices
WHERE Date >= CASE WHEN DAY('12/19/2012')<=19 
                    THEN CAST(MONTH(DATEADD (mm , -1 , '12/19/2012' )) as varchar(2))+
                         '/20/'+CAST(YEAR(DATEADD (mm , -1 , '12/19/2012' )) as varchar(4))
                    ELSE CONVERT(VARCHAR(10), '12/19/2012', 101) 
              END

Result with second query :

| INVOICENUMBER |
-----------------
|             z |
|            aa |
|            bb |

Upvotes: 1

teran
teran

Reputation: 3234

the code below solves your task. I used table variable @invoices instead of your dbo.invoices table

DECLARE @invoices TABLE ([date] DATE, invoiceNumber varchar(255));

INSERT INTO @invoices VALUES
    ('10/20/2012', 'x'),
    ('11/13/2012', 'y'),
    ('11/20/2012', 'z'),
    ('12/19/2012', 'aa'),
    ('12/21/2012', 'bb');

DECLARE @StartDate DATE;
DECLARE @today DATE ;

SET @today = '11/13/2012';
--SET @today = '11/20/2012';
--SET @today = '12/19/2012';
--SET @today = '12/21/2012';

IF DAY(@today) <= 19 BEGIN
    SET @startDate = DATETIMEFROMPARTS(YEAR(@today), MONTH(@today) - 1, 20,0,0,0.0,0);
END
ELSE BEGIN
    SET @startDate = @today
END

SELECT [date], invoiceNumber
FROM @invoices
WHERE [date] >= @StartDate

Upvotes: 1

Hugo V
Hugo V

Reputation: 184

You need to use DATEPART(DAY, [YOUR DATE COLUMN]) to tell what day of the month it is.

Upvotes: 1

Related Questions