July 15, 2015 at 1:23 pm
I am still new to SQL and I need help in trying to turn the following select code below into a SP and then have the output that is given output to a .csv file onto the desktop or in a folder location. Does anyone know if that is possible or can be done? The code below does give me the output I need, put I have to copy and paste it into excel at the present moment and I want to get away from that.
select
cust_code,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(SHIPPED * PRICE)
else 0 end) as TenTenSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as TenTenGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
then
(SHIPPED * PRICE)
else 0 end) as CYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as CYTDGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') AND
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1) )))
then
(SHIPPED * PRICE)
else 0 end) as PYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1 ))))
THEN
SHIPPED * (PRICE - COST)
else 0 end) as PYTDGP
from orders_ALL ords
JOIN ORD_LIST AS OL ON OL.ORDER_NO = ORDS.ORDER_NO AND EXT = ORDER_EXT
join inv_master as im on im.part_no = ol.part_no
--JOIN ARMASTER_ALL AS AR ON CUST_CODE = CUSTOMER_CODE AND ADDRESS_TYPE = 0
where ORDS.status not in ('V') AND
YEAR(date_entered) > YEAR(GETDATE()) - 2
and im.status = 'P' and cust_code <> '80990000'
GROUP BY cust_code
ORDER BY cust_code
July 15, 2015 at 1:34 pm
To change this into a procedure, read the CREATE PROCEDURE entry from BOL (Books OnLine).
To create the file, read about the bcp utility.
You could also create the file by using SSIS. You can even create the SSIS package through the Import/Export wizard.
July 17, 2015 at 3:08 am
Once you've created the Stored Procedure as has been suggested, in addition to using bcp you could also use the SQLCMD utility to execute the stored procedure and output the results to file or probably even more elegant and somewhat more flexible you could use Powershell...
If you need help with any of the suggested approaches you can always post back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply