January 8, 2014 at 1:59 pm
I have a table composed as the following:
VendorNbr varchar(15),
CCOde varchar(1),
PayDate numeric(8,0),
NetPaid numeric(11,2)
with the following sample data
VendorNbr CCode PayDate NetPaid
AAAAAAAAA 1 20120101 3000.00
AAAAAAAAA 1 20120101 6000.00
AAAAAAAAA 1 20120103 56000.00
AAAAAAAAA 1 20120105 10000.00
I need to determine for each vendor/company code listed what the date was that the netpaid
amount crossed over a pre-defined threshhold ( e.g. 50000.00 )
Any help would be greatly appreciated
January 8, 2014 at 2:03 pm
Well, since I can't run test code since I don't have a sample I can directly use, best I can offer is an idea of the code:
SELECT
vendorno,
MIN(datetime)
FROM
table
WHERE
value >= @threshold
GROUP BY
vendorno
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 8, 2014 at 2:22 pm
Which is probably faster than this, but you get the idea:
WITH SampleData (VendorNbr, CCode, PayDate, NetPaid) AS (
SELECT 'AAAAAAAAA', 1, 20120101, 3000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120101, 6000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120103, 56000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120105, 100000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120101, 3000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120101, 6000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120103, 56000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120105, 100000.00
)
SELECT *
FROM (
SELECT
VendorNbr, CCode, PayDate, NetPaid,
dr = DENSE_RANK() OVER(PARTITION BY VendorNbr, CCode ORDER BY CASE WHEN NetPaid < 50000 THEN NULL ELSE PayDate END)
FROM SampleData
) d
WHERE dr = 2
ORDER BY VendorNbr, CCode, PayDate
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 8, 2014 at 2:58 pm
I'm sure that this is a running total problem that I would love to try with SQL 2012, but I don't have it at work.
There's another option that works great on previous versions as long as you play by the rules. You can find all the information in this article: http://qa.sqlservercentral.com/articles/T-SQL/68467/
And here's an example:
CREATE TABLE RunningTotalTable(
VendorNbr char(10),
CCode int,
PayDate date,
NetPaid decimal(15,2),
RunningTotal decimal(15,2)
)
CREATE CLUSTERED INDEX CX_Test ON RunningTotalTable(VendorNbr, CCode, PayDate)
DECLARE @Paid decimal(15,2) = 0,
@Vendor char(10),
@CCode int
INSERT INTO RunningTotalTable(VendorNbr, CCode, PayDate, NetPaid)
SELECT 'AAAAAAAAA', 1, '20120101', 3000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, '20120101', 6000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, '20120103', 56000.00 UNION ALL
SELECT 'AAAAAAAAA', 3, '20120105', 100000.00 UNION ALL
SELECT 'BBBBBBBBB', 2, '20120101', 3000.00 UNION ALL
SELECT 'BBBBBBBBB', 2, '20120101', 6000.00 UNION ALL
SELECT 'BBBBBBBBB', 2, '20120103', 56000.00 UNION ALL
SELECT 'BBBBBBBBB', 2, '20120105', 100000.00
UPDATE rt SET
@Paid = RunningTotal = CASE WHEN @Vendor = VendorNbr AND @CCode = CCode THEN @Paid ELSE 0 END + NetPaid,
@Vendor = VendorNbr,
@CCode = CCode
FROM RunningTotalTable rt WITH (TABLOCKX)
OPTION( MAXDOP 1)
SELECT *
FROM RunningTotalTable
SELECT VendorNbr, CCode, MIN(PayDate)
FROM RunningTotalTable
WHERE RunningTotal > 50000
GROUP BY VendorNbr, CCode
DROP TABLE RunningTotalTable
January 8, 2014 at 5:53 pm
Luis Cazares (1/8/2014)
I'm sure that this is a running total problem that I would love to try with SQL 2012, but I don't have it at work.
I agree and I think this would be it.
WITH SampleData (VendorNbr, CCode, PayDate, NetPaid) AS (
SELECT 'AAAAAAAAA', 1, 20120101, 3000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120101, 6000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120103, 56000.00 UNION ALL
SELECT 'AAAAAAAAA', 1, 20120105, 100000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120101, 3000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120101, 6000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120103, 56000.00 UNION ALL
SELECT 'AAAAAAAAA', 2, 20120105, 100000.00
)
SELECT VendorNbr, CCode, PayDate, NetPaid, CumNetPaid
FROM
(
SELECT VendorNbr, CCode, PayDate, NetPaid, CumNetPaid, rn1
,rn2=MIN(rn1) OVER (PARTITION BY VendorNbr, CCode)
FROM
(
SELECT VendorNbr, CCode, PayDate, NetPaid
,CumNetPaid=SUM(NetPaid) OVER
(
PARTITION BY VendorNbr, CCode ORDER BY PayDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
,rn1=ROW_NUMBER() OVER (PARTITION BY VendorNbr, CCode ORDER BY PayDate)
FROM SampleData
) a
WHERE CumNetPaid > 50000
) a
WHERE rn1=rn2;
It won't be quite as fast as your QU Luis, but at least no one can claim this is "undocumented" behavior.
Edit: My initial solution missed the part about "for each CCode" so above is corrected.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply