How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I have a SQL database that has multiple tables I need to

Customer Question

I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data. Thanks for the help!
Submitted: 1 year ago.
Category: Programming
Expert:  Ratheesh Ravi replied 1 year ago.
Hi,
Thank you for posting the question.
I will work on this. Could you please let me know by when the answer is required?
Also what is meant by UPC?
Regards
Ratheesh
Customer: replied 1 year ago.
UPC is the Universal Product Code for an item (10-14 digit unique number for an item).Here is the full setup and issue I need help with. Table structure and queries are below. Essentially I need to join the tables so that I can see both Product, Store, and Quantity sold in one table. I also need to see when purchases are not made. So I think I need a FULL OUTER JOIN, but I am not seeing the results desired. Essentially I am trying to PIVOT the data so that I can see all stores as columns and products as rows with either quantity or 0 in the cell. Then we can look for gaps in distribution of products.Products Table:
UPC (int), Product Info (text), Manufacturer_ID (Foreign Key referring to Manufacturers TableStores Table:
Store Num (unique int), Store Information including address, Territory Num (Foreign key referring to personnel assigned to store)UniData:
Store Num, UPC, Weekly Date, and Quantity purchasedCREATE VIEW [dbo].[All_UniData6MonSum]
AS
SELECT IIf([ProdUPC] LIKE '%[^0-9]%',0, IIf([ProdUPC] = 0,0,IIf([ProdUPC]=NUll, 0, IIf(Len(Cast([ProdUPC] as varchar))>=11,Cast(Left(Right(Cast([ProdUPC] as varchar),11),10) as BIGINT), Cast(Left(Cast([ProdUPC] as varchar),10) as BIGINT))))) AS CompareUPC,
CONCAT(Whse_Num,ProdNum) AS ProdNum, VendorNum, Whse_Num, Store_Num, CorpNum, SUM(Qty) AS Qty
FROM dbo.UniDataTable
WHERE (StartDate >= DATEADD(WEEK, - 26, (SELECT MAX (StartDate) FROM UniDataTable)))
GROUP BY IIf([ProdUPC] LIKE '%[^0-9]%',0, IIf([ProdUPC] = 0,0,IIf([ProdUPC]=NUll, 0, IIf(Len(Cast([ProdUPC] as varchar))>=11,Cast(Left(Right(Cast([ProdUPC] as varchar),11),10) as BIGINT), Cast(Left(Cast([ProdUPC] as varchar),10) as BIGINT))))),
CONCAT(Whse_Num,ProdNum), VendorNum, Whse_Num, Store_Num, CorpNum;_____
CREATE VIEW [dbo].[All_6MonStores]
AS
SELECT dbo.All_UniData6MonSum.CompareUPC, dbo.All_UniData6MonSum.ProdNum, dbo.All_UniData6MonSum.VendorNum, dbo.All_UniData6MonSum.Whse_Num, dbo.All_UniData6MonSum.Store_Num,
dbo.All_UniData6MonSum.CorpNum, dbo.All_UniData6MonSum.Qty, dbo.Stores.StoreName, dbo.Stores.StoreAddress, dbo.Stores.StoreCity, dbo.Stores.StoreState, dbo.Stores.StoreZipCode, dbo.Stores.Cust_Num,
dbo.Stores.Territory_Num
FROM dbo.Stores FULL OUTER JOIN
dbo.All_UniData6MonSum ON dbo.Stores.StoreNum = dbo.All_UniData6MonSum.Store_Num
WHERE (dbo.Stores.Cust_Num = 41) OR
(dbo.Stores.Cust_Num = 42)________
CREATE VIEW [dbo].[All_6MonLookback]
ASSELECT Manufacturers.MfgName, IIf([UPC] LIKE '%[^0-9]%', 0, IIf(Cast([UPC] AS BIGINT) = 0, 0, IIf(Cast([UPC] AS BIGINT) = NULL, 0, IIf(Len(Cast([UPC] AS varchar)) >= 11, Cast(LEFT(RIGHT(Cast([UPC] AS varchar), 11), 10)
AS BIGINT), Cast(LEFT(Cast([UPC] AS varchar), 10) AS BIGINT))))) AS CompareUPCProd, All_UniProducts.BrandName, All_UniProducts.CategoryName, All_UniProducts.Pk, All_UniProducts.Size,
All_UniProducts.Description, All_6MonStores.*
FROM Manufacturers RIGHT JOIN
(All_UniProducts FULL OUTER JOIN
All_6MonStores ON IIf([UPC] LIKE '%[^0-9]%', 0, IIf(Cast([UPC] AS BIGINT) = 0, 0, IIf(Cast([UPC] AS BIGINT) = NULL, 0, IIf(Len(Cast([UPC] AS varchar)) >= 11, Cast(LEFT(RIGHT(Cast([UPC] AS varchar), 11), 10)
AS BIGINT), Cast(LEFT(Cast([UPC] AS varchar), 10) AS BIGINT))))) = All_6MonStores.CompareUPC) ON Manufacturers.MfgID = All_UniProducts.Mfg_ID;The last view should have all data ready for PIVOT, but when I PIVOT it seems to duplicate a lot of rows.
When I use a PIVOT table in Excel the data displays properly, but then it hides empty columns when I want to see empty columns (yes I have tried all of the settings, but the second I filter by columns, it hides empty columns as well).
Finally, when trying to run it in Access I get timeout errors.I have the 95% solution I think, just need a way to present it and leave it for manipulation.Thank you!
Expert:  Ratheesh Ravi replied 1 year ago.
Thank you for the details.
I am working on this.
Regards
Ratheesh