June 11, 2008 at 3:22 pm
Hi experts,
Can I do the following:
I have a report that joins three tables and someone wants to add a column to the output. Tables A, B, and C share a key XX which is unique in Table A but not B or C. Tables B and C share a key YY which is unique in Table B but not in Table C. The new data column STR_DATA desired in the output is in Table C. For each one or more XX-YY records in Table C there is different/identical data in each STR_DATA column.
If STR_DATA were numerical I could sum it and group by XX-YY but what can I do since it is string data?
Thanks very much for your help.
Warm regards,
June 11, 2008 at 3:40 pm
This looks like a story problem. This sounds pretty simple, but you'll have to help out a bit with some sample data and desired results. See the link below for good posting ettiquete.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2008 at 4:03 pm
Hi,
Here is the current stuff with the tables B and C (Payroll and PayrollShipping) commented out and the data (all strings) I used to get from them now sends NULL to the report.
SELECT
CI.fkProjectID as ProjectID,
CONVERT(varchar(10),GETDate(),101) AS CurrDate,
ISNULL(CI.ClientNumber,'') AS ClientNumber,
ISNULL(CI.PayPlusID,'') AS PayPlusID,
UPPER(REPLACE(PRO.ProjectName, '-CLIENT', '')) AS ClientName,
UPPER(RTRIM(ISNULL(CI.DBA,''))) AS DBA,
CONVERT(varchar(10),ClientEffectiveDate,101) AS StartDate,
CONVERT(varchar(10),ReInstateDate,101) AS ReinstateDate,
CONVERT(varchar(10),ContractDate,101) AS ContractDate,
CONVERT(varchar(10),RenewalDate,101) AS RenewalDate,
CONVERT(varchar(10),TerminationDate,101) AS TermDate,
CONVERT(varchar(10),DateTerminated,101) AS TermProcessed,
CASE WHEN (CI.Status)='9 - Termed Client'
THEN CONVERT(decimal,(CT.TerminationDate-CI.ClientEffectiveDate))/365
ELSE CONVERT(decimal,(Getdate()-ClientEffectiveDate))/365 END AS Tenure,
ISNULL(CT.ReasonForTerm,'') AS TermReason,
dbo.fn_nw_UserLookup(CT.Followup)as FollowUp,
ISNULL(CT.Notes,'') as TermNotes,
RTRIM(ISNULL(CI.Address,'')) AS Add1,
RTRIM(ISNULL(CI.Address2,'')) AS Add2,
RTRIM(ISNULL(CI.City,'')) AS City,
RTRIM(ISNULL(CI.State,'')) AS State,
RTRIM(ISNULL(CI.Zip,'')) AS Zip,
RTRIM(ISNULL(CI.Address,''))+' '+RTRIM(ISNULL(CI.Address2,'')) AS Street,
RTRIM(ISNULL(CI.City,''))+', '+RTRIM(ISNULL(CI.State,''))+' '+RTRIM(ISNULL(CI.Zip,'')) AS Location,
RTRIM(ISNULL(CI.County,'')) AS County,
ISNULL(CI.Status,'') AS Status,
ISNULL(CI.Phone1,'') AS Phone,
ISNULL(CI.Phone2,'')AS Phone2,
ISNULL(CI.FAX,'') AS Fax,
ISNULL(CI.PESEntity,'') AS PESEntity,
CASE
When CI.fkCarrierID=1 THEN 'HRC'
WHEN CI.fkCarrierID=2 THEN 'UBI'
WHEN CI.fkCarrierID=3 THEN 'Carve Out'
End As Carrier,
RTRIM(ISNULL(CI.BusOwner,'')) AS Owner,
RTRIM(ISNULL(CI.FEIN,'')) AS FEIN,
RTRIM(ISNULL(CI.NAICSCodes,''))AS NAICS,
RTRIM(ISNULL(CI.SICCode,'')) AS SIC,
RTRIM(ISNULL(CI.Industry,''))AS Industry,
RTRIM(ISNULL(CI.ProcessCenter,''))AS Office,
ISNULL(US.FirstName,'')+' '+ISNULL(US.LastName,'') AS SalesRep1,
ISNULL(US2.FirstName,'')+' '+ISNULL(US2.LastName,'') AS SalesRep2,
ISNULL(CON.FirstName,'')+' '+ISNULL(CON.LastName,'') AS Broker,
ISNULL(US6.FirstName,'')+' '+ISNULL(US6.LastName,'') AS SafetyRep,
ISNULL(US4.FirstName,'')+' '+ISNULL(US4.LastName,'') AS AcctMgr,
ISNULL(US3.FirstName,'')+' '+ISNULL(US3.LastName,'') AS BenefitsRep,
NULL AS Team, --ISNULL(PR.Team, '') AS Team,
NULL AS HrsSubmitby1, --ISNULL(PR.HrsInVia1,'') AS HrsSubmitby1,
NULL AS PayFreq1, --ISNULL(PR.PayFreq1,'') AS PayFreq1,
NULL AS CheckDate1, --ISNULL(PR.CheckDate1,'') AS CheckDate1, --day of week
NULL AS CallDate1, --ISNULL(PR.CallDate1,'') AS CallDate1, --day of week
NULL AS DelDay1, --ISNULL(PS.DelDay1,'') AS DelDay1, --day of week
NULL AS PayType1, --ISNULL(PS.PayType1,'') AS PayType1,
NULL AS DelMeth1, --ISNULL(PS.DelMeth1,'') AS DelMeth1,
ISNULL(US5.FirstName,'')+' '+ISNULL(US5.LastName,'') AS PaylRep,
ISNULL(US7.FirstName,'')+' '+ISNULL(US7.LastName,'') AS PayMGR,
ISNULL(CI.RelateClient,'')AS RelatedClient,
ISNULL(CI.PrimLang,'')AS PrimLang,
ISNULL(CI.StateUCT,'')AS StateUCT,
ISNULL(CI.FaxHeader,'')AS FaxHeader,
ISNULL(CI.Website,'')AS Website,
ISNULL(CI.Email,'')AS Email,
ISNULL(CI.SpecialStatus,'')AS SpecialStatus,
CONVERT(varchar(10),GLRDate,101) AS GLRenewDate,
ISNULL(CI.GLCert,'')AS GLCert,
ISNULL(CI.DFWP,'')AS DFWP,
ISNULL(CI.EAP,'')AS EAP,
ISNULL(CI.Handbook,'')AS Handbook,
ISNULL(CI.BookAttach,'')AS BookAttach,
ISNULL(CI.BackScreen,'')AS BackScreen,
ISNULL(CI.CorpStructure,'')AS CorpStructure,
ISNULL(CI.Policy,'')AS Policy,
ISNULL(CI.MasterPolicy,'')AS MasterPolicy,
ISNULL(CI.WCLimits,'')AS WCLimits,
CI.YearsinBusiness AS YearsinBus,
ISNULL(CI.BankPreference,'')AS Bank,
ISNULL(CI.NumofW2sPriorYear,'')AS W2sPriorYear,
ISNULL(CI.NumofCommVehicles,'')AS CommVehicles,
ISNULL(CI.NumofLocations,'')AS NumofLocations,
ISNULL(CI.Acquired,'')AS Acquired,
CASE
WHEN CI.Status ='8 - Active Client' THEN dbo.fn_EE_Count_Active(CI.fkProjectID)
WHEN CI.Status ='9 - Termed Client' THEN dbo.fn_EE_Count_Term_End(CI.fkProjectID)
END AS EECount
FROM gen_CompanyInformation CI
LEFT JOIN Projects PRO ON CI.fkProjectID = PRO.ProjectID
--LEFT JOIN gen_Payroll PR ON CI.fkProjectID = PR.fkProjectID
--LEFT JOIN gen_PayrollShipping PS ON CI.fkProjectID = PS.fkProjectID
LEFT JOIN Users US ON CI.SalesRep = US.UserID
LEFT JOIN Users US2 ON CI.SalesRep2 = US2.UserID
LEFT JOIN Users US3 ON CI.BenefitsRep = US3.UserID
LEFT JOIN Users US4 ON CI.AcctMgr = US4.UserID
LEFT JOIN Users US5 ON CI.PayRep = US5.UserID
LEFT JOIN Users US6 ON CI.Safety = US6.UserID
LEFT JOIN Users US7 ON CI.Safety = US7.UserID
LEFT JOIN tblContact CON ON CI.BrokerID = CON.pkContactId
LEFT JOIN gen_ClientTermination CT ON CI.fkProjectID = CT.fkProjectID
WHERE
(((ClientEffectiveDate) >=@ClientEffectiveDate_From OR @ClientEffectiveDate_From IS NULL)
AND ((ClientEffectiveDate) <=@ClientEffectiveDate_To OR @ClientEffectiveDate_To IS NULL))
AND (PESEntity = @PESEntity or @PESEntity IS NULL)
AND (ProcessCenter = @ProcessCenter or @ProcessCenter IS NULL)
AND (AcctMgr = @AcctMgr OR AcctMgr =''OR @AcctMgr IS NULL)
AND (BenefitsRep = @BenefitsRep OR BenefitsRep =''OR @BenefitsRep IS NULL)
AND (Safety = @Safety OR Safety =''OR @Safety IS NULL)
AND (SalesRep = @SalesRep OR SalesRep =''OR @SalesRep IS NULL)
AND (PayrollMGR = @PayrollMGR OR PayrollMGR =''OR @PayrollMGR IS NULL)
AND (CI.PayRep = @PayRep OR CI.PayRep = '' OR @PayRep IS NULL)
AND (Team = @Team or @Team IS NULL)
AND (Status = @Status or @Status IS NULL)
AND (CI.Status ='8 - Active Client'or CI.Status ='9 - Termed Client')
June 11, 2008 at 4:30 pm
June 11, 2008 at 4:52 pm
Hi John,
Okay, but I have been working late and I'm going home now. I will post something from home. I may decide to shorten the number of columns being gathered
Warm regards,
June 12, 2008 at 4:29 am
Easy answer, of course you can group by the STR_DATA column, this will have the effect of returning the rows aggregated by XX-YY AND the distinct values in the STR_DATA column!!
If this is not what you want then you are pretty limited on what else you can do.
You could always use aggregate function on STR_DATA to return either the MAX(STR_DATA) value, or the MIN.
Depends on what your users want to see......
June 12, 2008 at 6:37 am
Thank you bobjbain. I will have to go the extra steps with John because I really do want one row. Now that the structure of the database is changing one-to-many this probably won't be the only time I want to do this sort of thing. I'm hopeful.
June 12, 2008 at 7:10 am
Another option is to write a function (that you pass XX and YY to) that returns a comma delimited list of the distinct STR_DATA values in 'C'.
You can then call the function in your select statement.
Might have an impact on performance though!!
Again, it all depends on what your users want to see
June 12, 2008 at 7:26 am
bobjbain that sounds perfect. I only need to pass key XX since all tables and their related rows have XX. If a row in Table C has key XX then I want to collect column STR_DATA into a comma delimited list right in the select statement. I have not ever written a function let alone one that returns a comma delimited list. Would you have a cookbook type example that I could learn from?
Table C is not a terribly large table, about 3,500 records and this report is generally run once a month so it shouldn't be too bad at all.
Warm regards,
June 12, 2008 at 7:43 am
Try something like this, you'll obviously have to change table names and column names but it should do what you want.
It also will only return a max of 1000 characters (no warning if the string length exceeds this.....). You can change this to up to 8000...
it uses cursors too, I'm sure some people may come back with a more 'efficient' way of doing it :-
alter function str_data_list(@keyvalue int) returns varchar(1000) as
begin
declare @retstr varchar(1000)
declare @str_data varchar(100)
declare @cVals cursor for
select distinct str_data
from table_c
where xx = @keyvalue
set @retstr = ""
open @cVals
while 1=1 begin
fetch next from @cVals into @str_data
if @@fetch_status <> 0 break
if @retstr <> ""
@retstr = @retstr + ',' + @str_data
else
@retstrt = @str_date
end
close @cVals
return @retstr
end
once you've done that you just call the function as follows :-
select dbo.str_data_list(xx)
from table_a
June 12, 2008 at 8:00 am
Thank you for the clear example. I should have no problems and I can try it out later today. I too am curious to see the performance of cursors. If anyone does have another method I am sure they will propose it and we can both learn. Thank you so very much, you have been very kind and generous with your time and abilities. It's a good day and it's not even Friday.
Warm regards,
June 12, 2008 at 8:44 am
You don't need a cursor to do this. Keep in mind this has not been tested as it is based off of the last example:
alter function str_data_list(@keyvalue int) returns varchar(1000) as
begin
declare @retstr varchar(1000)
declare @str_data varchar(100)
set @retstr = ''
select @retstr = @retstr + COALESCE(str_data,'')
from table_c
where xx = @keyvalue
GROUP BY str_data
return @retstr
end
June 12, 2008 at 9:03 am
nice, knew there was a better way, just couldn't remember it (ORACLE has been LIFOing my SQL Server ATM)
However you did forget the ',' so
select @retstr = @retstr + case when @retstr <> '' then ',' else '' end + coalesce(str_data, '')
June 12, 2008 at 9:33 am
June 13, 2008 at 10:58 am
And here is one without cursors:
CREATE FUNCTION fGetSTR_DATAList (@XX INT, @YY INT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @STR_DATAList VARCHAR(1000)
SELECT @STR_DATAList = ISNULL(@STR_DATAList + ', ', '') + TableC.STR_DATA
FROM
WHERE TableC.XX=@XX AND TableC.YY=@YY
RETURN @TrainerList
END
And then, as with the previous soultion, you use the function as you would a field name in a SELECT:
SELECT TableA.Field1, TableB.Field3, TableC.Field9, fGetSTR_DATAList (TableC.XX,TableC.YY)
FROM
I give thanks to the guy who showed this to me last year - life saver. I'm happy to be able to pass it on.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply