question

postingID int carid int sellerType int sellerID int SaleDate datetime dateOfPosting datetime expirtyDate datetime packageID int UserPackID int paymentID int PaymentDate datetime PDDate datetime PSID1 int PSID2 int PSID3 int PD1Amount varchar PD2Amount varchar QCID int PayActive bit ListingActive bit isActive bit LeadStatus int DispositionID int CallBackID int AdStatus int InternalreviewID int cancelledBy int cancelledReason varchar cancelledDate datetime zipcode varchar uid int Source varchar SourcePicID varchar WCCallID int Days30CallID int Days60CallID int HomeViewCount int BannerViewCount int SearchViewCount int FilterCount int MakeViewCount int MakeModelViewCount int MakeModelALLViewCount int DetailsViewCount int SaleEnteredBy varchar ThisWeek int ThisMonth int IsLocked int IsLockedDateTime datetime Ip varchar LastUpdatedDate datetime VoiceFileNumber varchar TransferDate datetime SmartzStatus int SmartzCarID int SmartzMovedDate datetime UCS_DiscountId decimal These are columns thah i have in the table. Subject: I need a monthly calender with total sales per day in table format. Please find the Attachment for required output in that i need total sales per day along with the date cell (13911) is the total sales of the month.![alt text][1] [1]: /storage/temp/1379-untitled1.jpg

Hi All, I am creating a calendar which has to display total sales along with the day in calendar format.Here I am getting calendar format but total sales is not getting per particular day. I Write Query like this Create procedure Calender -- 'Mar 24 2014' @Date datetime as begin Declare @Time datetime set @Time=@Date print @Time SET DATEFORMAT dmy ;with monthDates as ( select DATEADD(month, datediff(month, 0, cast(@Time as varchar(12))),0) as d ,DATEPART(week, DATEADD(month, datediff(month, 0, cast(@Time as varchar(12))),0)) as w union all select DATEADD(day, 1, d) ,DATEPART(week, DATEADD(day, 1, d)) from monthDates where d < DATEADD(month, datediff(month, 0, cast(@Time as varchar(12)))+1,-1) ) select(max(case when datepart(dw, d) = 1 then convert(varchar(100),datepart(d,d)) else null end) **+' '+ convert(varchar(100),max(case when datepart(dw, d) = 1 then (convert(varchar(100),((2)))) else null end)))** as [Sun] --- I AM USING THIS QUERY IN THE PLACE OF **'2'** (SELECT count(postingid) FROM Tbl_UsedCarsForSale group BY DATEPART(d,dateofposting)) THROWING ERROR ,max(case when datepart(dw, d) = 2 then datepart(d,d) else null end) as [Mon] ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tue] ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wed] ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thu] ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Fri] ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Sat] from monthDates group by w END select(max(case when datepart(dw, d) = 1 then convert(varchar(100),datepart(d,d)) else null end) +' '+ convert(varchar(100),max(case when datepart( d,dateofposting) = 1 then (convert(varchar(100), ((select count(postingid) from Tbl_usedcarsforsale )))) else null end))) as [Sun] This is trying for date along with total sales but its giving error like aggregate function is not working in subquery. Please give any suggestions.......how to solve this.. Table structure wouid be as follows: ![alt text][1] I NEED OUTPUT LIKE THIS: ![alt text][2] [1]: /storage/temp/1387-2.jpg [2]: /storage/temp/1388-1.jpg

First of all let me say that I would never do this using SQL. This kind of formatting is for the front end /application and not for the database engine. However here is a way to do what you want declare @YourTable table ( PostingID int, dateofposting datetime) insert into @YourTable select 1, '1 Mar 2014' insert into @YourTable select 2, '2 Mar 2014' insert into @YourTable select 3, '2 Mar 2014' insert into @YourTable select 4, '1 Mar 2014' insert into @YourTable select 5, '4 Mar 2014' insert into @YourTable select 6, '5 Mar 2014' declare @date datetime set @date = '24 march 2014' declare @startmonth datetime set @startmonth = dateadd(month, datediff(month, 0, @date),0) --beginning of month ;with cte_tally as ( select top 10000 ROW_NUMBER() over(order by sc1.column_id) N from sys.columns sc1,sys.columns sc2 ) , CalendarMonth as ( select dateadd(day,N-1,@startmonth) as CalendarDate, datepart(week,dateadd(day,N-1,@startmonth)) as WeekNum, datepart(day,dateadd(day,N-1,@startmonth)) as DayNum from cte_tally where dateadd(day,N-1,@startmonth) < dateadd(month,1,@startmonth) ) ,AllDates as ( select CalendarDate, WeekNum, cast(DayNum as varchar) + ' ' + isnull(cast(Sales as varchar),'') as Value from CalendarMonth left join ( select dateofposting, count(*) as Sales from @YourTable yt group by dateofposting ) AggregatedSales on CalendarMonth.CalendarDate = AggregatedSales.dateofposting ) --manual pivot --only works whilst datefirst = 7 (DEFAULT SETTING) select max(case when datepart(weekday,CalendarDate) = 1 then Value end) as 'Sun', max(case when datepart(weekday,CalendarDate) = 2 then Value end) as 'Mon', max(case when datepart(weekday,CalendarDate) = 3 then Value end) as 'Tue', max(case when datepart(weekday,CalendarDate) = 4 then Value end) as 'Wed', max(case when datepart(weekday,CalendarDate) = 5 then Value end) as 'Thur', max(case when datepart(weekday,CalendarDate) = 6 then Value end) as 'Fri', max(case when datepart(weekday,CalendarDate) = 7 then Value end) as 'Sat' from AllDates group by WeekNum order by WeekNum

Hi Thanks Kev Riley, I Solved the issue like this CREATE procedure USP_MonthlySales --'03-03-2014' @SDate Datetime as begin Declare @Time datetime set @Time=convert(varchar(10),@SDate, 105) print @Time SET DATEFORMAT dmy ;with monthDates as ( select DATEADD(month, datediff(month, 0, cast(@Time as varchar(12))),0) as d ,DATEPART(week, DATEADD(month, datediff(month, 0, cast(@Time as varchar(12))),0)) as w union all select DATEADD(day, 1, d) ,DATEPART(week, DATEADD(day, 1, d)) from monthDates where d < DATEADD(month, datediff(month, 0, cast(@Time as varchar(12)))+1,-1) ) -- max(case when datepart(dw,d)=1 then datepart(d,count(postingid) select convert(varchar(50),MonthTb.Sun)+'('+ convert(varchar(50),( select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Sun and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Sunday, convert(varchar(50),MonthTb.Mon)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Mon and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Monday, convert(varchar(50),MonthTb.Tue)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Tue and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Tuesday, convert(varchar(50),MonthTb.Wed)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Wed and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Wednesday, convert(varchar(50),MonthTb.Thu)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Thu and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Thursday, convert(varchar(50),MonthTb.Fri)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Fri and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Friday, convert(varchar(50),MonthTb.Sat)+'('+ convert(varchar(50),(select count(postingid) as C1 from Tbl_UsedCarsForSale where datepart(d,dateofposting)=MonthTb.Sat and datepart(m,dateofposting)=datepart(m,@Time) and leadstatus=1 and datepart(yy,dateofposting)=datepart(yy,@Time) ))+')' as Saturday from ( select max(case when datepart(dw, d) =1 then datepart(d,d) else null end) as [Sun] ,max(case when datepart(dw, d) =2 then datepart(d,d) else null end) as [Mon] ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tue] ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wed] ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thu] ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Fri] ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Sat] from monthDates group by w ) as MonthTb END Thanks&Regards Balakrishna --