SQL Server Performance

Outer Joins not Working?

Discussion in 'General Developer Questions' started by mtm81, Sep 15, 2006.

  1. mtm81 New Member

    Hi,
    I have the following slightly complicated query:

    SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, SUM(db:confused:rderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
    db:confused:rders_Retail.OrderDate) AS SalesWeek, YEAR(db:confused:rders_Retail.OrderDate) AS SalesYear
    FROM db:confused:rders_Retail RIGHT OUTER JOIN
    dbo.Calendar ON CONVERT(datetime, CONVERT(Nvarchar, db:confused:rders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(Nvarchar,
    dbo.Calendar.dt, 102), 102) RIGHT OUTER JOIN
    db:confused:rderDetails_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID RIGHT OUTER JOIN
    dbo.Products ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    WHERE (db:confused:rders_Retail.account = @Account) AND (db:confused:rders_Retail.OrderStatus <> 'Deleted') AND (db:confused:rders_Retail.PayStatus <> 'Pending') AND
    (db:confused:rders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, db:confused:rders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
    db:confused:rders_Retail.OrderDate, @EndDate) >= 0)
    GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(db:confused:rders_Retail.OrderDate), DATEPART(wk,
    db:confused:rders_Retail.OrderDate)


    Basically. I bring in a products tbl, outer join that with an orders_detail tbl, then join that to a orders tbl and finally join that to calendar tbl.

    What SHOULD happen is that the query should find all orders in a particular date range and return the qty's purchased of the individual products , grouped by weeks of the year.


    This actually works no problem.

    However where there were no items purchased for a particular week for a particular prod, that simply isn't show, where I want it to show with a qty of zero.. example:

    Currently I get:
    Prod | Qty | Week

    1 23 1

    1 12 2

    2 10 3

    3 22 1

    3 15 2

    3 12 3

    3 4 4

    Although this looks fine - what I actually need is:

    1 23 1

    1 12 2

    1 0 3

    1 0 3

    2 0 1

    2 0 2

    2 10 3

    2 0 4

    3 22 1

    3 15 2

    3 12 3

    3 4 4

    Does that make sense?


    I thought by outer joining both the products tbl and also that calendar tbl I would then get the results - but it appears not so...

    Any ideas?

    James
  2. Chappy New Member

    By using Table1 RIGHT OUTER JOIN Table2 (im simplyfing it to two tables), you are telling sql server than you only want rows in the resultset, where a record exists in Table2.

    Rows in Table1 which do not have a joined row in Table2 will not appear in the resultset.

    You could try LEFT OUTER JOIN but I dont understand the precedence of your tables, so youd need to think about this.
    Alternatively if you want all rows in both tables regardless if they dont have a corresponding join not, FULL OUTER JOIN might be useful

  3. mtm81 New Member

    Hi,
    Thanks for your reply.
    I've tried changing the joins around without success..

    With regrds to my tbls..
    The products tbl simply holds an ID, a Brand and Name.

    The Orders_Details hold the ID, and the qty bought and the ORDERID,

    The Orders tbl hold the OrderID, a load of other data not used in this query and the Order_date

    And finally the calendar tbl holds individual dates..


    I guess what SHOULD happen (I'm not starting to think I may need more than one query, but I'm trying to avoid that for performance).. is that:

    1) - The calendary tbl gets the dates and works out the SalesYear and SalesWeek columns.. these should appear regardless of whether there are order in here or not.

    the orders tbl then checks those dates and find the records within that period.
    It then grabs all the order_details so I then have a large list of the products ID's and WQty's bought in that period.

    This is then matched the products tbl to get the name and brand.


    However by doing the outer join I was hoping to get all dates between the user selected dates.. and then all the products.. and of course if there has been any purchases for a particular product in that time frame on whichever week or year, that field is populate as such...


    I know it's pretty complex but I'm an ASP coder at heart and doing it in ASP would really need several nested queries to get the same result... which I'm trying to avoid...


    Any ideas would be great!

  4. Adriaan New Member

    You need the query to start from the calendar, otherwise you simply won't see the periods where there are no orders. I always use LEFT JOIN, and never OUTER, to keep things simple.

    You have to add the CONVERT function around the dates in the JOIN only if the data actually records the time of day: if all rows have 00:00:00.000 for the time of day, you can drop the CONVERT function. If you can drop CONVERT, then the response time may improve as well. If not, then I would convert to VARCHAR - there's no need to complicate things with NVARCHAR.

    FROM dbo.Calendar C
    LEFT JOIN db:confused:rders_Retail O_R ON c.Dt = O_R.OrderDate
    LEFT JOIN db:confused:rderDetails_Retail OD_R ON O_R.OrderID = OD_R.OrderID
    LEFT JOIN dbo.Products P ON OD_R.ProductID = P.ProductID
  5. Adriaan New Member

    ... uhm ... I notice that you have embedded CONVERT. Are you joining columns that are not of the DATETIME data type?
  6. mtm81 New Member

    Hi,
    ok if I go your query eith just LEFT JOINS it doesn't like just the word LEFT - it's wants OUTER LEFT for example.

    with regards to the convert..
    The DAtetime field in the calendar tbl just holds a date value, whereas the order tbl order_date holds the date and in most cases (some early records don't), the time also..
    therefore I needed to do a convert to get rid of the time from the order_table...

    James
  7. mtm81 New Member

    ok here's my current query as requested.
    This seems to be doing a bit more, but the problem is now that I get a load of nulls at the top for the product name and brand, then a load of results.. but still no "zero" values for the weeks where nothing purchased...

  8. mtm81 New Member

    oops- here you go:

    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, SUM(db:confused:rderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
    db:confused:rders_Retail.OrderDate) AS SalesWeek, YEAR(db:confused:rders_Retail.OrderDate) AS SalesYear
    FROM dbo.Calendar LEFT JOIN
    db:confused:rders_Retail ON CONVERT(datetime, CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(nvarchar,
    dbo.Calendar.dt, 102), 102) LEFT JOIN
    db:confused:rderDetails_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID LEFT JOIN
    dbo.Products ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(db:confused:rders_Retail.OrderDate), DATEPART(wk, db:confused:rders_Retail.OrderDate)
  9. Adriaan New Member

    Perhaps this ...

    SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0))
  10. mtm81 New Member

    Thanks for your help by the way!

    Nope - still not working.

    basically it's returning the records ok.. but only up to the point that they exist..

    so still no zero values in that qty column - it's as if, if there wasn't a sale of that product in that salesweek, then it's being disregarded instead what I would expect to happen which would be to get a NULL value but still show the product...

    odd.. very odd..

    James
  11. mtm81 New Member

    ok I've done a bit more playing and can't see what id going wrong.

    It now produces a load of blank records top and bottom of results, and loads of the accurate data in the middle.

    I'm starting to think this just can't be done..

    In ASP I'd check the date (year and week), open a RS checking the qty's bought at the date, build my code I needed... shut the rs and move to the next date.. but seeing as the two dates select could be many years apart, it means I could be opening and closing recordset hundreds of times which is not good!
  12. Adriaan New Member

    Are you working in Enterprise Manager? In that case use LEFT OUTER JOIN, not OUTER LEFT JOIN.

    The convert only has to be done to VARCHAR(10) - the convert to DATETIME on top of that is a waste of time.

    I guess you need to add a date range criteria before the GROUP BY:

    WHERE dbo.Calender.dt BETWEEN (SELECT MIN(OrderDate) FROM db:confused:rders_Retail) AND (SELECT MAX(OrderDate) FROM db:confused:rders_Retail)
  13. mtm81 New Member

    Hi (again!!),
    Yeah I know about that date range thing criteria, but surely, if that WHERE clause isn't there it should see everything (i.e all the dates that are in the calendar tbl) but it isn't.

    I am already using the LEFT OUTER JOIN, but that made no difference..

    I'll see about getting rid of that convert datetime, but I don't think that's the problem.. it's more something to do with showing ALL the products.. which it currently doesn't.. it only shows those that have been purchased "THAT WEEK".. where as I need all for that week, and then what QTY where bought (eventually I'll trim which products are selected via a WHERE clause, but I wanted it working correctly before I messed with that!!

    Thanks

    James
  14. Chappy New Member

    Perhaps the problem isnt in the joins but in the WHERE clause ?
  15. mtm81 New Member

    that's what I'm saying - without the wHERE clause, it should return ALL DATES and then ALL PRODUCTS for each week and their qty's purchased for that WEEK.

    but it doesn't...

    James
  16. Adriaan New Member

    Ah - "showing ALL the products" ...

    The main query should be on Products, with a LEFT JOIN on a derived table, which in turn is the query that we already have ... including (once more) Products.
  17. cmdr_skywalker New Member

    I am not sure if this is what you need. I tried to simplify the query so the idea is communicated clearly and you may confirm if this what you want. Then we can workout with the details.


    quote:
    1) - The calendary tbl gets the dates and works out the SalesYear and SalesWeek columns.. these should appear regardless of whether there are order in here or not.

    I used the ISNULL which was communicated earlier. You can also use the WITH ROLLUP or WITH CUBE argument in GROUP BY clause. See BOL for more info on these.



    if (select object_id('dbo.calendar')) is not null drop table dbo.calendar
    go
    create table dbo.calendar(
    cal_id int identity(1,1) not null,
    yearmonth varchar(6),
    day_dt datetime
    )
    go

    if (select object_id('dbo.sales')) is not null drop table dbo.sales
    go

    create table dbo.sales(
    sales_id int identity(1,1) not null,
    sales_dt datetime,
    sales_amt numeric(7,3)
    )
    go

    --populate calendar
    insert into dbo.calendar(
    yearmonth,
    day_dt
    ) values(
    '200601',
    '2006-01-01'
    )

    insert into dbo.calendar(
    yearmonth,
    day_dt
    ) values(
    '200602',
    '2006-02-01'
    )
    go

    --populate sales
    insert into dbo.sales(
    sales_dt,
    sales_amt
    )values(
    '2006-01-01',
    20
    )

    insert into dbo.sales(
    sales_dt,
    sales_amt
    )values(
    '2006-01-01',
    30
    )
    go

    select
    c.yearmonth,
    sum(isnull(s.sales_amt,0))
    from
    dbo.calendar c left join dbo.sales s on (
    c.day_dt = s.sales_dt
    )
    group by c.yearmonth
    go

    if (select object_id('dbo.calendar')) is not null drop table dbo.calendar
    go

    if (select object_id('dbo.sales')) is not null drop table dbo.sales
    go


    The result is:
    YearMonth Amount
    200601 50.000
    200602 0.000

    I have the 200602 replaced with 0. You can include a product table in the left join and replaced a default value for the NULL items (no product or no match). I hope this helps.


    May the Almighty God bless us all!
    www.empoweredinformation.com
  18. Twan New Member

    HI ya,

    shouldn't the query be:


    SELECT dbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    SUM(isnull(db:confused:rderDetails_Retail.Quantity,0)) AS Qty,
    DATEPART(wk,db:confused:rders_Retail.OrderDate) AS SalesWeek,
    YEAR(db:confused:rders_Retail.OrderDate) AS SalesYear
    FROM dbo.Calendar
    LEFT JOIN db:confused:rders_Retail
    INNER JOIN db:confused:rderDetails_Retail
    INNER JOIN dbo.Products
    ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    ON convert( nvarchar, db:confused:rders_Retail.OrderDate, 102) = CONVERT(nvarchar, dbo.Calendar.dt, 102)
    GROUP BY dbo.Products.ProductName,
    dbo.Products.ProductBrand,
    YEAR(db:confused:rders_Retail.OrderDate),
    DATEPART(wk, db:confused:rders_Retail.OrderDate)


    Cheers
    Twan
  19. mtm81 New Member

    Hi All,
    Thanks for the replies- skywalker - your version looked impressive but I'm not sure that it needs to be that complicated does it?

    I've made yours up Twan and although that is correct - it doesn't show the null values.. which is what I need..

    so that query only shows a product in the weeks it was actually purchased - I need entries for the weeks it wasn't...

    James
  20. mtm81 New Member

    ok just so everyone who is kindly helping me..
    I have (as a example) four products, and four years worth of data.

    For each seven days, I'm using MSSQL to group as a week of the year and then per year.

    I then need a total for each week of the year of how many of an individual item where sold, including where none where.

    example:

    Prod | Qty | Week | Year
    1 | 3 | 23 | 2004
    2 | 5 | 23 | 2004
    3 | 0 | 23 | 2004
    4 | 11 | 23 | 2004
    1 | 0 | 24 | 2004
    2 | 0 | 24 | 2004
    3 | 23 | 24 | 2004
    4 | 5 | 24 | 2004


    I have four tbls to get this info from (if required).

    I have an orders tbl with order dates in to get the data.. however as there are sometimes days with no orders, I have a calendar tbl so I can get "all dates" if required.


    I also have an order_detail tbl which shows an orderid to link it to the main order tbl and also productid's and qty.

    Finally, I have a products tbl which holds the ID's and the names of the products.




    Ok, as far as I can work out I need to join the calendar to the orders tbl via an outer join so that all dates are returned even those without orders.

    I also need to do a JOIN of some kind of the order_detail tbl to get the qty's sold (if any for a particular order).

    Finally, I need to do a JOIN from the order_detail to the products to get the product name etc.


    However every way I have tried so far, exlcudes (usually) the days where there where no sales so I never get a zero qty, I just don't get an entry..

    I need to include ALL PRODUCTS on ALL WEEKS of ALL YEARS, grouped up week by week..

    Hope that makes sense to everyone - and thanks a million for your help!!!!

    James
  21. FrankKalis Moderator

    I might be missing something as I'm in a hurry now, but instead of SELECTing and GROUPing BY


    YEAR(db:confused:rders_Retail.OrderDate),
    DATEPART(wk, db:confused:rders_Retail.OrderDate)

    GROUP BY the corresponding column in your calendar table.


    YEAR(dbo.Calendar.dt),
    DATEPART(wk, dbo.Calendar.dt)

    That should give all weeks of all years and the corresponding data from the other table(s).

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  22. Twan New Member

    Frank has a great eye for detail!

    yes with the query as is it will give one row which has a null week and year as well as each week/year where there were orders rather than getting all week/year combos from he calendar table

    Cheers
    Twan
  23. Adriaan New Member

    But you need a FULL JOIN to get the unmatched calender weeks. Should have thought of that last week ...
  24. Twan New Member

    Hi Adriaan,

    I don't follow, you'd only need a left outer join wouldn't you? since you're not after products that have a week/year not in the calendar table only the other way around?

    Cheers
    Twan
  25. FrankKalis Moderator

    quote:Originally posted by Adriaan

    But you need a FULL JOIN to get the unmatched calender weeks. Should have thought of that last week ...
    No. By using the Calendar table as the preserved table in the LEFT JOIN you automatically take care of this.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  26. mtm81 New Member

    ok I think you lot have nearly got it there but still a problem.

    this is the current query I'm using:

    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0)) AS Qty, DATEPART(wk,
    dbo.Calendar.dt) AS SalesWeek, YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar LEFT OUTER JOIN
    db:confused:rders_Retail INNER JOIN
    db:confused:rderDetails_Retail INNER JOIN
    dbo.Products ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID ON
    CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102) = CONVERT(nvarchar, dbo.Calendar.dt, 102)
    GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt)


    What I get as results is a load of blank records (by blank I mean no productbrand or product name info, a qty of zero and a week and year... then I get all the products as asked.

    The problem with this is, I suspect all of these blanks are the weeks the individual products were not bought, but seeing as they don't have any product info in their respective fields I have no way of know.. plus they all appear at the top of the records..

    Does that make sense?



  27. Twan New Member

    ahah, so Adriaan was right!

    so you want:
    - all calendar rows with a matching orders retail row
    - any other calendar rows with any products that were not ordered in that time frame

    This is a cartesian product between the calendar and products table also known as a cross join

    the query would be something like


    SELECTdbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0)) AS Qty,
    DATEPART(wk, dbo.Calendar.dt) AS SalesWeek,
    YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar
    CROSS JOIN dbo.Products
    LEFT OUTER JOIN db:confused:rderDetails_Retail
    ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    LEFT OUTER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID ON
    AND CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102) = CONVERT(nvarchar, dbo.Calendar.dt, 102)
    GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt)


    Cheers
    Twan

    PS NOTE that a CROSS JOIN generates a large number of rows, e.g. with 1000 calendar rows and 100 products it equals 100,000 rows returned

  28. mtm81 New Member

    that's doesn't work for me -get unable to parse query..

    when trying to run it.

    James
  29. mtm81 New Member

    if I take out the word AND from near that convert function i get a incorrect syntax near 'ON'

    James
  30. Adriaan New Member

    LEFT OUTER JOIN db:confused:rders_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID ON
    AND CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102) = CONVERT(nvarchar, dbo.Calendar.dt, 102)
  31. mtm81 New Member

    ok nearly there....

    ok I know this would return an awful lot of entries, so when I first ran the query I got a timeout..

    so thinking it was just because it was too many records to return, I added a where clause to narrow it down to just one productid..

    however this is still timing out...

    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0)) AS Qty, DATEPART(wk,
    dbo.Calendar.dt) AS SalesWeek, YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar CROSS JOIN
    dbo.Products LEFT OUTER JOIN
    db:confused:rderDetails_Retail ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID LEFT OUTER JOIN
    db:confused:rders_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID AND CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102)
    = CONVERT(nvarchar, dbo.Calendar.dt, 102)
    WHERE (dbo.Products.ProductID = 206)
    GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt)
  32. mtm81 New Member

    ok just to update I think that query is not quite right... I've gone so far as to add a daterange WHERE clause to narrow it down to one product in a very small date range and it does return it's results but is painfully slow (the results where only for around 50 records and it took the best part of a minute to do it..)

    If I then leave the date value intact but add an additional product to the query I get the timeout.

  33. mtm81 New Member

    ok just noticed - the qty's bought are totally wrong..

    Here's the results from the one product version:

    I get a massive qty for the first week, a slightly higher one for the second week and then the same as that for the rest.

    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0)) AS Qty, DATEPART(wk,
    dbo.Calendar.dt) AS SalesWeek, YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar CROSS JOIN
    dbo.Products LEFT OUTER JOIN
    db:confused:rderDetails_Retail ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID LEFT OUTER JOIN
    db:confused:rders_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID AND CONVERT(nvarchar, db:confused:rders_Retail.OrderDate, 102)
    = CONVERT(nvarchar, dbo.Calendar.dt, 102)
    WHERE (dbo.Products.ProductID = 206) AND (dbo.Calendar.dt > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (dbo.Calendar.dt < CONVERT(DATETIME,
    '2006-11-01 00:00:00', 102))
    GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt)
    ORDER BY YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt), dbo.Products.ProductBrand, dbo.Products.ProductName
  34. Twan New Member

    Alrighty lets change tack slightly...



    SELECT
    dbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    ISNULL( SELECTsum(db:confused:rderDetails_Retail.Quantity)
    FROM
    db:confused:rderDetails_Retail
    INNERT JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHEREdb:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    ANDDATEPART(wk, dbo.Calendar.dt) = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    ANDYEAR(dbo.Calendar.dt) = YEAR(db:confused:rders_Retail.OrderDate)
    , 0)) AS Qty,
    DATEPART(wk, dbo.Calendar.dt) AS SalesWeek,
    YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar CROSS JOIN
    dbo.Products
    WHEREdbo.Products.ProductID = 206
    ANDdbo.Calendar.dt > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
    AND dbo.Calendar.dt < CONVERT(DATETIME,'2006-11-01 00:00:00', 102)
    ORDER BY
    YEAR(dbo.Calendar.dt),
    DATEPART(wk, dbo.Calendar.dt),
    dbo.Products.ProductBrand,
    dbo.Products.ProductName


    or we can try to modify the abvoe statement slightly



    SELECT
    dbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    SUM(ISNULL(db:confused:rderDetails_Retail.Quantity, 0)) AS Qty,
    DATEPART(wk, dbo.Calendar.dt) AS SalesWeek,
    YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar
    CROSS JOIN dbo.Products
    LEFT OUTER JOIN db:confused:rderDetails_Retail
    ON db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    LEFT OUTER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    AND YEAR(dbo.Calendar.dt) = YEAR(db:confused:rders_Retail.OrderDate)
    AND DATEPART(wk, dbo.Calendar.dt) = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    WHERE
    dbo.Products.ProductID = 206
    AND dbo.Calendar.dt > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)
    AND dbo.Calendar.dt < CONVERT(DATETIME, '2006-11-01 00:00:00', 102)
    GROUP BY
    dbo.Products.ProductName,
    dbo.Products.ProductBrand,
    YEAR(dbo.Calendar.dt),
    DATEPART(wk, dbo.Calendar.dt)
    ORDER BY
    YEAR(dbo.Calendar.dt),
    DATEPART(wk, dbo.Calendar.dt),
    dbo.Products.ProductBrand,
    dbo.Products.ProductName


    Cheers
    Twan
  35. mtm81 New Member

    thanks for the input Twan,
    The first query you sent seems to have a syntax error somewhere in that nested select but I can't see it myself.

    the second one does the same as what I already had which is get the wrong qty's..
  36. mtm81 New Member

    ok that first query you said, if I take out the ISNULL part of the nested query i generates errors about orderdetail_retail not matchin a table or alias used...
  37. Twan New Member

    Hi ya,

    It looks like I misspelt the words INNER JOIN putting INNERT JOIN instead... Since I don't have the tables created here I'm not able to run the query for syntax check sorry.

    Cheers
    Twan
  38. mtm81 New Member

    oops - still not working.

    Is Is use the ISNULL wrap it doesn't like it.. if I take that out i get a !MISSING FROM CLAUSE error
  39. mtm81 New Member

    dear me this is doing my head in - so thanks all for your help to this point- I know we are nearly there!

    here's my current query:

    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName,
    (SELECT SUM(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail INNER JOIN
    db:confused:rders_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID AND DATEPART(wk, dbo.Calendar.dt) = DATEPART(wk,
    db:confused:rders_Retail.OrderDate) AND YEAR(dbo.Calendar.dt) = YEAR(db:confused:rders_Retail.OrderDate)) AS Qty, DATEPART(wk, dbo.Calendar.dt)
    AS SalesWeek, YEAR(dbo.Calendar.dt) AS SalesYear
    FROM dbo.Calendar CROSS JOIN
    dbo.Products
    WHERE (dbo.Products.ProductID = 206) AND (dbo.Calendar.dt > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (dbo.Calendar.dt < CONVERT(DATETIME,
    '2006-11-01 00:00:00', 102))
    ORDER BY YEAR(dbo.Calendar.dt), DATEPART(wk, dbo.Calendar.dt), dbo.Products.ProductBrand, dbo.Products.ProductName


    This version gets what looks to be the correct qty's but for this product I get between 5-7 records for each week and year all the same with the same qty and then onto the next week...

  40. mtm81 New Member

    ok another update - I thought the results I was getting was because that query didn't have any grouping - so I added it and now get a "Deferred prepare could not be completed" internal SQL server error!

    James
  41. Twan New Member

    Hi James,

    Could you please post the table creation and some sample data insert statements. I can then push these into a tempdb here and get the query to work. I think that the isnull wasn't working because I should have enclosed the select statement itself in ()

    I can see why you're getting between 5 and 7 rows, but hard to troubleshoot it without having the tables and some sample data. It is because in the outer query we're gettting a row per date in the calendar table, but in the inner row we're aggregating to year/week

    Cheers
    Twan
  42. mtm81 New Member

    ok Twan - no problem.. just tell me how to do what you want...

    basically I'm very much at the beginning of my work with MSSQL and am simply using Access Project to make my tbls and some very very basic SP's and views...

    most of my stuff I code up in ASP and so do the inner nesting etc in there instead.. (not great I know).

    so this tbl info you want- where do I go to get it for you?

    (I do hve access to enterprise manager and the query analyser which I can use a little)

    James
  43. Twan New Member

    Hi ya,

    alrighty the following statement does work BUT it ain't pretty



    SELECT
    dbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    ISNULL( SELECT sum(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail
    INNER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    AND Calendar.SalesWeek = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    AND dbo.Calendar.Salesyear = YEAR(db:confused:rders_Retail.OrderDate)
    , 0)) AS Qty,
    Calendar.SalesWeek AS SalesWeek,
    Calendar.Salesyear AS SalesYear
    FROM
    (select distinct
    YEAR(dbo.Calendar.dt) Salesyear,
    DATEPART(wk, dbo.Calendar.dt) SalesWeek
    from dbo.Calendar
    ) calendar
    CROSS JOIN
    dbo.Products
    ORDER BY
    Salesyear,
    SalesWeek,
    dbo.Products.ProductBrand,
    dbo.Products.ProductName



    Cheers
    Twan
  44. mtm81 New Member

    Hi I get an "Error in list of function arguments: 'SELECT' not recognized.
    Unable to parse query text." error... when I paste that in...

  45. mtm81 New Member

    If I take out teh ISNULL wrap I get:

    the dbo.column preix does not match...........
  46. Twan New Member

    Hi ya,

    sorry, copying problem when taking the query from our airgapped system. Unfortunately I don't have SQL on my laptop and the SQL Server is airgapped and so has no Internet access. I should have put the code on my usb key rather than re-type it. I hope this will be better



    SELECT
    dbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    ISNULL( (SELECT sum(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail
    INNER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    AND Calendar.SalesWeek = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    AND Calendar.Salesyear = YEAR(db:confused:rders_Retail.OrderDate) )
    , 0) AS Qty,
    Calendar.SalesWeek AS SalesWeek,
    Calendar.Salesyear AS SalesYear
    FROM
    (select distinct
    YEAR(dbo.Calendar.dt) Salesyear,
    DATEPART(wk, dbo.Calendar.dt) SalesWeek
    from dbo.Calendar
    ) calendar
    CROSS JOIN dbo.Products
    ORDER BY
    Salesyear,
    SalesWeek,
    dbo.Products.ProductBrand,
    dbo.Products.ProductName


    Cheers
    Twan
  47. mtm81 New Member

    THANK YOU THANK YOU THANK YOU!

    I haven't checked the quantities yet but they look about right.. and it works a treat.

    Sure getting all the dates for ALL the products takes an age, but that will never happen there will always be where clauses which shorten it down to only a few product plus a date range of only one or two years max...

    Thanks!

    James
  48. mtm81 New Member

    ok slight problem.

    I need someway of adding a datediff comparison to the last WHERE clause..
    SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, ISNULL
    ((SELECT SUM(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail INNER JOIN
    db:confused:rders_Retail ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID AND Calendar.SalesWeek = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    AND Calendar.Salesyear = YEAR(db:confused:rders_Retail.OrderDate)), 0) AS Qty, calendar.SalesWeek AS SalesWeek,
    calendar.Salesyear AS SalesYear
    FROM (SELECT DISTINCT YEAR(dbo.Calendar.dt) Salesyear, DATEPART(wk, dbo.Calendar.dt) SalesWeek
    FROM dbo.Calendar) calendar CROSS JOIN
    dbo.Products
    WHERE (dbo.Products.ProductID = 204)
    ORDER BY calendar.Salesyear, calendar.SalesWeek, dbo.Products.ProductBrand, dbo.Products.ProductName

    If I try and add it to the line

    WHERE (dbo.Products.ProductID = 204)

    so:

    WHERE (dbo.Products.ProductID = 204) AND ((DATEDIFF(d, dbo.Calendar.dt, '@StartDate') <= 0) AND (DATEDIFF(d, dbo.Calendar.dt, '@EndDate') >= 0))

    it doesn't like it, says that the dbo.Calendar.dt is not in the query..

    obviously you're sub query just returns the weeks and years from the calendar tbl, so if I added in there it would mess that up...

    where can I add something to narrow that query to the selected date ranges?
  49. Adriaan New Member

    Either move the calender criteria to the derived table (preferred here, I think) or let it point to the alias that you created, which is Calender without the owner prefix.

    Not quite sure why you would have the variable names in a quoted string?! This would already make a bit more sense:
    AND ((DATEDIFF(d, dbo.Calendar.dt, @StartDate) <= 0) AND (DATEDIFF(d, dbo.Calendar.dt, @EndDate) >= 0))

    But it doesn't look like you need the DATEDIFF at all, just this:
    AND (dbo.Calendar.dt BETWEEN @StartDate AND @EndDate)
  50. Twan New Member

    Hi ya,

    The answer to this depends...

    1. if you want the sum to only be for anything between those dates, then you'd need to put the restriction in two places



    SELECTdbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    ISNULL((SELECTSUM(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail
    INNER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    AND Calendar.SalesWeek = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    AND Calendar.Salesyear = YEAR(db:confused:rders_Retail.OrderDate)
    AND db:confused:rders_Retail.OrderDate between @StartDate AND @EndDate), 0) AS Qty,
    calendar.SalesWeek AS SalesWeek,
    calendar.Salesyear AS SalesYear
    FROM (SELECTDISTINCT YEAR(dbo.Calendar.dt) Salesyear,
    DATEPART(wk, dbo.Calendar.dt) SalesWeek
    FROM dbo.Calendar
    WHERE dbo.Calendar.dt between @StartDate AND @EndDate) calendar
    CROSS JOIN dbo.Products
    WHERE (dbo.Products.ProductID = 204)
    ORDER BY
    calendar.Salesyear,
    calendar.SalesWeek,
    dbo.Products.ProductBrand,
    dbo.Products.ProductName


    2. if you want the weeks to be restricted to between those dates, but then the sums for those weeks to be the entire week and not a part week then only add it in one place



    SELECTdbo.Products.ProductBrand,
    dbo.Products.ProductName AS ProdName,
    ISNULL((SELECTSUM(db:confused:rderDetails_Retail.Quantity)
    FROM db:confused:rderDetails_Retail
    INNER JOIN db:confused:rders_Retail
    ON db:confused:rders_Retail.OrderID = db:confused:rderDetails_Retail.OrderID
    WHERE db:confused:rderDetails_Retail.ProductID = dbo.Products.ProductID
    AND Calendar.SalesWeek = DATEPART(wk, db:confused:rders_Retail.OrderDate)
    AND Calendar.Salesyear = YEAR(db:confused:rders_Retail.OrderDate)), 0) AS Qty,
    calendar.SalesWeek AS SalesWeek,
    calendar.Salesyear AS SalesYear
    FROM (SELECTDISTINCT YEAR(dbo.Calendar.dt) Salesyear,
    DATEPART(wk, dbo.Calendar.dt) SalesWeek
    FROM dbo.Calendar
    WHERE dbo.Calendar.dt between @StartDate AND @EndDate) calendar
    CROSS JOIN dbo.Products
    WHERE (dbo.Products.ProductID = 204)
    ORDER BY
    calendar.Salesyear,
    calendar.SalesWeek,
    dbo.Products.ProductBrand,
    dbo.Products.ProductName


    Cheers
    Twan
  51. mtm81 New Member

    thanks you two!
    option 2 seems the preferred option Twan as the dates I'll add to the system are already formatted to be the beginning of a set week and the end of another...

    Adrian.. thanks for your replies--

    I do believe even twan has commented that this query is a little "ugly" - so even though it's working.. I'm sure any input into how to clean it up would be welcome... however twans version is the only that that outputs what I want to date (if you look at the postings in this thread you'll see the various versions I've had!!)

    Thanks!

    James
  52. mtm81 New Member

    just out of interest..
    a long time ago I did use BETWEEN for the dates.. but found that in some cases it would miss certain orders in that date range for the first and last dates if the order time was outside

    so for example if a user selected 1/1/2000 & 20/1/2000

    the orders would in BETWEEN that.. so from 2/1/2000 to 19/1/2000 whereas I wanted the 1/1 and the 20/1 included, which is why I switched to the datediff version...

  53. Adriaan New Member

    James,

    With a CROSS JOIN, like Twan commented, you probably need to have the date criteria on both the calender and the Products table.

    You might also notice that Twan agrees on how to filter the date range. It's not so much less ugly (this type of query can't be helped, really) as it is proper use of T-SQL syntax.
  54. Roji. P. Thomas New Member

    quote:Originally posted by mtm81

    just out of interest..
    a long time ago I did use BETWEEN for the dates.. but found that in some cases it would miss certain orders in that date range for the first and last dates if the order time was outside

    so for example if a user selected 1/1/2000 & 20/1/2000

    the orders would in BETWEEN that.. so from 2/1/2000 to 19/1/2000 whereas I wanted the 1/1 and the 20/1 included, which is why I switched to the datediff version...

    When you write BETWEEN '1/1/2000' AND '20/1/2000'

    SQL Server fetches data in the range of 1/1/2000 12:00:00 AM to 20/1/2000 12:00:00 AM


    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  55. Adriaan New Member

    The missed dates cannot be blamed on BETWEEN, which really is inclusive.

    The problem was quite probably to do with the time-of-day part of either the criteria and/or the values recorded on the rows.
  56. Roji. P. Thomas New Member

  57. Twan New Member

    One alternative to all of this is to do some of the work client-side...

    i.e.

    get the client application to:
    a) get the products of interest (the column headings)
    b) get the dates of interest (the row headings)
    c) get the sum of the units sold for the dates/products (but without having to do any complex cross join with subselect) (the cells)

    and then client side present the information (presumably a table/matrix?)

    Makes the client side a little more complex, but the database code a lot more efficient and simple

    Cheers
    Twan
  58. mtm81 New Member

    Hi Twan,
    Yeah I do actually grab the ProductID's from a form value(s) and also the dates I grab from the user already.. I then just feed them into the query.

    The only other way I could think of doing it would be a simpler query, but one which I had to do for every dates and then for every product selected which would have mean opening and closing recordsets several hundred times which I thought was much less efficient that this way (even if the SQL is "ugly")
    James
  59. Twan New Member

    Hi James,

    If you already get the product ids and the dates, then you could use SQL to only get the non-zero values, and in the application assume that if the cell value isn't there then it is zero...

    Cheers
    Twan

Share This Page