Need a batch process solution (Non-SSIS if possible)

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  August 9, 2013 at 11:34 am

  #276857

  Hello...

  I have the following query which returns information to me on whether a user has gone from just being a user to a customer in our system. Here's the SQL:

  SELECT AM_invoiceheader.BuyerID, COUNT(DISTINCT AM_invoiceitems.InvoiceID) AS Invoices, SUM(AM_invoiceitems.SalePrice) AS Revenue,

  YEAR(AM_invoiceheader.ShipDate) AS Year, SUM(AM_invoiceitems.Quantity) AS Units, AM_buyers.BillToEmail, AM_buyers.ShipToEmail

  FROM AM_invoiceheader INNER JOIN

  AM_invoiceitems ON AM_invoiceheader.InvoiceID = AM_invoiceitems.InvoiceID INNER JOIN

  AM_buyers ON AM_invoiceheader.BuyerID = AM_buyers.BuyerID

  WHERE (AM_invoiceitems.Typeid IN (1)) AND (AM_invoiceheader.InvoiceSourceID IN (5, 6)) OR

  (AM_invoiceitems.Typeid IN (1)) AND (AM_invoiceheader.InvoiceSourceID IN (5, 6))

  GROUP BY AM_invoiceheader.BuyerID, YEAR(AM_invoiceheader.ShipDate), AM_buyers.BillToEmail, AM_buyers.ShipToEmail

  HAVING (AM_buyers.BillToEmail LIKE N'user@gmail.com') OR

  (AM_buyers.ShipToEmail LIKE N'user@embarqmail.com')

  ORDER BY AM_invoiceheader.BuyerID

  I have 17k of email addresses that I need to run through this query, and I am hoping there is a way for me to be able to do this in just a T-SQL query by way of a SQL Agent Job or some other batch process (other than SSIS - long story for why I am avoiding that method).

  Please let me know if you need additional information from me to help out, and any and all help is definitely appreciated!

  Thank you in advance...

  SQL_ME_RICH

• tim.hansen

  Old Hand

  Points: 316

  More actions

  August 9, 2013 at 3:46 pm

  #1640308

  Are the 17k of email addresses in a table?

  ---

  Living in Paradise: N 34°16'07.99" W 119°12'17.58"

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  August 9, 2013 at 4:01 pm

  #1640311

  Good question...No, they are in an .xlsx file that I can convert as needed (if needed). Also - and sorry I forgot to include this initially, but I want to save the result set for each of the addresses (Either in a temp table or if there is an easier way to output them to say an .csv/.xls file, I am open to that as well).

  Thanks again