Controller:
Can you give me a list of all companies in GP?
Me:
Sure, do you need anything else while you I am at it?
Controller:
I also need to be able to tell if there is any activity or not in any given company.
Me:
Okay, let me see what I can do.
I built the below script to generate the SQL I needed.
Begin SQL script
use Dynamics
SELECT 'SELECT '+''''+RTRIM(CMPANYID)+'-'+RTRIM(INTERID) +''''+' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM '+rtrim(INTERID)+'.DBO.SY40101 union' sql1
FROM [dbo].SY01500
where cmpanyid <> - 1
UNION
SELECT 'SELECT '+''''+RTRIM(CMPANYID)+'-'+RTRIM(INTERID) +''''+' COMPANY, MIN(year1) ,MAX(year1) FROM '+rtrim(INTERID)+'.DBO.SY40101 ' sql1
FROM [dbo].SY01500
where cmpanyid = - 1
order by SQL1 desc
End SQL Script
My SQL looked like this
SELECT '80-LFFMT' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFMT.DBO.SY40101 union
SELECT '79-LFMRE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMRE.DBO.SY40101 union
SELECT '78-PURCH' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PURCH.DBO.SY40101 union
SELECT '77-OCR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM OCR.DBO.SY40101 union
SELECT '76-LFMTG' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMTG.DBO.SY40101 union
SELECT '75-DUMMY' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM DUMMY.DBO.SY40101 union
SELECT '74-LFFMR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFMR.DBO.SY40101 union
SELECT '73-WJMC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM WJMC.DBO.SY40101 union
SELECT '72-LHDQ2' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LHDQ2.DBO.SY40101 union
SELECT '71-LFIRE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFIRE.DBO.SY40101 union
SELECT '70-LFMVE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMVE.DBO.SY40101 union
SELECT '7-PRS' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PRS.DBO.SY40101 union
SELECT '69-LFHSC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFHSC.DBO.SY40101 union
SELECT '68-LFFIN' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFIN.DBO.SY40101 union
SELECT '67-LVENT' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LVENT.DBO.SY40101 union
SELECT '66-HQ2' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM HQ2.DBO.SY40101 union
SELECT '65-VISLD' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM VISLD.DBO.SY40101 union
SELECT '63-GRYVA' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM GRYVA.DBO.SY40101 union
SELECT '62-SAGEG' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM SAGEG.DBO.SY40101 union
SELECT '61-STPRO' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM STPRO.DBO.SY40101 union
SELECT '58-SSDSJ' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM SSDSJ.DBO.SY40101 union
SELECT '57-KEYSP' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM KEYSP.DBO.SY40101 union
SELECT '5-MET' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM MET.DBO.SY40101 union
SELECT '45-goaks' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM goaks.DBO.SY40101 union
SELECT '43-RGSMD' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM RGSMD.DBO.SY40101 union
SELECT '42-RGS' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM RGS.DBO.SY40101 union
SELECT '40-FONV' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM FONV.DBO.SY40101 union
SELECT '4-MST' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM MST.DBO.SY40101 union
SELECT '3-LFIA' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFIA.DBO.SY40101 union
SELECT '27-PMPR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PMPR.DBO.SY40101 union
SELECT '20-LIC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LIC.DBO.SY40101 union
SELECT '19-LFC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFC.DBO.SY40101 union
SELECT '17-HDQ' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM HDQ.DBO.SY40101 union
SELECT '16-LHI' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LHI.DBO.SY40101 union
SELECT '15-ETL' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM ETL.DBO.SY40101 union
SELECT '11-LLC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LLC.DBO.SY40101 union
SELECT '1-LFI' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFI.DBO.SY40101 union
SELECT '-1-TWO' COMPANY, MIN(year1) ,MAX(year1) FROM TWO.DBO.SY40101
My Report data when pasted in excel looked like this
COMPANY | OldestFY | RecentFY |
-1-TWO | 2010 | 2018 |
1-LFI | 1998 | 2020 |
11-LLC | 1998 | 2012 |
15-ETL | 1999 | 2017 |
16-LHI | 1998 | 2007 |
17-HDQ | 1999 | 2008 |
19-LFC | 1998 | 2020 |
20-LIC | 1999 | 2020 |
27-PMPR | 2003 | 2015 |
3-LFIA | 1998 | 2019 |
4-MST | 1998 | 2019 |
40-FONV | 2006 | 2010 |
42-RGS | 2007 | 2019 |
43-RGSMD | 2007 | 2018 |
45-goaks | 2006 | 2013 |
5-MET | 1998 | 2020 |
57-KEYSP | 2006 | 2019 |
58-SSDSJ | 2007 | 2019 |
61-STPRO | 2007 | 2019 |
62-SAGEG | 2007 | 2019 |
63-GRYVA | 2007 | 2019 |
65-VISLD | 2008 | 2010 |
66-HQ2 | 2008 | 2018 |
67-LVENT | 2010 | 2019 |
68-LFFIN | 2009 | 2019 |
69-LFHSC | 2009 | 2017 |
7-PRS | 1998 | 2009 |
70-LFMVE | 2010 | 2019 |
71-LFIRE | 1998 | 2020 |
72-LHDQ2 | 2005 | 2019 |
73-WJMC | 2009 | 2019 |
74-LFFMR | 2006 | 2018 |
75-DUMMY | 2013 | 2017 |
76-LFMTG | 2013 | 2019 |
77-OCR | 2016 | 2019 |
78-PURCH | 2017 | 2019 |
79-LFMRE | 2017 | 2019 |
80-LFFMT | 2018 | 2019 |
The excel is what I gave my controller and she was very happy.
If you have another consolidated report idea in mind, please do leave a comment and I will be happy to compile a script for the same.
I thank you for reading my blog.
*This post is locked for comments