August 19, 2004 at 1:51 pm
I'm trying to find away to a export a series of queries to separate excel files. The only difference between the queries is an EmployeeID number. I've set up a DTS package that contains each query that I can run individually, but I'd like to run one DTS package that saves each excel file separately based on the EmployeeID.
Here is my current Query: 'E705327' is the EmployeeID
SELECT
Sales.Quarter02.[ProjectName ],
Sales.Quarter02.[ReportNo ],
Sales.Quarter02.[Valuation ],
Sales.Quarter02.[Stage ],
Sales.Quarter02.[ProjectType ],
Sales.Quarter02.[Address ],
Sales.Quarter02.[City ],
Sales.Quarter02.[County ],
Sales.Quarter02.[State ],
Sales.Quarter02.[ZipCode ],
Sales.Quarter02.[GC ],
Sales.Quarter02.[Architect ],
Sales.Quarter02.[Consultant ],
Sales.Quarter02.[ConsultingEngineer ],
Sales.Quarter02.[ElectricalEngineer ],
Sales.Quarter02.[MechanicalEngineer ],
Sales.Quarter02.[Owner ]
FROM Sales.SalesRep INNER JOIN
Sales.CountyEID INNER JOIN
Sales.Quarter02 ON Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[County ] OR
Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[City ] ON
Sales.SalesRep.EID = Sales.CountyEID.Rep1
WHERE (Sales.SalesRep.EID = 'E705327')
ORDER BY Sales.Quarter02.[Valuation ] DESC
August 20, 2004 at 8:40 am
Using either a WHILE LOOP or CURSOR...
build your list of unique EmployeeIDs.
Looping/Cursoring through your list of EmployeeIDs, Updating value of @EmployeeID and @MyExcelFileName
bcp " SELECT
Sales.Quarter02.[ProjectName ],
Sales.Quarter02.[ReportNo ],
Sales.Quarter02.[Valuation ],
Sales.Quarter02.[Stage ],
Sales.Quarter02.[ProjectType ],
Sales.Quarter02.[Address ],
Sales.Quarter02.[City ],
Sales.Quarter02.[County ],
Sales.Quarter02.[State ],
Sales.Quarter02.[ZipCode ],
Sales.Quarter02.[GC ],
Sales.Quarter02.[Architect ],
Sales.Quarter02.[Consultant ],
Sales.Quarter02.[ConsultingEngineer ],
Sales.Quarter02.[ElectricalEngineer ],
Sales.Quarter02.[MechanicalEngineer ],
Sales.Quarter02.[Owner ]
FROM Sales.SalesRep INNER JOIN
Sales.CountyEID INNER JOIN
Sales.Quarter02 ON Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[County ] OR
Sales.CountyEID.State = Sales.Quarter02.[State ] AND Sales.CountyEID.County = Sales.Quarter02.[City ] ON
Sales.SalesRep.EID = Sales.CountyEID.Rep1
WHERE (Sales.SalesRep.EID = @EmployeeID)
ORDER BY Sales.Quarter02.[Valuation ] DESC
" queryout @MyExcelFileName
Corie Curcillo
MCT, MCDBA, MCSD
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy