August 18, 2014 at 6:18 am
I have two tables. From the first table i need DISTINCT license_number, and last_renewal_date where the customer_number and location match the incoming parameters for these values.
From the second table I need to get a COUNT of the license_number(how many time that license_plate_number occurs) and sls_quantity based on the same where clause of customer_number and location.
Table 1
license_id, license_number, last_renewal_date, customer_number , location, etc
Table 2
sale_id, license_number, customer_number,location, sls_quantity, etc
I'm thinking something like this:
SELECT DISTINCT lplate_license_plate_nbr, lplate_branch, LEFT(lplate_cust_shipto_nbr,6) AS 'Customer'--, b.NumberOfShipments, b.sls_qty_shipped FROM dbo.license_plate a WHERE @Branch= lplate_branch AND @CustomerShipTo = LEFT(lplate_cust_shipto_nbr,6) LEFT JOIN
(SELECT sls_license_plate_nbr, COUNT(sls_license_plate_nbr) AS NumberOfShipments, sls_qty_shipped
FROM DM_SALES.dbo.sls_line GROUP BY sls_license_plate_nbr, sls_qty_shipped) b ON a.lplate_license_plate_nbr = b.sls_license_plate_nbr
Any ideas?
August 18, 2014 at 10:48 am
If you read the 1st link in my signature and post using those suggestions you'll get more and probably better answers because what you have posted so far doesn't really seem to be enough information. I'm going to give it a shot anyway.
This is what I come up with based on the business requirements you stated, but it doesn't return everything that your example query returns:
SELECT DISTINCT
license_number,
last_renewal_date,
COUNT(T2.license_number) AS license_number_count,
SUM(sls_quantity) AS sls_quantity_sum
FROM
Table1 AS T1
JOIN table2 AS T2
ON T1.license_number = T2.license_number
WHERE
T1.customer_number = @customer_number AND
T1.location = @location
GROUP BY
T1.license_number,
T1.last_renewal_date;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2014 at 1:27 pm
That was perfect! Thank you.
November 4, 2014 at 5:28 pm
Jack Corbett (8/18/2014)
If you read the 1st link in my signature and post using those suggestions you'll get more and probably better answers because what you have posted so far doesn't really seem to be enough information. I'm going to give it a shot anyway.This is what I come up with based on the business requirements you stated, but it doesn't return everything that your example query returns:
SELECT DISTINCT
license_number,
last_renewal_date,
COUNT(T2.license_number) AS license_number_count,
SUM(sls_quantity) AS sls_quantity_sum
FROM
Table1 AS T1
JOIN table2 AS T2
ON T1.license_number = T2.license_number
WHERE
T1.customer_number = @customer_number AND
T1.location = @location
GROUP BY
T1.license_number,
T1.last_renewal_date;
Just to note, that by creating a left join you can also see the those license numbers that had zero in sales. Also I too agree there could be more information here, yet the distinct keyword is probably not needed above.
----------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply