dear all ,
who is early solve this problem .please help me ...below that store procedure:
ALTER PROC [dbo].[GET_MEDI_STOCK_DATE_WISE_SP]
--EXEC GET_MEDI_STOCK_DATE_WISE_SP '3','2017-01-06','2017-30-07'
@MedecinId VARCHAR(50)=NULL,
@FROMDATE VARCHAR(50)=NULL,
@TODATE VARCHAR(50)=NULL
AS
DECLARE @condition VARCHAR(MAX)
SET @condition=''
IF(@MedecinId<>'0')
SET @condition='WHERE MedecinId='''+@MedecinId+''''
EXEC('WITH CTE AS(
SELECT CONVERT(DATETIME,'''+@FROMDATE+''',103) AS DT
UNION ALL
SELECT DATEADD(D,1,DT) FROM CTE WHERE DT<CONVERT(DATETIME,'''+@TODATE+''',103)
),
TEMPT AS (
SELECT c.DT, mt.MedecinId,mt.MedecinName, SUM(ISNULL(INSM.Balance,0)) AS [OpeningQty], SUM(ISNULL(PSM.balance,0)) AS [PurchaseQty],
SUM(ISNULL(DISM.DispatchBalance,0)) AS [DispatchQty], SUM(ISNULL(PSM.balance,0)-ISNULL(DISM.DispatchBalance,0)) AS [tempcol],
DISM.MedicineDisName AS DISPLACE,MDPL.MedicineDisName AS DISPLACENAME,MAX(PSM.MedicineExpireDate) AS MedicineExpireDate,
MAX(PSM.Remarks) AS Remarks
FROM CTE c
CROSS JOIN (SELECT DISTINCT MedecinId,MedecinName FROM MedecinMST '+@condition+') mt
LEFT JOIN InicialStockMedicine INSM ON CONVERT(DATE,INSM.MedicineStockDate,103) = CONVERT(DATE,c.DT,103) AND INSM.MedecinId = mt.MedecinId
LEFT JOIN MedicineStock PSM ON CONVERT(DATE,PSM.StockDate,103) = CONVERT(DATE,c.DT,103) AND PSM.MedecinId = mt.MedecinId
LEFT JOIN MedicineDispatch DISM ON CONVERT(DATE,DISM.MediDisPatchDate,103) = CONVERT(DATE,c.DT,103) AND DISM.MedecinId = mt.MedecinId
LEFT JOIN MedecineDistribution MDPL ON DISM.MedicineDisName=MDPL.MediDistribuId
GROUP BY c.DT, mt.MedecinId,mt.MedecinName,DISM.MedicineDisName,MDPL.MedicineDisName
)
SELECT t1.MedecinId,t2.MedecinName, t1.DT,t2.[OpeningQty] AS InitialQty, t1.[ClosingQty] - t2.[PurchaseQty] + t2.[DispatchQty] AS [OpeningQty],
t2.[PurchaseQty], (t1.[ClosingQty] - t2.[PurchaseQty] + t2.[DispatchQty])+t2.[PurchaseQty] AS TotalQty, t2.[DispatchQty], t1.[ClosingQty],
t2.DISPLACE,t2.DISPLACENAME,t2.MedicineExpireDate,t2.Remarks
FROM (
SELECT t1.DT, t1.MedecinId, sum(t2.[OpeningQty] + t2.[PurchaseQty] - t2.[DispatchQty]) AS [ClosingQty]
FROM TEMPT t1
LEFT JOIN TEMPT t2 ON t1.MedecinId = t2.MedecinId AND t1.DT >= t2.DT
GROUP BY t1.DT, t1.MedecinId
) t1
INNER JOIN TEMPT t2 ON t1.MedecinId = t2.MedecinId AND t1.DT = t2.DT
ORDER BY t1.MedecinId, t1.DT
')
and cs report where is problem .its deos not execute formdate and todate is showing error.
private void REPORT_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgse)
{
Dataset.StockTableAdapters.GET_MEDI_STOCK_DATE_WISE_SPTableAdapter da = new Dataset.StockTableAdapters.GET_MEDI_STOCK_DATE_WISE_SPTableAdapter();
da.Fill(this.stock1.GET_MEDI_STOCK_DATE_WISE_SP, this.FROMDATE.Value.ToString(), this.FROMDATE.Value.ToString(), this.TODATE.Value.ToString());
}