Skip to main content

Notifications

Announcements

No record found.

I have way too many companies

26P2ER Profile Picture 26P2ER 1,773

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.

 

Comments

*This post is locked for comments