I have 2 sql commands that run in under 3 minutes in sql as follows, the problem I am having is when I pull them into Crystal the run and hang forever, I have killed it everytime after an hour, I am linking them together on the d.yearmonthnum, I have tried linking every possible way, can anybody see any glaring reason why this won't work??? Thanks in advance.
SQL 1
SELECT d.yearmonthnum,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Paid1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Paid1
else 0
end) AS Paid,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Fee1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Fee1
else 0
end) AS Fees,
SUM (Case when ph.batchtype = 'DAR' then -PH.Paid1
when ph.batchtype = 'DA' then ph.Paid1
else 0
end) AS Adjustments,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.PayHistory PH (NOLOCK)
ON M.Number = PH.Number
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob
SQL 2
SELECT d.yearmonthnum, SUM(M.Original1) AS PlaceAmt,
Count(M.Number) AS PlaceAccts,
SUM(case when s.statustype <> '1 - Closed' Then m.current1 else 0 end) as activeamt,
SUM(case when s.statustype <> '1 - Closed' then 1 else 0 end) as activeaccts,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as closedamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as closedacct,
SUM(case when S.Code = 'PIF' then m.current1 else 0 end) as pifamt,
SUM(case when S.Code = 'PIF' then 1 else 0 end) as pifacct,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as withdrawnamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as withdrawnacct,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
INNER JOIN dbo.Status S (NOLOCK)
ON M.Status = S.Code
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob
SQL 1
SELECT d.yearmonthnum,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Paid1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Paid1
else 0
end) AS Paid,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Fee1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Fee1
else 0
end) AS Fees,
SUM (Case when ph.batchtype = 'DAR' then -PH.Paid1
when ph.batchtype = 'DA' then ph.Paid1
else 0
end) AS Adjustments,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.PayHistory PH (NOLOCK)
ON M.Number = PH.Number
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob
SQL 2
SELECT d.yearmonthnum, SUM(M.Original1) AS PlaceAmt,
Count(M.Number) AS PlaceAccts,
SUM(case when s.statustype <> '1 - Closed' Then m.current1 else 0 end) as activeamt,
SUM(case when s.statustype <> '1 - Closed' then 1 else 0 end) as activeaccts,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as closedamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as closedacct,
SUM(case when S.Code = 'PIF' then m.current1 else 0 end) as pifamt,
SUM(case when S.Code = 'PIF' then 1 else 0 end) as pifacct,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as withdrawnamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as withdrawnacct,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
INNER JOIN dbo.Status S (NOLOCK)
ON M.Status = S.Code
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob