What would be the best way? 1

[michaela18]

I have to run a daily report, pretty manual work. I have to pull data from sql then manually enter it into excel for that day.

I am trying to come up with a way to automate this process more. I try to use MS Query and just pivot the data from there but there are too many conditions to be everything on 1 pivot table. Is there a better way to automate this? Maybe a series of update queries in MS Access? Please help

Select COUNT (CASE WHEN Product_Code LIKE '%12563%'
		OR	 Product_Code LIKE '%12567%'
		THEN Product_Code 
                END) AS 'IBM_5_bundle'
FROM 	history
where Mail_date= '20100210'
and State in (‘In Process’, ‘Order Fulfilled’)
and Prom_Code in (‘AB101’, ‘AB102’, ‘AB103’)
and FileName like '%FA%'

Select COUNT (CASE WHEN Product_Code LIKE '%72147%'
		    OR	 Product_Code LIKE '%72148%'
		    THEN Product_Code 
                    END) AS  a,
       COUNT (CASE WHEN Product_Code LIKE '%7215%'
		    OR	 Product_Code LIKE '%2150%'
		    THEN Product_Code 
                    END) AS b
       COUNT (CASE WHEN Product_Code LIKE '%7219%'
		    OR	 Product_Code LIKE '%7291%'
		    THEN Product_Code 
                    END) AS ‘c,
       COUNT (CASE WHEN Product_Code LIKE '%709%'
OR	 Product_Code LIKE '%717%'
		    OR	 Product_Code LIKE '%7361%'
		    OR	 Product_Code LIKE '%7176%'
		    OR	 Product_Code LIKE '%3715%'
		    OR	 Product_Code LIKE '%7715%'
		    THEN Product_Code 
                    END) AS F,
	 COUNT (CASE WHEN Product_Code LIKE '%7512%'
		    OR	 Product_Code LIKE '%71247%'
		    OR	 Product_Code LIKE '%75412%'
			THEN Product_Code 
                    END) AS G,
	COUNT (CASE WHEN Product_Code LIKE '%71231%'
		    OR	 Product_Code LIKE '%45612%'
		    THEN Product_Code 
                    END) AS H,
	COUNT (CASE WHEN Product_Code LIKE '%1233%'
		    OR	 Product_Code LIKE '%6541%'
		    THEN Product_Code 
                    END) AS I

FROM 	history
WHERE 	Mail_date	= '20100210'
AND 	State IN (‘In Process’, ‘Order Fulfilled’)
AND PROM_CODE IN (‘AB101’, ‘AB102’, ‘AB103’)


select planB = 
(select count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID 
and a.Mail_date= '20100210' 
and a.State in (‘In Process’, ‘Order Fulfilled’)
and a.Priceplan <> ''
and a.Prom_Code in (‘AB101’, ‘AB102’, ‘AB103’)
and a.priceplan in 
(
‘4243324’,’234234234’,’234234’)
and b.price_plan not in (
‘4243324’,’234234234’,’234234’
))
+
(select count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID 
and a.Mail_date= '20100224' 
--and calldate >= '20100220'
and a.State in (‘In Process’, ‘Order Fulfilled’)
and a.Priceplan <> ''
and a.Prom_Code in (‘AB101’, ‘AB102’, ‘AB103’)
and (a.priceplan <> b.price_plan)
and a.priceplan in (‘234234’,’234231’,’68967’)
and b.price_plan not in (‘234234’,’234231’,’68967’))

Select  sum((1+len(NULLIF(LTRIM(Accessory_Code),''))-len(replace(NULLIF(LTRIM(AccessoryCodeList),''),' ','')))) as K
from history
where State in (‘In Process’, ‘Order Fulfilled’)
and Mail_date= '20100210'
--and calldate >= '20100220'
and Prom_Code IN (‘AB101’, ‘AB102’, ‘AB103’)
and AccessoryCode > ' '

 

[RiverGuy]

I would suggest to set up SQL Server Reporting Services, create your report pivoted or sliced however you wish, with a default parameter for the date, and then subscribe to the report on a daily schedule with Excel rendering output. I mean, what you are looking to do is exactly what SSRS was designed for.

 

[michaela18]

Thanks but what if the company doesn't have SSRS?

 

[RiverGuy]

I'm sure you can write Excel macros to connect to SQL, run the query, bring back a recordset, and then construct a pivot table within Excel. I can't really tell you how, since I don't write Excel macros very often, but I'm sure either someone else in here, or maybe someone in the VBA forum could help you.