Prev: Trigger Modify Record Date
Next: Select on current date
From: Snedker on 16 Mar 2010 04:48
Hi folks,

I am having problems figuring this one out:

The pivot below is intended to be used in statistics (how does each
operator (employee) perform). My problem is that I need for it to by
dynamic, so that a date interval can be chosen before presenting the
result. The date is in [Shipment].[ArrivalDate].

I just don't know how to get this where clause implemented i a dynamic
way. The PIVOT works in its basics:


SELECT DISTINCT *
FROM
vPivot_Operators
PIVOT
(
AVG(Score)
FOR ControlName IN (Tipped, [Space], Wrap, Layers, Corners, Nails,
[Weight], Fit, Shippingmark, Drypole, [Top box])
) as p


The pivot above selects from [vPivot_Operators] that looks like this:

SELECT DISTINCT dbo.Employee.Name, dbo.ControlItem.Name AS
ControlName, dbo.ControlAnswer.[Index] AS Score
FROM dbo.Control INNER JOIN
dbo.[Order] ON dbo.Control.OrderID = dbo.
[Order].OrderID INNER JOIN
dbo.Employee ON dbo.[Order].OperatorID =
dbo.Employee.EmployeeID INNER JOIN
dbo.Control_ControlAnswer ON
dbo.Control.ControlID = dbo.Control_ControlAnswer.ControlID INNER JOIN
dbo.ControlAnswer INNER JOIN
dbo.ControlItem ON
dbo.ControlAnswer.ControlItemID = dbo.ControlItem.ControlItemID ON
dbo.Control_ControlAnswer.ControlAnswerID =
dbo.ControlAnswer.ControlAnswerID INNER JOIN
dbo.Shipment_Order ON dbo.[Order].OrderID =
dbo.Shipment_Order.OrderID INNER JOIN
dbo.Shipment ON dbo.Shipment_Order.ShipmentID =
dbo.Shipment.ShipmentID
WHERE (dbo.ControlItem.ControlTypeID = 2)


Thanks in advance for any help!

Regards
Morten Snedker
From: Plamen Ratchev on 16 Mar 2010 10:22
Really not sure what you are asking for. Do you mean to parameterize the WHERE clause? If that is the case, then you can
do this:

1). Add the column ArrivalDate to the SELECT list in the view definition
2). Change the pivot query to

SELECT <columns>
FROM (
SELECT <columns_except_arrival_date>
FROM vPivot_Operators
WHERE ArrivalDate >= @start_date
AND ArrivalDate < @end_date) AS V
PIVOT
(...) AS P;

In the above @start_date and @end_date are parameters.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Snedker on 16 Mar 2010 17:30
Thanks to the both of you!

Guess I wanted it to be more complex than needed. Works like a
charm..thx! :)

/Morten

On 16 Mar., 15:22, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Really not sure what you are asking for. Do you mean to parameterize the WHERE clause? If that is the case, then you can
> do this:
>
> 1). Add the column ArrivalDate to the SELECT list in the view definition
> 2). Change the pivot query to
>
> SELECT <columns>
> FROM (
> SELECT <columns_except_arrival_date>
> FROM vPivot_Operators
> WHERE ArrivalDate >= @start_date
>    AND ArrivalDate <  @end_date) AS V
> PIVOT
> (...) AS P;
>
> In the above @start_date and @end_date are parameters.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

 | 
Pages: 1
Prev: Trigger Modify Record Date
Next: Select on current date