CODEWITHSUNDEEP
sqlsql-server
Sinnerv
Sinnerv

Reputation: 263

Add values according to date

I have the following table where I want the Quantity_Sold value to be added for an Item and a Customer if the item has been invoiced more than once in the same month. and I want to get this Sum of Quantity sold per month value in a separate column

Item   Customer         Invoice_Date         Quantity_Sold
 A        XX      2014-11-04 00:00:00.000         13
 A        XX      2014-11-21 00:00:00.000         23
 A        XX      2014-12-19 00:00:00.000        209
 A        YY      2014-12-01 00:00:00.000         10
 A        YY      2014-12-22 00:00:00.000          6
 B        XX      2014-10-29 00:00:00.000        108
 B        YY      2014-11-06 00:00:00.000         70
 B        YY      2014-11-24 00:00:00.000         84

EX: XX has invoiced Item A twice in November so I'd want to get 36 (13+23) in a separate column.

So the result table I'd like is,

Item   Customer          Invoice_date         Sum_Qty_Invoiced
A         XX               2014-Nov                  36
A         XX               2014-Dec                 209
A         YY               2014-Dec                  16
B         XX               2014-Oct                 108   
B         YY               2014-Nov                 154

great if anyone could help me with this Thanks

Upvotes: 4

Views: 75

Answers (3)

Lucky M
Lucky M

Reputation: 11

Its a simple GroupBy clause. Just add group by on

Item,Customer,CAST(YEAR(Invoice_Date) AS Varchar(4))+'-'+LEFT(DATENAME(m,Invoice_Date),3)

Your query will be something like:

SELECT Item, Customer, 
     CAST(YEAR(Invoice_Date) AS Varchar(4)) +'-'+
       LEFT(DATENAME(m,Invoice_Date),3)
         AS Invoice_Date,SUM(Quantity_Sold)
         AS Sum_Qty_Invoiced FROM TableName GROUP BY Item,Customer, 
       Item,Customer,CAST(YEAR(Invoice_Date) AS Varchar(4))+'-
     '+LEFT(DATENAME(m,Invoice_Date),3)

Upvotes: 0

Ashish Singh
Ashish Singh

Reputation: 275

You can achieve this by using DatePart and DateName functions of SQL Server.

SELECT Item
    , Customer
    , CONVERT (CHAR(4), DATEPART(YEAR, Invoice_date)) + '-' + CONVERT(CHAR(3), DATENAME(MONTH, Invoice_date)) AS Invoice_date
    , SUM(Quantity_Sold) AS Sum_Qty_Invoiced
FROM [dbo].[Table1]
GROUP BY DATEPART(YEAR, Invoice_date), DATENAME(MONTH, Invoice_date), Item, Customer

Upvotes: 0

Jamiec
Jamiec

Reputation: 136174

This is a simple group by with some string manipulation on the Invoice_Date column.

SELECT
  Item,
  Customer,
  CAST(Year(Invoice_Date) AS VARCHAR(4)) + '-' + LEFT(DateName(m,Invoice_Date),3) AS Invoice_Date,
  SUM(Quantity_Sold) AS Sum_Qty_Sold
FROM MyTable
GROUP BY Item,
  Customer,
  CAST(Year(Invoice_Date) AS VARCHAR(4)) + '-' + LEFT(Datename(m,Invoice_Date),3)

Live example: http://www.sqlfiddle.com/#!6/8fea75/3

Upvotes: 4

Related Questions