Date Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Problems

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-23 : 15:56:17

Hi, I'm new on Sql 2008 programming and I can't found the error on the followed code, I need the current Month and the same Month but of the one year ago, I mean June 2014 and June 2013, this is my code,

Thanks in advance for your help and pacient.

SELECT     Invoice, InvoiceDate, Año, Mes, DebtorNr, Debtor, SaleOrder, SaleOrderDate, ItemCode, ItemDescription, CostPrice, SalePrice, CUR, XRate, Quantity, Rep, faknr, 
                      TSale, TCost, Profit, LotNumber, RepName, ordernr, OrderType, InvoiceType, TC, CostPriceUSD, SalePriceUSD, TCostUSD, TSaleUSD, ProfitUSD
FROM         dbo.View_VK_LAST_SALES
WHERE     (MONTH(InvoiceDate) = MONTH(GETDATE())) AND (InvoiceDate BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE() - 1))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-23 : 16:21:47

where (InvoiceDate >= dateadd(month, datediff(month, 0, @d), 0) and InvoiceDate < dateadd(month, 1, dateadd(month, datediff(month, 0, @d), 0)))
or (InvoiceDate >= dateadd(year, -1, dateadd(month, datediff(month, 0, @d), 0)) and InvoiceDate < dateadd(month, 1, dateadd(year, -1, dateadd(month, datediff(month, 0, @d), 0))))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-23 : 16:25:51

Alternative:

quote:

Originally posted by mjimenezh

SELECT     Invoice, InvoiceDate, Año, Mes, DebtorNr, Debtor, SaleOrder, SaleOrderDate, ItemCode, ItemDescription, CostPrice, SalePrice, CUR, XRate, Quantity, Rep, faknr, 
                      TSale, TCost, Profit, LotNumber, RepName, ordernr, OrderType, InvoiceType, TC, CostPriceUSD, SalePriceUSD, TCostUSD, TSaleUSD, ProfitUSD
FROM         dbo.View_VK_LAST_SALES
WHERE     (MONTH(InvoiceDate) = MONTH(GETDATE())) AND (YEAR(InvoiceDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE() - 1))

Mind you, tkizer's method is probably much much faster.

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-23 : 19:07:17

Thanks both of You, now my new problem is this: I need show everything on a Pivot table but I'm lost I Must get all the sales of the diferent debtors on just two columns (2013-06) and (2014-06)

SELECT     Invoice, InvoiceDate, Año, Mes, DebtorNr, Debtor, SaleOrder, SaleOrderDate, ItemCode, ItemDescription, CostPrice, SalePrice, CUR, XRate, Quantity, Rep, faknr, 
                      TSale, TCost, Profit, LotNumber, RepName, ordernr, OrderType, InvoiceType, TC, CostPriceUSD, SalePriceUSD, TCostUSD, TSaleUSD, ProfitUSD
FROM         dbo.View_VK_LAST_SALES
WHERE     (MONTH(InvoiceDate) = MONTH(GETDATE())) AND (YEAR(InvoiceDate) = YEAR(GETDATE()) OR
                      YEAR(InvoiceDate) = YEAR(GETDATE()) - 1)

Thanks again for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources