August 28, 2016 at 7:09 pm
i have a table that has a type column to indicate record type for our software.
table1
t_col1 <---- CUSTOMER NUMBER
t_col2 <---- integer incremental value
t_type <---- 'A', 'P', B', 'L', 'M', 'X', 'V',....
I am needing a way to query the table to only get all the rows until a 'B' type is found but if the first row is a 'B' then I need to find all the rows to the next 'B' type record.
row 1 type = 'P'
row 2 type = 'A'
row 3 type = 'L'
row 4 type = 'V'
row 5 type = 'P'
row 6 type = 'B'
row 7 type = 'P'
...
i need to just query rows 1-6 and stop for that customer
HOWEVER, if row 1 type = 'B' i would need to query 1 thru 6 or till i find the next 'B' row type.
August 28, 2016 at 9:44 pm
First things first, since you clearly missed it.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
If you post some consumable data, this probably isn't that hard. Something like a
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SomeDate)
would give you a sequential numbering... then you can just find the nth one or whatever (just filter for it in your WHERE clause).
August 29, 2016 at 12:32 am
roy.tollison (8/28/2016)
I am needing a way to query the table to only get all the rows until a 'B' type is found
Assuming the order of rows is defined by auto-incremental t_col2.
1. " a 'B' type is found" - means you know min value in t_col 2 for the rows of 'B' type.
Must be easy to find.
2. "all the rows until" - means rows with t_co2 less than the value found in (1).
Should not cause any difficulties too.
3. to search for "next" 'B' type row when the firs row is 'B' type you need to add to the query 1 an extra condition:
WHERE t_col2 > (select MIN(t_col2) from TheTable)
Hope it helps.
_____________
Code for TallyGenerator
August 29, 2016 at 6:19 am
I'm curious, since you have a customer number already to group on, why don't you just ignore type 'B' rows?
WHERE t_col1 <> 'B'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2016 at 7:39 am
basically this is a utility billing transaction history table.
'B' = billing record.
'P' = Payment record
'A' = Adjustment record
and so on.
so now i am trying to come up with a way to find the LAST bill amount which would be the first 'B' record. then i am needing to sum/group all of the other types.
needing a summary of all the transactions that have occurred since the last bill. if the first row found is a bill type then summarize back to the next bill.
NOTE: The t_col2 integer value is written by our software in reverse order so 999 is the very first entry, then 998,997,996. the min value is always the latest transaction entry not the max value.
August 29, 2016 at 1:48 pm
Have a look at this.
declare @sample table (Custno int, RowID int identity(1000,-1), RowType char(1))
insert into @sample (custno, rowtype)
select top 100 N, RowType
from vTally
cross apply (values ('P')
,('A')
,('L')
,('V')
,('P')
,('B')
) v (rowType)
update @sample
set custno = 1
where custno = 8
select * from @sample
order by custno,-RowID;
with cte as (select CustNo,RowID,RowType
,ROW_NUMBER() over(order by custno, RowID) as SimpleSeq
,SUM(CASE when RowType = 'B' then 0 else 1 end)
OVER(order by Custno,RowID ROWS Unbounded preceding) as Testsum
from @sample)
select *,SimpleSeq - TestSum as GroupBy
from cte
order by custno asc, RowID asc
This code calculates a value in the GroupBy column that tells you all the transactions that belong to the same "B" row according to what I understood. Please tell me if the code above is good enough for your purposes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2016 at 2:01 pm
The code above was modified to remove an excess payment row.
And to change the final presentation order.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2016 at 2:15 pm
this gives me something to work with thanks...
August 29, 2016 at 4:46 pm
roy.tollison (8/29/2016)
this gives me something to work with thanks...
What do you want to work on?
You've been given the solution.
All you need to do is to translate it into SQL.
EzipayEmailList
1. " a 'B' type is found" - means you know min value in t_col 2 for the rows of 'B' type.
Must be easy to find.
declare @LastB_ID int
select @LastB_ID = MIN(t_col2)
FROM TeTable
WHERE t_Type = 'B'
3. to search for "next" 'B' type row when the firs row is 'B' type you need to add to the query 1 an extra condition:
WHERE t_col2 > (select MIN(t_col2) from TheTable)
declare @LastB_ID int
select @LastB_ID = MIN(t_col2)
FROM TeTable
WHERE t_Type = 'B'
AND t_col2 > (select MIN(t_col2) from TheTable)
2. "all the rows until" - means rows with t_co2 less than the value found in (1).
Should not cause any difficulties too.
declare @LastB_ID int
select @LastB_ID = MIN(t_col2)
FROM TeTable
WHERE t_Type = 'B'
AND t_col2 > (select MIN(t_col2) from TheTable)
SELECT * FROM TheTable
WHERE t_col2 < @LastB_ID
-- exclude the possible 1st "billing" record
AND NOT (t_Type = 'B')
What do you intend to work on here?
_____________
Code for TallyGenerator
August 30, 2016 at 8:45 am
My apologies, i do have the working solution. What i meant by 'something to work on' is that i have other fields in the table that i need to include in the query along with a couple of joins.
Again sorry for the confusion.
August 30, 2016 at 11:37 am
I certainly don't require an apology. Glad it works for you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply