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

Sql report - inactive suppliers Expand / Collapse
Author
Message
Posted Wednesday, October 13, 2010 8:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:17 PM
Points: 13, Visits: 112
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


Post #1004093
Posted Wednesday, October 13, 2010 9:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:17 PM
Points: 13, Visits: 112
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).
Post #1004101
Posted Wednesday, October 13, 2010 10:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 6,582, Visits: 8,861
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1004113
Posted Wednesday, October 13, 2010 11:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:17 PM
Points: 13, Visits: 112
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!
Post #1004137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse