SQL Query

• yusufm 48726

  SSC Eights!

  Points: 908

  More actions

  April 10, 2015 at 6:09 am

  #296870

  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,

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  April 10, 2015 at 6:41 am

  #1790133

  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

• yusufm 48726

  SSC Eights!

  Points: 908

  More actions

  April 10, 2015 at 8:20 am

  #1790179

  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;

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 10, 2015 at 9:27 am

  #1790219

  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?

• yusufm 48726

  SSC Eights!

  Points: 908

  More actions

  April 10, 2015 at 9:32 am

  #1790222

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 10, 2015 at 10:16 am

  #1790235

  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;

• yusufm 48726

  SSC Eights!

  Points: 908

  More actions

  April 13, 2015 at 5:45 am

  #1790484

  Thanks a lot Lynn Pettis, it worked.