April 10, 2015 at 6:09 am
Hi Guys,
I am trying to create a report from view and I am stuck on dates and Value.
I have got scenario like
Date Value
4-10-2015 5
4-10-2015 5
4-10-2015 4
4-10-2015 3
2-10-2015 4
2-10-2015 3
2-10-2015 2
I want to only show
Date Value
4-10-2015 17
2-10-2015 9 and so on.
Can you please help how can I sort it?
Thanks,
April 10, 2015 at 6:41 am
yusufm 48726 (4/10/2015)
Hi Guys,I am trying to create a report from view and I am stuck on dates and Value.
I have got scenario like
Date Value
4-10-2015 5
4-10-2015 5
4-10-2015 4
4-10-2015 3
2-10-2015 4
2-10-2015 3
2-10-2015 2
I want to only show
Date Value
4-10-2015 17
2-10-2015 9 and so on.
Can you please help how can I sort it?
Thanks,
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
[Date] DATE NOT NULL
,[Value] INT NOT NULL
);
INSERT INTO @SAMPLE_DATA ([Date],[Value])
VALUES
('4-10-2015',5)
,('4-10-2015',5)
,('4-10-2015',4)
,('4-10-2015',3)
,('2-10-2015',4)
,('2-10-2015',3)
,('2-10-2015',2);
/*
Simple SUM and GROUP BY example
*/
SELECT
SD.[Date]
,SUM(SD.[Value]) AS Value
FROM @SAMPLE_DATA SD
GROUP BY SD.[Date];
Results
Date Value
---------- ------
2015-02-10 9
2015-04-10 17
April 10, 2015 at 8:20 am
Thanks but I have already got a query ready and finding it hard to work it through.
SELECT TransactionDate, AccountNumber, ProductName, ProductGroup, Qty
,CountryCode, Price, FValue, Price, Value,
FROM view_SalesStatistics
WHERE (TransactionDate > CONVERT(DATETIME, '2014-12-31 00:00:00', 102)) AND
(View_SalesStatistics.ProductGroup) Not Like ''
ORDER BY View_SalesStatistics.TransactionDate, View_SalesStatistics.ProductName;
April 10, 2015 at 9:27 am
yusufm 48726 (4/10/2015)
Thanks but I have already got a query ready and finding it hard to work it through.SELECT TransactionDate, AccountNumber, ProductName, ProductGroup, Qty
,CountryCode, Price, FValue, Price, Value,
FROM view_SalesStatistics
WHERE (TransactionDate > CONVERT(DATETIME, '2014-12-31 00:00:00', 102)) AND
(View_SalesStatistics.ProductGroup) Not Like ''
ORDER BY View_SalesStatistics.TransactionDate, View_SalesStatistics.ProductName;
You do realize that the above does not match what you had originally posted as shown below:
yusufm 48726 (4/10/2015)
Hi Guys,I am trying to create a report from view and I am stuck on dates and Value.
I have got scenario like
Date Value
4-10-2015 5
4-10-2015 5
4-10-2015 4
4-10-2015 3
2-10-2015 4
2-10-2015 3
2-10-2015 2
I want to only show
Date Value
4-10-2015 17
2-10-2015 9 and so on.
Can you please help how can I sort it?
Thanks,
Based on your latest post, what are you trying to accomplish?
April 10, 2015 at 9:32 am
I am trying to create a report that only shows the date and the total on that date.
I am not sure how to change the format so it only sums number of task on a particular date.
April 10, 2015 at 10:16 am
yusufm 48726 (4/10/2015)
I am trying to create a report that only shows the date and the total on that date.I am not sure how to change the format so it only sums number of task on a particular date.
Your original query, slightly modified:
SELECT
TransactionDate,
AccountNumber,
ProductName,
ProductGroup,
Qty,
CountryCode,
Price,
FValue,
Price,
Value
FROM
view_SalesStatistics
WHERE
(TransactionDate > CONVERT(DATETIME, '2014-12-31 00:00:00', 102)) AND
(View_SalesStatistics.ProductGroup) Not Like ''
ORDER BY
View_SalesStatistics.TransactionDate,
View_SalesStatistics.ProductName;
Modified code:
SELECT
ss.TransactionDate,
sum(ss.Qty) Qty
FROM
view_SalesStatistics ss
WHERE
ss.TransactionDate > '20141231 00:00:00' AND
ss.View_SalesStatistics.ProductGroup Not Like ''
GROUP BY
ss.TransactionDate
ORDER BY
ss.TransactionDate;
April 13, 2015 at 5:45 am
Thanks a lot Lynn Pettis, it worked.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply