SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Returning values with multiple where values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jughead1111
Starting Member

14 Posts

Posted - 04/12/2013 :  09:24:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/12/2013 :  10:12:41  Show Profile  Reply with Quote
lower case your "code" tags.

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/12/2013 :  10:33:14  Show Profile  Reply with Quote
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

Edited by - TG on 04/12/2013 10:34:35
Go to Top of Page

jughead1111
Starting Member

14 Posts

Posted - 04/12/2013 :  11:36:15  Show Profile  Reply with Quote
I knew I could not see the forest through the tress.

Thanks - Works perfectly!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/12/2013 :  11:45:30  Show Profile  Reply with Quote
Cool - no prob!

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000