December 30, 2003 at 1:59 am
Hi Everyone,
Good Morning, i am having a table called sales which is having 3 fields SaleID, SaleDate, SaleAmount. Now i want to get sales amount group by date, week , month and year. when i want the report date wise i can give as group by salesdate and when i need month wise then we can group by datepart(mm, SalesDate) but it will give problem when we are having same months with different years, when we need report year wise we can give group by as datepart(yyyy, SalesDate). Now i am facing the problem with group by month and group by week. I am not able to get idea how to group by week. when the report needs to display group by i want to display the salesAmount as below
12/7/03 - 12/13/0315000
12/14/03 - 12/20/034500
12/21/03 - 12/27/035000
like the above i need the output. In the same way for month i need to display the month name and year instead of date range.
so pls give me your valuable suggession
Thanks in advance.
Pratap
Software Engineer,
PH: +91 9849781009
December 30, 2003 at 2:23 am
Might be conveniant if you had a dates-table (like in most datawarehouse-env)
Then you would have a materialized week-start and week-end date, so you could join both tables.
table might contain :
[Calender_YEAR] [smallint] NOT NULL ,
[WEEK_NO] [int] NOT NULL ,
[STARTDATE_WEEK] [datetime] NOT NULL ,
[ENDDATE_WEEK] [datetime] NOT NULL ...
That might ease joining and grouping.
Found this in my TEST-archive :
create procedure spc_ALZ_WeekByDate
@RefDate varchar(25),
@Format int = 103
as
/*
The following procedure will get week information ( number,day start-day finish) for requested date.
Format of the output dates could be changed. (see 'style' info in the SQL HelpFile for 'Convert' function)
Without With
century century
(yy)(yyyy)Standard Input/Output**
-0 or 100 (*) Defaultmon dd yyyy hh:miAM (or PM)
1101USAmm/dd/yy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106-dd mon yy
7107-mon dd, yy
8108-hh:mm:ss
-9 or 109 (*) Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd
-13 or 113 (*) Europe default + millisecondsdd mon yyyy hh:mm:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (*) ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
-21 or 121 (*) ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm(24h)
Created by Eva Zadoyen
04/08/2002
*/
set nocount on
declare @firstday varchar(25)
declare @endday varchar(25)
declare @day int
declare @week int
declare @WrkDate datetime
declare @dd int
declare @mm int
declare @yyyy int
select @yyyy = datepart(yyyy,@RefDate)
Select @mm = datepart(mm,@RefDate)
Select @dd = datepart(dd,@RefDate)
select @WrkDate = cast(cast(@yyyy as char(4))+'-'+cast(@mm as char(2))+'-'+cast(@dd as char(2)) as datetime)
select @week = datepart(ww,@WrkDate)
-- select @day = @@datefirst- datepart(dd,@RefDate) -- origineel, maar fout !!
select @day = (datepart(dw,@WrkDate) - 1) * (-1)
select @firstday = convert(varchar(25),dateadd( dd,@day, @WrkDate),@format)
-- select @endday = convert(varchar(25),dateadd(dd,7,@firstday),@format) -- ook fout (1 dag teveel)
-- we willen immers maar van Zondag tot Zaterdag of van Maandag tot Zondag en niet van Zondag tot Zondag
select @endday = convert(varchar(25),dateadd(ms,-2,dateadd(dd,7,@firstday)),@format)
select @RefDate 'Date',@week 'Week', @firstday 'First Day of the Week',@endday 'Last Day of the Week'
go
declare @mydate varchar(25)
-- set @mydate = '2002-01-01 00:00:00'
set @mydate = getdate()
exec spc_ALZ_WeekByDate @mydate,121
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2003 at 2:34 am
I think when you want the report month wise you can use group by datepart(yyyy, SalesDate), datepart(mm, SalesDate), also the same for week group by datepart(yyyy, SalesDate), datepart(wk, SalesDate)
But for week wise if you group like that how about the week between two year should it be in one group or two group?
If should be in one group you have convert the date exactly like your sample and group with that example - 12/7/03 - 12/13/03
To get that you should use datepart(dw, SalesDate) in the formula.
Hope this can help.
rgds,
Bobie
December 30, 2003 at 3:00 am
quote:
I think when you want the report month wise you can use group by datepart(yyyy, SalesDate), datepart(mm, SalesDate), also the same for week group by datepart(yyyy, SalesDate), datepart(wk, SalesDate)But for week wise if you group like that how about the week between two year should it be in one group or two group?
If should be in one group you have convert the date exactly like your sample and group with that example - 12/7/03 - 12/13/03
To get that you should use datepart(dw, SalesDate) in the formula.
Hope this can help.
rgds,
Bobie
Hi Bobie,
You solved 50% of my problem, now the only problem is with the format datepart(dw,date) won't give the week range. but we need to show the week range in the report. according to you we can get the year and week in that year, from that we need to get week range. so please have a look once again.
Thank you
Pratap
December 30, 2003 at 3:36 am
Hi Pratap,
Sory i didn't give you the full formula, here it is :
select convert(varchar(10),dateadd(dd,-datepart(dw,SalesDate)+2,SalesDate),101) + '-' + convert(varchar(10),dateadd(dd,datepart(dw,SalesDate)+2,SalesDate),101)
The formula will be like that but the constanta 2 is depend on what day you want to start the week, my example is for monday until sunday.
I am not sure about the performance but I think it's better to use temp table because it's not good to use the formula twice in select and in the group by statement especially for table with many rows.
Hope this can solve your problems.
rgds,
Bobie
December 30, 2003 at 3:57 am
Maybe this is wat you're looking for to add the remaining 50%
CREATE FUNCTION dbo.udf_convert_date2WeekRange (@RefDate datetime, @FirstLast char(1) = 'F')
RETURNS datetime
AS
BEGIN
DECLARE @return_date as datetime
declare @firstdate datetime
declare @WrkDate datetime
declare @refDays int
select @WrkDate = cast(convert(char(10), @RefDate, 121) as datetime)
select @refDays = (datepart(dw,@WrkDate) - 1) * (-1)
select @firstdate = dateadd( dd,@refDays, @WrkDate)
if @FirstLast = 'F'
set @return_date = @firstdate
else
select @return_date = dateadd(ms,-2,dateadd(dd,7,@Firstdate))
RETURN (@return_date)
END
go
--
select dbo.udf_convert_date2WeekRange(getdate(),'F'), dbo.udf_convert_date2WeekRange(getdate(),'L')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply