Returning values with multiple where values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning values with multiple where values

Author

Topic

jughead1111
Starting Member

14 Posts

Posted - 2013-04-12 : 09:24:17

I have a simple query that returns a aggregate value for sales for a given period.

[Code]
SELECT SUM(dbo.CUST_ORDER_LINE.TOTAL_AMT_ORDERED * dbo.CUSTOMER_ORDER.SELL_RATE) AS SALES
FROM dbo.CUSTOMER INNER JOIN
dbo.CUSTOMER_ORDER INNER JOIN
dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID ON
dbo.CUSTOMER.ID = dbo.CUSTOMER_ORDER.CUSTOMER_ID LEFT OUTER JOIN
dbo.vwJCTSelectCurrentEMData ON dbo.CUST_ORDER_LINE.PART_ID = dbo.vwJCTSelectCurrentEMData.BASE_ID LEFT OUTER JOIN
dbo.DEMAND_SUPPLY_LINK ON dbo.CUST_ORDER_LINE.LINE_NO = dbo.DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO AND
dbo.CUST_ORDER_LINE.CUST_ORDER_ID = dbo.DEMAND_SUPPLY_LINK.DEMAND_BASE_ID LEFT OUTER JOIN
dbo.WORK_ORDER ON dbo.DEMAND_SUPPLY_LINK.SUPPLY_SUB_ID = dbo.WORK_ORDER.SUB_ID AND
dbo.DEMAND_SUPPLY_LINK.SUPPLY_SPLIT_ID = dbo.WORK_ORDER.SPLIT_ID AND
dbo.DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID = dbo.WORK_ORDER.LOT_ID AND
dbo.DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = dbo.WORK_ORDER.BASE_ID AND
dbo.DEMAND_SUPPLY_LINK.SUPPLY_TYPE = dbo.WORK_ORDER.TYPE WHERE (dbo.CUSTOMER_ORDER.STATUS <> 'X') AND (dbo.CUST_ORDER_LINE.PRODUCT_CODE <> 'CAT NONJARVIS') AND
(dbo.ufn_GetDateOnly(dbo.CUSTOMER_ORDER.ORDER_DATE) BETWEEN BEGIN_DATE AND END_DATE)
[/Code]

I need to run this query multiple times using specific date values. I need to return the SALES value for each of the dates in the temp table.
I populated a Temporary table with the values I need using this code.

[Code]

Declare @Date Table (
BEGIN_DATE datetime,
END_DATE datetime,
ACCT_YEAR int,
ACCT_PERIOD int,
MonthYear datetime
)

INSERT @DATE (
BEGIN_DATE,
END_DATE,
ACCT_YEAR,
ACCT_PERIOD,
MonthYear
)
SELECT BEGIN_DATE, END_DATE, ACCT_YEAR, ACCT_PERIOD, CONVERT(datetime, CONVERT(varchar(2), ACCT_PERIOD) + '/' + '1/' + CONVERT(varchar(4), ACCT_YEAR))
AS MonthYear
FROM dbo.ACCOUNT_PERIOD
WHERE (ACCT_YEAR BETWEEN DATEPART(Year, GETDATE()) - 1 AND DATEPART(Year, GETDATE()))

DELETE FROM @DATE WHERE MonthYear > (SELECT CONVERT(datetime, CONVERT(varchar(2), ACCT_PERIOD) + '/' + '1/' + CONVERT(varchar(4), ACCT_YEAR)) AS CurrentMonth
FROM dbo.ACCOUNT_PERIOD
WHERE (END_DATE >= dbo.ufn_GetDateOnly(GETDATE())) AND (BEGIN_DATE <= dbo.ufn_GetDateOnly(GETDATE())))

[/Code]

The BEGIN_DATE and END_DATE I have in a temporary table that looks like this. The data in the table looks like this.

BEGIN_DATE END_DATE ACCT_YEAR ACCT_PERIOD YearMonth
2012-01-01 00:00:00.000 2012-01-27 00:00:00.000 2012 1 2012-01-01 00:00:00.000
2012-01-28 00:00:00.000 2012-02-24 00:00:00.000 2012 2 2012-02-01 00:00:00.000
2012-02-25 00:00:00.000 2012-03-30 00:00:00.000 2012 3 2012-03-01 00:00:00.000
2012-03-31 00:00:00.000 2012-04-27 00:00:00.000 2012 4 2012-04-01 00:00:00.000
2012-04-28 00:00:00.000 2012-05-25 00:00:00.000 2012 5 2012-05-01 00:00:00.000
2012-05-26 00:00:00.000 2012-06-29 00:00:00.000 2012 6 2012-06-01 00:00:00.000
2012-06-30 00:00:00.000 2012-07-27 00:00:00.000 2012 7 2012-07-01 00:00:00.000
2012-07-28 00:00:00.000 2012-08-24 00:00:00.000 2012 8 2012-08-01 00:00:00.000
2012-08-25 00:00:00.000 2012-09-28 00:00:00.000 2012 9 2012-09-01 00:00:00.000
2012-09-29 00:00:00.000 2012-10-26 00:00:00.000 2012 10 2012-10-01 00:00:00.000
2012-10-27 00:00:00.000 2012-11-23 00:00:00.000 2012 11 2012-11-01 00:00:00.000
2012-11-24 00:00:00.000 2012-12-31 00:00:00.000 2012 12 2012-12-01 00:00:00.000
2013-01-01 00:00:00.000 2013-01-25 00:00:00.000 2013 1 2013-01-01 00:00:00.000
2013-01-26 00:00:00.000 2013-02-22 00:00:00.000 2013 2 2013-02-01 00:00:00.000
2013-02-23 00:00:00.000 2013-03-29 00:00:00.000 2013 3 2013-03-01 00:00:00.000
2013-03-30 00:00:00.000 2013-04-26 00:00:00.000 2013 4 2013-04-01 00:00:00.000

Is there an easy way to run the first query with the begin_Date and End_Date from the temp table? I can modify the schema of the temp table if need be to add columns.

I've been toying with using a loop but wanted to find out if there was an easier way of coding this query. Each month the date values in the temp table will change.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-12 : 10:12:41

lower case your "code" tags.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-12 : 10:33:14

quote:
Is there an easy way to run the first query with the begin_Date and End_Date from the temp table? I can modify the schema of the temp table if need be to add columns.

Can't you simply join to your temp table?


FROM Customer_Order co
INNER JOIN @Date d
       on co.order_date between d.begin_date and d.end_date

EDIT:
and SELECT and GROUP BY appropriate columns from your @date table.

Be One with the Optimizer
TG

jughead1111
Starting Member

14 Posts

Posted - 2013-04-12 : 11:36:15

I knew I could not see the forest through the tress.

Thanks - Works perfectly!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-12 : 11:45:30

Cool - no prob!

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources