August 2, 2006 at 12:59 am
Hi
I am having a dataset where the values are selected according to the startdate and enddate.I am passing a parameter for the startdate and enddate. For ex:
SELECT
MAS_PRODUCTLINE.ProductLineDescription,
Count(CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo) AS ItemCount,
SUM(CUSTOMER_ORDER_ITEM_SHIPPED.UnitPrice * MAS_PRODUCT_PRICE.StandardPrice) AS COGS,
SUM(CUSTOMER_ORDER_ITEM_SHIPPED.UnitPrice * CUSTOMER_ORDER_ITEM_SHIPPED.ShippedQuantity) AS Sales
FROM CUSTOMER_ORDER_ITEM_SHIPPED INNER JOIN
MAS_PRODUCT_PRICE
ON CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo = MAS_PRODUCT_PRICE.ComponentPartNo INNER JOIN
MAS_ITEM_PRODUCTLINE
ON CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo = MAS_ITEM_PRODUCTLINE.ItemNo INNER JOIN
MAS_PRODUCTLINE
ON MAS_ITEM_PRODUCTLINE.ProductLineID = MAS_PRODUCTLINE.ProductLineID
WHERE
CUSTOMER_ORDER_ITEM_SHIPPED.PromisedShipDate BETWEEN @StartDate AND @EndDate
GROUP
BY MAS_PRODUCTLINE.ProductLineDescription
I am getting this @StartDate and @EndDate from another dataset
Ex:
SELECT FiscalStartDate,FiscalEndDate
FROM dbo.MAS_FISCAL_DATE
WHERE FiscalDate = @FiscalDate
And this @FiscalDate i am getting from another dataset
Ex:
SELECT FiscalDate FROM dbo.MAS_FISCAL_DATE
So how do i pass this @FiscalDate to get the startdate and enddate.
I want to display these two dates in text box.
August 2, 2006 at 5:51 pm
1. Use the query to get the available fiscal dates for your parameter.
2. Combine the other two queries into one
----------------
declare @startdate datetime, @enddate datetime
SELECT @startDate = FiscalStartDate, @endDate = FiscalEndDate
FROM dbo.MAS_FISCAL_DATE
WHERE FiscalDate = @FiscalDate
SELECT MAS_PRODUCTLINE.ProductLineDescription,
Count(CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo) AS ItemCount,
SUM(CUSTOMER_ORDER_ITEM_SHIPPED.UnitPrice * MAS_PRODUCT_PRICE.StandardPrice) AS COGS,
SUM(CUSTOMER_ORDER_ITEM_SHIPPED.UnitPrice * CUSTOMER_ORDER_ITEM_SHIPPED.ShippedQuantity) AS Sales
FROM CUSTOMER_ORDER_ITEM_SHIPPED INNER JOIN
MAS_PRODUCT_PRICE ON CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo = MAS_PRODUCT_PRICE.ComponentPartNo INNER JOIN
MAS_ITEM_PRODUCTLINE ON CUSTOMER_ORDER_ITEM_SHIPPED.ItemNo = MAS_ITEM_PRODUCTLINE.ItemNo INNER JOIN
MAS_PRODUCTLINE ON MAS_ITEM_PRODUCTLINE.ProductLineID = MAS_PRODUCTLINE.ProductLineID
WHERE CUSTOMER_ORDER_ITEM_SHIPPED.PromisedShipDate BETWEEN @StartDate AND @EndDate
GROUP BY MAS_PRODUCTLINE.ProductLineDescription
---------------
Just a guess, but worth a try.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply