WRITE A QUERY

$10-30 USD

Ολοκληρώθηκε

Αναρτήθηκε

πάνω από 10 χρόνια πριν

$10-30 USD

Πληρωμή κατά την παράδοση

How could I modify the query way down below to report inventory as an "In Stock" at the product level and not the variant level? For example the query below returns: productid,variantid,productname,variantname,categoryname,manufacturer,producttype,image,url,inventory,description,extensiondata,misctext,price p123,v123,widget,blue,categoryA,acme,shirt,[login to view URL],[login to view URL],3,wave shirt,cotton,pre-shrunk,$10 p123,v124,widget,green,categoryA,acme,shirt,[login to view URL],[login to view URL],2,wave shirt,cotton,pre-shrunk,$10 p123,v125,widget,red,categoryA,acme,shirt,[login to view URL],[login to view URL],5,wave shirt,cotton,pre-shrunk,$10 p124,v123,widget,blue,categoryA,acme,shirt,[login to view URL],[login to view URL],3,wave shirt,cotton,pre-shrunk,$10 p124,v124,widget,green,categoryA,acme,shirt,[login to view URL],[login to view URL],2,wave shirt,cotton,pre-shrunk,$10 p124,v125,widget,red,categoryA,acme,shirt,[login to view URL],[login to view URL],5,wave shirt,cotton,pre-shrunk,$10 I'd rather get a result like below where only one line shows for product P123 and it says "In Stock" if any of the variants (color) have any inventory greater than 0 and null for anything out of stock: productid,variantid,productname,variantname,categoryname,manufacturer,producttype,image,url,inventory,description,extensiondata,misctext,price p123,v123,widget,categoryA,acme,shirt,[login to view URL],[login to view URL],IN STOCK,wave shirt,cotton,pre-shrunk,$10 p124,v125,widget,categoryA,acme,shirt,[login to view URL],[login to view URL],[NULL],wave shirt,cotton,pre-shrunk,$10 Here is the current query I use. WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS ( SELECT [login to view URL], [login to view URL], [login to view URL], 0 AS ComputedLevel, CAST('\'+[login to view URL] AS NVARCHAR(255)) FROM Category AS c WHERE ParentCategoryID = 0 AND Deleted=0 AND Published=1 UNION ALL SELECT [login to view URL], [login to view URL], [login to view URL], ComputedLevel + 1, CAST([login to view URL] + '\'+[login to view URL] AS NVARCHAR(255)) FROM Category AS c INNER JOIN Categories AS s ON [login to view URL] = [login to view URL] WHERE Deleted=0 AND Published=1 ) SELECT [login to view URL], [login to view URL], [login to view URL] AS ProductName, [login to view URL] AS VariantName, [login to view URL] AS CategoryName, [login to view URL] AS Manufacturer, '/images/product/icon/' + Convert(varchar(max), [login to view URL]) +'.jpg' as IMAGE, '/p-' + cast([login to view URL] as nvarchar) + '-' + [login to view URL] + '.aspx' as URL, [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] FROM ProductVariant AS pv WITH (NOLOCK) JOIN Product AS p WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN ProductCategory AS pc WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN ProductManufacturer AS pm WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN Manufacturer AS m WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN Categories AS c WITH (NOLOCK) ON [login to view URL] = [login to view URL] WHERE pv.Deleted=0 AND pv.Published=1 AND p.Deleted=0 and pv.Published=1

ASP

SQL

Ταυτότητα εργασίας: 4915545

Σχετικά με την εργασία

14 προτάσεις

Απομακρυσμένη Εργασία

Ενεργός/ή 11 χρόνια πριν

Ψάχνεις τρόπο για να κερδίσεις μερικά χρήματα;

Διεύθυνση Email

Πλεονεκτήματα πλειοδοσίας στο Freelancer

Καθόρισε τον προϋπολογισμό σου και το χρονοδιάγραμμα

Πληρώσου για τη δουλειά σου

Περίγραψε την πρόταση σου

Η εγγραφή και η πλειοδοσία σε εργασίες είναι δωρεάν

Βραβεύτηκε στον/στην:

@suraj99p

I am working with oracle and have vast experience in writing sql queries. I have written more than 500 queries for my product. i can do this for you

$25 USD σε 1 ημέρα

0,0

(0 αξιολογήσεις)

0,0

14 freelancers δίνουν μια μέση προσφορά $22 USD για αυτή τη δουλειά

@prashushinde9

Greetings! I can code in any language and provide 3 months support. Error free code and browser compatibility.

$33 USD σε 3 ημέρες

5,0

(14 αξιολογήσεις)

3,8

@ibbuzz

I can help you on that. I am ready to start it now

$30 USD σε 3 ημέρες

5,0

(3 αξιολογήσεις)

3,3

@Jchaudhari14

Hi, please check PMB for more information. Thanks :)

$25 USD σε 1 ημέρα

4,9

(11 αξιολογήσεις)

2,6

@AliSafder

Hi, Let me do this for you.

$25 USD σε 3 ημέρες

5,0

(2 αξιολογήσεις)

1,2

@SHVRomania

Hy, I`m ready to start this job now.

$25 USD σε 3 ημέρες

0,0

(0 αξιολογήσεις)

0,0

@denisariel

Expert SQL developer, got the answer you need.

$15 USD σε 0 ημέρα

0,0

(0 αξιολογήσεις)

0,0

@eComOS

Hello btappan, I have read your requirements and I am very confident to do this project. I guarantee COMPLETE satisfaction before payment. Please check PM for more details!

$23 USD σε 1 ημέρα