SQLTeam.com | Weblogs | Forums

Records from the Last 4 Months


#1

Hi,
I have a sql 2008 view that must show me the last 4 months of sales (including the current month) but the month part is not working and I cannot found the problem, I have records of evey month of the year, my code is this

SELECT TOP (100) PERCENT YearNr, MonthNr, DebtorNr, Debtor, SUM(SubTotalUSD) AS StUSD, SUM(TaxesUSD) AS TaxUSD, SUM(TotalUSD) AS TUSD, SUM(CostUSD) AS CUSD, RepNr, Rep
FROM dbo.VK_YSALES_REP_3
WHERE (YearNr = YEAR(GETDATE())) AND (MonthNr = DATEADD(MM, - 4, GETDATE())) AND
GROUP BY MonthNr, YearNr, DebtorNr, Debtor, RepNr, Rep
ORDER BY YearNr, MonthNr, Debtor

thanks in advance


#2

I think you need to change the query criteria a bit - note this will not work for year change

SELECT TOP (100) PERCENT YearNr
	,MonthNr
	,DebtorNr
	,Debtor
	,SUM(SubTotalUSD) AS StUSD
	,SUM(TaxesUSD) AS TaxUSD
	,SUM(TotalUSD) AS TUSD
	,SUM(CostUSD) AS CUSD
	,RepNr
	,Rep
FROM dbo.VK_YSALES_REP_3
WHERE (YearNr = YEAR(GETDATE()))
	AND (MonthNr >= Month(DATEADD(MM, - 4, GETDATE())))
	AND
GROUP BY MonthNr
	,YearNr
	,DebtorNr
	,Debtor
	,RepNr
	,Rep
ORDER BY YearNr
	,MonthNr
	,Debtor

#3

Code below should always work, regardless of starting month.

SELECT TOP (100) PERCENT YearNr, MonthNr, DebtorNr, Debtor, 
       SUM(SubTotalUSD) AS StUSD, SUM(TaxesUSD) AS TaxUSD, 
       SUM(TotalUSD) AS TUSD, SUM(CostUSD) AS CUSD, 
       RepNr, Rep
FROM (
    SELECT YEAR(GETDATE()) AS YearToMatch, MONTH(GETDATE()) AS MonthToMatch
    UNION ALL
    SELECT YEAR(DATEADD(MONTH, -1, GETDATE())), MONTH(DATEADD(MONTH, -1, GETDATE()))
    UNION ALL
    SELECT YEAR(DATEADD(MONTH, -2, GETDATE())), MONTH(DATEADD(MONTH, -2, GETDATE()))
    UNION ALL
    SELECT YEAR(DATEADD(MONTH, -3, GETDATE())), MONTH(DATEADD(MONTH, -3, GETDATE()))
    --UNION ALL
    --SELECT YEAR(DATEADD(MONTH, -4, GETDATE())), MONTH(DATEADD(MONTH, -4, GETDATE()))
) AS Years_And_Months_To_Match
INNER JOIN dbo.VK_YSALES_REP_3 ON
    YearNr = YearToMatch AND
    MonthNr = MonthToMatch
GROUP BY YearNr, MonthNr, DebtorNr, Debtor, RepNr, Rep
ORDER BY YearNr, MonthNr, Debtor

#4

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

I have a sql 2008 view that must show me the last 4 months of sales (including the current month) but the month part is not working and I cannot find the problem, I have records [sic: rows are not records] of every month of the year, my code is this..

This is a 1970's COBOL program badly written in T-SQL! Why did you put meta data (USD is a unit of measure in your data model) into the data element names? Why did you use the old Sybase/UNIX getdate()?

Report Range Tables

A common application is have special reporting periods, perhaps based on the fiscal calendar or business defined events (“Annual Going out Of Business Sale!” was one of my favorites).

CREATE TABLE ReportRanges
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);
These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.

The basic skeleton for use with these tables is

SELECT R.report_name, << summary computations >>
FROM ReportRanges AS R, [Events] AS E
WHERE E.event_date BETWEEN R.report_start_date AND report_end_date
AND R.report_name IN (<>)
GROUP BY R.report_name;

The <> would probably be events nested inside each other, like fiscal quarters inside a fiscal year. While this is a useful and portable programming trick, you need to consider replacing it with the newer OLAP extensions to the GROUP BY clause such as ROLLUP and CUBE.

Since you were too rude to post DDL, I will return the favor and not post DML. It is now very easy, isn't it?