August 12, 2010 at 5:14 am
Dear All.
I have developed a system for Manufacturing.
IN which I have to display production line results.
in this requirement, I have to check all the batches which resides in a financial year; e.g 01/04/2009 to 31/03/2010.
The query is.
DECLARE @StDt DATETIME;
DECLARE @EndDt DATETIME;
SET @StDt=CONVERT(DATETIME,'01/07/2010',103);
SET @EndDt=CONVERT(DATETIME,'31/07/2010',103);
WITH InspProd(Date,Drg,Opr,Batch,Rew,Qty)
AS
(
SELECT
IsMst.Date,
IsDtl.ToDrgID,
IsDtl.ToOprID,
IsDtl.BatchNo,
IsDtl.Rew,
IsDtl.Qty
FROM
Iss_Mst IsMst
INNER JOIN Iss_Dtl IsDtl
ON IsMst.ID=IsDtl.IssMstID
WHERE
IsMst.FromDeptID=8 AND IsMst.ToDeptID!=8
)
,ProdInsp(Date,Drg,Opr,Batch,Rew,Qty)
AS
(
SELECT
IsMst.Date,
IsDtl.ToDrgID,
IsDtl.ToOprID,
IsDtl.BatchNo,
IsDtl.Rew,
IsDtl.Qty
FROM
Iss_Mst IsMst
INNER JOIN Iss_Dtl IsDtl
ON IsMst.ID=IsDtl.IssMstID
WHERE
IsMst.FromDeptID!=8 AND IsMst.ToDeptID=8
)
SELECT
*
FROM
InspProd IProd
FULL OUTER JOIN ProdInsp PInsp
ON
IProd.Drg=PInsp.Drg
And
IProd.Opr=PInsp.Opr
And
IProd.Batch=PInsp.Batch
AND
Year(IProd.Date)=Year(PInsp.Date)
WHERE
convert(datetime,iprod.date,103) BETWEEN @StDt and @EndDt
Now i have to check that financial year condition in the last join condition that is of the Year.
How do i do this?
Pleas help...
August 12, 2010 at 6:14 am
All you have to do is add/subtract the appropriate amount to adjust the beginning of your fiscal year to Jan. 1. In your case, subtracting three months (or adding nine months) will do the trick.
DateAdd(month, -3, YourDate)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply