Post reply

filter two table by date arrange

  • November 1, 2011 at 4:46 am

    #262182

    Hi. i have two table. My tables are:

    ProductDetail table: ProductDetailId,ProductId,Title,Description

    HitsTable: HitsId, ProductId, TimeCreated,Hit

    these table have a relation on ProductId column.my aim is finding the products that have most hits in giving date arrange.

    i wrote a query like tihs. but i can't make filtering according to date. My query is:

    select top 10 p.Title,p.ProductId,COUNT(*) as counts from ProductDetails p,Hits h

    where p.ProductId=h.ItemId

    group by p.Title,p.ProductId

    order by counts desc

    i list the top 10 product that have most hitting. How can i make the filter aoccording to date range.

    Thanks in advance.

  • November 1, 2011 at 5:12 am

    #1401515

    No DDL in your question ,so untested code:

    declare @dateFrom datetime;

    declare @dateTo datetime;

    select @dateTo = getdate(),

    @dateFrom = dateadd(day, -14, @dateTo);

    select top 10 p.*, c.cnt

    from ProductDetails p

    inner join (

    select h.ItemId, count(*) as cnt

    from Hits h

    where h.TimeCreated >= @dateFrom

    and h.TimeCreated < @dateTo

    group by h.ItemId

    ) c on (c.ItemID = p.ItemID)

    order by c.cnt desc


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • November 1, 2011 at 7:20 am

    #1401596

    thank you so much. it worked.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply