June 16, 2008 at 4:36 pm
Look up CROSS APPLY. in 2005 that will do what you want it to do.
Edit: never mind - if you're doing the SCALAR option (have the function return the count), just use it directly in the select.
Select lcID, dbo.myfunction(myCSVColumn,';',9) from MyTable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 4:38 pm
Matt Miller (6/16/2008)
select pk, count(*)
from
(SELECT lc_id pk,
SUBSTRING(','+SelectedDays+',',N+1,
CHARINDEX(',',','+SelectedDays+',',N+1)-N-1) AS Value
FROM dbo.Tally t CROSS JOIN FAS_LaborCalendar mh
WHERE N < LEN(','+SelectedDays+',') AND SUBSTRING(','+SelectedDays+',',N,1) = ',') t
where value>9
group by pk
after doing so I got my result as
PK (No Column Name)
18
28
38
49
512
69
79
88
99
119
128
148
158
168
178
188
198
208
218
I understand it gave me the count > 9 in the second column, how can add this result to my table with rest of the columns, I tried inner join but didnt work.
thanks
June 16, 2008 at 4:40 pm
Post what your inner join looks like - it should work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 4:47 pm
CREATE TABLE #tempdays (
PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
days VARCHAR(255)
)
INSERT INTO #tempdays select SelectedDays from FAS_LaborCalendar
--select * from #tempdays
select pk,count(*) from (
SELECT lc_id pk,SUBSTRING(';'+SelectedDays+';',pk+1, CHARINDEX(';',';'+SelectedDays+';',pk+1)-pk-1) AS Value
FROM dbo.#tempdays t cross JOIN FAS_LaborCalendar mh
WHERE pk < LEN(';'+SelectedDays+';') AND SUBSTRING(';'+SelectedDays+';',pk,1) = ';'
)t
inner join FAS_LaborCalendar fl on t.pk on fl.LC_Id
where Value >19
group by pk
DROP TABLE #tempdays
I am not sure where to add other columns from table FAS_LaborCalendar.
As you have seen my table structure, I want a result set where I can show the count >9 in an additional column which will be temp just to show my result.
June 16, 2008 at 5:15 pm
You're just about there:
CREATE TABLE #tempdays (
PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
days VARCHAR(255)
)
INSERT INTO #tempdays
select SelectedDays from FAS_LaborCalendar
select fl.*, daysleft
from
FAS_LaborCalendar fl
LEFT OUTER join
(select pk,count(*) daysLeft from
(
SELECT lc_id pk,
SUBSTRING(';'+SelectedDays+';',pk+1, CHARINDEX(';',';'+SelectedDays+';',pk+1)-pk-1) AS Value
FROM dbo.#tempdays t cross JOIN FAS_LaborCalendar mh
WHERE pk < LEN(';'+SelectedDays+';') AND
SUBSTRING(';'+SelectedDays+';',pk,1) = ';'
) tmp
where Value >19
group by pk
) t
on t.pk on fl.LC_Id
DROP TABLE #tempdays
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply