Last Six Months of Sales

Transact-SQL
1

Hello, I have a sql 2008r2 view that shows Me the invoices on db and I need to show just the last six months of a customers, I'm not talking about the last six months of the year, I'm talking about the last 6 months of the invoices that a customer haves, this is the code I have :

CODE :

SELECT faknr AS InvoiceNr, fakdat AS InvoiceDate, YEAR(fakdat) AS YearNr, MONTH(fakdat) AS MonthNr, dagbknr AS InvoiceType, debnr AS DebtorNr, inv_debtor_name AS DebtorName
FROM dbo.frhkrg WITH (NOLOCK)

RESULT :

| InvoiceNr | InvoiceDate | YearNr | MonthNr | InvoiceType | DebtorNr| DebtorName |
| 1348 | 2012-11-10 | 2012 | 11 | 132 | 1005 | Deb1005 |
| 1622 | 2012-12-23 | 2012 | 12 | 132 | 1005 | Deb1005 |
| 1821 | 2016-01-25 | 2016 | 01 | 132 | 1005 | Deb1005 |
| 1923 | 2016-02-10 | 2016 | 02 | 132 | 1005 | Deb1005 |
| 2049 | 2016-03-25 | 2016 | 03 | 132 | 1005 | Deb1005 |
| 2223 | 2016-04-10 | 2016 | 04 | 132 | 1005 | Deb1005 |
| 2305 | 2016-05-25 | 2016 | 05 | 132 | 1005 | Deb1005 |
| 2322 | 2016-05-29 | 2016 | 05 | 132 | 1005 | Deb1005 |

In this particular case the last six months will be :

| InvoiceNr | InvoiceDate | YearNr | MonthNr | InvoiceType | DebtorNr| DebtorName |
| 1622 | 2012-12-23 | 2012 | 12 | 132 | 1005 | Deb1005 |
| 1821 | 2016-01-25 | 2016 | 01 | 132 | 1005 | Deb1005 |
| 1923 | 2016-02-10 | 2016 | 02 | 132 | 1005 | Deb1005 |
| 2049 | 2016-03-25 | 2016 | 03 | 132 | 1005 | Deb1005 |
| 2223 | 2016-04-10 | 2016 | 04 | 132 | 1005 | Deb1005 |
| 2305 | 2016-05-25 | 2016 | 05 | 132 | 1005 | Deb1005 |
| 2322 | 2016-05-29 | 2016 | 05 | 132 | 1005 | Deb1005 |

Thanks in advance...

2

I don't think so and there are two reasons for that:

  1. invoice no 1622 has invoice date way earlier than 2016-05-29 minus 6 months
  2. you are using "with (nolock)" which can give you all kinds of results (see here)

One way of solving your issue, could be:

select b.faknr as InvoiceNr
      ,b.fakdat as InvoiceDate
      ,datepart(year,b.fakdat) as YearNr
      ,datepart(month,b.fakdat) as MonthNr
      ,b.dagbknr as InvoiceType
      ,b.debnr as DebtorNr
      ,b.inv_debtor_name as DebtorName
  from (select debnr
              ,dateadd(month,-6,max(fakdat)) as fakdat
          from dbo.frhkrg
         group by debnr
       ) as a
       inner join dbo.frhkrg as b
               on b.debnr=a.debnr
              and b.fakdat>=a.fakdat
;
3

+1 to Yuck ...

4

hI bitsmed, it works but it's not considering the year, it showing the last 6 months but from differents years and I just need the last 6 months of purchasing of the customer, if this year the customer dont has purchasing then the query must look more months back on past to found them, when it found them then from that last date tt must look 6 months to the past

Thank You soi much for your help.

Kind Regards...

5

Have you tried the query I gave you? It should do exactly what you described. If not, please provide sample data (in the form of insert statements) so we have something to work with. Also please show the expected output (from the sample data you provide).