Export Cost of Sales date-to-date to Excel

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Export Cost of Sales date-to-date to Excel

Danny Kahumba
Administrator

Export Cost of Sales date-to-date to Excel

1.       Open Query Express on your Desktop. If you do not have query express on your computer, you can download it for free from
http://www.albahari.com/QueryExpress.exe


2.       If prompted with the message below, click Run.


3.       On the next screen,
under Server type forecourt [or any other applicable server]
Select SQL Server Authentication
Under Login name:  type sa
Then click connect


4.       Once connected, under Databases on the left, select SmartfuelProduction [Or the applicable database]


5.       Copy the script below and paste it in the blank space on the right. [The script is also attached on this article if you cannot copy it from here]

Declare @StartDate datetime Set @StartDate= '2013-05-01'

Declare @EndDate datetime Set @EndDate=     '2013-05-31'

 

SELECT   Stockcode, Stockdescription,Stockcategory, Sum (stocksold) Quantity, (MAX(stockcostpriceex)*Sum (stocksold)) stockcostpriceex, 

SUM (stockvat) Vat, SUM (stocktotal) TotalInclVat,(SUM (stocktotal) -(MAX(stockcostpriceex)*Sum (stocksold)))  GPAmount,

CAST(round(((SUM (stocktotal) -(MAX(stockcostpriceex)*Sum (stocksold)))/((SUM (stocktotal)-SUM (stockvat))/100)),2) AS numeric(12,2) )GPPerc

FROM monthendsummary_stock_date a

WHERE tdate  BETWEEN @StartDate

AND  @EndDate

AND type IN ('01','02','03','05','06','07','09')

GROUP BY Stockcode, Stockcategory, Stockdescription, stockcostpriceex

ORDER BY stockcategory, Stockdescription

6.       At the top of the script, change your start and end date to the ones that you want in the following format Year - Month – Day
For example

Declare @StartDate datetime Set @StartDate='2013-05-01'

for the 1st of May 2013

7.       Click on the green “Play” button to execute the query. This will generate the Cost of sales information over the selected period. Note that depending of the amount of information requested, this script can take up to a few minutes to run [Typically 1 month of information takes less than 5 seconds ]. When the query if done running, it will be written Query Batch completed at the bottom of the screen.




8.       Click on File > Save Query Results…


9.       Select the location where you want to save the file and give it a file name then click Save


10.   Close Query Express and when prompted to Save the file, click No

11.   Open Excel > Open > Make sure to select All files (*.*) under Files of type:


12.   Open the file that you save and Voila!! You have successfully imported the Cost of sales and can use it as a normal spreadsheet.

 

Danny Kahumba


Cost of sales export Excel.txt (763 bytes) Download Attachment