Avatar of itinfo7
itinfo7
 asked on

SQL view problem

I have view as below
SELECT     O.OrderID, O.CreatedOn,
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'LastName')) AS 'LastName',
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'FirstName')) AS 'FirstName', SUBSTRING
                          ((SELECT     (', ' + CR.Name)
                              FROM         dbo.Nop_Customer_CustomerRole_Mapping CCRM INNER JOIN
                                                    dbo.Nop_CustomerRole CR ON CR.CustomerRoleID = CCRM.CustomerRoleID
                              WHERE     CCRM.CustomerID = C.CustomerID AND CCRM.CustomerRoleID <> 0 FOR XML PATH('')), 2, 999999) AS 'UserGroup', 
                      O.PurchaseOrderNumber AS 'BillingCode', PV.Name, COALESCE (CAST(OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / CAST(OPV.Quantity AS decimal(10, 2))) 
                      + (NULLIF (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0)) AS decimal(10, 3)), 0) 
                      AS 'All-In Item Cost', OPV.Quantity AS 'Number Purchased', COALESCE (CAST((OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)) 
                      + NULLIF ((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)), 0)) * OPV.Quantity AS decimal(10, 3)), 0) 
                      AS 'Extended All-In Cost', OPV.[UnitPriceInclTax] AS 'Base Item Cost', COALESCE (CAST(CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0) 
                      AS decimal(10, 3)), 0) AS 'Freight Item Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) 
                      * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)) / CAST(OPV.Quantity AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Handling Item Cost', 
                      OPV.[UnitPriceInclTax] * OPV.Quantity AS 'Extended Base Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Freight', 
                      COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Handling',
                          (((SELECT     COUNT(*)
                               FROM         dbo.Nop_OrderProductVariant OPV2
                               WHERE     OPV2.OrderID = O.OrderID) * 1) + 5) AS 'TotalHandling', O.[OrderShippingInclTax] AS 'TotalFreight', O.StoreID
FROM         dbo.Nop_Order AS O INNER JOIN
                      dbo.Nop_OrderProductVariant AS OPV ON OPV.OrderID = O.OrderID INNER JOIN
                      dbo.Nop_Customer AS C ON C.CustomerID = O.CustomerID INNER JOIN
                      dbo.Nop_ProductVariant AS PV ON PV.ProductVariantId = OPV.ProductVariantID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40

Open in new window



I am modifiying the view and adding this piece of code at the end of my view.

inner join  Nop_ShippingSpecialityServiceInfo sp
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40

so final view will be like this
SELECT O.OrderID, O.CreatedOn, o.ShippingRateComputationMethodID,
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'LastName')) AS 'LastName',
                          (SELECT     TOP (1) Value
                            FROM          dbo.Nop_CustomerAttribute AS CA
                            WHERE      (CustomerId = C.CustomerID) AND ([Key] = 'FirstName')) AS 'FirstName', SUBSTRING
                          ((SELECT     (', ' + CR.Name)
                              FROM         dbo.Nop_Customer_CustomerRole_Mapping CCRM INNER JOIN
                                                    dbo.Nop_CustomerRole CR ON CR.CustomerRoleID = CCRM.CustomerRoleID

                              WHERE     CCRM.CustomerID = C.CustomerID AND CCRM.CustomerRoleID <> 0 FOR XML PATH('')), 2, 999999) AS 'UserGroup', 
                      O.PurchaseOrderNumber AS 'BillingCode', PV.Name, COALESCE (CAST(OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / CAST(OPV.Quantity AS decimal(10, 2))) 
                      + (NULLIF (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0)) AS decimal(10, 3)), 0) 
                      AS 'All-In Item Cost', OPV.Quantity AS 'Number Purchased', COALESCE (CAST((OPV.[UnitPriceInclTax] + (CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)) 
                      + NULLIF ((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0)), 0)) * OPV.Quantity AS decimal(10, 3)), 0) 
                      AS 'Extended All-In Cost', OPV.[UnitPriceInclTax] AS 'Base Item Cost', COALESCE (CAST(CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)) / NULLIF (CAST(OPV.Quantity AS decimal(10, 2)), 0), 0) 
                      AS decimal(10, 3)), 0) AS 'Freight Item Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) 
                      * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)) / CAST(OPV.Quantity AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Handling Item Cost', 
                      OPV.[UnitPriceInclTax] * OPV.Quantity AS 'Extended Base Cost', COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) 
                      / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST(O.[OrderShippingInclTax] AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Freight', 
                      COALESCE (CAST((CAST(OPV.[PriceInclTax] AS decimal(10, 2)) / NULLIF (CAST(O.[OrderSubtotalInclTax] AS decimal(10, 2)) * CAST
                          ((((SELECT     COUNT(*)
                                FROM          dbo.Nop_OrderProductVariant OPV2
                                WHERE      OPV2.OrderID = O.OrderID) * 1) + 5) AS decimal(10, 2)), 0)) AS decimal(10, 3)), 0) AS 'Extended Handling',
                          (((SELECT     COUNT(*)
                               FROM         dbo.Nop_OrderProductVariant OPV2
                               WHERE     OPV2.OrderID = O.OrderID) * 1) + 5 ) AS 'TotalHandling', (O.[OrderShippingInclTax] ) AS 'TotalFreight', 
                      O.StoreID
                      , sp.AdditionalPrice
FROM         dbo.Nop_Order AS O INNER JOIN
                                           dbo.Nop_OrderProductVariant AS OPV ON OPV.OrderID = O.OrderID INNER JOIN
                      dbo.Nop_Customer AS C ON C.CustomerID = O.CustomerID INNER JOIN
                      dbo.Nop_ProductVariant AS PV ON PV.ProductVariantId = OPV.ProductVariantID 
                      
                      
inner join  Nop_ShippingSpecialityServiceInfo sp 
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40 

Open in new window


addition piece of code:---> I am joining tbale called  Nop_ShippingSpecialityServiceInfo(attached image) to the current view as you can see in modified view(attached image) what I am trying to build column with orderID 3886 is reaping 72 times.because I am comparing  sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID. and ShippingRateComputationMethodID. is 13 is repeating 18 times for each order.I don't want to be repeate that many times I want to be as it is like in first image orderinfo.png
3886 is repeating 4 times. and I also want to add my piece of code

inner join  Nop_ShippingSpecialityServiceInfo sp
on sp.ShippingRateComputationMethodID = O.ShippingRateComputationMethodID and sp.StoreID = O.StoreID
WHERE     (O.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2') AND O.OrderStatusID <> 40 .

Please guide me in this.
orderInfo.PNG
modiiedorder.PNG
shippingspecialityservicetable.PNG
Microsoft SQL ServerMicrosoft SQL Server 2008MySQL Server

Avatar of undefined
Last Comment
itinfo7

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
itinfo7

ASKER
Original order 3886 is only one. and I am comparing shipping speciality service is 18 so orginal order should repeat 18 times right that's what I need to do.right now it is going longer till 72.Can I add some subquery for orginal order and make  that It repeat 18 times.

somehting like this.

elect  o.orderID ,sp.AdditionalPrice,o.ShippingRateComputationMethodID,sp.ShippingSpecialityServiceInfoID 
from [Nop_Order] o
inner join  Nop_ShippingSpecialityServiceInfo sp 
on sp.ShippingRateComputationMethodID = o.ShippingRateComputationMethodID and sp.StoreID = o.StoreID
where  sp.AdditionalPrice<>0 and sp.StoreID = '3F803C48-3FD3-4542-9C36-C2948E86DBE2'

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes