June 8, 2015 at 4:12 am
Since you're new to the forums, please read through this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2015 at 5:20 am
Thanks Gail Shaw.
June 8, 2015 at 8:22 am
You edited your post to add the query, but the most important items which the article mentions still aren't there.
Table definition and sample data please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2015 at 8:52 am
GilaMonster (6/8/2015)
You edited your post to add the query, but the most important items which the article mentions still aren't there.Table definition and sample data please.
Hi,
I am trying to find out what is table definition and how to get it?
June 8, 2015 at 8:54 am
If you read the article, it's all laid out in detail in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2015 at 8:09 am
I am still unable to figure out what is in the article that I need to provide.
You can call me dumb and verrrrrrryyy dumb.
May I ask another way? I have worked out one column and I need more two more column like these then how could I get it?
Select CompanyName, SUM(SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) as '2014'
FROM Company
INNER JOIN CustomerAccount
ON Company.CompanyID = CustomerAccount.CompanyID
INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID
INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
INNER JOIN Product ON SalesLine.ProductID = Product.ProductID
Where SalesOrder.DateEntered between '2014-01-01' and '2014-12-31'
Group By Companyname
I have worked out one column that shows 2014 and all sales how can I add 2013,2012 within same query.
Please treat me like a newbie and dumb newbie.
June 11, 2015 at 1:03 pm
I'm giving an example on how you should post your data. As you see, the code can be copied and ran without any major effort.
I'm including a possible solution with some links to further explanations. Try to understand and apply it to your real tables and ask any questions that you have.
Remember that for coded solutions, you might need to show more effort as most people won't take the time to figure out and create the sample data.
--Sample Data Generation
CREATE TABLE CustomerAccount(
Customer_id int,
AccountNumber char(10),
CompanyName varchar(100));
INSERT INTO CustomerAccount VALUES ( 1, 'ABC', 'Company');
INSERT INTO CustomerAccount VALUES ( 2, 'DEF', 'Company');
CREATE TABLE Sales_Order(
Customer_id int,
DateEntered datetime,
Total decimal(18,2));
INSERT INTO Sales_Order VALUES (1, '20110503', 500);
INSERT INTO Sales_Order VALUES (1, '20120203', 300);
INSERT INTO Sales_Order VALUES (1, '20120507', 250);
INSERT INTO Sales_Order VALUES (1, '20130503', 700);
INSERT INTO Sales_Order VALUES (1, '20140503', 800);
INSERT INTO Sales_Order VALUES (1, '20150503', 900);
INSERT INTO Sales_Order VALUES (1, '20160503', 1000);
INSERT INTO Sales_Order VALUES (2, '20110503', 300);
INSERT INTO Sales_Order VALUES (2, '20120203', 200);
INSERT INTO Sales_Order VALUES (2, '20120507', 250);
INSERT INTO Sales_Order VALUES (2, '20130503', 600);
INSERT INTO Sales_Order VALUES (2, '20140503', 800);
INSERT INTO Sales_Order VALUES (2, '20150503', 900);
INSERT INTO Sales_Order VALUES (2, '20160503', 1000);
--End of Sample Data generation
--Beginning of the solution
DECLARE @StartDate datetime;
--Define the start date using calculations as shown in http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
SET @StartDate = DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 4, 0);
--Use a Cross tabs approach as shown in http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT AccountNumber,
CompanyName,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -4, GETDATE())) THEN Total ELSE 0 END) AS YearMinus4,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -3, GETDATE())) THEN Total ELSE 0 END) AS YearMinus3,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -2, GETDATE())) THEN Total ELSE 0 END) AS YearMinus2,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -1, GETDATE())) THEN Total ELSE 0 END) AS YearMinus1,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(GETDATE()) THEN Total ELSE 0 END) CurrentYear
From Sales_Order so
JOIN CustomerAccount ca ON so.Customer_id = ca.Customer_id
WHERE DateEntered >= @StartDate
AND DateEntered < GETDATE()
GROUP BY AccountNumber, CompanyName;
--Clean my database
DROP TABLE Sales_Order;
DROP TABLE CustomerAccount;
June 11, 2015 at 1:25 pm
I have got one question as I have only got read access to use this query.
June 11, 2015 at 1:31 pm
Change the tables so they can be temp tables https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx
Or table variables.
June 16, 2015 at 9:12 am
Thanks Luis,
I managed to change and get this working so far
"ELECT
CompanyName, CustomerAccount.AccountNumber ,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -4, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2011',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -3, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2012',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -2, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2013',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -1, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2014',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(GETDATE()) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) '2015',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, 1, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2016'
From Company
INNER JOIN CustomerAccount
ON Company.CompanyID = CustomerAccount.CompanyID
INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID
INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
INNER JOIN Product ON SalesLine.ProductID = Product.ProductID
Group By CompanyName,AccountNumber "
But not getting the right figures like between 01 Jan2012 to 31Dec2012 etc. unless I change date format.
Any thought as when I use
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEDIFF(YY, '01/01/2013','12/31/2013')) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2013', it all goes to zero.
June 16, 2015 at 9:29 am
gazy007 (6/16/2015)
Thanks Luis,I managed to change and get this working so far
"ELECT
CompanyName, CustomerAccount.AccountNumber ,
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -4, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2011',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -3, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2012',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -2, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2013',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, -1, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2014',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(GETDATE()) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) '2015',
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEADD(YY, 1, GETDATE())) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2016'
From Company
INNER JOIN CustomerAccount
ON Company.CompanyID = CustomerAccount.CompanyID
INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID
INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
INNER JOIN Product ON SalesLine.ProductID = Product.ProductID
Group By CompanyName,AccountNumber "
But not getting the right figures like between 01 Jan2012 to 31Dec2012 etc. unless I change date format.
Any thought as when I use
SUM( CASE WHEN YEAR( DateEntered) = YEAR(DATEDIFF(YY, '01/01/2013','12/31/2013')) THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2013', it all goes to zero.
You need to understand how DATEADD and DATEDIFF work. DATEADD will add or substract periods of time depending if the second parameter is positive or negative, and it will return a datetime data type. DATEDIFF will return the difference or number of "jumps" needed to get from the first date to the second date, and it will return an integer.
DATEDIFF(YY, '01/01/2013','12/31/2013') will return 12 which will be then converted to datetime '01/13/1900' and I'm assuming that you don't have data for that year.
Using DATEADD will allow the query to change years automatically, but you could change YEAR(DATEADD(YY, -4, GETDATE())) to the literal/constant 2011. That way you end up with:
SUM( CASE WHEN YEAR( DateEntered) = 2011 THEN (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) ELSE 0 END) AS '2011',
About the part of not getting the right figures, could you give an example of a date that should be included but it's not or the other way around?
June 26, 2015 at 10:07 am
Hi ,
Sorry and I am grateful for your help and something I have never understood about aggregate probelm
SELECT
ca.AccountNumber , CompanyName, Value,
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -4, GETDATE())) THEN (Value ) ELSE 0 END) AS '2011',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -3, GETDATE())) THEN (Value ) ELSE 0 END) AS '2012',
SUM( CASE WHEN YEAR( TransactionDate ) = YEAR(DATEADD(YY, -2, GETDATE())) THEN (Value ) ELSE 0 END) AS '2013',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -1, GETDATE())) THEN (Value ) ELSE 0 END) AS '2014',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(GETDATE()) THEN (Value ) ELSE 0 END) '2015',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, 1, GETDATE())) THEN (value) ELSE 0 END) AS '2016'
FROM view_SalesStatistics
INNER JOIN CustomerAccount ca
ON view_SalesStatistics.CustomerAccountID = ca.CustomerAccountID
GROUP BY ca.AccountNumber, CompanyName;
I get this error when I run the above query.
Msg 8120, Level 16, State 1, Line 2
Column 'view_SalesStatistics.Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 26, 2015 at 10:15 am
gazy007 (6/26/2015)
Hi ,Sorry and I am grateful for your help and something I have never understood about aggregate probelm
SELECT
ca.AccountNumber , CompanyName, Value,
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -4, GETDATE())) THEN (Value ) ELSE 0 END) AS '2011',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -3, GETDATE())) THEN (Value ) ELSE 0 END) AS '2012',
SUM( CASE WHEN YEAR( TransactionDate ) = YEAR(DATEADD(YY, -2, GETDATE())) THEN (Value ) ELSE 0 END) AS '2013',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, -1, GETDATE())) THEN (Value ) ELSE 0 END) AS '2014',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(GETDATE()) THEN (Value ) ELSE 0 END) '2015',
SUM( CASE WHEN YEAR( TransactionDate) = YEAR(DATEADD(YY, 1, GETDATE())) THEN (value) ELSE 0 END) AS '2016'
FROM view_SalesStatistics
INNER JOIN CustomerAccount ca
ON view_SalesStatistics.CustomerAccountID = ca.CustomerAccountID
GROUP BY ca.AccountNumber, CompanyName;
I get this error when I run the above query.
Msg 8120, Level 16, State 1, Line 2
Column 'view_SalesStatistics.Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Remove Value from your column list.
July 2, 2015 at 9:29 am
Thanks Luis, But I am not getting right numbers for 2015 and 2016 as we need to get for the whole year rather than getdate() or today. I hope I am making sense here.
SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 0,)))) THEN Value Else 0 End) as '2015'
SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 1,)))) THEN Value Else 0 End) as '2016'
Thanks Luis.
July 2, 2015 at 10:33 am
gazy007 (7/2/2015)
Thanks Luis, But I am not getting right numbers for 2015 and 2016 as we need to get for the whole year rather than getdate() or today. I hope I am making sense here.SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 0,)))) THEN Value Else 0 End) as '2015'
SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 1,)))) THEN Value Else 0 End) as '2016'
Thanks Luis.
Why do you believe that you're getting numbers for just one day?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply