Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a batch process solution (Non-SSIS if possible) Expand / Collapse
Author
Message
Posted Friday, August 09, 2013 11:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:41 PM
Points: 461, Visits: 1,266
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
Post #1482870
Posted Friday, August 09, 2013 3:46 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 12:16 PM
Points: 178, Visits: 332
Are the 17k of email addresses in a table?


Living in Paradise: N 34°16'07.99" W 119°12'17.58"
Post #1482966
Posted Friday, August 09, 2013 4:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:41 PM
Points: 461, Visits: 1,266
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
Post #1482969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse