Displaying Monthly Record - 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

General SQL Server Forums

New to SQL Server Programming

Displaying Monthly Record

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-07-04 : 06:57:28

Team,

I want to display the monthly order datails, please find the sample table with value

Table Name : tblORDERS

OrderId Price OrderDate
1 100 2009-06-02 02:10:11.001
2 200 2009-06-02 03:01:00.000
3 500 2009-06-03 03:05:04.002

I want to display the July month order details,SO i will pass month and Year in where condition,

So for example i want to Display the July month record means i have to display all the 31 Days ( 1 to 31 ) and display the total sales for the each date, If the table does not contain any order for the date it should display the Total sales as 0

please find my expected Result

DATE TOTALSALES
1 0
2 300
3 500
4 0
5 0
6 0
.
.
.
.
.
.
.
31 0

I am using the below Query but it is displaying only for the Available date in the orders table

DATE TOTALSALES
2 300
3 500

select day(orderdate) as DATE, sum(Price) as TotalSales from rr_order where month(orderdate)=6 and year(orderdate)=2009 group by day(orderdate)

Please help me to get the expected output

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 07:13:18

if you have a calendar table, use it. If not, use F_TABLE_DATE as a replacement


declare @year   int,
        @month  int,
        @mth_st datetime,
        @mth_en datetime

select	@mth_st	= dateadd(month, @month - 1, dateadd(year, @year - 1900, 0))
select	@mth_en	= dateadd(month, @month, dateadd(year, @year - 1900, -1))

select  d.DAY_OF_MONTH, TotalSales = sum(isnull(o.Price, 0))
from    F_TABLE_DATE d
        left join tblORDERS o  ON  o.OrderDate >= d.[DATE]
                               AND o.OrderDate <  dateadd(day, 1, d.[DATE])
group by d.DAY_OF_MONTH

KH
[spoiler]Time is always against us[/spoiler]

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-07-04 : 07:39:35

Khtan,

Thank you so much for your Reply,

I am working on SQL SERVER 2000
I dont have Order Table and F_Table_Date ,

I have tblOrders Table only, please let me know using this table alone can i able to get my expected output,

Please guide me and help to get my expected output

Thanks in Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 08:07:08

you can get F_TABLE_DATE on the link that i provided.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 02:53:05

[code]
DECLARE @Month int,@Year int,@StartDate datetime,@EndDate datetime

SELECT @Month=6,--passed on month value
@Year=2009--passed on year value
SELECT @StartDate=DATEADD(mm,@Month-1,DATEADD(yy,@Year-1900,0)),
@EndDate=DATEADD(mm,@Month,DATEADD(yy,@Year-1900,0))-1

SELECT cal.DateVal AS [DATE],COALESCE(ord.TotalPrice,0) AS [TOTALSALES]
FROM
(
SELECT DATEADD(dd,number,@StartDate) AS DateVal
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<=@EndDate
)cal
LEFT JOIN
(SELECT DATEADD(dd,DATEDIFF(dd,0,OrderDate),0) AS DateVal,
SUM(Price) AS TotalPrice
FROM tblORDERS
GROUP BY DATEADD(dd,DATEDIFF(dd,0,OrderDate),0)
)ord
ON ord.DateVal=cal.DateVal
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources