Combining full text search results with index server/service

I have a solutions database that I'm setting up Full text search on. Part of
the "solutions" is a huge folder of attachments on the lan.
I've done both FTS alone and also Index server alone.
Are there any whitepapers or good websites that talk about combining the
two? I'd like to conduct the searches via sql server - ideally expanding the
full text index to include the content of the files on the lan.
- Jack
Please refer to the above post.
There is no white paper per se focusing on this. However you might want to
check out this paper which does touch on it.
http://msdn.microsoft.com/library/de...filedatats.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"jack" <jack@.discussions.microsoft.com> wrote in message
news:10938D81-5E92-483F-ABC1-208DDF331112@.microsoft.com...
> I have a solutions database that I'm setting up Full text search on. Part
of
> the "solutions" is a huge folder of attachments on the lan.
> I've done both FTS alone and also Index server alone.
> Are there any whitepapers or good websites that talk about combining the
> two? I'd like to conduct the searches via sql server - ideally expanding
the
> full text index to include the content of the files on the lan.
> - Jack

Combine 3 huge queryies to produce another SUM

I have to combine 3 huge queries to get a GT for ProjGross (see calcs.xls on formulas). Please check out ProjGross here
http://www.photopizzaz.biz/projgross.txt
and see this for the logic for projgross
http://www.photopizzaz.biz/calcs.xls
I am trying to combine (Daily Run Rate * Days Remaining )+ InHouse1 as (stated in the xls) using the previous select statements respectively to get my final ProjGross

All queries so far in my projgross.txt work great...now I just need to combine what's needed to move forward in my calcs.xls to create ProjGross...at the Company level.

You'll see that I attempted to start the ProjGross Query in my .txt file

I have rewritten INHOUSE1 (START) as follows:

SELECT sub.CustomerNumber, sub.PostedAmount + sub.CCsOld + sub.CCsNew + sub.PDCsNew + sub.PDCsOld
FROM
( Select d0.CustomerNumber AS CustomerNumber,
CASE WHEN d0.Type = 'In-House'
THEN SUM(ISNULL(d0.PostedAmount,0))
ELSE 0
END AS PostedAmount,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsOld,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsOld
FROM DCR d0 With(NOLOCK)
JOIN CustomerNumber cno
ON cno.customernumber = d0.customernumber
AND (d0.branch = '00002' AND cno.CheckBranch = 1)
OR (cno.CheckBranch = 0)
WHERE d0.Type = 'In-House'
GROUP BY d0.CustomerNumber
) AS sub

You should be able to get the rest using the same principle. You are using the same table each time, so all this can be done in one query, albeit a *** of a query, and you can do away with the ...customernumber in... by joining to that table, which should boost peformance a lot. Let me know if you have any more problems. That should work, but you may get a compilation errors, as I do not have the db here in front of me to test.

|||yea but is it going to go through each Case? each case statement will evaluate to true because there are records for each instance that you're checking so I don't think CASE is good here..only one can evaluate true in a CASE|||

I've actually already solved it on my own just earlier today. Thanks for your input though!

Select @.ProjFee = SUM(ProjFee)

FROM

(

Select d1.CustomerNumber, ((((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0)) / (select CurrentPostingDay from v_CurrentPostingDay))) + ((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsOldTable.OldCCs,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0) + ISNULL(PDCsOldTable.OldPDCs,0)))) * d1.FeeSchedule / 100 as ProjFee

FROM (Select distinct CustomerNumber, FeeSchedule From DCR) d1

- $Posted

LEFT JOIN

(Select d0.CustomerNumber, SUM(ISNULL(d0.PostedAmount,0)) AS PostedAmount

FROM DCR d0 (NOLOCK)

WHERE d0.Type = 'In-House'

AND ((d0.branch = '00002' and d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

GROUP BY d0.CustomerNumber) as PostedTable ON PostedTable.CustomerNumber = d1.CustomerNumber

- OldCCs

LEFTJOIN

(Select d3.CustomerNumber, SUM(ISNULL(d3.CC,0)) AS OldCCs

FROM DCR d3 (NOLOCK)

WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d3.Type = 'CC'

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d3.dc_OnHoldDate IS NULL

GROUP BY d3.CustomerNumber) as CCsOldTable ON CCsOldTable.CustomerNumber = d1.CustomerNumber

- NewCCs

LEFTJOIN

(Select d4.CustomerNumber, SUM(ISNULL(d4.CC,0)) AS NewCCs

FROM DCR d4 (NOLOCK)

WHERE d4.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d4.Type = 'CC'

AND ((d4.branch = '00002' and d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d4.dc_OnHoldDate IS NULL

GROUP BY d4.CustomerNumber) as CCsNewTable ON CCsNewTable.CustomerNumber = d1.CustomerNumber

- NewPDCs

LEFTJOIN

(Select d5.CustomerNumber, SUM(ISNULL(d5.PDC,0)) AS NewPDCs

FROM DCR d5 (NOLOCK)

WHERE d5.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d5.Type = 'PDC'

AND ((d5.branch = '00002' and d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d5.pdc_OnHold IS NULL

GROUP BY d5.CustomerNumber) as PDCsNewTable ON PDCsNewTable.CustomerNumber = d1.CustomerNumber

- OldPDCs

LEFTJOIN

(Select d6.CustomerNumber, SUM(ISNULL(d6.PDC,0)) AS OldPDCs

FROM DCR d6 (NOLOCK)

WHERE d6.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d6.Type = 'PDC'

AND ((d6.branch = '00002' and d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d6.pdc_OnHold IS NULL

GROUP BY d6.CustomerNumber) as PDCsOldTable ON PDCsOldTable.CustomerNumber = d1.CustomerNumber

GROUP BY d1.CustomerNumber,

PostedTable.PostedAmount,

CCsOldTable.OldCCs,

CCsNewTable.NewCCs,

PDCsNewTable.NewPDCs,

PDCsOldTable.OldPDCs,

d1.FeeSchedule

) as z

SELECT ProjFee = @.ProjFee

|||Ok, but my query is much simpler, and there are only two cases for each case statement, true, so add value, or false so add 0. Try it and see. It will only add the to the approriate totals for the relevant records, the rest it will ignore by adding 0. The query you have written is way too complicated and thus probably very slow. I would try and simplify it, hopefully my example will help.