Is this a Date Comparison bug or in one of my field?
Good Day Every One
as i create queries for my project i stumbled upon this bug
when i use this code
DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'
SELECT COUNT (*) AS CYJEWELRY
FROM Transactions.tbl_PawnItem PIT
INNER JOIN Transactions.tbl_PawnMain PMN
ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
INNER JOIN Transactions.tbl_PawnHisto PHI
ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
WHERE PMN.fld_StatusID = 3 /* OR PMN.fld_StatusID = 4*/
AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =
DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
AND PMN.fld_StorageGroupID >= 3 and PMN.fld_StorageGroupID <= 14
The results are CYJEWELRY = 23
When i use the status ID 4 like this
DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'
SELECT COUNT (*) AS CYJEWELRY
FROM Transactions.tbl_PawnItem PIT
INNER JOIN Transactions.tbl_PawnMain PMN
ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
INNER JOIN Transactions.tbl_PawnHisto PHI
ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
WHERE /*PMN.fld_StatusID = 3 OR */ PMN.fld_StatusID = 4
AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =
DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
AND PMN.fld_StorageGroupID >= 3 and PMN.fld_StorageGroupID <= 14
The results are CYJEWELRY = 34
The question is why does when i use both of them like this
DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'
SELECT COUNT (*) AS CYJEWELRY
FROM Transactions.tbl_PawnItem PIT
INNER JOIN Transactions.tbl_PawnMain PMN
ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
INNER JOIN Transactions.tbl_PawnHisto PHI
ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
WHERE PMN.fld_StatusID = 3 OR PMN.fld_StatusID = 4
AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =
DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
AND PMN.fld_StorageGroupID >= 3 and PMN.fld_StorageGroupID <= 14
the results turn to be CYJEWELRY = 380?(i know this is wrong) instead of 57?
any tips? or suggestions? to improve my code and remove the bug?? that
would be higly appreciated by me im new in t-sql please be gentle :) thank
you so much :)
No comments:
Post a Comment