January 15, 2008 at 3:41 pm
Hi Experts.
I have this query which count how many sellers who sold at least one item from a category IN (5, 7, 41), on site_id=3 in the calendar month of October 2007.
Select Count(distinct slr_id)
from gdw_tables.DW_CATEGORY_GROUPINGS g, gdw_tables.dw_lstg_item l
where g.site_id = 3 and g.USER_DEFINED_FIELD1 IN ('5', '7', '41') and l.success_yn='Y' and l.auct_end_dt between '2007-10-01' and '2007-10-31'
and l.leaf_categ_id=g.leaf_categ_id and g.site_id =l.item_site_id
The only criteria needs to be changed in the query is: in each of pervious 3 months.
That means, I need to find out how many sellers who sold at least one item from a category IN (5, 7, 41), on site_id=3, in each of pervious 3 calendar month as of today.
Can someone help?
Thanks.
January 15, 2008 at 3:58 pm
I think this is what you need
Select Count(distinct slr_id)
from gdw_tables.DW_CATEGORY_GROUPINGS g, gdw_tables.dw_lstg_item l
where g.site_id = 3 and g.USER_DEFINED_FIELD1 IN ('5', '7', '41') and l.success_yn='Y' and l.auct_end_dt >= dateadd(month,-3,getdate())
and l.leaf_categ_id=g.leaf_categ_id and g.site_id =l.item_site_id
January 16, 2008 at 2:54 am
it doesn't sound that simple. you're going to need some intermediate summary to determine sales by month.
declare @startDate smalldatetime, @endDate smalldatetime
set @startDate = dateadd( month,-3,getdate() ) -- 3 months ago
set @startDate = dateadd( day, 1 - datepart( day, @startDate ), @startDate ) -- 1st of month, 3 months ago
set @endDate = dateadd( day, 1 - datepart( day, getdate() ), getdate() ) -- 1st of this month
set @startDate = cast(convert(varchar,@startDate,101) as smalldatetime)
set @endDate = cast(convert(varchar,@endDate,101) as smalldatetime)
select @startDate, @endDate
select slr_id, count(*) as months, min(sales) as min_sales, sum(sales) as total_sales
from
( select slr_id, month(l.auct_end_dt) as month, count(*) as sales
from gdw_tables.DW_CATEGORY_GROUPINGS g, gdw_tables.dw_lstg_item l
where g.site_id = 3 and g.USER_DEFINED_FIELD1 IN ('5', '7', '41') and l.success_yn='Y'
and l_auct_end_dt >= @startDate and l_auct_end_dt < @endDate
group by slr_id ) as MONTHLY_SALES
group by slr_id
having months = 3 and min_sales >= 3
the pseduo-table aliased as MONTHLY_SALES should summarize sales by slr_id for each of the three prior full months. from that summarization, it then gets all sellers who have sold product in each month (months = 3) and sold at least 3 items in each month (min_sales >= 3). you will wind up with a list of sellars and their sales summary but you can just count that list to get the total.
January 16, 2008 at 4:02 am
a slightly different approach using top 3 and order by to get the top 3 months.
I also limited the query to only count up to 4 months in the past, not sure how your tables are indexed but this should help keep query times down.
Select top 3
Count(distinct slr_id) as [cnt],
datename(mm, cast(cast(year(l.auct_end_dt) as varchar)+right('00'+cast(month(l.auct_end_dt) as varchar), 2)+'01'as datetime)) as [Month]
from gdw_tables.DW_CATEGORY_GROUPINGS g,
gdw_tables.dw_lstg_item l
where g.site_id = 3
and g.USER_DEFINED_FIELD1 IN ('5', '7', '41')
and l.success_yn='Y'
and month(l.auct_end_dt) <> month(getdate())
and l.auct_end_dt > dateadd(mm, -4, getdate())
and l.leaf_categ_id=g.leaf_categ_id
and g.site_id =l.item_site_id
group by
cast(cast(year(l.auct_end_dt) as varchar)+right('00'+cast(month(l.auct_end_dt) as varchar), 2)+'01'as datetime)
order by
cast(cast(year(l.auct_end_dt) as varchar)+right('00'+cast(month(l.auct_end_dt) as varchar), 2)+'01'as datetime)
desc
January 16, 2008 at 7:53 am
re: Samuel Vella
that query does not seem to only consider sellers who have sold at least 3 items in each of the past 3 months. have i misinterpreted the query?
January 16, 2008 at 7:59 am
antonio.collins (1/16/2008)
re: Samuel Vellathat query does not seem to only consider sellers who have sold at least 3 items in each of the past 3 months. have i misinterpreted the query?
I think you might have over complicated it a bit, the original post only specified at least 1 item
January 16, 2008 at 8:21 am
re: Samuel Vella
yup, you're right -- 1 per month. i've been doing a rebuild/release all night and letters and digits are starting to run together. 😀
still, how does your query ensure at least one sale in each month?
January 16, 2008 at 8:30 am
antonio.collins (1/16/2008)
re: Samuel Vellayup, you're right -- 1 per month. i've been doing a rebuild/release all night and letters and digits are starting to run together. 😀
I know that feeling! its not good
antonio.collins (1/16/2008)
still, how does your query ensure at least one sale in each month?
Good point... Took a slight gamble there as the request wasn't too clear
Wait and see what the original poster comes back with
January 16, 2008 at 10:30 am
antonio.collins (1/16/2008)
re: Samuel Vellathat query does not seem to only consider sellers who have sold at least 3 items in each of the past 3 months. have i misinterpreted the query?
Sorry, I should provide a clear question.
I only want to see the sellers who have sold at least ONE item in each of the past 3 month.
I actually need list of sellers and count of sellers who have sold at least ONE item in each of the past 3 month, but once I get the count, I will know how to get a list.
I am running SQL statement in Teradata to retrieve datawarehouse data, and I don't see the stored proc will work in Teradata, so please provide T-SQL only.
Thanks.
January 16, 2008 at 8:53 pm
Good luck with that... does TeraData use the same date functions as T-SQL?
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply