Outer Joins not Working?

Hi,
I have the following slightly complicated query:

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

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

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!

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 dbrders_Retail O_R ON c.Dt = O_R.OrderDate
LEFT JOIN dbrderDetails_Retail OD_R ON O_R.OrderID = OD_R.OrderID
LEFT JOIN dbo.Products P ON OD_R.ProductID = P.ProductID

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

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

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...

oops- here you go:

SELECT dbo.Products.ProductBrand, dbo.Products.ProductName AS ProdName, SUM(dbrderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
dbrders_Retail.OrderDate) AS SalesWeek, YEAR(dbrders_Retail.OrderDate) AS SalesYear
FROM dbo.Calendar LEFT JOIN
dbrders_Retail ON CONVERT(datetime, CONVERT(nvarchar, dbrders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(nvarchar,
dbo.Calendar.dt, 102), 102) LEFT JOIN
dbrderDetails_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID LEFT JOIN
dbo.Products ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID
GROUP BY dbo.Products.ProductName, dbo.Products.ProductBrand, YEAR(dbrders_Retail.OrderDate), DATEPART(wk, dbrders_Retail.OrderDate)

Perhaps this ...

SUM(ISNULL(dbrderDetails_Retail.Quantity, 0))

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

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!

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 dbrders_Retail) AND (SELECT MAX(OrderDate) FROM dbrders_Retail)

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

Perhaps the problem isnt in the joins but in the WHERE clause ?

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

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.

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

HI ya,

shouldn't the query be:


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


Cheers
Twan

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

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

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

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

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

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(dbrderDetails_Retail.Quantity, 0)) AS Qty, DATEPART(wk,
dbo.Calendar.dt) AS SalesWeek, YEAR(dbo.Calendar.dt) AS SalesYear
FROM dbo.Calendar LEFT OUTER JOIN
dbrders_Retail INNER JOIN
dbrderDetails_Retail INNER JOIN
dbo.Products ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID ON
CONVERT(nvarchar, dbrders_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?

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(dbrderDetails_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 dbrderDetails_Retail
ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID
LEFT OUTER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID ON
AND CONVERT(nvarchar, dbrders_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

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

when trying to run it.

James

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

James

LEFT OUTER JOIN dbrders_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID ON
AND CONVERT(nvarchar, dbrders_Retail.OrderDate, 102) = CONVERT(nvarchar, dbo.Calendar.dt, 102)

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(dbrderDetails_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
dbrderDetails_Retail ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID LEFT OUTER JOIN
dbrders_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID AND CONVERT(nvarchar, dbrders_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)

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.

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(dbrderDetails_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
dbrderDetails_Retail ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID LEFT OUTER JOIN
dbrders_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID AND CONVERT(nvarchar, dbrders_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

Alrighty lets change tack slightly...



SELECT
dbo.Products.ProductBrand,
dbo.Products.ProductName AS ProdName,
ISNULL( SELECTsum(dbrderDetails_Retail.Quantity)
FROM
dbrderDetails_Retail
INNERT JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHEREdbrderDetails_Retail.ProductID = dbo.Products.ProductID
ANDDATEPART(wk, dbo.Calendar.dt) = DATEPART(wk, dbrders_Retail.OrderDate)
ANDYEAR(dbo.Calendar.dt) = YEAR(dbrders_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(dbrderDetails_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 dbrderDetails_Retail
ON dbrderDetails_Retail.ProductID = dbo.Products.ProductID
LEFT OUTER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
AND YEAR(dbo.Calendar.dt) = YEAR(dbrders_Retail.OrderDate)
AND DATEPART(wk, dbo.Calendar.dt) = DATEPART(wk, dbrders_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

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..

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...

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

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

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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail INNER JOIN
dbrders_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID AND DATEPART(wk, dbo.Calendar.dt) = DATEPART(wk,
dbrders_Retail.OrderDate) AND YEAR(dbo.Calendar.dt) = YEAR(dbrders_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...

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

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

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

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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail
INNER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID
AND Calendar.SalesWeek = DATEPART(wk, dbrders_Retail.OrderDate)
AND dbo.Calendar.Salesyear = YEAR(dbrders_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

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

If I take out teh ISNULL wrap I get:

the dbo.column preix does not match...........

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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail
INNER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID
AND Calendar.SalesWeek = DATEPART(wk, dbrders_Retail.OrderDate)
AND Calendar.Salesyear = YEAR(dbrders_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

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

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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail INNER JOIN
dbrders_Retail ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID AND Calendar.SalesWeek = DATEPART(wk, dbrders_Retail.OrderDate)
AND Calendar.Salesyear = YEAR(dbrders_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?

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)

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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail
INNER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID
AND Calendar.SalesWeek = DATEPART(wk, dbrders_Retail.OrderDate)
AND Calendar.Salesyear = YEAR(dbrders_Retail.OrderDate)
AND dbrders_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(dbrderDetails_Retail.Quantity)
FROM dbrderDetails_Retail
INNER JOIN dbrders_Retail
ON dbrders_Retail.OrderID = dbrderDetails_Retail.OrderID
WHERE dbrderDetails_Retail.ProductID = dbo.Products.ProductID
AND Calendar.SalesWeek = DATEPART(wk, dbrders_Retail.OrderDate)
AND Calendar.Salesyear = YEAR(dbrders_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

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

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...

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.

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

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.

See
http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

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

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

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