SQLTeam.com | Weblogs | Forums

Create a view to transform data from the table


#1

I would like to create a view from my table called InvQty

Please at the attached examples. the top column needs to sum the data by year and month.
Capture2

Thank You


#2

Please post all example data in the form of create table and insert statements.


#3

warehouse is always 02

CREATE TABLE [dbo].[MForecast](
[StockCode] varchar NOT NULL,
[ForecastDate] NOT NULL,
[ForecastWh] [varchar] NOT NULL,
[Line] [decimal](3, 0) NOT NULL,
[Qty] [decimal](18, 6) NOT NULL,
[TimeStamp] [timestamp] NULL,
CONSTRAINT [MForecastKey] PRIMARY KEY CLUSTERED


#4

http://www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/


#5

Below Is A Query I Am Using for The Same Purpose
I Have Three Tables (Invoice, Invoice Details, Stock)

SELECT CAST(MONTH(Invoices.INDATE) AS nvarchar) + '/' + CAST(YEAR(Invoices.INDATE) AS nvarchar) AS Date, Invoices_Details.TNITEM, STOCK.STDESC,
SUM(Invoices_Details.TNQTYI) AS QTY
FROM Invoices INNER JOIN
Invoices_Details ON Invoices.INTRNS = Invoices_Details.TNTRNS INNER JOIN
STOCK ON Invoices_Details.TNITEM = STOCK.STCODE
GROUP BY Invoices_Details.TNITEM, STOCK.STDESC, CAST(MONTH(Invoices.INDATE) AS nvarchar) + N'/' + CAST(YEAR(Invoices.INDATE) AS nvarchar)

hope This Would help you