October 19, 2009 at 11:53 am
i'm new to t-sql and struggling with something...the scenario is i have a table with ids and records that correspond to those ids...lets say one particular id has 7 records associated with it (similarly there are other records with different ids)...my problem is that i need only a particular number of records for each id(this number also varies for each id and is stored in a separate table).
so to sum it up:
1-get info for particular id from one table (this info includes number of records i need).
2-use above info with another table (this table has all records for all ids) and get particular number of records for one particular id.
ulitmately i'm going to use this SP to generate a crystal report.
i was also able to get this done with code-behind(C#)...but now the requirement is such that i need SP.
thanks
October 19, 2009 at 12:07 pm
Hi there,
One option may be to do a join on the two tables, add a row number and filter on that.
You'll get better answers if you can post some table structures, test data and your expected output. The following article tells you how to do that. http://www.sqlservercentral.com/articles/Best+Practices/61537/
Allister
October 19, 2009 at 12:23 pm
thanks for the prompt reply...i'll get on it and post some sample data ASAP...thanks again.
October 19, 2009 at 12:58 pm
okay...i went over the forum etiquette page and i hope this qualifies as a valid code sample.
IF OBJECT_ID('TempDB..#mytemp','U') IS NOT NULL
DROP TABLE #mytemp
IF OBJECT_ID('TempDB..#mytemp2','U') IS NOT NULL
DROP TABLE #mytemp2
--Create TempTable
create table #mytemp
(
MerchantID int,
MerchantLegalName varchar(20),
RequestAmount decimal,
RequestDate datetime
)
set dateformat dmy
--Insert data into TempTable
insert into #mytemp
(MerchantID,MerchantLegalName,RequestAmount,RequestDate)
Select'109','Test Merchant 12','100','Oct 8 2009 5:46PM' union all
Select'109','Test Merchant 12','200','Oct 8 2009 5:48PM' union all
Select'109','Test Merchant 12','200','Oct 8 2009 5:46PM' union all
Select'109','Test Merchant 12','200','Oct 8 2009 5:46PM' union all
Select'109','Test Merchant 12','200','Oct 8 2009 5:46PM' union all
Select'109','Test Merchant 12','200','Oct 8 2009 5:46PM' union all
Select'110','ABC, Inc.','100','Oct 8 2009 5:46PM' union all
Select'110','ABC, Inc.','100','Oct 8 2009 5:46PM' union all
Select'110','ABC, Inc.','100','Oct 8 2009 5:46PM' union all
Select'254','Test Merchant V3.1','100','Oct 8 2009 5:46PM' union all
Select'254','Test Merchant V3.1','100','Oct 8 2009 5:46PM'
--Create second TempTable
create table #mytemp2
(
MerchantID int,
Invoices int,
)
--Insert data into second TempTable
insert into #mytemp2
(MerchantID,Invoices)
Select'109','2' union all
Select'110','1'union all
Select'254','1'
Select * from #mytemp
Select * from #mytemp2
in light of above data...the second TempTable has Invoices and i need records=total minus invoices.
i hope this helps.
thanks.
October 20, 2009 at 4:58 am
Hi,
Thanks for posting the test data and sorry for delay getting back to you!
Here is a solution, hope it helps.
Allister
SELECT WithRow.MerchantID, MerchantLegalName, RequestAmount, RequestDate
FROM
(
SELECT
MerchantID, MerchantLegalName, RequestAmount, RequestDate,
--This row assigns a number from 1 to n number of invoices per MerchantIDin
--order of request date desc i.e. the newest #mytemp2.Invoices number of invoices are returned
ROW_NUMBER() OVER (PARTITION BY MerchantID ORDER BY RequestDate DESC) AS Row
FROM #mytemp
) WithRow
JOIN #mytemp2 ON #mytemp2.MerchantID = WithRow.MerchantID
WHERE Row <= #mytemp2.Invoices
October 20, 2009 at 6:20 am
no apologies necessary my friend...i've tested your solution and it works great...now i just have to tweak it a bit to suit my needs...thanks again and always!
October 20, 2009 at 6:23 am
Thanks for the feedback!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply