J Livingston SQL (4/10/2013)
Can you suggest me from where can i know everything about TSQL and MSSQL?
Knowing "everything" may take a little time....its a vast subject...try putting "TSQL" into a search engine....see how many results you get 😀
dependent upon what you know already...this series http://www.sqlservercentral.com/stairway/ may be worth reading.
Also...keep reading these forums / posting questions and also try to answer where possible ...its a great place to learn.
kind regards
One problem i am finding is, connecting this with other 2 tables, CASHMEMO,voucherPRETSRET
CASHMEMO contains information about items sold against cash
voucherPRETSRET contains information about items PURCHASE RETURN(OUT) or SALES RETURN(IN)
CASHMEMO table definition
CREATE TABLE [dbo].[cashmemo](
[srno] [int] IDENTITY(1,1) NOT NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[partyname] [nvarchar](50) NULL,
[itemname] [nvarchar](50) NULL,
[batch] [nvarchar](50) NULL,
[qty] [float] NULL,
[unit] [nvarchar](50) NULL,
[rate] [float] NULL,
[amt] [float] NULL,
[vatclass] [nvarchar](50) NULL,
[vat] [float] NULL,
[adtaxclass] [nvarchar](50) NULL,
[adtax] [float] NULL,
[total] [float] NULL
)
voucherPRETSRET table definition
CREATE TABLE [dbo].[voucherPRETSRET](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[oppvouchertype] [nvarchar](50) NULL,
[oppvoucherprefix] [nvarchar](50) NULL,
[oppvoucherno] [int] NULL,
[oppinvtype] [nvarchar](50) NULL,
[selectedvouchersrno] [int] NULL,
[item] [nvarchar](50) NULL,
[batchno] [nvarchar](50) NULL,
[qty] [float] NULL,
[unit] [nvarchar](50) NULL,
[rate] [float] NULL,
[itemamt] [float] NULL,
[vatclass] [nvarchar](50) NULL,
[vat] [float] NULL,
[adtaxclass] [nvarchar](50) NULL,
[adtax] [float] NULL,
[total] [float] NULL
)
CASHMEMO contains following data
SET IDENTITY_INSERT [cashmemo] ON
INSERT [cashmemo] ([srno], [voucherprefix], [voucherno], [partyname], [itemname], [batch], [qty], [unit], [rate], [amt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (3, N'CM1213', 1, N'Mahesh', N'Rani Cotton Oil 1LTR pouch', N'RNP1ltr10', 1, N'LTR', 70, 70, N'Sales @4', 2.8, N'Sales adtax @1', 0.7, 73.5)
SET IDENTITY_INSERT [cashmemo] OFF
voucherPRETSRET doesnt contain any data but needs to be checked
I tried with following query but not getting CASHMEMO(OUT) qty in OUTGOING column
;WITH cte AS
(
SELECT
V1.item,
V1.batchno,
--V1.voucherno AS VNO,
SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS OPENING,
SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else null end),0) AS INCOMING,
SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE RETURN' then V1.quantity else null end),0)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else null end),0) AS OUTGOING,
SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS CLOSING,
(SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end))*sum(IBS.priceperunit) AS VALUE
FROM voucher1 AS V1 INNER JOIN
voucher AS V ON V.vouchertype = V1.vouchertype
AND V.voucherprefix = V1.voucherprefix
AND V.voucherno = V1.voucherno
AND V.invoicetype = V1.invoicetype
left outer join voucherPRETSRET VPR
on V.voucherprefix=VPR.voucherprefix
and V.voucherno=VPR.voucherno
and V.vouchertype=VPR.vouchertype
left outer join cashmemo CM
on V.voucherprefix=CM.voucherprefix
and V.voucherno=CM.voucherno
inner join item_batch_stock IBS
on IBS.item=V1.item and IBS.batch=V1.batchno
GROUP BY V1.item,V1.batchno
--,V1.voucherno
)
SELECT (select city from company) as GODOWN, items.itemname as NAME,NULL as HSN, sum(CTE.OPENING) as OPENING,sum(CTE.INCOMING) as INCOMING
,sum(CTE.OUTGOING) as OUTGOING,sum(CTE.CLOSING) as CLOSING,sum(CTE.VALUE) as VALUE
FROM items LEFT OUTER JOIN
CTE ON items.srno = CTE.item
group by items.itemname
Plz help