Check this out, this just popped up for me:
Are any of you "aHadoop & Nutch" Enthusiasts? I've never really liked their products...
Check this out, this just popped up for me:
Are any of you "aHadoop & Nutch" Enthusiasts? I've never really liked their products...
I just had to add my two cents...
<FONT color=#0000ff size=2>DECLARE</FONT><FONT size=2> @birthDate </FONT><FONT color=#0000ff size=2>datetime</FONT></FONT><FONT color=#0000ff size=2>
DECLARE</FONT><FONT size=2> @currentDate </FONT><FONT color=#0000ff size=2>datetime
DECLARE</FONT><FONT size=2> @yearDiff </FONT><FONT color=#0000ff size=2>int</FONT></FONT><FONT color=#0000ff size=2>DECLARE</FONT><FONT size=2> @calcDate </FONT><FONT color=#0000ff size=2>datetime
SELECT</FONT><FONT size=2> @birthDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'02-29-1976'</FONT><FONT size=2> </FONT><FONT color=#008000 size=2>-- '06-11-1979'
</FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @currentDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>GETDATE</FONT><FONT color=#808080 size=2>()
</FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @yearDiff </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>YEAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>@currentDate</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>-</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>YEAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>@birthDate</FONT><FONT color=#808080 size=2>)
</FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>DATEADD</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff00ff size=2>year</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @yearDiff</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @birthDate</FONT><FONT color=#808080 size=2>)
</FONT><FONT color=#008000 size=2>-- we'll have the birthday in the current year, if it's already passed then we'll add a year and use next year's date
</FONT><FONT color=#0000ff size=2>IF</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2><</FONT><FONT size=2> @currentDate
</FONT><FONT color=#0000ff size=2>BEGIN
</FONT><FONT size=2></FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>DATEADD</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff00ff size=2>year</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @calcDate</FONT><FONT color=#808080 size=2>)
</FONT><FONT color=#0000ff size=2>END
SELECT</FONT><FONT size=2> @calcDate
</FONT>You can do this in SQL Server, but there are no native functions to do what you want. I have a system here that has a similar relationship to what you are dealing with and put together the following that accomplishes what you want. My system is relating timesheets to invoices, whereas yours is invoices to orders. In my system one invoice can have multiple timesheets associated with it. One of the suggestions was to use program code and that's essentially what I've done here, but done procedural code in SQL Server:
-- table to hold results
DECLARE @results TABLE
(
InvoiceNumber int,
timesheets varchar(MAX)
)
-- temp table for working with raw data
DECLARE @temp1 TABLE
(
InvoiceNumber int,
timesheet_num int,
group_num int,
item_num int
)
-- pull raw data into temp table
insert @temp1
select HEAD.InvoiceNumber, DET.timesheet_num, GRPS.group_num, ROW_NUMBER() OVER(PARTITION BY HEAD.InvoiceNumber ORDER BY HEAD.InvoiceNumber, DET.timesheet_num) [item_num]
from Invoicing.tblInvoiceHeader HEAD (nolock)
inner join (select distinct InvoiceNumber, ROW_NUMBER() OVER(ORDER BY InvoiceNumber) [group_num] from Invoicing.tblInvoiceHeader (nolock)) GRPS on GRPS.InvoiceNumber = HEAD.InvoiceNumber
inner join Invoicing.tblSubInvoice SUB (nolock) on SUB.InvIDKey = HEAD.IDKey
inner join Invoicing.tblInvoiceDetails DET (nolock) on DET.SubInvIDKey = SUB.IDKey
where DET.SourceTypeIDKEy = 1 AND SUB.InvoiceStatus = 1
group by HEAD.InvoiceNumber, DET.timesheet_num, GRPS.group_num
order by HEAD.InvoiceNumber, DET.timesheet_num
-- variables to control loops
DECLARE @groupNum int
DECLARE @maxGroupNum int
DECLARE @itemNum int
DECLARE @maxItemNum int
DECLARE @invoiceNum int
DECLARE @timesheetNum varchar(10)
SELECT @groupNum = 1, @maxGroupNum = (select max(group_num) from @temp1)
WHILE @groupNum <= @maxGroupNum
BEGIN
-- get the invoice number of this group
SELECT @invoiceNum = (select distinct InvoiceNumber from @temp1 where group_num = @groupNum)
-- get max item num for the current group and initialize counter
SELECT @maxItemNum = (select max(item_num) from @temp1 where group_num = @groupNum), @itemNum = 1
-- loop over the items within the current group and concatenate the value into the results table
WHILE @itemNum <= @maxItemNum
BEGIN
SELECT @timesheetNum = CAST((select timesheet_num from @temp1 where group_num = @groupNum and item_num = @itemNum) as varchar(10))
-- for first entry, insert new record; otherwise update existing record
IF @itemNum = 1
INSERT @results (InvoiceNumber, timesheets) VALUES (@invoiceNum, @timesheetNum)
ELSE
UPDATE @results SET timesheets = timesheets + ',' + @timesheetNum
-- increment item counter
SELECT @itemNum = @itemNum + 1
END
-- increment group counter
SELECT @groupNum = @groupNum + 1
END
select * from @results
I apologize if the formatting is all screwy here, but the main idea is using the ROW_NUMBER() function to assign a group number and an item number to each item. You'll see that I assign the group number by joining to a sub query where I use the ROW_NUMBER function; this is because I was having problems doing it in the main query and was content with this alternative as it has the desired effect. Then, there are two loops that iterate over the items and construct a result string of comma-separated values. I've created this and it runs against SQL Server 2005, but it may not work against other versions.