|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sql query - splist of metro cities between a specific time frame and group by time frame This is working but I also want it to return the list of metro cities even if there are no leads produced during that specific time frame. For instance when there are leads between 6/1/2006 - 6/30/2006, results will be Date Num of leads Metro Cities 6/1/2006 20000 Altanta 6/1/2006 35000 San Diego If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because there are no leads yet the results are 0. But the user want it to still list in the result set Date Num of leads Metro Cities 7/1/2006 0 Atlanta 7/1/2006 0 San Diego So forth, does anyone know how to return a result set even if the value is 0? SAM wrote:
Show quote > I created a stored procedure to return a number of leads for customers, the Please provide DDL. You're going to have to SELECT FROM <list of > list of metro cities between a specific time frame and group by time frame > > This is working but I also want it to return the list of metro cities even > if there are no leads produced during that specific time frame. For instance > when there are leads between 6/1/2006 - 6/30/2006, results will be > > Date Num of leads Metro Cities > 6/1/2006 20000 Altanta > 6/1/2006 35000 San Diego > > If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because there > are no leads yet the results are 0. But the user want it to still list in > the result set > > Date Num of leads Metro Cities > 7/1/2006 0 Atlanta > 7/1/2006 0 San Diego > > So forth, does anyone know how to return a result set even if the value is 0? cities> LEFT JOIN <leads table>, but without DDL, we can't offer much more than that. The parameters that I am submitting to the query to test:
I get results for the below timeframe but if I put in a time frame 7/1/6 and 7/31/6, since there are no leads for this time, my results are 0. DECLARE @startdate datetime SET @startdate = CONVERT(DATETIME, '6/1/6') DECLARE @enddate datetime SET @enddate = CONVERT(DATETIME, '6/30/6') DECLARE @metroidlist varchar(8000) SET @metroidlist = 'ALL' --SET @metroidlist = '82268A5A-E5D2-4F8C-997F-D0D7E1DED4FC,B2C0692E-1B8F-4D19-909D-4E0124C8B0FA,58F869E1-F668-4916-8A23-FF2E1CC1A17B' DECLARE @groupingtimeframe varchar(10) SET @groupingtimeframe = 'monthly' /********************************************************/ IF @metroidlist = 'ALL' BEGIN SELECT CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END as 'DATE', Count(*) as 'Leads', m.metroid, m.metroname FROM db.rcadreplies as r JOIN db.rcads as a on a.adid = r.adid JOIN db.rctransactions as t on t.adid = a.adid JOIN db.rcproducts AS p ON p.productid = t.productid AND (p.producttype = 'Ad' OR p.producttype = 'Ad Upgrade') JOIN db.rczipcode AS z ON z.zipcode = a.zip JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, @enddate) GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END, m.metroid, m.metroname ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END ASC END ELSE BEGIN CREATE TABLE #Metrolist (metroid varchar(100)) INSERT #Metrolist SELECT * FROM db.rcudf_ConvertMetroTabletoList(@metroidlist) BEGIN TRAN SELECT CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END as 'DATE', Count(*) as 'Leads', m.metroname, m.metroid FROM db.rcadreplies as r JOIN db.rcads as a on a.adid = r.adid JOIN db.rctransactions as t on t.adid = a.adid JOIN db.rcproducts AS p ON p.productid = t.productid AND (p.producttype = 'Ad' OR p.producttype = 'Ad Upgrade') JOIN db.rczipcode AS z ON z.zipcode = a.zip JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid JOIN #Metrolist as ml on ml.metroid = m.metroid WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, @enddate) GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END, m.metroname, m.metroid ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END ASC END GO Show quote "Tracy McKibben" wrote: > SAM wrote: > > I created a stored procedure to return a number of leads for customers, the > > list of metro cities between a specific time frame and group by time frame > > > > This is working but I also want it to return the list of metro cities even > > if there are no leads produced during that specific time frame. For instance > > when there are leads between 6/1/2006 - 6/30/2006, results will be > > > > Date Num of leads Metro Cities > > 6/1/2006 20000 Altanta > > 6/1/2006 35000 San Diego > > > > If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because there > > are no leads yet the results are 0. But the user want it to still list in > > the result set > > > > Date Num of leads Metro Cities > > 7/1/2006 0 Atlanta > > 7/1/2006 0 San Diego > > > > So forth, does anyone know how to return a result set even if the value is 0? > > Please provide DDL. You're going to have to SELECT FROM <list of > cities> LEFT JOIN <leads table>, but without DDL, we can't offer much > more than that. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > You are trying to display data that doens't exist in your system. The only
way to make work is to enlist the help of some form of tool that will 'manufacture' the mission data. One method is a Calendar Table. See previous post. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "SAM" <S**@discussions.microsoft.com> wrote in message news:D3A37A0F-47F8-4E0F-AC77-9E0387C0EDD8@microsoft.com... > The parameters that I am submitting to the query to test: > I get results for the below timeframe but if I put in a time frame 7/1/6 > and > 7/31/6, since there are no leads for this time, my results are 0. > DECLARE @startdate datetime > SET @startdate = CONVERT(DATETIME, '6/1/6') > DECLARE @enddate datetime > SET @enddate = CONVERT(DATETIME, '6/30/6') > DECLARE @metroidlist varchar(8000) > SET @metroidlist = 'ALL' > --SET @metroidlist = > '82268A5A-E5D2-4F8C-997F-D0D7E1DED4FC,B2C0692E-1B8F-4D19-909D-4E0124C8B0FA,58F869E1-F668-4916-8A23-FF2E1CC1A17B' > DECLARE @groupingtimeframe varchar(10) > SET @groupingtimeframe = 'monthly' > /********************************************************/ > IF @metroidlist = 'ALL' > BEGIN > > SELECT > CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, > 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END as 'DATE', > Count(*) as 'Leads', > m.metroid, m.metroname > > FROM db.rcadreplies as r > JOIN db.rcads as a on a.adid = r.adid > JOIN db.rctransactions as t on t.adid = a.adid > JOIN db.rcproducts AS p ON p.productid = t.productid > AND (p.producttype = 'Ad' OR p.producttype = 'Ad Upgrade') > JOIN db.rczipcode AS z ON z.zipcode = a.zip > JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid > JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid > > WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, > @enddate) > > GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END, m.metroid, m.metroname > ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END ASC > > END > ELSE > BEGIN > CREATE TABLE #Metrolist (metroid varchar(100)) > > INSERT #Metrolist > SELECT * FROM db.rcudf_ConvertMetroTabletoList(@metroidlist) > > BEGIN TRAN > > SELECT > > CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, > 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END as 'DATE', > Count(*) as 'Leads', > m.metroname, m.metroid > FROM db.rcadreplies as r > JOIN db.rcads as a on a.adid = r.adid > JOIN db.rctransactions as t on t.adid = a.adid > JOIN db.rcproducts AS p ON p.productid = t.productid > AND (p.producttype = 'Ad' OR p.producttype = 'Ad Upgrade') > JOIN db.rczipcode AS z ON z.zipcode = a.zip > JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid > JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid > JOIN #Metrolist as ml on ml.metroid = m.metroid > > WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, > @enddate) > > GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END, m.metroname, m.metroid > > ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END ASC > END > GO > > > "Tracy McKibben" wrote: > >> SAM wrote: >> > I created a stored procedure to return a number of leads for customers, >> > the >> > list of metro cities between a specific time frame and group by time >> > frame >> > >> > This is working but I also want it to return the list of metro cities >> > even >> > if there are no leads produced during that specific time frame. For >> > instance >> > when there are leads between 6/1/2006 - 6/30/2006, results will be >> > >> > Date Num of leads Metro Cities >> > 6/1/2006 20000 Altanta >> > 6/1/2006 35000 San Diego >> > >> > If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because >> > there >> > are no leads yet the results are 0. But the user want it to still list >> > in >> > the result set >> > >> > Date Num of leads Metro Cities >> > 7/1/2006 0 Atlanta >> > 7/1/2006 0 San Diego >> > >> > So forth, does anyone know how to return a result set even if the value >> > is 0? >> >> Please provide DDL. You're going to have to SELECT FROM <list of >> cities> LEFT JOIN <leads table>, but without DDL, we can't offer much >> more than that. >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com >> You may want to check out the use of a Calendar table.
http://www.aspfaq.com/show.asp?id=2519 -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "SAM" <S**@discussions.microsoft.com> wrote in message news:114E9691-E234-43C1-B221-9DDA7F379EC5@microsoft.com... >I created a stored procedure to return a number of leads for customers, the > list of metro cities between a specific time frame and group by time frame > > This is working but I also want it to return the list of metro cities even > if there are no leads produced during that specific time frame. For > instance > when there are leads between 6/1/2006 - 6/30/2006, results will be > > Date Num of leads Metro Cities > 6/1/2006 20000 Altanta > 6/1/2006 35000 San Diego > > If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because there > are no leads yet the results are 0. But the user want it to still list in > the result set > > Date Num of leads Metro Cities > 7/1/2006 0 Atlanta > 7/1/2006 0 San Diego > > So forth, does anyone know how to return a result set even if the value is > 0? I'm little confused how to use what you referred in my stored procedure and
how to resolve my issue. Show quote "Arnie Rowland" wrote: > You may want to check out the use of a Calendar table. > > http://www.aspfaq.com/show.asp?id=2519 > -- > Arnie Rowland* > "To be successful, your heart must accompany your knowledge." > > > > > "SAM" <S**@discussions.microsoft.com> wrote in message > news:114E9691-E234-43C1-B221-9DDA7F379EC5@microsoft.com... > >I created a stored procedure to return a number of leads for customers, the > > list of metro cities between a specific time frame and group by time frame > > > > This is working but I also want it to return the list of metro cities even > > if there are no leads produced during that specific time frame. For > > instance > > when there are leads between 6/1/2006 - 6/30/2006, results will be > > > > Date Num of leads Metro Cities > > 6/1/2006 20000 Altanta > > 6/1/2006 35000 San Diego > > > > If I entered another time frame, i.e. 7/1/2006 - 7/31/2006, because there > > are no leads yet the results are 0. But the user want it to still list in > > the result set > > > > Date Num of leads Metro Cities > > 7/1/2006 0 Atlanta > > 7/1/2006 0 San Diego > > > > So forth, does anyone know how to return a result set even if the value is > > 0? > > > As Tracy and Arnie suggested you need to outer join your data to a calendar
table. This example refers to a different issue but maybe it will help you understand the issue: http://milambda.blogspot.com/2006/06/filling-gaps.html ML --- http://milambda.blogspot.com/ SAM (S**@discussions.microsoft.com) writes:
> I'm little confused how to use what you referred in my stored procedure By having a table of dates, you could change the query to:> and how to resolve my issue. FROM dates d LEFT JOIN (db.rcadreplies as r JOIN db.rcads as a on a.adid = r.adid JOIN db.rctransactions as t on t.adid = a.adid JOIN db.rcproducts AS p ON p.productid = t.productid AND (p.producttype = 'Ad' OR p.producttype = 'Ad Upgrade') JOIN db.rczipcode AS z ON z.zipcode = a.zip JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid) ON d.date = r.datecreated WHERE d.datecreated >= @startdate AND d.datecreated < DATEADD(dd, 1, @enddate) Either you make this a permanent table, or a temp table you fill up from the input parameters in a little loop.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx SAM wrote:
> I'm little confused how to use what you referred in my stored procedure and Your problem is that you want a value returned for dates and locations > how to resolve my issue. > that you don't actually have data for in your tables. You can't make up data that doesn't exist, therefore you have to ensure that you have a source for ALL dates, and a source for ALL locations. You will then have to write a query similar to this: SELECT datestable.date_value, locationstable.location, SUM(leadstable.leads_count) AS leads_counts FROM datestable CROSS JOIN locationstable INNER JOIN leadstable ON datestable.date_value = leadstable.date_of_lead AND locationstable.location = leadstable.location WHERE datestable.date_value BETWEEN @StartDate AND @EndDate Does that help? Thanks everyone. I was out on Friday. I am working on your suggestions now. I
created the table on Thursday and populated based on the articles you guys provided and now I am going to try to test it out with my query. Show quote "Tracy McKibben" wrote: > SAM wrote: > > I'm little confused how to use what you referred in my stored procedure and > > how to resolve my issue. > > > > Your problem is that you want a value returned for dates and locations > that you don't actually have data for in your tables. You can't make up > data that doesn't exist, therefore you have to ensure that you have a > source for ALL dates, and a source for ALL locations. You will then > have to write a query similar to this: > > SELECT > datestable.date_value, > locationstable.location, > SUM(leadstable.leads_count) AS leads_counts > FROM datestable > CROSS JOIN locationstable > INNER JOIN leadstable > ON datestable.date_value = leadstable.date_of_lead > AND locationstable.location = leadstable.location > WHERE datestable.date_value BETWEEN @StartDate AND @EndDate > > Does that help? > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > Ok, I tested with but I am not exactly the results I was hoping for.
I may need to have a separate query within the sp to handle 0 results for calendar dates. Curerntly, when I add the calendar table to my query my results differ when there are results for a given timeframe. I don't want that. I only want it to reflect a different result set when there is no data for a given timeframe. So I still need to work on this logic to meet the other user requirements to list the dates and city names even when there is no leads for that particular city. Show quote "Tracy McKibben" wrote: > SAM wrote: > > I'm little confused how to use what you referred in my stored procedure and > > how to resolve my issue. > > > > Your problem is that you want a value returned for dates and locations > that you don't actually have data for in your tables. You can't make up > data that doesn't exist, therefore you have to ensure that you have a > source for ALL dates, and a source for ALL locations. You will then > have to write a query similar to this: > > SELECT > datestable.date_value, > locationstable.location, > SUM(leadstable.leads_count) AS leads_counts > FROM datestable > CROSS JOIN locationstable > INNER JOIN leadstable > ON datestable.date_value = leadstable.date_of_lead > AND locationstable.location = leadstable.location > WHERE datestable.date_value BETWEEN @StartDate AND @EndDate > > Does that help? > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > SAM wrote:
> Ok, I tested with but I am not exactly the results I was hoping for. I'm not sure I understand... Your resultset should be based on the > > I may need to have a separate query within the sp to handle 0 results for > calendar dates. Curerntly, when I add the calendar table to my query my > results differ when there are results for a given timeframe. I don't want > that. I only want it to reflect a different result set when there is no data > for a given timeframe. So I still need to work on this logic to meet the > other user requirements to list the dates and city names even when there is > no leads for that particular city. > intersection of dates and locations, and oh, by the way, you may or may not have X number of leads attached to each intersection point. In other words, you first have to establish the resultset consisting of date + location, thus an inner join between the dates table and locations. You then need to take that resultset and look at leads, summing up how many leads occur for each date/location pair. You may not have any leads for a given pair, thus you need a LEFT join to the leads table. Can you post the query you're using? SAM (S**@discussions.microsoft.com) writes:
> I may need to have a separate query within the sp to handle 0 results What actual query did you use? And what result do you get?> for calendar dates. Curerntly, when I add the calendar table to my query > my results differ when there are results for a given timeframe. I don't > want that. I only want it to reflect a different result set when there > is no data for a given timeframe. So I still need to work on this logic > to meet the other user requirements to list the dates and city names > even when there is no leads for that particular city. When looking at Tracy´s outline, I think there is an error: >> SELECT The INNER JOIN should be a LEFT JOIN, I think. Else you would not >> datestable.date_value, >> locationstable.location, >> SUM(leadstable.leads_count) AS leads_counts >> FROM datestable >> CROSS JOIN locationstable >> INNER JOIN leadstable >> ON datestable.date_value = leadstable.date_of_lead >> AND locationstable.location = leadstable.location >> WHERE datestable.date_value BETWEEN @StartDate AND @EndDate get the zeroes. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland Sommarskog wrote:
Show quote > Indeed, that was my intent. I didn't even notice the typo when I > What actual query did you use? And what result do you get? > > When looking at Tracy´s outline, I think there is an error: > >>> SELECT >>> datestable.date_value, >>> locationstable.location, >>> SUM(leadstable.leads_count) AS leads_counts >>> FROM datestable >>> CROSS JOIN locationstable >>> INNER JOIN leadstable >>> ON datestable.date_value = leadstable.date_of_lead >>> AND locationstable.location = leadstable.location >>> WHERE datestable.date_value BETWEEN @StartDate AND @EndDate > > The INNER JOIN should be a LEFT JOIN, I think. Else you would not > get the zeroes. > > replied to him earlier today. This is the original query without the calendar table:
DECLARE @startdate datetime SET @startdate = CONVERT(DATETIME, '6/1/6') DECLARE @enddate datetime SET @enddate = CONVERT(DATETIME, '6/30/6') DECLARE @metroidlist varchar(8000) SET @metroidlist = 'ALL' DECLARE @groupingtimeframe varchar(10) SET @groupingtimeframe = 'monthly' IF @metroidlist = 'ALL' BEGIN SELECT CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END as 'DATE', Count(*) as 'Leads' FROM db.rcadreplies as r JOIN db.rcads as a on a.adid = r.adid JOIN db.rczipcode AS z ON z.zipcode = a.zip JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, @enddate) GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, r.datecreated),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,r.datecreated),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,r.datecreated), 0) END ASC New code with the calendar table: IF @metroidlist = 'ALL' BEGIN SELECT CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, 0,c.dt) ,0) WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, c.dt),-1) WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, 0,c.dt),0) WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, 0,c.dt), 0) END as 'DATE', Count(*) as 'Leads' FROM dbo.Calendar c LEFT JOIN (db.rcadreplies as r JOIN db.rcads as a on a.adid = r.adid JOIN db.rczipcode AS z ON z.zipcode = a.zip JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid) ON c.dt = r.datecreated WHERE c.dt >= @startdate AND c.dt < DATEADD(dd, 1, @enddate) Show quote "Tracy McKibben" wrote: > Erland Sommarskog wrote: > > > > What actual query did you use? And what result do you get? > > > > When looking at Tracy´s outline, I think there is an error: > > > >>> SELECT > >>> datestable.date_value, > >>> locationstable.location, > >>> SUM(leadstable.leads_count) AS leads_counts > >>> FROM datestable > >>> CROSS JOIN locationstable > >>> INNER JOIN leadstable > >>> ON datestable.date_value = leadstable.date_of_lead > >>> AND locationstable.location = leadstable.location > >>> WHERE datestable.date_value BETWEEN @StartDate AND @EndDate > > > > The INNER JOIN should be a LEFT JOIN, I think. Else you would not > > get the zeroes. > > > > > > Indeed, that was my intent. I didn't even notice the typo when I > replied to him earlier today. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > First query I received for the month of June
2006/6/1 - 47632 leads The new query for the same month I received 30 leads I was wondering if I need to create separate criteria to meet the timeframe where there are no leads. Show quote "SAM" wrote: > This is the original query without the calendar table: > > DECLARE @startdate datetime > SET @startdate = CONVERT(DATETIME, '6/1/6') > DECLARE @enddate datetime > SET @enddate = CONVERT(DATETIME, '6/30/6') > DECLARE @metroidlist varchar(8000) > SET @metroidlist = 'ALL' > DECLARE @groupingtimeframe varchar(10) > SET @groupingtimeframe = 'monthly' > > IF @metroidlist = 'ALL' > BEGIN > > SELECT > CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, > 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END as 'DATE', > Count(*) as 'Leads' > > > FROM db.rcadreplies as r > JOIN db.rcads as a on a.adid = r.adid > JOIN db.rczipcode AS z ON z.zipcode = a.zip > JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid > JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid > > WHERE r.datecreated >= @startdate AND r.datecreated < DATEADD(dd, 1, > @enddate) > > GROUP BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END > ORDER BY CASE WHEN @groupingtimeframe = 'daily' THEN > DATEADD(dd,DATEDIFF(day, 0,r.datecreated) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > r.datecreated),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,r.datecreated),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,r.datecreated), 0) > END ASC > > > New code with the calendar table: > > IF @metroidlist = 'ALL' > BEGIN > > SELECT > CASE WHEN @groupingtimeframe = 'daily' THEN DATEADD(dd,DATEDIFF(day, > 0,c.dt) ,0) > WHEN @groupingtimeframe = 'weekly' THEN DATEADD(ww, DATEDIFF(week,0, > c.dt),-1) > WHEN @groupingtimeframe = 'monthly' THEN DATEADD(mm,DATEDIFF(month, > 0,c.dt),0) > WHEN @groupingtimeframe = 'yearly' THEN DATEADD(yy,DATEDIFF(year, > 0,c.dt), 0) > END as 'DATE', > Count(*) as 'Leads' > > > FROM dbo.Calendar c > LEFT JOIN (db.rcadreplies as r > JOIN db.rcads as a on a.adid = r.adid > JOIN db.rczipcode AS z ON z.zipcode = a.zip > JOIN db.rcarea AS ar ON ar.areaid = z.primaryareaid > JOIN db.rcmetro AS m ON m.metroid = ar.primarymetroid) > ON c.dt = r.datecreated > WHERE c.dt >= @startdate AND c.dt < DATEADD(dd, 1, @enddate) > > > "Tracy McKibben" wrote: > > > Erland Sommarskog wrote: > > > > > > What actual query did you use? And what result do you get? > > > > > > When looking at Tracy´s outline, I think there is an error: > > > > > >>> SELECT > > >>> datestable.date_value, > > >>> locationstable.location, > > >>> SUM(leadstable.leads_count) AS leads_counts > > >>> FROM datestable > > >>> CROSS JOIN locationstable > > >>> INNER JOIN leadstable > > >>> ON datestable.date_value = leadstable.date_of_lead > > >>> AND locationstable.location = leadstable.location > > >>> WHERE datestable.date_value BETWEEN @StartDate AND @EndDate > > > > > > The INNER JOIN should be a LEFT JOIN, I think. Else you would not > > > get the zeroes. > > > > > > > > > > Indeed, that was my intent. I didn't even notice the typo when I > > replied to him earlier today. > > > > > > -- > > Tracy McKibben > > MCDBA > > http://www.realsqlguy.com > > SAM (S**@discussions.microsoft.com) writes:
> First query I received for the month of June Looks like you are missing some rows. You could try to remove COUNT(*)> > 2006/6/1 - 47632 leads > > The new query for the same month I received > 30 leads and GROUP BY to get an idea of what is going on. > I was wondering if I need to create separate criteria to meet the Well, you have:> timeframe where there are no leads. >> Count(*) as 'Leads' This should be COUNT(r.datecreated) or somesuch. COUNT(*) counts allrows, COUNT(r.datecreated) only counts the rows where r.datecreated is non-NULL. That is, you don't include the rows for the dates where there are no leads. >> New code with the calendar table: I don't see a GROUP BY in this query?-- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx It is the same group by, I just didn't copy the code here. The only
difference is the join tables including the calendar table. Show quote "Erland Sommarskog" wrote: > SAM (S**@discussions.microsoft.com) writes: > > First query I received for the month of June > > > > 2006/6/1 - 47632 leads > > > > The new query for the same month I received > > 30 leads > > Looks like you are missing some rows. You could try to remove COUNT(*) > and GROUP BY to get an idea of what is going on. > > > I was wondering if I need to create separate criteria to meet the > > timeframe where there are no leads. > > Well, you have: > > >> Count(*) as 'Leads' > > This should be COUNT(r.datecreated) or somesuch. COUNT(*) counts all > rows, COUNT(r.datecreated) only counts the rows where r.datecreated is > non-NULL. That is, you don't include the rows for the dates where > there are no leads. > > >> New code with the calendar table: > > I don't see a GROUP BY in this query? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > I think I know why I am only getting 30 rows back when I join in the Calendar
table. The r.datecreated field from the rcadreplies table is logging the date and time. However, in the calendar table that I created only has the date with time being 2006-06-01 00:00:00 compared to 2006-06-01 00:00:38.000; therefore, it will only return rows where the two tables are equal to 2006-06-01 00:00:00. So I have to determine either how to ignore the time or factor the time in the calendar table. Show quote "SAM" wrote: > It is the same group by, I just didn't copy the code here. The only > difference is the join tables including the calendar table. > > "Erland Sommarskog" wrote: > > > SAM (S**@discussions.microsoft.com) writes: > > > First query I received for the month of June > > > > > > 2006/6/1 - 47632 leads > > > > > > The new query for the same month I received > > > 30 leads > > > > Looks like you are missing some rows. You could try to remove COUNT(*) > > and GROUP BY to get an idea of what is going on. > > > > > I was wondering if I need to create separate criteria to meet the > > > timeframe where there are no leads. > > > > Well, you have: > > > > >> Count(*) as 'Leads' > > > > This should be COUNT(r.datecreated) or somesuch. COUNT(*) counts all > > rows, COUNT(r.datecreated) only counts the rows where r.datecreated is > > non-NULL. That is, you don't include the rows for the dates where > > there are no leads. > > > > >> New code with the calendar table: > > > > I don't see a GROUP BY in this query? > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > How about adding a computed column to the table based on this popular
"time-cutting" function: select convert(datetime, convert(char(10), <your datetime column>, 112), 112) This way you could also index the column, rather than use the function in the join clause which would most probably lead to a table scan. ML --- http://milambda.blogspot.com/ SAM wrote:
Show quote > I think I know why I am only getting 30 rows back when I join in the Calendar http://realsqlguy.com/twiki/bin/view/RealSQLGuy/TipsForWorkingWithDateTimeValues> table. > > The r.datecreated field from the rcadreplies table is logging the date and > time. However, in the calendar table that I created only has the date with > time being 2006-06-01 00:00:00 compared to 2006-06-01 00:00:38.000; > therefore, it will only return rows where the two tables are equal to > 2006-06-01 00:00:00. > > So I have to determine either how to ignore the time or factor the time in > the calendar table. > > > > "SAM" wrote: > >> It is the same group by, I just didn't copy the code here. The only >> difference is the join tables including the calendar table. >> >> "Erland Sommarskog" wrote: >> >>> SAM (S**@discussions.microsoft.com) writes: >>>> First query I received for the month of June >>>> >>>> 2006/6/1 - 47632 leads >>>> >>>> The new query for the same month I received >>>> 30 leads >>> Looks like you are missing some rows. You could try to remove COUNT(*) >>> and GROUP BY to get an idea of what is going on. >>> >>>> I was wondering if I need to create separate criteria to meet the >>>> timeframe where there are no leads. >>> Well, you have: >>> >>>>> Count(*) as 'Leads' >>> This should be COUNT(r.datecreated) or somesuch. COUNT(*) counts all >>> rows, COUNT(r.datecreated) only counts the rows where r.datecreated is >>> non-NULL. That is, you don't include the rows for the dates where >>> there are no leads. >>> >>>>> New code with the calendar table: >>> I don't see a GROUP BY in this query? >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> SAM (S**@discussions.microsoft.com) writes:
> I think I know why I am only getting 30 rows back when I join in the Simplest is to change the ON bit:> Calendar table. > > The r.datecreated field from the rcadreplies table is logging the date and > time. However, in the calendar table that I created only has the date with > time being 2006-06-01 00:00:00 compared to 2006-06-01 00:00:38.000; > therefore, it will only return rows where the two tables are equal to > 2006-06-01 00:00:00. > > So I have to determine either how to ignore the time or factor the time in > the calendar table. ON c.dt = r.datecreated to ON r.datecreated >= c.dt AND r.datecreated < dateadd(DAY, 1, c.dt) ML's suggestion of adding a index computed column which holds the date only, may well be worth pursuing. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics