Group By Date Query

Hi,
I want to arrange some of the data retrieved from the database with
respect to a range of date. The tables from which I am retrieving the
data are, namely;
1. Transaction
2. Credit
3. Debit
4. Account
My stored procedure is as follows
CREATE PROCEDURE [dbo].[SP_TransactionInfo]
@.startDate datetime, @.endDate datetime
AS
BEGIN
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
the problem is that I want to add a statement that would use the
@.startDate and @.endDate to as a range of dates according to which all
the data will be arranged in descending order.
Thanking you.
Chao.
Hi
Don't you have a column that identifies the debit, credit..whatever?
Add WHERE condtion like dt_column >=@.startDate AND dt_column
<DATEADD(d,1,@.endDate )
It is probably good to hhave an index of dt_column
<umairsyed19@.gmail.com> wrote in message
news:574964e1-dbc1-4f42-b7a3-cc5f1f6e5a9f@.n75g2000hsh.googlegroups.com...
> Hi,
> I want to arrange some of the data retrieved from the database with
> respect to a range of date. The tables from which I am retrieving the
> data are, namely;
> 1. Transaction
> 2. Credit
> 3. Debit
> 4. Account
> My stored procedure is as follows
> CREATE PROCEDURE [dbo].[SP_TransactionInfo]
> @.startDate datetime, @.endDate datetime
> AS
> BEGIN
> SELECT distinct
> T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
> C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
> AC.Account_Name as Deb_AccName
> FROM Transactions T
> JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
> JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
> JOIN Account AC ON C.Account_ID = AC.Account_ID
> JOIN Account AD ON D.Account_ID = AD.Account_ID
> the problem is that I want to add a statement that would use the
> @.startDate and @.endDate to as a range of dates according to which all
> the data will be arranged in descending order.
>
> Thanking you.
> Chao.
|||Perhaps you mean to filter the Transaction Date (as this is the only date
column based on the name) and order by it descending:
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date BETWEEN @.startDate AND @.endDate
ORDER BY T.Transaction_Date DESC
The above will have the date range including the start and end dates. To
exclude one or both of them you can change as follows (below the end date is
excluded):
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date >= @.startDate
AND T.Transaction_Date < @.endDate
ORDER BY T.Transaction_Date DESC
HTH,
Plamen Ratchev
http://www.SQLStudio.com