May 19, 2017 at 11:45 am
I am looking to output approximately 100 tables to excel with the following code.
One for work each day from Jan. 1st to now to investigate trends.
I am somewhat familiar with looping in other languages, but I am unsure how I would accomplish this in SQL and save + export each of these so I can summarize and aggregate the data later in excel.
Is there a faster way than manually changing the date range to a day later, executing and saving the grid 100 times?
SELECTtlorder.origcity AS "Destination", COUNT(*) AS "Number ofOrders", SUM(total_charges) AS "Customer Bill",SUM(order_interliner.functional_amt) AS "Total Partner Costs"
FROM tlorder
JOIN order_interliner ON tlorder.detail_line_id = order_interliner.detail_line_id
WHERE origcountry = 'CA'
AND destcity = 'COLUMBUS'
AND created_time >= '2017-1-3'
AND created_time <= '2017-1-4'
GROUP BY origcity
ORDER BY SUM(order_interliner.functional_amt) desc
May 19, 2017 at 12:56 pm
ross_cunningham813 - Friday, May 19, 2017 11:45 AMI am looking to output approximately 100 tables to excel with the following code.
One for work each day from Jan. 1st to now to investigate trends.I am somewhat familiar with looping in other languages, but I am unsure how I would accomplish this in SQL and save + export each of these so I can summarize and aggregate the data later in excel.
Is there a faster way than manually changing the date range to a day later, executing and saving the grid 100 times?
SELECTtlorder.origcity AS "Destination", COUNT(*) AS "Number ofOrders", SUM(total_charges) AS "Customer Bill",SUM(order_interliner.functional_amt) AS "Total Partner Costs"FROM tlorder
JOIN order_interliner ON tlorder.detail_line_id = order_interliner.detail_line_id
WHERE origcountry = 'CA'
AND destcity = 'COLUMBUS'
AND created_time >= '2017-1-3'
AND created_time <= '2017-1-4'
GROUP BY origcity
ORDER BY SUM(order_interliner.functional_amt) desc
Why don't you export the whole 100 days at once and then work with a single set in Excel? Why do you need the additional steps?
May 19, 2017 at 1:06 pm
Is there a way to query multiple (100) tables at once?
I thought about doing that, but I need all the information separated as it needs to be analyzed day to day, week to week, month to month.
EDIT: Realized that I could just get all the information like you said, sort by date entered and then manipulate it in excel. Originally I had it grouped so it would have been totally worthless all together in a huge table for the entire time period, but then I figured I can just ungroup it and leave each row separated.
Thanks! It's not a fully automated process, but way easier than manually exporting that many!!
May 19, 2017 at 1:15 pm
Can you give more detail about how you're using Excel to access the data?
Are you looking for separate Excel files for each range? Or one file, with separate worksheets?
Or can you use an Excel Pivot Table to pull in from a SQL view?
May 19, 2017 at 1:26 pm
You can do something like this:
SELECT CAST( created_time AS date) AS "created time",
tlorder.origcity AS "Destination",
COUNT(*) AS "Number ofOrders",
SUM(total_charges) AS "Customer Bill",
SUM(order_interliner.functional_amt) AS "Total Partner Costs"
FROM tlorder
JOIN order_interliner ON tlorder.detail_line_id = order_interliner.detail_line_id
WHERE origcountry = 'CA'
AND destcity = 'COLUMBUS'
AND created_time >= '2016-1-3'
AND created_time <= '2017-1-4'
GROUP BY origcity, CAST( created_time AS date)
ORDER BY SUM(order_interliner.functional_amt) desc
You don't need the CAST if the column type is date instead of datetime.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply