Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

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!!
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
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.
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

ASKER

sorry been out of the office this week.. Will get to it Monday!! Happy new year!!
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

ASKER

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