March 9, 2015 at 2:27 am
Hi, I'm wondering what I'm doing wrong.
I have an order table which I want to sum up by month. For months with no orders I want to show zero.
I've joint my order table with a view containing the previous 12 months in the format 201503 (YYYYMM).
The result I'm getting however are only the months that exist in both tables.
I would expect to get all months and NULL for those that do not have a value in table OH and OD.
Would appreciate any advice on what I'm doing wrong.
SELECT OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth, p.YearMonth AS PastYearMonth
FROM OEORDH AS OH INNER JOIN
OEORDD AS OD ON OH.ORDUNIQ = OD.ORDUNIQ RIGHT OUTER JOIN
Past12Months AS p ON LEFT(OH.ORDDATE, 6) = p.YearMonth
WHERE (OD.QTYBACKORD <> 0) OR
(OD.QTYBACKORD IS NULL)
March 9, 2015 at 4:29 am
I think this is what you're trying to do.
I'm assuming you will want to total OEORDD values:
--== TEST DATA ==--
USE [tempdb]
GO
IF OBJECT_ID('OEORDH') IS NOT NULL DROP TABLE OEORDH;
IF OBJECT_ID('OEORDD') IS NOT NULL DROP TABLE OEORDD;
IF OBJECT_ID('Past12Months') IS NOT NULL DROP TABLE Past12Months;
CREATE TABLE OEORDH (SHIPTO Varchar(30), ORDDATE Char(8), ORDUNIQ Int)
CREATE TABLE OEORDD (QTYBACKORD Int, ORDUNIQ Int)
CREATE TABLE Past12Months (YearMonth Char(6))
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201503', 1)
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201502', 2)
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201501', 2)
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201412', 3)
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201411', 4)
INSERT OEORDH (SHIPTO, ORDDATE, ORDUNIQ) VALUES ('London', '201411', 5)
INSERT OEORDD (QTYBACKORD, ORDUNIQ) VALUES (55, 4)
INSERT OEORDD (QTYBACKORD, ORDUNIQ) VALUES (25, 5)
INSERT Past12Months (YearMonth) VALUES ('201503')
INSERT Past12Months (YearMonth) VALUES ('201501')
/*
SELECT OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth, p.YearMonth AS PastYearMonth
FROM OEORDH AS OH INNER JOIN
OEORDD AS OD ON OH.ORDUNIQ = OD.ORDUNIQ RIGHT OUTER JOIN
Past12Months AS p ON LEFT(OH.ORDDATE, 6) = p.YearMonth
WHERE (OD.QTYBACKORD <> 0) OR
(OD.QTYBACKORD IS NULL)
*/
--== TRY THIS ==--
;WITH CTE AS
(
SELECT OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth, [QTYBACKORD]=ISNULL(SUM(QTYBACKORD), 0)
FROM OEORDH AS OH
LEFT OUTER JOIN OEORDD AS OD ON OH.ORDUNIQ = OD.ORDUNIQ
WHERE (OD.QTYBACKORD <> 0) OR
(OD.QTYBACKORD IS NULL)
GROUP BY OH.SHIPTO, LEFT(OH.ORDDATE, 6)
)
SELECT CTE.*, p.YearMonth AS PastYearMonth
FROM CTE
LEFT OUTER JOIN Past12Months AS p ON CTE.YearMonth = p.YearMonth;
I've used a CTE to split the query into 2 parts & allow totalling.
Without sample test data it's difficult to be sure why your query doesn't work.
March 9, 2015 at 6:52 am
Thank you for your reply.
My table 'Past12Months' is just a rolling list of the previous 12 months.
YearMonth
201503
201502
201501
201412
201411
201410
201409
201408
201407
201406
201405
201404
The table OEORDH looks like this:
ORDUNIQ | SHIPTO | ORDDATE
1 | 10GEI | 20140512
2 | 10MWA | 20140403
3 | 20ZAM | 20150205
4 | 30SUK | 20141223
5 | 36TAS | 20141005
The table OEORDD looks like this:
ORDUNIQ | QTYBACKORD
1 | 0
2 | 3
3 | 2
4 | 0
5 | 10
I'm not concerned about the grouping or summing up the ordered quantities.
What I'm struggling to understand is why I cannot get a value (not even NULL) against every of the last 12 months?
it will only return values for the months which have an ordered quantity.
My SQL statement will return this (like an inner join):
SHIPTO | YearMonth | PastYearMonth
10MWA | 201404 | 201404
20ZAM | 201502 | 201502
36TAS | 201410 | 201410
What it should return is:
SHIPTO | YearMonth | PastYearMonth
10MWA | 201404 | 201404
20ZAM | 201502 | 201502
36TAS | 201410 | 201410
NULL | NULL | 201405
NULL | NULL | 201406
NULL | NULL | 201407
NULL | NULL | 201408
NULL | NULL | 201409
NULL | NULL | 201411
NULL | NULL | 201412
NULL | NULL | 201501
NULL | NULL | 201503
Is my statement wrong or is my logic wrong?
March 9, 2015 at 6:57 am
If Past12Months contains ALL 12 months then you need to start with Past12Months and left join OEORDH and OEORDD
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2015 at 7:00 am
It looks like your inner join is the issue. If you want all dates, then if you start with that, then left outer join to your other tables, it will give you all in your dates table and those rows that match from the tables you join...your query is starting at your OEORDH table, so that you are restricting to output to the inlcude only the rows in that table.
Bex
March 9, 2015 at 7:11 am
Example
SELECT h.SHIPTO,LEFT(h.ORDDATE,6) AS [YearMonth],p.YearMonth AS [PastYearMonth]
FROM Past12Months p
LEFT JOIN OEORDH h ON LEFT(h.ORDDATE,6) = p.YearMonth
NOTE: The use of LEFT to match ORDDATE is not efficient as it will not use any indexes (if present) and will cause table scans
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2015 at 7:36 am
Thank you for your replies.
I have modified my SQL statement but still no luck. I still only see the dates from the OEORDH table and no NULL values.
I reckon the issue is the SHIPTO field which exists in the OEORDH table but not in the Past12Months table, i.e. if I was to add a location to my Past12Months table I'd reckon it would do what I'm after.
Question is whether that is the only way of fixing it - which I find that hard to believe.
SELECT p.YearMonth AS PastYearMonth, OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth
FROM Past12Months AS p LEFT OUTER JOIN
OEORDH AS OH ON LEFT(OH.ORDDATE, 6) = p.YearMonth INNER JOIN
OEORDD AS OD ON OD.ORDUNIQ = OH.ORDUNIQ
WHERE (OH.SHIPTO = '63GRM') OR
(OH.SHIPTO IS NULL)
March 9, 2015 at 7:41 am
You have to LEFT JOIN both tables, i.e.
SELECTp.YearMonth AS PastYearMonth, OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth
FROMPast12Months AS p
LEFT JOIN OEORDH AS OH ON LEFT(OH.ORDDATE, 6) = p.YearMonth
AND OH.SHIPTO = '63GRM'
LEFT JOIN OEORDD AS OD ON OD.ORDUNIQ = OH.ORDUNIQ
Why are you joining OEORDD when you do not use any values from it?
p.s. And why are you checking for '63GRM' when your expected results is not filtered?
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2015 at 8:17 am
Thanks for your reply David.
I was including OEORDD because I will require this table in my final query to do sums.
I filter for location because in my final query I need to report by location.
I've removed OEORDD from my statement, however do still not get any NULL values.
Could the filter for SHIPTO location be the issue? I would need all previous 12 months by location however.
For the months with no values I would want to see NULL for each location.
I think the SHIPTO location needs to be in the Past12Months table for it to work?
The goal here is to be able to create a chart for order values over the last 12 rolling months.
For this I need a value in every month by location.
SELECT p.YearMonth AS PastYearMonth, OH.SHIPTO AS Location, LEFT(OH.ORDDATE, 6) AS YearMonth
FROM Past12Months AS p LEFT OUTER JOIN
OEORDH AS OH ON LEFT(OH.ORDDATE, 6) = p.YearMonth
WHERE (OH.SHIPTO = '63GRM') OR
(OH.SHIPTO IS NULL)
March 9, 2015 at 8:46 am
OK back to the beginning, what you needed to post was
Create tables
CREATE TABLE Past12Months (YearMonth char(6))
CREATE TABLE OEORDH (ORDUNIQ int,SHIPTO char(5),ORDDATE char(8))
CREATE TABLE OEORDD (ORDUNIQ int,QTYBACKORD int)
Populate tables
INSERT Past12Months (YearMonth) VALUES
('201503'),
('201502'),
('201501'),
('201412'),
('201411'),
('201410'),
('201409'),
('201408'),
('201407'),
('201406'),
('201405'),
('201404')
INSERT OEORDH (ORDUNIQ,SHIPTO,ORDDATE) VALUES
(1,'10GEI','20140512'),
(2,'10MWA','20140403'),
(3,'20ZAM','20150205'),
(4,'30SUK','20141223'),
(5,'36TAS','20141005')
INSERT OEORDD (ORDUNIQ,QTYBACKORD) VALUES
(1,0),
(2,3),
(3,2),
(4,0),
(5,10)
Exspected results
--SHIPTO | YearMonth | PastYearMonth
--10MWA | 201404 | 201404
--20ZAM | 201502 | 201502
--36TAS | 201410 | 201410
--NULL | NULL | 201405
--NULL | NULL | 201406
--NULL | NULL | 201407
--NULL | NULL | 201408
--NULL | NULL | 201409
--NULL | NULL | 201411
--NULL | NULL | 201412
--NULL | NULL | 201501
--NULL | NULL | 201503
This would have resulted in my answer as
SELECTOH.SHIPTO AS [Location],LEFT(OH.ORDDATE, 6) AS [YearMonth],p.YearMonth AS [PastYearMonth]
FROMPast12Months p
LEFT JOIN OEORDH OH ON LEFT(OH.ORDDATE, 6) = p.YearMonth
LEFT JOIN OEORDD OD ON OD.ORDUNIQ = OH.ORDUNIQ;
However your logic was not clear as you wanted to filter SHIPTO, so would this do
WITH cte (SHIPTO,YearMonth,QTYBACKORD) AS (
SELECTOH.SHIPTO,LEFT(OH.ORDDATE, 6),OD.QTYBACKORD
FROM OEORDH OH
JOIN OEORDD OD ON OD.ORDUNIQ = OH.ORDUNIQ
WHEREOH.SHIPTO = '20ZAM'
)
SELECTcte.SHIPTO AS [Location],cte.YearMonth,p.YearMonth AS [PastYearMonth]
FROMPast12Months p
LEFT JOIN cte ON cte.YearMonth = p.YearMonth
As I explained earlier you will have performance problems with the dates (unless they are date/datetime columns which we would know from the DDL)
and also with the cte unless you can add a filter for date range.
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2015 at 9:02 pm
Excellent. That is working. Apologies for the confusion.
Thank you so much!
Out of interest though, how would I achieve the same when not filtering by location?
For example show all locations with all 12 months and for those with no order value show NULL?
March 10, 2015 at 2:50 am
boettger.andreas (3/9/2015)
Out of interest though, how would I achieve the same when not filtering by location?For example show all locations with all 12 months and for those with no order value show NULL?
Make a Cartesian using Past12Months and distinct SHIPTO (from OEORDH) and then left join OEORDH and OEORDD
Like this
WITH cte (SHIPTO) AS (
SELECTDISTINCT OH.SHIPTO
FROM OEORDH OH
)
SELECTcte.SHIPTO AS [Location],LEFT(OH.ORDDATE, 6) AS [YearMonth],
p.YearMonth AS [PastYearMonth]
FROMPast12Months p
CROSS JOIN cte
LEFT JOIN OEORDH OH ON OH.SHIPTO = cte.SHIPTO
AND LEFT(OH.ORDDATE, 6) = p.YearMonth
LEFT JOIN OEORDD OD ON OD.ORDUNIQ = OH.ORDUNIQ;
Far away is close at hand in the images of elsewhere.
Anon.
March 10, 2015 at 3:08 am
Very nice. Working as well.
Thank you very much!
March 10, 2015 at 11:45 pm
I'm sorry to continue this.
I thought I understood but trying to achieve the same results as previously with other tables I end up again with no NULL values.
The only difference to the previous statement I can see is the additional join in the cte part.
But that should not have an affect on the left join with the table Past12Months or should it?
WITH cte (YearMonth, LocalVendor, LOCATION ) AS
(
SELECT LEFT(ph.DATE, 6) AS YearMonth, lv.LocalVendor, pl.LOCATION
FROM POPORH1 AS ph JOIN
POPORL AS pl ON ph.PORHSEQ = pl.PORHSEQ JOIN
LocalInternationalVendors AS lv ON ph.VDCODE = lv.VendorID
WHERE pl.LOCATION = '20ZAM'
)
SELECT cte.YearMonth, cte.LocalVendor, cte.LOCATION, p.YearMonth as PastYearMonth
FROM Past12Months p LEFT JOIN cte ON cte.YearMonth = p.YearMonth
March 11, 2015 at 3:19 am
Refer to my previous post regarding creating a Cartesian product, in this case of yearmonth and vendor.
Also refer to my post referring to providing DDL, data and expected results as your requirements have changed.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply