October 13, 2010 at 8:05 pm
Hi all,
I'm writing a report on inactive suppliers, just not sure how to approach it correctly.
I'm using the creditors table and the purchases table to find out what suppliers we have not been purchasing with for a given date range and minimum amount:
Select Creditor.ACCT_NO, Creditor.L_NAME
,sum(Purchases.SALE_PRICE), Purchases.DATE
from Creditor full join Purchases
on Purchases.ACCT_NO = Creditor.ACCT_NO
where
Purchases.DATE >= @fromDate and Purchases.DATE <= @toDate
group by Creditor.ACCT_NO, Creditor.L_NAME
having SUM(Purchases.SALE_PRICE) <= @minPurchase
order by Creditor.L_NAME
In this case, the date range is a month or so and mimimum purchases are 0, so it should list all suppliers I have *not* purchased from this month.
I had figured the full join should show up all creditors, but it does not. I think I might have to perform a subquery here to get a list of suppliers i have purchased from (recently) so i can strike them off the list.
Any help is of course, appreciated.
Dallas
October 13, 2010 at 9:30 pm
Database schema/values for your perusal:
Creditor:
CREATE TABLE [dbo].[Creditor](
[ACCT_NO] [int] NOT NULL,
[L_NAME] [nvarchar](30) NULL)
INSERT INTO [Database].[dbo].[Creditor]
([ACCT_NO]
,[L_NAME])
values (1,'Mr Circus'),(2,'The artists'),(3,'Hope. Co.'),
(4,'La Mariposa'),(5,'Zoli Antonio S.P.A'),(6,'One small cake shop')
Purchases:
CREATE TABLE [dbo].[Purchases](
[ptrans_id] [int] IDENTITY(1,1) NOT NULL,
[ACCT_NO] [int] NULL,
[DATE] [datetime] NULL,
[INV_NO] [nchar](10) NULL,
[STOCK_CODE] [nchar](10) NULL,
[QUANTITY] [decimal](11, 2) NULL,
[UNIT_COST] [decimal](12, 4) NULL,
[SALE_PRICE] [decimal](10, 2) NULL)
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD FOREIGN KEY([ACCT_NO]) REFERENCES [dbo].[Creditor] ([ACCT_NO])
INSERT INTO [Database].[dbo].[Purchases]
([ACCT_NO]
,[DATE]
,[INV_NO]
,[STOCK_CODE]
,[QUANTITY]
,[UNIT_COST]
,[SALE_PRICE])
VALUES
(2,'2010-01-07 00:00:00.000','kbore-66 ','OP-106 ',30.00,3.0148,99.49),
(1,'2010-01-15 00:00:00.000','wby-66-2 ','VTL204 ',15.00,602.3200,9938.28),
(1,'2010-01-19 00:00:00.000','wby-68nz ','NSA8S1226 ',2.00,420.0250,840.05),
(1,'2010-01-22 00:00:00.000','wby-65 ','WA22455 ',5.00,39.0473,214.76),
(1,'2010-01-22 00:00:00.000','wby-65 ','WA340250 ',20.00,44.7900,985.38),
(3,'2010-02-12 00:00:00.000','aerko-01 ','FREEZE5 ',40.00,73.0268,3213.18),
(1,'2010-02-12 00:00:00.000','wby-67-1 ','WP1968 ',2.00,2.5818,5.68),
(1,'2010-02-12 00:00:00.000','wby-67-2 ','AM270WBY ',1.00,1320.7273,1452.80),
(1,'2010-02-12 00:00:00.000','wby-67-2 ','WP7911 ',5.00,1.3745,7.56),
(2,'2010-02-16 00:00:00.000','kbore-67 ','A-173 ',10.00,15.3800,169.18),
(2,'2010-02-16 00:00:00.000','kbore-67 ','BF-CLP4 ',400.00,3.9772,1749.95),
(4,'2010-02-18 00:00:00.000','5511 ','303-7-10 ',1.00,168.0000,184.80),
(4,'2010-02-18 00:00:00.000','5511 ','243S-7-10 ',1.00,260.1545,286.17),
(4,'2010-03-02 00:00:00.000','5548 ','224-HS-12 ',2.00,188.2000,414.04),
(1,'2010-03-05 00:00:00.000','wby-66-1 ','VP22250 ',3.00,567.4545,1872.60),
(1,'2010-03-16 00:00:00.000','wby-69nz ','NSA8S1228 ',44.00,455.7784,20054.25),
(4,'2010-03-26 00:00:00.000','5569 ','224S-LS-14',1.00,275.5000,303.05),
(4,'2010-03-26 00:00:00.000','5570 ','224-7-12 ',5.00,188.2000,1035.10),
(1,'2010-03-30 00:00:00.000','wby-71au ','WG300WIN ',10.00,365.9545,4025.50),
(1,'2010-03-30 00:00:00.000','wby-71au ','CAT-WBY10 ',1050.00,0.0000,0.00),
(1,'2010-04-06 00:00:00.000','wby-67-2 ','WP1819 ',10.00,3.2473,35.72),
(1,'2010-04-06 00:00:00.000','wby-67-2 ','CAT-WBY10 ',300.00,0.0000,0.00),
(1,'2010-04-08 00:00:00.000','triggers ','RMV204 ',1.00,699.5455,769.50),
(1,'2010-04-08 00:00:00.000','triggers ','WG300WSM ',1.00,377.7273,415.50),
(1,'2010-04-08 00:00:00.000','triggers ','WGS257 ',1.00,514.5455,566.00),
(4,'2010-04-19 00:00:00.000','5625 ','224S-5-14 ',3.00,275.5000,909.15),
(1,'2010-04-23 00:00:00.000','wby-70 ','WA22455 ',5.00,39.6636,218.15),
(4,'2010-04-28 00:00:00.000','5644 ','243-5-10 ',1.00,188.2000,207.02),
(4,'2010-04-28 00:00:00.000','5644 ','224-3-12 ',3.00,188.2000,621.06),
(4,'2010-05-12 00:00:00.000','5665 ','30S-3-10 ',2.00,257.5000,566.50),
(1,'2010-05-12 00:00:00.000','wby-71 nz ','WP653623 ',2.00,285.0227,627.05),
(1,'2010-05-20 00:00:00.000','wby-29bras','WC378 ',6.00,47.0530,310.55),
(1,'2010-05-20 00:00:00.000','wby-42 ','AM240 ',2.00,1189.2000,2616.24),
(1,'2010-05-20 00:00:00.000','wby-42 ','VGD270 ',4.00,439.1545,1932.28),
(1,'2010-05-20 00:00:00.000','wby-28nz ','NRMV223 ',3.00,453.3933,1360.18),
(1,'2010-05-20 00:00:00.000','wby-28nz ','NWG223 ',20.00,393.5100,7870.20),
(1,'2010-05-20 00:00:00.000','fix trr 68','TRRM300WB ',2.00,2297.7273,5055.00),
(4,'2010-05-24 00:00:00.000','5708 ','224-LS-14 ',2.00,188.2000,414.04),
(4,'2010-06-03 00:00:00.000','5748 ','224S-HS-14',1.00,275.5000,303.05),
(1,'2010-06-16 00:00:00.000','wby-72part','WP6810-3 ',1.00,0.0000,0.00),
(1,'2010-06-16 00:00:00.000','wby-72part','WP6211 ',1.00,0.0000,0.00),
(1,'2010-06-17 00:00:00.000','wby-72 ','AM257 ',2.00,1479.0909,3254.00),
(1,'2010-06-17 00:00:00.000','wby-72 ','CAT-WBY10 ',150.00,0.0000,0.00),
(4,'2010-06-29 00:00:00.000','5795 ','224S-7-14 ',2.00,275.5000,606.10),
(4,'2010-06-29 00:00:00.000','5795 ','224S-3-12 ',2.00,275.5000,606.10)
A report with start/end date of june should return accounts 2 and 3 (since we have not purchased from these suppliers in june).
October 13, 2010 at 10:40 pm
You also don't have any sales for acct's 5/6 in June.
How does this work for you?
SELECT c.ACCT_NO, c.L_NAME, sum(IsNull(p.SALE_PRICE,0))
FROM Creditor c
LEFT JOIN Purchases p
ON p.ACCT_NO = c.ACCT_NO
AND p.DATE >= @fromDate
AND p.DATE <= @toDate
GROUP BY c.ACCT_NO, c.L_NAME
HAVING sum(IsNull(p.SALE_PRICE,0)) <= @minPurchase
ORDER BY c.L_NAME
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 11:49 pm
This seems to be exactly what i needed. I integrated your code bit by bit, to see what was happening each step of the way. I found that the isnull call in havingby made a striking difference; it wouldn't display anything otherwise.
Thanks for your help wayne!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply