get amount in the date range

Hanney

Hi there,
I create a report which display local amount with in date range..like current,31-60,
61-90,91-120,120+,my report have parameter,@startdate and @enddate.....
so my 1st query like this

SELECT gle.[G_L Account No_],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN gle.[Posting Date] AND DATEADD(dd,30,gle.[Posting Date]))
) AS [CurrAmount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,31,gle.[Posting Date]) AND DATEADD(dd,60,gle.[Posting Date]))
)AS [31-60Amount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,61,gle.[Posting Date]) AND DATEADD(dd,90,gle.[Posting Date]))
)AS [61-90Amount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,91,gle.[Posting Date]) AND DATEADD(dd,120,gle.[Posting Date]))
)AS [91-120Amount]
FROM [CRONUS$G_L Entry] gle

for example @StartDate:21/1/2007 and @EndDate:15/5/2007
I try to put: WHERE gle.[Posting Date] BETWEEN 21/1/2007 AND 15/5/2007
the result correct until amount for 91-120Amount and 120+Amount..
how to make they stop calculate the amount until @enddate..
any idea? [-o<

Thank you..

ara3n

Can you paste your whole sql statement?

From what I see you only need a Start Date.

If you really want a start date and End date in your query you can change it like this.

SELECT gle.[G_L Account No_],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN gle.[Posting Date] AND DATEADD(dd,30,gle.[Posting Date]) AND
   gle.[Posting Date]  >= @startdate   and  gle.[Posting Date]  >= @Enddate)
) AS [CurrAmount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,31,gle.[Posting Date]) AND DATEADD(dd,60,gle.[Posting Date]) and
  gle.[Posting Date]  >= @startdate   and  gle.[Posting Date]  >= @Enddate)
)AS [31-60Amount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,61,gle.[Posting Date]) AND DATEADD(dd,90,gle.[Posting Date]) AND
gle.[Posting Date]  >= @startdate   and  gle.[Posting Date]  >= @Enddate)
)AS [61-90Amount],
(
SELECT SUM(gle.Amount )
WHERE (gle.[Posting Date] BETWEEN DATEADD(dd,91,gle.[Posting Date]) AND DATEADD(dd,120,gle.[Posting Date]) AND
gle.[Posting Date]  >= @startdate   and  gle.[Posting Date]  >= @Enddate)
)AS [91-120Amount]
FROM [CRONUS$G_L Entry] gle

Hanney

I already paste the whole code..
However I face several problem the posting_date data type is date and time..
how to make posting_date is only accept date? and no different result when i put SUM(gle.Amount) and gle.Amount..

For example:
SELECT gle.[G_L Account No_],gle.[Posting Date], SUM(gle.Amount) AS [2006-12-31 ]
FROM [CRONUS$G_L Entry] gle
WHERE (gle.[G_L Account No_] = 1110) AND (gle.[Posting Date] = '2006-12-31 23:59:59.000' )
GROUP BY gle.[G_L Account No_],gle.[Posting Date],gle.Amount

and

SELECT gle.[G_L Account No_],gle.[Posting Date], gle.Amount
FROM [CRONUS$G_L Entry] gle
WHERE (gle.[G_L Account No_] = 1110) AND (gle.[Posting Date] = '2006-12-31 23:59:59.000' )
GROUP BY gle.[G_L Account No_],gle.[Posting Date],gle.Amount

Result
G_L Account No_ gle.[Posting Date] gle.Amount
1110 2006-12-31 23:59:59.000 855378.46000000000000000000
1110 2006-12-31 23:59:59.000 7336232.03000000000000000000

how to fix it? #-o

Hanney

I try to convert the posting date, so I use this syntax but it didn't work..
convert(DATETIME, [Posting Date],104) :-k