Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2007 2:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 9:58 AM
Points: 231, Visits: 197
Hi,
I m having problem in creating a query for counting Articles of a same FAmily in an Invoice:
I have two Tables Invoice, InvoiceDetails that are related through InvoiceId.
In Invoice Table I keep the Salesman Code. In InvoiceDetails I keep the Articles that they Sell ( Marlboro Family). The Marlboro Family is defines as three GRoups (Marlboro Light, Marlboro Ultra Light and Marlboro Full Flavour) . Their Code is like:
PMM1112, PMM1212, PMM1313 (MArlboro Light)
PMM2111, PMM2112, PMM2112 (Marlboro Full Flavour)
PMM3111 (Marlboro Ultra Light)

Now I have to count the Invoices for Each Salesman that have an Invoice that Include all Three of them in one Invoice
E.c InvoiceId 00001 (PMM1112, PMM1212, PMM2111, PMM3111).

Thanks in Advance
Post #436307
Posted Wednesday, December 26, 2007 2:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Something like this?


;WITH family1 AS (
SELECT DISTINCT InvoiceID
FROM invoiceDetails
WHERE ItemCode LIKE 'PMM1%'
), family2 AS (
SELECT DISTINCT InvoiceID
FROM invoiceDetails
WHERE ItemCode LIKE 'PMM2%'
), family3 AS (
SELECT DISTINCT InvoiceID
FROM invoiceDetails
WHERE ItemCode LIKE 'PMM3%'
)

SELECT SalesRepID, COUNT(*) FROM Invoices i
INNER JOIN family1 f1 ON f1.invoiceID = i.InvoiceID
INNER JOIN family2 f2 ON f2.InvoiceID = i.InvoiceID
INNER JOIN family3 f3 ON f3.InvoiceID = i.InvoiceID
GROUP BY SalesRepID


.
Post #436308
Posted Wednesday, December 26, 2007 3:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 9:58 AM
Points: 231, Visits: 197
I don't know How to Thank You. That Did the Trick. Thanks a lot and I wish you a happy new Year
Post #436311
Posted Wednesday, December 26, 2007 3:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Happy new year!

.
Post #436312
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse