Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updated datetime)
insert into #blindman (pkey, fkey, updated)
select 1, 'A', '1/1/2006'
UNION
select 2, 'B', '1/1/2006'
UNION
select 3, 'A', '1/2/2006'
UNION
select 4, 'B', '1/2/2006'
UNION
select 5, 'A', '1/4/2006'
UNION
select 6, 'B', '1/2/2006'
UNION
select 7, 'A', '1/3/2006'
UNION
select 8, 'B', '1/3/2006'
UNION
select 9, 'A', '1/5/2006'
UNION
select 10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?Where's the challange?
SELECT *
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )|||Here's a quick and dirty solution that works. I'm not in your leaque, so I'm sure you can clean it up:
CREATE PROCEDURE [dbo].[procBlindman] AS
SELECT pkey, fkey, updated, IDENTITY(int, 1,1) AS Sequence
INTO #temp
FROM blindman Order BY fkey, updated
SELECT *,
(SELECT pkey FROM #temp AS Alias2 WHERE Sequence = (SELECT Min(Sequence) FROM #temp AS Alias
WHERE Alias.Sequence > #temp.Sequence
AND Alias.fkey = #temp.fkey)) AS NextPkey
FROM #temp
GO|||Does SQL2000 vs SQL 2005 matter? OR can you use bells and whistles from either?|||Brett, your solution does not give the correct output. It gives this:pkey fkey updated nextpkey
-- -- ---------------- ---
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULLThe nextpkey for fkey 'A' should be in the order 3, 7, 5, 9. Your solution has record 3 being followed by record 5, when record seven is the next to follow.
pbaldy, your solution may be the one I have to go with, either using a temp table or a hideously large subquery to assign ordinal value, but I'd rather avoid the first method because I want this as a view, and the second method I fear will be very slow.|||Dude,
I'm hurt and dismayed
All you need is an ORDER BY
SELECT a.pkey, a.fkey, a.updated, b.pkey
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )
ORDER BY a.fkey, a.updated
This Returns
pkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULL
(10 row(s) affected)
[code]|||Desired output:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
Brett's outputpkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
One of these things is not like the other.
One of these things is kinda-the-same.
One of these things is not like the other.
Can you tell me now before I finish my song?|||OK, so you want it by date...the comparison then just shift to look at that...
Let me fire up QA...but it's really not that big of a change|||Does this float your boat?SELECT a.pkey, a.fkey, a.updated, Coalesce(Min(b.pkey), Min(c.pkey)) AS nextpkey
FROM #blindman AS a
LEFT JOIN #blindman AS b
ON (b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
LEFT JOIN #blindman AS c
ON (c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))
GROUP BY a.fkey, a.updated, a.pkey
ORDER BY a.fkey, a.updated, a.pkey
-PatP|||A double outer-join with coalesce. Very creative. Definitely an "outside the box" solution. I will try applying it to my actual problem.
Thanks.|||As I suspected, there was a better solution (the plan scales much better for large result sets):SELECT a.pkey, a.fkey, a.updated, Coalesce(
(SELECT Min(b.pkey)
FROM #blindman AS b
WHERE b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
, (SELECT Min(c.pkey)
FROM #blindman AS c
WHERE c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))) AS nextpkey
FROM #blindman AS a
ORDER BY a.fkey, a.updated, a.pkey-PatP|||I missed the challenge(actually I tried ;) today,didnt got the solution).Anyway congratulation Pat.U deserve a :beer:|||OK, and the reason for this is?|||Its a homework assignment.
;)
Actually, the example given is a simplified version of a view I need to develop for a project.|||sounds like you need some table normalization|||Not a normalization issue. The problem is that the sequence of the surrogate key is not in sync with the order of the natural key. But since this is pulling from the source database, it's not a schema I could change anyway.|||And the coding challenge winner is....Pat!
Very impressive SQL, dude.
You should know that while both solutions produced correct results, your first solution generated an "internal sql server error" when added as a subquery. Apparently, too complex for the optimizer to handle.
The second version did run about twice as fast, and shoe-horned into my final code without error. And just so you know this WASN'T a homework assignment, here is the final code. See if you can find your logic in it!
select case ClaimReserve.ReserveType when 'med' then 'M' when 'comp' then 'I' end as LOSS_DETAIL_ID,
Convert(varchar(50), ClaimReserve.fkClaim) as CLAIM_ID,
replace(replace(replace(convert(char(16), ClaimReserve.DateLup, 120), '-', ''), ' ', ''), ':', '') + convert(varchar(20), ClaimReserve.pkClaimReserve) as ACTIVITY_SEQUENCE,
case when ClaimReserve.ReserveAmt = isnull(PTD1.MedicalPTD, 0) + isnull(PTD2.PharmacyPTD, 0) then 'CL'
when PriorClaimReserve.PriorpkClaimReserve is null then 'OC'
else 'RC'
end as ACTIVITY_TYPE_CODE,
ClaimReserve.DateLup as DATE_OF_TRANSACTION,
case when ClaimReserve.ReserveAmt = isnull(MedicalPTD, 0) + isnull(PharmacyPTD, 0) then 0 --CL
when PriorClaimReserve.PriorpkClaimReserve is null then ClaimReserve.ReserveAmt --OC
else ClaimReserve.ReserveAmt - coalesce(PriorClaimReserve.PriorReserveAmt, 0) --RC
end as RESERVE_CHANGE_AMOUNT,
null as TRANSACTION_TYPE_CODE,
null as PAYMENT_AMOUNT
from ClaimReserve
left outer join --PriorClaimReserve
(SELECT Coalesce(
(SELECT Min(b.pkClaimReserve)
FROM ClaimReserve AS b
WHERE b.fkClaim = a.fkClaim
AND b.DateLup = a.DateLup
AND a.pkClaimReserve < b.pkClaimReserve),
(SELECT Min(c.pkClaimReserve)
FROM ClaimReserve AS c
WHERE c.fkClaim = a.fkClaim
AND c.DateLup =
(SELECT Min(d.DateLup)
FROM ClaimReserve AS d
WHERE d.fkClaim = a.fkClaim
AND a.DateLup < d.DateLup))) AS pkClaimReserve,
a.pkClaimReserve as PriorpkClaimReserve,
a.ReserveAmt as PriorReserveAmt
FROM ClaimReserve AS a) PriorClaimReserve
on ClaimReserve.pkClaimReserve = PriorClaimReserve.pkClaimReserve
left outer join --PTD1
(Select CashFlow.fkClaim,
sum(Voucher.Amount) as MedicalPTD
from Voucher
inner join CashFlow on CashFlow.pkCashFlow = Voucher.fkCashFlow
Where Voucher.Status <> 'Reversed'
and Voucher.ParentType = 'BillHeader'
group by CashFlow.fkClaim) PTD1
on ClaimReserve.fkClaim = PTD1.fkClaim
left outer join --PTD2
(Select Cashflow.fkClaim,
isnull(sum(Voucher.Amount), 0) as PharmacyPTD
from Cashflow
inner join Voucher on Cashflow.pkCashFlow = Voucher.fkCashFlow
inner join ExpenseHeader
on Voucher.fkParent = ExpenseHeader.pkExpenseHeader
and Voucher.ParentType = 'ExpenseHeader'
Where ExpenseHeader.ExpenseType = 4
and Voucher.Status <> 'Reversed'
group by CashFlow.fkClaim) PTD2
on ClaimReserve.fkClaim = PTD2.fkClaim
where ClaimReserve.ReserveType not in ('exp', 'legal')
Showing posts with label challenge. Show all posts
Showing posts with label challenge. Show all posts
Thursday, March 22, 2012
A challenging query about grouping and order
Greetings !!
I am facing a challenge and hope some one can help me with the query.
I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:
Section A
Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3
Student 4 Roll no 4 and so on.
Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A
So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.
How do I write a query for this. I need to run this as a batch process every fortnight.
Thanks
Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.
Solution for SQL Server 2005,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
;WITH CTE
as
(
Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo
from #data
)
Update
CTE
Set
RollNo = NewRollNo
Where
RollNo <> NewRollNo
|||For SQL Server 2000,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
Update #data
Set
RollNo = (Select Count(*) From #data Sub
Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)
|||Thanks Manivannan for the solution. I will try the solution and revert back.
Regards,
Lalit
|||Hi,
Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.
I hope you help me out. Thanks a ton Manivannan.
|||The above query is capable to handle multiple calssrooms also..
A challenging query about grouping and order
Greetings !!
I am facing a challenge and hope some one can help me with the query.
I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:
Section A
Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3
Student 4 Roll no 4 and so on.
Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A
So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.
How do I write a query for this. I need to run this as a batch process every fortnight.
Thanks
Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.
Solution for SQL Server 2005,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
;WITH CTE
as
(
Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo
from #data
)
Update
CTE
Set
RollNo = NewRollNo
Where
RollNo <> NewRollNo
|||For SQL Server 2000,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
Update #data
Set
RollNo = (Select Count(*) From #data Sub
Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)
|||Thanks Manivannan for the solution. I will try the solution and revert back.
Regards,
Lalit
|||Hi,
Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.
I hope you help me out. Thanks a ton Manivannan.
|||The above query is capable to handle multiple calssrooms also..
Tuesday, March 20, 2012
A Challenge: Check Layout
I have a questions for those familiar with the tools and work-arounds of SSRS 2005. My task is to produce a report that has page(s) consisting of three parts that are equal divided vertically amoung the 11 inches of the report height. The first section on the top section has a a check on it. The second and third sections have two seperate yet identical tables.
The trick for this is that each table (in sections part two and three) must not leak into each other's section. Essentially each section has X amount of rows that are shown on each page with the next X amount of rows on seperate pages after that. The problem, as you can most likely guess, is that you cannot force a table to continue only into it's appriopriate section on the next page. I've tried many, many combinations of tricks to try and trick SSRS into working. I know that SSRS most likely doesn't support this directly, but I'm looking for a genius or just anybody who might have a work-around. I'll update this as I find out more. Please feel free to ask for more of an explaination if needed. Thanks!
Mguvu,
I can think of only one way to accomplish this. You'll need to limit the number of row returned by the data set by using a Top (X) clause in the select statement to return only the first few rows. To display the remaining row on a following page will require a filter of some sort on another data set. Not knowing your data, I can't say for sure how to do it, but I'm assuming you are sorting the data set by date.You might try creating the data set with enumerated rows then filtering out the rows displayed in the first table.
Subscribe to:
Posts (Atom)