Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of itinfo7
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

Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent