Prev: insert duplicate key errors
Next: SQL
From: AHartman on 22 Jul 2008 20:41
How can I select the dates in question based upon these tables. The pounds
tables has history for the past 3 years. The workdays table has current year
plus 09 and 2010. I need to grab from the pounds table the customer
shipments based based upon the next 6 months from the workdays table. I'm
doing some analysis based upon that same time period for the previous 2
years.

I need to get the next 6 months from workdays..

08 -2008
09 -2008
10 -2008
11 -2008
12 -2008
01-2009

then use those(months) as a driver to pull activity from the pounds table
for the previous 2 years.

CREATE TABLE pounds (
customer VARCHAR(10),
shipdate datetime,
fctlbs INT);

INSERT INTO pounds VALUES( 'jim',08/01/2006, 100);
INSERT INTO pounds VALUES( 'dave',10/01/2006, 100);
INSERT INTO pounds VALUES( 'abc',01/01/2007, 100);
INSERT INTO pounds VALUES( 'abc',11/01/2006, 100);
INSERT INTO pounds VALUES( 'jjh',11/01/2007, 100);
INSERT INTO pounds VALUES( 'abc',09/01/2007, 100);



CREATE TABLE workdays(
work_month CHAR(2),
work_year char(4),
work_days char(2));

INSERT INTO workdays VALUES( '01',2008,21);
INSERT INTO workdays VALUES( '02',2008,21);
INSERT INTO workdays VALUES( '03',2008,21);
INSERT INTO workdays VALUES( '04',2008,21);
INSERT INTO workdays VALUES( '05',2008,21);
INSERT INTO workdays VALUES( '06',2008,21);
INSERT INTO workdays VALUES( '07',2008,21);
INSERT INTO workdays VALUES( '08',2008,21);
INSERT INTO workdays VALUES( '09',2008,21);
INSERT INTO workdays VALUES( '10',2008,21);
INSERT INTO workdays VALUES( '11',2008,21);
INSERT INTO workdays VALUES( '12',2008,21);
INSERT INTO workdays VALUES( '01',2009,21);
INSERT INTO workdays VALUES( '02',2009,21);
INSERT INTO workdays VALUES( '03',2009,21);
INSERT INTO workdays VALUES( '04',2009,21);
INSERT INTO workdays VALUES( '05',2009,21);
INSERT INTO workdays VALUES( '06',2009,21);
INSERT INTO workdays VALUES( '07',2009,21);
INSERT INTO workdays VALUES( '08',2009,21);
INSERT INTO workdays VALUES( '09',2009,21);
INSERT INTO workdays VALUES( '10',2009,19);
INSERT INTO workdays VALUES( '11',2009,21);
INSERT INTO workdays VALUES( '12',2009,20);


Thanks.





From: Plamen Ratchev on 22 Jul 2008 22:53
It is not clear what result set you expect. The following query will give
you the next 6 months from the Workdays table and the matching dates in the
Pounds table for the past two year.

SELECT work_year,
work_month,
customer,
shipdate,
fctlbs
FROM (SELECT CAST(work_year AS CHAR(4)) +
'-' +
work_month AS year_month,
work_year,
work_month
FROM Workdays) AS W
LEFT JOIN Pounds AS P
ON W.work_month = MONTH(P.shipdate)
AND W.work_year BETWEEN YEAR(P.shipdate) + 1
AND YEAR(P.shipdate) + 2
WHERE year_month > CONVERT(CHAR(7), CURRENT_TIMESTAMP, 126)
AND year_month < CONVERT(CHAR(7),
DATEADD(MONTH, 7, CURRENT_TIMESTAMP), 126);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: insert duplicate key errors
Next: SQL