This post is about posting only new data from inventory table into vendor ledgers and material control account  by automatically checking exisiting data. We are using true example from our accounting database. Transactionz table is a financial transactions table resulting from material received or returned. In inventory table data is recorded with date, material id,  quantity received and vendor id.

This data is posted into two tables with transact-sql statements  below statement 1 post the data into transactionz table as mentioned above and statement 2 post the same transaction into material ledger a/c after checking exisiting transaction ids by using ‘where not (tr_id = any (select trn from transactionz))’ so no duplicate transaction is posted as well if priceis 0 in case rate not finalized no transcation will be posted into ledgers though quantity is already received.

These statements made accounting entry very simple with 1 step i.e. once data is recorded in inventory it is automatically posted in effected ledger accounts accounts.

Statement-1

insert into transactionz  (trn, transactiondate, transactiondescription, accountid, credit, debit)

select  tr_id, transactiondate, cast(quantitycr as varchar)+ ‘ / ‘+ cast(quantity as varchar)+ ‘ ‘+ +cast([description/name] as nvarchar) + ‘ @ Rs. ‘ +cast(price as varchar) ,accountid,  (quantity*price), (quantitycr*price)  from view38

where not (tr_id = any (select trn from transactionz) and (price<> 0)

Statement-2

insert into transactionmat  (trn, transactiondate, transactiondescription, accountid, debit, credit)

select  tr_id, transactiondate, cast(quantity as varchar)+ ‘ / ‘+cast(quantitycr as varchar)+ ‘ ‘+ +cast([description/name] as nvarchar) + ‘ @ Rs. ‘ +cast(price as varchar) ,215,  (quantity*price), (quantitycr*price)  from view38

where not (tr_id = any (select trn from transactionmat) and (price<> 0)

 Reporting the payments received against shipment’s C&F value.

In this example our client was facing an  issue where shipments are made  and C&F value amount is taken as Accounts Receivable, payments are received in 2 to 3  installments.  Management wants to know how much is outstanding against each shipment C&F Invoice.

In database invoice is generated on C&F basis where comprehensive information is recorded like invoice no,  Bill of Lading No, C&F Amount, Shipment NO and so on.

When Payments are received it is recorded exportpayments table along with shipment NO.

In order to create a report to what is outstanding against respective invoice we created a SQL Query  using select into Transact-SQL  as follows;

select sn1, sum(amt) as cfvl

 into #sd4

from dbo.exportpayments

group by sn1

Select Sn, CFvlu,sn1, cfvl, Cfvlu-cfvl as balance from

 dbo.invocesexpressions inner join #sd4 on

dbo.invocesexpressions.sn = #sd4.sn1

drop table #sd4

In the above example we selected Shipment NO SN1 and sum amount Sum(amt) as cfvlu

Into temp table SD4 and then we selected Shipment no SN, CFVLU from invoices table and CFVL from Temp table #SD4 and join Invoices table and #sd4 based on Shipment NO

At the end we dropped thetemp table  #SD4.

This example utilized the temp table for generating outstanding payments report.

Shipmnt              CFVLU          Shpmnt  Amnt RCD       Blnc

This post is our practical experience of using Case statement in Calculating monthly overtime amount. Issue was actually usage of different no hours for Overtime worked for different departments.

In this case Security departments overtime was calculated based on 12 hours and in all other departments base was 8 hours.

Salary was categorize by monthly type as [monthlytype] SG for Security Guard where ovetime was calculated based on 12 hours.

code is given below and was in included in select statement;

CASE monthlytype WHEN ’sg’ THEN dbo.wrkshopattndnc.TOTAL_OVERTIME * dbo.[wrkshop employees].Salary / DAY(dbo.wrkshopattndnc.month)/12

ELSE dbo.wrkshopattndnc.TOTAL_OVERTIME * dbo.[wrkshop employees].Salary / DAY(dbo.wrkshopattndnc.month) / 8

END AS ovtamnt

This post is about fast changing dimension, for example A customer dimension may have large number of attributes and many rows. It happens sometimes in the “customers” are health insurance policy claimants, and other times they are owners of motor vehicles. We found a usefull material on Fast change management of Complex dimensions written by Ralph Kimbal. please visit here to read more, to read bout slowly changing dimensions by Datamart.org

We are writing this post to share our experience of usefulness of Floor function. We use the floor function in calculating product manufactured and raw material wastage in the process. The problem was to calculate how many units of a product can be manufactured from certain weight of a raw material.

We were getting answer like 8.67 products manufactured from 50 kgs of raw material. We talked to the manufacturing department they confirmed that 8 no of products are manufactured and 0.67 is the wastage. We calculated the weight of each product and then multiply by 8 which gave us the weight in kgs of 8 no of products.
After that weight of 1 product was multiplied with .67 of product and got the weight of wastage in kgs.
In the above scenario we needed the decimal value of .67 separated from 8.67 in separate column to show wastage. Our data was in SQL Server, we utilized the floor function to make 8.67 into 8 and then subtracted the 8 from 8.67. The issue was resolved by using floor function.

Below is the related information about Floor function as obtained from SQL Server books Online.

CEILING and FLOOR

The CEILING function returns the smallest integer greater than or equal to the given numeric expression. The FLOOR function returns the largest integer less than or equal to the given numeric expression. For example, given a numeric expression of 12.9273, CEILING returns 13, and FLOOR returns 12. The return value of both FLOOR and CEILING has the same data type as the input numeric expression.

We are writing this post because recently we received a query regarding automatic rounding in quantity column. The user was previously using MS Access and they upgraded to SQL Server database. Although solution is simple but sometimes it becomes difficult to resolve when confronting the problem.
SQL Server Books online very well addressed the concern and is reproduced below;

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

This post about export Crystal Reports data to MS Excel 2007.  Although you can export to MS Excel (97-2003) but exporting to Excel 2007 requires extra step and presented below;

1-       Go to export option in Crystal Reports

2-       Export it MS Excel (97-2003)

3-       Once exported open it with MS Excel 2007

4-       Then in file menu save it as Excel files and will be saved  in Excel 2007 with .xlsx extension.

Please note that in above example we used Crystal Reports 2008.

We believe that change is important but not at the cost of additional training, but should compliment existing experience. Creating ACCESS Project file (.adp) extension for connecting to MS SQL Server data was found to bechallenging / required additional time to search how to do that; however we implemented the same and presenting it in simple words for our readers;

1-      Create New File by clicking blank database the click on the file location just after giving your name.

2-      After clicking location you will be at menu named – File New Database

3-      On this menu go to option box save as type you will find various options select Microsoft Office Access Projects, and you will be at New Project with File name ending with .adp and then just click create.

4-      Here you will be presented with Option to connect an existing database or or new Sql Server database.

Crystal Reports 2008 is a reporting tool which can access and present data from Relational Databases and OLAP follwing are the supported OLAP databases:

1. Hyperion Essbase
2. Seagate Holos and Seagate INFO/Crystal Info
3. IBM DB2 OLAP Server
4. Informix MetaCube
5. Microsoft SQL Server 2007 and above
6. OLEDB for OLAP Sources(Oor other “Open OLAP Sources)

1- In business Intelligence Studio, Start new Report project.
2- On the select data source, name your data source, and select Microsoft SQL Server Analyses Services in the type option box and then press edit connection string button.
3- On Connection Properties enter the name of your server and then select Database Name.
4- On report wizard screen click next.
5- You will be at Query Builder screen where you can use Graphical query builder.
6- Note In the above steps you can select Query Builder in Report Project.
Please see the previous post on the same topic