sql,sql-server , Accounts Ledger query in sql
Accounts Ledger query in sql
I need a query which maintain my accounts ledger
Here is my tables
CREATE TABLE [dbo].[Accounts](
[Account_ID] [int] primary key,
CREATE TABLE [dbo].[Purchase_Invoice](
[Purchase_ID] [int] primary key,
[Purchase_Amount] [decimal](9, 2))
CREATE TABLE [dbo].[Sale_Invoice](
[Invoice_ID] [int] primary key,
[Invoice_Amount] [decimal](10, 2))
CREATE TABLE [dbo].[Transection](
[Trans_ID] [int] primary key,
CREATE TABLE [dbo].[DebitCredit](
[Id] [int] IDENTITY(1,1),
[Amount] [decimal](9, 2),
Insert into Accounts values(1,'Alicia', 'Customer')
Insert into Accounts values(2,'Mike', 'Supplier')
Insert into Accounts values(3,'AbcBank', 'Bank')
Insert into Purchase_Invoice values (1,'12/7/2014',2,0,0,0,'1254',5000)--Payment Debit in Supplier Mike Account
Insert into Sale_Invoice values (1,'12/7/2014',1,0,0,0,1500) --Payment Credit in Customer Alicia Account
Insert into Transection values (1,'12/7/2014','Payment Recevie From Customer')
insert into DebitCredit values (1,1,1500,'DB')--Payment Debit in Customer Alicia Account
insert into DebitCredit values (1,3,1500,'CR')--Payment Credit in Bank AbcBank Account
Insert into Transection values (2,'12/7/2014','Payment Send to Supplier')
insert into DebitCredit values (2,3,1500,'DB')--Payment Debit in Bank AbcBank Account
insert into DebitCredit values (2,2,1500,'CR')--Payment Credit in Supplier Mike Account
I purchased some item from supplier 'Mike' amount of invoice is 5000, Amount is credit on Mike then I sale item to Alicia for 1500, Amount Debit to Alicia.
In transaction table, I receive amount 1500 from Alicia, the amount 1500 now debit to Alicia Account and receive amount credit to abcbank.
Now In transaction table, I send amount 1500 to Mike, the amount 1500 now Credit to Mike Account and debit to abcbank.
Result may be like this
Account_Name | Debit | Credit | Balance
Kindly help me
Rework your table model to make more sense. I ngeneral you only need one Transaction table. YOu also should - and often are legally required to - keep a running number AND the total post transaction,. Helps a ton with performance.
You seem to avoid having account categories with having tons of tables - one for every account category. And you seem to think that a model where everything is calculated on demand will scale above a minimal example level.
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...
I've the following table in my PHPMYADMIN The desired output is headline impressions clicks Buy A new Iphone ...
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid . ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529' select @A Results: R2300529? any explanation? i'm using SQL server 2012....
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...