Comments by vusamozi - CodeProject

vusamozi 17-Dec-12 3:46am View

select StockNumber, [Description], InventoryName, [Mth/Yr],Opn_Qty,Qty,Opn_Qty-Qty as Cls_Qty from ( select StockNumber, [Description], InventoryName, [Mth/Yr], ( SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN) -SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) ) as Qty, ( SELECT SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN) -SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) from dbo.Sheet2$ where [Mth/Yr] < i.[Mth/Yr] ) AS Opn_Qty from a.dbo.sheet2$ as i WHERE [Mth/Yr] >= '2004-01-01' AND [Mth/Yr] <= '2012-12-31' GROUP BY StockNumber, [Description], InventoryName, [Mth/Yr] ) as a

am still having a problem Aarti when i run the script it say Invalid object name 'a.dbo.sheet2$'.

vusamozi 12-Dec-12 1:08am View

Morning Aarti, i get this error when i use the isnull function:- Msg 174, Level 15, State 1, Line 12
The isnull function requires 2 argument(s).

And the script is picking the same figure closing quantity for all months as opening qty. if you can help to make the closing qty for 2004/01/01 to be the opening qty for 2004/02/01 and be added as this :: SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN + closing qty)
-SUM(ADJUSTMENT + ISSUE + PT_ISSUE + [TRANSFER]) closing qty of february 2004

vusamozi 11-Dec-12 9:24am View

thank you Aarti i have used the script and its working just fine but i have issues with the null values it brings after running the script, yet they is a qty value.

vusamozi 11-Dec-12 9:24am View

thank you Aarti i have used the script and its working just fine but i have issues with the null values it brings after running the script, yet they is a qty value.

vusamozi 10-Dec-12 9:26am View

thanx Aarti but the script is giving me errors and i have dibugged some and this error seems to pop up:- Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '-'.

below is the code and the table:

USE [centraldb]
GO
/****** Object: Table [dbo].[Sheet2$] Script Date: 12/10/2012 16:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sheet2$](
[StockNumber] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InventoryName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mth/Yr] [datetime] NULL,
[ADJUSTMENT] [float] NULL,
[ISSUE] [float] NULL,
[PT_ISSUE] [float] NULL,
[RECEIVE_ADJ] [float] NULL,
[RECEIVE_FROM_PO] [float] NULL,
[RECEIVE_XFER] [float] NULL,
[_RETURN] [float] NULL,
[TRANSFER] [float] NULL
) ON [PRIMARY]

SELECT StockNumber,InventoryName,Mth/Yr,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,
(SELECT SUM(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN + CLOSING_BAL) from dbo.Sheet2$ where Mth/Yr < i.Mth/Yr)
- SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER) FROM Sheet2$ AS OPENING_BAL,
(SELECT SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER) from dbo.Sheet2$ where Mth/Yr < i.Mth/Yr) - SUM(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER)
AS CLOSING_BAL FROM dbo.Sheet2$ AS i
WHERE Mth/Yr >= '2004-01-01' AND Mth/Yr <= '2012-12-31'
GROUP BY Mth/Yr

please help agently

Comments by vusamozi (Top 5 by date)