ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
...
This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??
...
Easy - but first, we need to sort out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
because i m passing the value from web is nvarchar, so i need to convert the nvarchar to date format, otherwise, Transaction_Date is DateTime,i just want to compare date, so i need to convert Datetime to Date only, if not some data didn't show in result.
p/s:i just want to compare date, not Date and Time
and Chris,
how we can do at here
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
if the Open_Balance is null between 16-09-2012 and 30-09-2012,then change the sql query to check the transaction ONLY before 16-09-2012(NOT BETWEEN, IS ONLY BEFORE 16-09-2012) , how to do this??
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'16-09-2012', 105)
ORDER BY Transaction_Date
) Option3
No,Chris, When the Acc No's Open_balance is null Between the date only change the sql query to before 16-09-12...
how to do that?