how to include the nulls??

how to include the nulls??

• nate-d-o-double-g

   
  

• Kolja

  Hi!,

  I don't think I mad myself clear - I'm not getting an errors -I'm just not getting any results.

  
  

• Hiral

  ok I'm really a novice with all that..

  I created it based on just pasting the example they had.. but that's not what I wanted..

  I guess what I want is a calendar with the following:

  Year, Week and that's it...

  that one in that example had a load of extra data which I couldn't make head nor tail of....

  
  

• rohit nagesh

  ok nearly there.. got my calendar as I want it, but now I've hit a bit of a problem with my join.

  If I join my orders tbl by orderdate with the Dt column in my calendar.. then it doesn't return any retults.. here we are:

  SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek,
  dbo.Calendar.W AS SalesWeek, dbo.Calendar.Y AS SalesYear
  FROM dbo.Calendar LEFT OUTER JOIN
  dbo.Orders_Retail ON dbo.Calendar.dt = dbo.Orders_Retail.OrderDate INNER JOIN
  dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID
  WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
  (dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(dbo.Calendar.D, dbo.Calendar.dt, @StartDate) <= 0) AND (DATEDIFF(dbo.Calendar.D,
  dbo.Calendar.dt, @EndDate) >= 0)
  GROUP BY dbo.Calendar.Y, dbo.Calendar.W, dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName
  ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, dbo.Calendar.Y, dbo.Calendar.W

  Any ideas

  
  

• CallahD

  Hi,
  
  look here: http://www.aspfaq.com/2519
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• Hyde

  Yes, sure that makes sense, but therefore yopu will have to join your "data" table either with a calendar table, or with a subquery returning the weeks that are present for all products to appear in the resultset (cross joining)
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• Adithi

  ok the calendar tbl sounds the correct option - is something already in my db, or do i have to create it

  if so, any examples of how to do this

  
  

• bingbangzoom

  ok I've narrowed it down to the fact that it doesn't like the join.

  The one that joins the orderdate in my orders tbl with the dt field in my calendar tbl..

  Even though both are datetime fields, both the same.. the only difference I can see between the two in terms of the values they have is that the order date usually has the date and then time, whereas the dt field just has the date...

  therefore I was thinking the:

  dbo.Calendar ON dbo.Orders_Retail.OrderDate = dbo.Calendar.dt

  needs to be changed so that the orderdate vlaue removes it's time from it perhaps

  Any ideas

  James

  
  

• Dietz

  Could you please post you table definitions to get a clear idea on how your tables look like. Seems like the datetime formats in the two columns are different. Schema definition would help in this case.

  HTH,

  ~riyaz~

  
  
  

• dteviot

  ok I'm getting somewhere with this now.. however I have another problem.. here's the current query

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

  What this does is first get the products tbl, join that the orders_detail tbl, joins that the orders tbl which is finally joined to the calendar tbl.

  What I'm finding is although it's working as intended, it's still not returning the product that haven't been bought..

  I've narrowed this down to the following:

  It I just to a Outer join on the products tbl to the order details tbl with no WHERE queries.. this returned what I want,, however as soon as I add the WHERE clause, it loses all the products and only shows the ones that have been purchased.

  I would have thoughy my Outer join on the products tbl would get round this problem

  
  

• ycjj

  anyone got any ideas

  I know exactly what the issue is....

  in the calendar the date is held as 1/1/2003.. and up to a point the dates where held in the db like this also, however after a certain date, the time was also held so the date looks like 1/1/2003 00:00:00

  This the results that aren't returned.

  As I'm doing my JOIN on the dt held in the calendar tbl and then dt in the orders tbl.. as the times are held in the orders tbl also, this means that the join won't work as the calendar tbl will not hold the EXACT date and time held in the orders tbl..

  does that make sense

  How do I fix that

  
  
• how to include the nulls??