reporting services - SSRS 2012 Join Tables without Joining - Stack Overflow

admin管理员组

文章数量:1395329

I am totally going around in circles with this and wanted to ask for a fresh pair of eyes on this.

This query works fine in that it identifies where the week beginning date in the Main table matches the Date Received in the Sub table. However, this is not completely correct because I am looking for a 'snapshot' as at 01/04/2024, 08/04/2024 etc so I also need the Dates Received before the week beginning, not just on that exact date, so I can show where an application was ongoing as at the week beginning. I know the issue is with the table join at the very bottom but I just cant figure it out so any advice would be hugely appreciated

SELECT        DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, dt)) % 7, CONVERT(date, dt, 103)) AS Week
INTO              [#Main]
FROM            RQIA_Custom_Calendar_View
WHERE        (CONVERT(date, dt, 103) >= DATEADD(year, DATEDIFF(month, 90, CURRENT_TIMESTAMP) / 12, 90)) OPTION (maxrecursion 0);
                         
SELECT        FilteredAccount.rqia_servicetypeidname AS ServiceType, FilteredAccount.rqia_subtypeidname AS ServiceSubtype, FilteredAccount.name AS RQIAService, 'Variation Application' AS ApplicationType, 
                                                       Filteredrqia_variation.rqia_name AS ApplicationID, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date, 
                                                       Filteredrqia_variation.rqia_applicationreceiveddate, 103)) AS WB, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103) AS DateReceived, CONVERT(date, 
                                                       Filteredrqia_variation.rqia_dateofoutcome, 103) AS OutcomeDate
                              INTO               #Sub
                              FROM            FilteredAccount AS FilteredAccount INNER JOIN
                                                       Filteredrqia_variation ON FilteredAccount.name = Filteredrqia_variation.rqia_serviceidname
                              WHERE        (Filteredrqia_variation.statuscodename NOT LIKE '%Withdrawn') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Refused') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Returned') 


                              GROUP BY FilteredAccount.rqia_directoratename, FilteredAccount.rqia_servicetypeidname, FilteredAccount.rqia_subtypeidname, FilteredAccount.name, Filteredrqia_variation.rqia_name, DATEADD(dd, 
                                                       0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103)), CONVERT(date, 
                                                       Filteredrqia_variation.rqia_applicationreceiveddate, 103), CONVERT(date, Filteredrqia_variation.rqia_dateofoutcome, 103)
                                                           /* Final Query combining all temp tables*/ SELECT Main.Week, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate, CASE WHEN Sub.DateReceived <= Main.Week AND (Sub.OutcomeDate IS NULL OR
                                                                                     Sub.OutcomeDate > Main.Week) THEN 'Yes' ELSE 'No' END AS Ongoing
                                                            FROM            #Main AS Main LEFT OUTER JOIN
                                                                                     #Sub AS Sub ON Sub.WB = Main.Week
                                                            GROUP BY Main.Week, Sub.ApplicationType, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate
                                                            ORDER BY Ongoing DESC, Main.Week /* Clean up temporary tables*/ DROP TABLE #Main; DROP TABLE #Sub

I am totally going around in circles with this and wanted to ask for a fresh pair of eyes on this.

This query works fine in that it identifies where the week beginning date in the Main table matches the Date Received in the Sub table. However, this is not completely correct because I am looking for a 'snapshot' as at 01/04/2024, 08/04/2024 etc so I also need the Dates Received before the week beginning, not just on that exact date, so I can show where an application was ongoing as at the week beginning. I know the issue is with the table join at the very bottom but I just cant figure it out so any advice would be hugely appreciated

SELECT        DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, dt)) % 7, CONVERT(date, dt, 103)) AS Week
INTO              [#Main]
FROM            RQIA_Custom_Calendar_View
WHERE        (CONVERT(date, dt, 103) >= DATEADD(year, DATEDIFF(month, 90, CURRENT_TIMESTAMP) / 12, 90)) OPTION (maxrecursion 0);
                         
SELECT        FilteredAccount.rqia_servicetypeidname AS ServiceType, FilteredAccount.rqia_subtypeidname AS ServiceSubtype, FilteredAccount.name AS RQIAService, 'Variation Application' AS ApplicationType, 
                                                       Filteredrqia_variation.rqia_name AS ApplicationID, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date, 
                                                       Filteredrqia_variation.rqia_applicationreceiveddate, 103)) AS WB, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103) AS DateReceived, CONVERT(date, 
                                                       Filteredrqia_variation.rqia_dateofoutcome, 103) AS OutcomeDate
                              INTO               #Sub
                              FROM            FilteredAccount AS FilteredAccount INNER JOIN
                                                       Filteredrqia_variation ON FilteredAccount.name = Filteredrqia_variation.rqia_serviceidname
                              WHERE        (Filteredrqia_variation.statuscodename NOT LIKE '%Withdrawn') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Refused') AND (Filteredrqia_variation.statuscodename NOT LIKE '%Returned') 


                              GROUP BY FilteredAccount.rqia_directoratename, FilteredAccount.rqia_servicetypeidname, FilteredAccount.rqia_subtypeidname, FilteredAccount.name, Filteredrqia_variation.rqia_name, DATEADD(dd, 
                                                       0 - (@@DATEFIRST + 5 + DATEPART(dw, Filteredrqia_variation.rqia_applicationreceiveddate)) % 7, CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103)), CONVERT(date, 
                                                       Filteredrqia_variation.rqia_applicationreceiveddate, 103), CONVERT(date, Filteredrqia_variation.rqia_dateofoutcome, 103)
                                                           /* Final Query combining all temp tables*/ SELECT Main.Week, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate, CASE WHEN Sub.DateReceived <= Main.Week AND (Sub.OutcomeDate IS NULL OR
                                                                                     Sub.OutcomeDate > Main.Week) THEN 'Yes' ELSE 'No' END AS Ongoing
                                                            FROM            #Main AS Main LEFT OUTER JOIN
                                                                                     #Sub AS Sub ON Sub.WB = Main.Week
                                                            GROUP BY Main.Week, Sub.ApplicationType, Sub.ApplicationID, Sub.DateReceived, Sub.OutcomeDate
                                                            ORDER BY Ongoing DESC, Main.Week /* Clean up temporary tables*/ DROP TABLE #Main; DROP TABLE #Sub

• reporting-services

Share Improve this question Follow edited Mar 18 at 20:25 Harry 2,95111 gold badge2121 silver badges3636 bronze badges asked Mar 14 at 15:13 Paula MorrisonPaula Morrison 3144 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

was so easy - should have known - just a change in the table join!

ON Sub.DateReceived <= Main.Week AND (Sub.OutcomeDate IS NULL OR
                                                                                     Sub.OutcomeDate > Main.Week)

本文标签： reporting servicesSSRS 2012 Join Tables without JoiningStack Overflow