Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date

Author

Topic

balagangadharan
Starting Member

5 Posts

Posted - 2008-06-10 : 00:54:19

Hello All

i have a table and i need to get datas from that table for outstanding report

i need to get result as this

Customer Name < 30 days > 30 to < 60 days > 60 days

Rogress Techno Pvt 6474 121855 122922
i have a date field in my table as Invoice Date
if i run a query i need to get this outstanding report as on date

for ex if as on date be 06/10/2008

and Invoive date between be 03/16/2008

now i need to get the value for different days as < 30 days and > 30 days < 60 days

please help me to do this

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 01:00:49

[code]SELECT CustomerName,
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) <30 THEN 1 ELSE 0 END) AS [<30days],
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) >30 AND DATEDIFF(dd,InvoiceDate,@Date) <60 THEN 1 ELSE 0 END) AS [> 30 to < 60 days ],
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) >60 THEN 1 ELSE 0 END) AS [> 60 days ]

FROM Table
GROUP BY CustomerName[/code]

Where @Date is date passed to report (as on date value)

balagangadharan
Starting Member

5 Posts

Posted - 2008-06-10 : 01:41:59

Hello Visakh

Thanks for replying

its ok

but i need to sum the amount for that date diff

how to do this
please help me
thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 02:21:03

[code]SELECT CustomerName,
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) <30 THEN AmountField ELSE 0 END) AS [<30days],
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) >30 AND DATEDIFF(dd,InvoiceDate,@Date) <60 THEN AmountField ELSE 0 END) AS [> 30 to < 60 days ],
SUM(CASE WHEN DATEDIFF(dd,InvoiceDate,@Date) >60 THEN AmountField ELSE 0 END) AS [> 60 days ]

FROM Table
GROUP BY CustomerName[/code]

balagangadharan
Starting Member

5 Posts

Posted - 2008-06-10 : 02:24:23

Hello Visakh
thanks for replying thats great its works fine

Thanks for your kind Help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 02:30:11

quote:
Originally posted by balagangadharan

Hello Visakh
thanks for replying thats great its works fine

Thanks for your kind Help

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources