SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql report - inactive suppliers


Sql report - inactive suppliers

Author
Message
dallas-1069889
dallas-1069889
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 139
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
dallas-1069889
dallas-1069889
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 139
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).
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21128 Visits: 10652
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
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

dallas-1069889
dallas-1069889
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 139
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search