SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sum with Multi Category and Group By Acc No ?


Sum with Multi Category and Group By Acc No ?

Author
Message
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
SELECT 
a.AccNo,
SUM( case when t.TransType_ID = 0 then t.Amount else 0 end ) as Total_Adjustment,
SUM( case when t.TransType_ID = 1 then t.Amount else 0 end ) as Total_Topup,
SUM( case when t.TransType_ID = 2 then t.Amount else 0 end ) as Total_Comm,
SUM( case when t.TransType_ID = 3 then t.Amount else 0 end ) as Total_Transfer,
SUM( case when t.TransType_ID = 4 then t.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then t.Amount else 0 end ) as Total_Maxis,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then t.Amount else 0 end ) as Total_Digi,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then t.Amount else 0 end ) as Total_Celcom,
Option1.Open_Balance,
Option2.Open_Balance,
Option3.Open_Balance
FROM Tbl_Account a
LEFT JOIN Tbl_Transaction t
ON a.AccNo = t.Transaction_AccNo
-- this date arithmetic should provide the same filter as the original
-- but is SARGable (use Google)
AND t.Transaction_Date > convert(Date,'16-9-2012', 105)
AND t.Transaction_Date < convert(Date,'01-10-2012', 105)
LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_ID

OUTER APPLY (-- first transaction of the date range, if there is one
SELECT TOP 1 Open_Balance = ti.Amount
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)
AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)
ORDER BY ti.Transaction_Date
) Option1

OUTER APPLY (-- largest value of a transaction, if there is one
SELECT TOP 1 Open_Balance = ti.Amount
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)
AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)
ORDER BY ti.Amount DESC
) Option2

CROSS APPLY ( -- SUM of all the amounts prior to date range
SELECT Open_Balance = SUM(ti.Amount)
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date <= convert(Date,'16-9-2012', 105)
) Option3

Group By a.AccNo
ORDER BY a.AccNo



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
chinye2020 (9/19/2012)
...


DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?


Performance will be very poor. What datatype is Tbl_Transaction.Transaction_Date?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 41
with CTE as
(
SELECT
Transaction_AccNo,
Amount,Before_Amount,After_Amount,
TransType_ID,
Topup_ID,
ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
ORDER BY Transaction_Date) as RowNum,
ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
ORDER BY Transaction_Date DESC) as LastRowNum
from Tbl_Transaction
where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30'
)

select
Tbl_Account.AccNo,
SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
SUM( case when RowNum = 1 then Before_Amount else 0 end) as Open_Balance,
SUM( case when LastRowNum = 1 then After_Amount else 0 end) as Close_Balance
FROM Tbl_Account
LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo
LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID
Group By Tbl_Account.AccNo
ORDER BY Tbl_Account.AccNo



DONE!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
chinye2020 (9/19/2012)
...
DONE!


That's not opening and closing balance - it's simply the amounts of the first and last transactions in the date range.

[code="sql"]SELECT
a.AccNo,
t.Total_Adjustment,
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a

LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = t.Topup_ID
WHERE ti.Transaction_Date >= convert(Date,'2012-09-1', 105)
AND ti.Transaction_Date < convert(Date,'2012-10-01', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo

OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Open_Balance = SUM(ti.Amount)
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'2012-09-1', 105)
) Option3

ORDER BY a.AccNo


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 41
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
...
DONE!


That's not opening and closing balance - it's simply the amounts of the first and last transactions in the date range.

[code="sql"]SELECT
a.AccNo,
t.Total_Adjustment,
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a

LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = t.Topup_ID
WHERE ti.Transaction_Date >= convert(Date,'2012-09-1', 105)
AND ti.Transaction_Date < convert(Date,'2012-10-01', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo

OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Open_Balance = SUM(ti.Amount)
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'2012-09-1', 105)
) Option3

ORDER BY a.AccNo



This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??


SELECT
a.AccNo,
isnull(t.Total_Adjustment,0.00),
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a WHERE A.AccNo <> 1 <---- add at here??? ERROR ...

LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_ID
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo

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

ORDER BY a.AccNo


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
chinye2020 (9/19/2012)
[quote]...
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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 41
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
[quote]...
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??
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
chinye2020 (9/19/2012)
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
[quote]...
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 41
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
[quote]...
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?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
chinye2020 (9/19/2012)
...
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?


Use ISNULL or COALESCE in the output set.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search