Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

SQL Query VII


Kev,
This was derived from one of your previous Queries.. What has changed in this one is the sales price is in a different location than Contract..

So I have referenced it accordingly so I think.. I dont get a syntax error, but something must be wrong because the query has been runing for 15 min as of right now and I am not getting any results..

Can you see if I missed something.. I commented out your Original code and placed new one

Thanks!!
0
Leo Torres
Asked:
Leo Torres
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I don't see an attached piece of code here.  It would help the experts involved if we could see the code and maybe sample of data involved with example of your expected results.
0
 
Leo TorresSQL DeveloperAuthor Commented:
dog it... thought i Did it sorry..
SELECT CountryCode,
       UDF_TERRITORY,
       CustomerNo,
       ProductLine,
       ItemCode,
       ItemCodeDesc,
   -- display uPvt values
   -- Units
      SUM(COALESCE([Jan Sales], 0.00)) AS 'Jan Sales',
      SUM(COALESCE([Feb Sales], 0.00)) AS 'Feb Sales',
      SUM(COALESCE([Mar Sales], 0.00)) AS 'Mar Sales',
      SUM(COALESCE([Apr Sales], 0.00)) AS 'Apr Sales',
      SUM(COALESCE([May Sales], 0.00)) AS 'May Sales',
      SUM(COALESCE([Jun Sales], 0.00)) AS 'Jun Sales',
      SUM(COALESCE([Jul Sales], 0.00)) AS 'Jul Sales',
      SUM(COALESCE([Aug Sales], 0.00)) AS 'Aug Sales',
      SUM(COALESCE([Sep Sales], 0.00)) AS 'Sep Sales',
      SUM(COALESCE([Oct Sales], 0.00)) AS 'Oct Sales',
      SUM(COALESCE([Nov Sales], 0.00)) AS 'Nov Sales',
      SUM(COALESCE([Dec Sales], 0.00)) AS 'Dec Sales',
      SUM(COALESCE([Jan Units], 0)) AS 'Jan Units',
      SUM(COALESCE([Feb Units], 0)) AS 'Feb Units',
      SUM(COALESCE([Mar Units], 0)) AS 'Mar Units',
      SUM(COALESCE([Apr Units], 0)) AS 'Apr Units',
      SUM(COALESCE([May Units], 0)) AS 'May Units',
      SUM(COALESCE([Jun Units], 0)) AS 'Jun Units',
      SUM(COALESCE([Jul Units], 0)) AS 'Jul Units',
      SUM(COALESCE([Aug Units], 0)) AS 'Aug Units',
      SUM(COALESCE([Sep Units], 0)) AS 'Sep Units',
      SUM(COALESCE([Oct Units], 0)) AS 'Oct Units',
      SUM(COALESCE([Nov Units], 0)) AS 'Nov Units',
      SUM(COALESCE([Dec Units], 0)) AS 'Dec Units',
      -- multiply uPvt values by lPvt values
      -- Units * ListPrice
      -- To display list price
      --	, add SUM(COALESCE([Jan List], 0.00)) as a column by itself
      --	, repeating for each month.
      SUM(COALESCE([Jan Units], 0) * COALESCE([Jan Sales], 0.00)) AS 'Jan Dollars',
      SUM(COALESCE([Feb Units], 0) * COALESCE([Feb Sales], 0.00)) AS 'Feb Dollars',
      SUM(COALESCE([Mar Units], 0) * COALESCE([Mar Sales], 0.00)) AS 'Mar Dollars',
      SUM(COALESCE([Apr Units], 0) * COALESCE([Apr Sales], 0.00)) AS 'Apr Dollars',
      SUM(COALESCE([May Units], 0) * COALESCE([May Sales], 0.00)) AS 'May Dollars',
      SUM(COALESCE([Jun Units], 0) * COALESCE([Jun Sales], 0.00)) AS 'Jun Dollars',
      SUM(COALESCE([Jul Units], 0) * COALESCE([Jul Sales], 0.00)) AS 'Jul Dollars',
      SUM(COALESCE([Aug Units], 0) * COALESCE([Aug Sales], 0.00)) AS 'Aug Dollars',
      SUM(COALESCE([Sep Units], 0) * COALESCE([Sep Sales], 0.00)) AS 'Sep Dollars',
      SUM(COALESCE([Oct Units], 0) * COALESCE([Oct Sales], 0.00)) AS 'Oct Dollars',
      SUM(COALESCE([Nov Units], 0) * COALESCE([Nov Sales], 0.00)) AS 'Nov Dollars',
      SUM(COALESCE([Dec Units], 0) * COALESCE([Dec Sales], 0.00)) AS 'Dec Dollars'
 
FROM (
SELECT
      AC.CountryCode,
      AC.UDF_TERRITORY,
      AC.CustomerNo,
      CI.ProductLine,
      CI.ItemCode,
      CI.ItemCodeDesc,
      -- can use P.CDate here instead if that makes more sense.
      LEFT(DATENAME(mm, AI.InvoiceDate),3) + ' Units' AS MMUnits, -- PIVOT column for Units
      AR.QuantityShipped, -- Units 
      LEFT(DATENAME(mm, AI.InvoiceDate),3) + ' Sales' AS MMList, -- PIVOT column for List
      AR.UnitPrice
FROM SQLMAS90.dbo.AR_InvoiceHistoryDetail AR--GSDatabase.dbo.Pricing P 
INNER JOIN SQLMAS90.dbo.CI_Item CI ON CI.ItemCode = AR.ItemCode 
LEFT OUTER JOIN (
	SQLMAS90.dbo.AR_Customer AC INNER JOIN SQLMAS90.dbo.AR_InvoiceHistoryHeader AI ON AC.CustomerNo = AI.CustomerNo
--	INNER JOIN dbo.AR_InvoiceHistoryDetail AR ON AI.InvoiceNo = AR.InvoiceNo AND AI.HeaderSeqNo = AR.HeaderSeqNo
) ON AR.ItemCode = CI.ItemCode
-- since CDate is always 1st of a month, get InvoiceDate as first of month as well.
-- doesn't matter which day of the month invoice date is, number of months from 0 should be same.
--AND AI.InvoiceDate = DATEADD(m, DATEDIFF(mm, 0, AI.InvoiceDate), 0)
WHERE YEAR(AI.InvoiceDate) = 2009
 ) tmp
  -- think you want SUM to total Qty Shipped instead of count rows
 PIVOT (SUM(QuantityShipped) FOR MMUnits IN ([Jan Units],[Feb Units],[Mar Units],[Apr Units],
 					[May Units],[Jun Units],[Jul Units],[Aug Units],
					[Sep Units],[Oct Units],[Nov Units],[Dec Units])) uPvt
 -- max or min shouldn't hurt here 
 PIVOT (MAX(UnitPrice) FOR MMList IN ([Jan Sales],[Feb Sales],[Mar Sales],[Apr Sales],
 				[May Sales],[Jun Sales],[Jul Sales],[Aug Sales],
				[Sep Sales],[Oct Sales],[Nov Sales],[Dec Sales])) lPvt
GROUP BY CountryCode,
         UDF_TERRITORY,
         CustomerNo,
         ProductLine,
         ItemCode,
         ItemCodeDesc

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
What happens when you just run this without the PIVOTs?
Trying to see if the data isn't as symmetrical as with the Contract.  In other words, remember that the shorthand with multiple PIVOT statements and group by is good when you have values that are one for one for each of the PIVOT'd sets (i.e., units shipped and unit price).  If they are not, then you should try the more robust solution provided in the PIVOT article which is to do each PIVOT separate and use an appropriate JOIN statement to combine the two for the final select.  Think I showed you an example in another question, but if not you have the article "Broaden Your Horizons: Pivot Again!" to refer back to.
0
 
Leo TorresSQL DeveloperAuthor Commented:
sorry been out of the office this week.. Will get to it Monday!! Happy new year!!
0
 
Leo TorresSQL DeveloperAuthor Commented:
Here is the correct one for reference.. too much data and to many references adjust the query to look at a summarized view instead.

Works fine!! thanks for the assist!!
SELECT CountryCode,
       UDF_TERRITORY,
       CustomerNo,
       ProductLine,
       ItemCode,
       ItemCodeDesc,
   -- display uPvt values
   -- Units
      SUM(COALESCE([Jan Sales], 0.00)) AS 'Jan Sales',
      SUM(COALESCE([Feb Sales], 0.00)) AS 'Feb Sales',
      SUM(COALESCE([Mar Sales], 0.00)) AS 'Mar Sales',
      SUM(COALESCE([Apr Sales], 0.00)) AS 'Apr Sales',
      SUM(COALESCE([May Sales], 0.00)) AS 'May Sales',
      SUM(COALESCE([Jun Sales], 0.00)) AS 'Jun Sales',
      SUM(COALESCE([Jul Sales], 0.00)) AS 'Jul Sales',
      SUM(COALESCE([Aug Sales], 0.00)) AS 'Aug Sales',
      SUM(COALESCE([Sep Sales], 0.00)) AS 'Sep Sales',
      SUM(COALESCE([Oct Sales], 0.00)) AS 'Oct Sales',
      SUM(COALESCE([Nov Sales], 0.00)) AS 'Nov Sales',
      SUM(COALESCE([Dec Sales], 0.00)) AS 'Dec Sales',
      SUM(COALESCE([Jan Units], 0)) AS 'Jan Units',
      SUM(COALESCE([Feb Units], 0)) AS 'Feb Units',
      SUM(COALESCE([Mar Units], 0)) AS 'Mar Units',
      SUM(COALESCE([Apr Units], 0)) AS 'Apr Units',
      SUM(COALESCE([May Units], 0)) AS 'May Units',
      SUM(COALESCE([Jun Units], 0)) AS 'Jun Units',
      SUM(COALESCE([Jul Units], 0)) AS 'Jul Units',
      SUM(COALESCE([Aug Units], 0)) AS 'Aug Units',
      SUM(COALESCE([Sep Units], 0)) AS 'Sep Units',
      SUM(COALESCE([Oct Units], 0)) AS 'Oct Units',
      SUM(COALESCE([Nov Units], 0)) AS 'Nov Units',
      SUM(COALESCE([Dec Units], 0)) AS 'Dec Units',
      -- multiply uPvt values by lPvt values
      -- Units * ListPrice
      -- To display list price
      --        , add SUM(COALESCE([Jan List], 0.00)) as a column by itself
      --        , repeating for each month.
      SUM(COALESCE([Jan Units], 0) * COALESCE([Jan Sales], 0.00)) AS 'Jan Dollars',
      SUM(COALESCE([Feb Units], 0) * COALESCE([Feb Sales], 0.00)) AS 'Feb Dollars',
      SUM(COALESCE([Mar Units], 0) * COALESCE([Mar Sales], 0.00)) AS 'Mar Dollars',
      SUM(COALESCE([Apr Units], 0) * COALESCE([Apr Sales], 0.00)) AS 'Apr Dollars',
      SUM(COALESCE([May Units], 0) * COALESCE([May Sales], 0.00)) AS 'May Dollars',
      SUM(COALESCE([Jun Units], 0) * COALESCE([Jun Sales], 0.00)) AS 'Jun Dollars',
      SUM(COALESCE([Jul Units], 0) * COALESCE([Jul Sales], 0.00)) AS 'Jul Dollars',
      SUM(COALESCE([Aug Units], 0) * COALESCE([Aug Sales], 0.00)) AS 'Aug Dollars',
      SUM(COALESCE([Sep Units], 0) * COALESCE([Sep Sales], 0.00)) AS 'Sep Dollars',
      SUM(COALESCE([Oct Units], 0) * COALESCE([Oct Sales], 0.00)) AS 'Oct Dollars',
      SUM(COALESCE([Nov Units], 0) * COALESCE([Nov Sales], 0.00)) AS 'Nov Dollars',
      SUM(COALESCE([Dec Units], 0) * COALESCE([Dec Sales], 0.00)) AS 'Dec Dollars'
 
FROM (
SELECT
      AC.CountryCode,
      AC.UDF_TERRITORY,
      AC.CustomerNo,
      AR.ProductLine,
      AR.ItemCode,
      CI.ItemCodeDesc,
      -- can use P.CDate here instead if that makes more sense.
      LEFT(DATENAME(mm, AR.InvoiceDate),3) + ' Units' AS MMUnits, -- PIVOT column for Units
      AR.QuantityShipped, -- Units 
      LEFT(DATENAME(mm, AR.InvoiceDate),3) + ' Sales' AS MMList, -- PIVOT column for List
      AR.UnitPrice
FROM  dbo.V_SKU_Detail AR --GSDatabase.dbo.Pricing P 
INNER JOIN SQLMAS90.dbo.CI_Item CI ON CI.ItemCode = AR.ItemCode 
LEFT OUTER JOIN SQLMAS90.dbo.AR_Customer AC ON AC.CustomerNo = AR.CustomerNo

-- since CDate is always 1st of a month, get InvoiceDate as first of month as well.
-- doesn't matter which day of the month invoice date is, number of months from 0 should be same.
--AND AI.InvoiceDate = DATEADD(m, DATEDIFF(mm, 0, AI.InvoiceDate), 0)
WHERE YEAR(AR.InvoiceDate) = 2009
 ) tmp
  -- think you want SUM to total Qty Shipped instead of count rows
 PIVOT (SUM(QuantityShipped) FOR MMUnits IN ([Jan Units],[Feb Units],[Mar Units],[Apr Units],
                                        [May Units],[Jun Units],[Jul Units],[Aug Units],
                                        [Sep Units],[Oct Units],[Nov Units],[Dec Units])) uPvt
 -- max or min shouldn't hurt here 
 PIVOT (MAX(UnitPrice) FOR MMList IN ([Jan Sales],[Feb Sales],[Mar Sales],[Apr Sales],
                                [May Sales],[Jun Sales],[Jul Sales],[Aug Sales],
                                [Sep Sales],[Oct Sales],[Nov Sales],[Dec Sales])) lPvt
GROUP BY CountryCode,
         UDF_TERRITORY,
         CustomerNo,
         ProductLine,
         ItemCode,
         ItemCodeDesc

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now