i want in sql one query

select   InstituteName, drugcode , FullDrug,recdate,isnull(batchqty,0) as batchqty,isnull(Issueqty,0) as issueqty
                        From (Select (select top 1 institutename from tblinstitutedetailmaster where instituteid=instid) InstituteName,
                          kdls.dbo.InstInwardView.wdrugid as drugcode ,Convert(Nvarchar(20),rcptdate,103) as 'recdate',
                        SUM(isnull(Batchqty,0)) as 'batchqty',0 as 'issueqty', FullDrug=(Select top 1 FullDrug From klem.dbo.vwdrugDetails as drugview
                        where drugview.drugcode=InstInwardView.wdrugid)
                        From kdls.dbo.InstInwardView where InstInwardView.instid='002128' and convert(datetime,rcptdate) between '2017-01-01' and '2021-01-01'
                        and (wdrugid='3.1.1' or '3.1.1' is null)   group by   instid,rcptdate,kdls.dbo.InstInwardView.wdrugid
                        UNION ALL
                        Select (select top 1 institutename from tblinstitutedetailmaster where instituteid=instid) InstituteName,
                        kdls.dbo .InstOutwardView.oDrugid as drugcode , Convert(Nvarchar(20),oIssueDate,103) as 'recdate', 0 as 'batchqty', SUM(isnull(obatchqty,0)) as 'Issueqty',
                        FullDrug=(Select top 1 FullDrug From klem.dbo.vwdrugDetails as drugview where drugview .drugcode=InstOutwardView.oDrugid)
                        From kdls.dbo.InstOutwardView   where InstOutwardView.instid='002128'
                        and convert(datetime,oIssueDate) between '2017-01-01' and '2021-01-01' and (oDrugid='3.1.1' or '3.1.1' is null) group by   instid,oIssueDate,kdls.dbo.InstOutwardView.oDrugid
                        ) TBL2

order by drugcode, YEAR(convert(datetime,recdate,103)) ,MONTH(convert(datetime,recdate,103)),Day(convert(datetime,recdate,103))

batchqty issueqty balanceqty

   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    01/02/2017    0           100                 -100
3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    17/03/2017     150      0                    50
3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    30/05/2017   0         -150   150
    3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    29/08/2017    200    0
   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    08/09/2017    0    200
   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    06/02/2018    250    0

hear i want batch qty 150 ont that onwards that date onwards need to display because first one 0 show balance comes negitive i want 150 -0 150 next row 150 -150 0 next 200-0 200 suppose prevois balance 100 then prevoiusbalance+batchqty-issueqty like that

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Thulasiram pakala5y ago
select   InstituteName, drugcode , FullDrug,recdate,isnull(batchqty,0) as batchqty,isnull(Issueqty,0) as issueqty
                        From (Select (select top 1 institutename from tblinstitutedetailmaster where instituteid=instid) InstituteName,
                          kdls.dbo.InstInwardView.wdrugid as drugcode ,Convert(Nvarchar(20),rcptdate,103) as 'recdate',
                        SUM(isnull(Batchqty,0)) as 'batchqty',0 as 'issueqty', FullDrug=(Select top 1 FullDrug From klem.dbo.vwdrugDetails as drugview
                        where drugview.drugcode=InstInwardView.wdrugid)
                        From kdls.dbo.InstInwardView where InstInwardView.instid='002128' and convert(datetime,rcptdate) between '2017-01-01' and '2021-01-01'
                        and (wdrugid='3.1.1' or '3.1.1' is null)   group by   instid,rcptdate,kdls.dbo.InstInwardView.wdrugid
                        UNION ALL
                        Select (select top 1 institutename from tblinstitutedetailmaster where instituteid=instid) InstituteName,
                        kdls.dbo .InstOutwardView.oDrugid as drugcode , Convert(Nvarchar(20),oIssueDate,103) as 'recdate', 0 as 'batchqty', SUM(isnull(obatchqty,0)) as 'Issueqty',
                        FullDrug=(Select top 1 FullDrug From klem.dbo.vwdrugDetails as drugview where drugview .drugcode=InstOutwardView.oDrugid)
                        From kdls.dbo.InstOutwardView   where InstOutwardView.instid='002128'
                        and convert(datetime,oIssueDate) between '2017-01-01' and '2021-01-01' and (oDrugid='3.1.1' or '3.1.1' is null) group by   instid,oIssueDate,kdls.dbo.InstOutwardView.oDrugid
                        ) TBL2
                        order by   drugcode, YEAR(convert(datetime,recdate,103)) ,MONTH(convert(datetime,recdate,103)),Day(convert(datetime,recdate,103))

                                                                                                             batchqty   issueqty balanceqty
   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    01/02/2017    0           100                 -100
3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    17/03/2017     150      0                    50
3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    30/05/2017   0         -150   150
    3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    29/08/2017    200    0
   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    08/09/2017    0    200
   3.1.1    Adrenaline Bitartrate Injection 1mg/ml 1x1ml    06/02/2018    250    0

hear i want batch qty 150 ont that onwards that date onwards need to display because first one 0 show balance comes negitive i want 150 -0    150 next row 150 -150 0 next 200-0 200 suppose prevois balance 100 then prevoiusbalance+batchqty-issueqty like that
Amit Gupta5y ago
It would be more helpful, if you can share table structure with expected output. Generally, the formula of calculating the balance stock is (OpeningStock +(purchase-PurchaseReturn) -(Sales-SalesReturn))