J Livingston SQL (8/12/2016)

---

drew.allen (8/12/2016)

---

This is actually an improved version of the test harness. I am putting a slight edge on credits, so that they should appear 60% of...

This is actually an improved version of the test harness. I am putting a slight edge on credits, so that they should appear 60% of the time. I...

I did a quick comparison of J. Livingston's solution and my solution with 10,000 records. J. Livingston's solution did not account for multiple customers, whereas mine did. I...

I found a faster solution (at least on this small dataset).

;

WITH totals AS (

SELECT *, ABS(Amount) AS Amt, SUM(ABS(Amount)) OVER(PARTITION BY Customer, TransactionType ORDER BY Date ROWS UNBOUNDED PRECEDING )...

First he doesn't post enough data to get from his sample data to his expected results, then when I post as much of a solution as possible given the sparse...

It was as close as I could get with the original data provided. Incorporating the additional data is fairly straightforward and is left as an...

Your sample data has no OrderID or Qty, so it's not clear how to get those values. I suspect that you want an UNPIVOT, although I prefer the alternate...

The problem here is that dates without times represent midnight on that date, so when you are looking for records between your two parameters, you are excluding records that fall...

No, it's not. Consider the following example:

-- THIS IS FOR EXAMPLE

DECLARE @TradeRoles VARCHAR(100)

SET @TradeRoles = '2,3,5,14'

-- If the user has role of 1&4 only then Proceed for furhter processing

IF...

Sometimes sarcasm is the only option http://www.sqlservercentral.com/Forums/FindPost1808418.aspx.

Drew

fafful (8/10/2016)

---

Thanks. I made a change to the syntax and the Jobno was not retrieved:

Update B

set B.[Processed] =

case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=1)

Then 'YES'

else 'NO'

End,

TB.[Jobno] =

case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=0)

Then T.[XMLResponse].value('(/Boot/Jobs/Job_entered/@Jobno[1]','varchar(255)')

End

from Table_B T inner join...

You want to do something like the following, which uses a CTE with a ROW_NUMBER and then filters based on the ROW_NUMBER.

;

WITH dataset AS (

SELECT *, ROW_NUMBER() OVER(PARTITION BY dataset.user_id...

There were actually two problems. twin.devil corrected both, but only mentioned one. The second problem is that jobno is an attribute, so you need to use "@" to...

I'm sorry, perhaps I should have been clearer. When I said to post data as specified in the link, I meant the method under the title The Correct Way...

As long as you're under 24 hours you can use the following.

DECLARE @duration_sec INT = 1300

SELECT DATEADD(SECOND, @duration_sec, CAST('00:00' AS TIME))

This method should work for any unit of time as...