I have a requirement to generate Invoice system for 1000 customers. how to create a report or SSIS Package to auto generate invoices everyday as per the start date of the customer.
View 1 RepliesHi,
Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)
I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? I use SQL server 2008.
View 4 Replies View RelatedWe have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:
YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.
I have the YYMMDDGP part down with the following expression:
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]
Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
hi!
i actually have more than question related to the SQL Server 2000
1) On my system I charge users for using an online service (SMS ) and I have a monthly fees charge and monthly allowance usage so if they exceed the limit I can also charge them for over usage this is an example for a rate that I have in my Rate Table
Rate Id = 1
MonthlyFees £10.0
MothlyAllowence = 100 sms/ month
Extra usage = 0.12 p for each extra SMS
The system should calculate the monthly invoice from the registration date so if you register in 14 April the invoice will be generated every month at the same day (15) each month.
Now! I need to generate and calculate the invoice …..Where shall I do the calculation? In the Business object Layer or in somewhere else? And for invoicing the client every month I might have 20 clients and each one might have different day so the billing should be auto generated also what properties or methods that can calculate or check monthly related to the day so it will run in the way like the phone bill!! any advice is appreciate it!
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help
Just seeing if people have any suggestions for me on this problem, i am trying to produce some form of invoice for a company and finding that cursors if the best method due to having to format the invoice using sql server. However i have come across a problem in a section where i need to have the invoice address and a shipping address i am finding it hard to sort out the spacing for example
Print ' Invoice address'+''+' Shipping address '
Print @Customername+''+@Customername
Print @Address+''+@ShippingAddress
Print @City+''+@ShippingCity
Print @PostalCode+''+@ShippingPostalCode
Now the problem comes that for space reasons I have the datatype for the variables as varchar mainly but when running the report obviously its a problem that it appears disjointed
eg.
Alex JohnsonAlex Johnson
16 Stobson Street 16 Stobson Street
Leeds Leeds
LS12 6GHLS12 6GH
Now is there a way that this can be sorted like should I us char() datatype to specify the lengths,
Also is there a command the insert spaces instead of just using the quotation markers ' '
Cheers in advance sql people
Hi
I have a invoice that displays the company name in a rectangle on top then it has a list in the list it has details tables my problem is that the invoice detail goes on the next page I need to have the company name as well on the second page I cant put it in the same table as the detail because I have a few tables in the detail so it is actually no header is it possible to have Repeated the Company name information on the next page?
Thanks
Hi,
I need assistance with the following please.
I have to display the total for an invoice. The price for the products change over time.
I want the invoice date to correspond with the current period's price.
Thus if invoice date is y, it should calculate using the price between x and z.
Thanks,
Arend
Hi,
I want know if is possible create a report, with this caracteristics.
Page 1:
Header:
Nome of Company Invoice nº 1
Original
Detail:
line 1
line 2
line 3
Page 2:
Header:
Nome of Company Invoice nº 1
Copy
Detail:
line 1
line 2
line 3
.
.
.
Many pages of parameter in my code in Visual Studio.
Tanks,
Camsoft77
Hi there
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
What is the best way to increment my invoice number?
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
Martyn Fewtrell
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
Hi,
I'm using SQL Server 7. I have two tables as follows:
Table Invoices:
CustNo
DocDate
Amount
Table Payments:
CustNo
DocDate
Amount
Can anyone think of a process to find the invoice with no payment for each customer? I need to do some kind of process of elimination; I suppose.
Any Suggestions?
Thanks,
Denise
I am using an invoice image that is preset and a fixed size. I am trying to lay text boxes on top of this image, but the boxes move around differently when you preview and/or print preview. If this is deployed, then the form prints and all of the text box information prints after the invoice image. Has any one experienced this before?
And one more question, is there a way to make a report a fixed length in size, ie, another invoice that might have 1 line or 23 lines and the total always needs to print at the bottom of the report and not move up the page. Is there a way to fix your report to accomodate for that? I have tried list box, matrix, etc and I am unable to find a solution to this.
Any help with either of these questions would be greatly appreciated!!!
Can anyone advise if it's viable to send order invoice reports to customers with SQL Reports.
I have a well formatted report that accepts an order number and generates an email to the customers address but i'm struggling on how to batch send this. Each order record has the email address to send to.
In our orders table I have a 'sent' flag so can easily write a query to bring back all orders that need sending. I just can't work out how to automate running the report for each order. Then after the report has been run for that order it needs to update the 'sent' flag.
Possibly this can work with a SSIS package that does the initial selection and updating of the flag but i can see how to run a report server report from an SSIS package.
Any ideas appreciated.
I have a report that prints 20+ invoices and I need a page break after each invoice.
STEPS TO REPRO:
1) Create a report to show an invoice (one row of data) using a query that pulls 20+ rows of invoice data.
2) Table Properties | "Insert a page break after this table" is checked.
RESULT:
1) 3-4 invoices appear on each page.
EXPECTED:
1) Page break after each invoice.
What is missing?
If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.
0712RW-IN 7/31/2012 8/30/2012 4,462.54 0.00 3,825.75 INV 7/31/2012 4,462.54
C/M 8/31/2012 636.79- Reference: 0712RW
CREATE TABLE [dbo].[INVOICE](
[SRC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ARDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUSTOMERNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Code]....
Hi all - struggling a bit with this one... I have made an accounts application and need to make a report called "Aged Debtors". It lists all of the clients with outstanding invoices and breaks them down into periods:
current (this month)
period 1 (last month)
period 2 (2 months ago)
period 4 (invoices raised 3 months ago or more)
My tables are as follows (simplified)
Clients
clientid int
clientname varchar(100)
invoices
invoiceid int
clientid int
invoicedate datetime
cinvoiceamount decimal(19,2)
paid int (1 = paid, 0 = unpaid)
paidamount decimal(19,2) (how much of this invoice has been paid)
I need the following columns in the report
clientname invoiceid current period1 period2 period3+
test client 1 £100 - - -
2 £50 - - -
3 - £100 - -
test client2
I can format all the report using a matrix in SSRS so just need the stored proc to get me going - any help greatly appreciated!
Thanks guys,
Stephen.
I have a field type of char(7) which holds an invoice number.
It has leading zeros that i want to remove.
0000001 I would like to make it 1.
How can I remove the leading zeros. If I need to replace them with spaces that is fine too.
I have a table with data like the following:
ClientID InvoiceDate
1 2012-01-01
1 2013-01-01
2 2012-01-01
2 2013-01-01
3 2012-01-01
3 2013-01-01
I would like to return a distinct ClientID and also the minimum InvoiceDate for that ClientID, so that the data looks like this:
ClientID InvoiceDate
1 2012-01-01
2 2012-01-01
3 2012-01-01
I need to create an invoice report, each page represents a single invoice. like a form report.
Which control is good, i am new to reports. i have used table control for few of our reports still learning.
Thank you very much for your help.
All I have a table full of invoices and in that table there is a field named HCC. An invoice can contain multiple HCC's. I also have a table named hierarchical codes (below is an example)
(COL1)HCC...........(COL2)If any of the HCCs in this column exist on an invoice along with the HCC in column 1 then use HCC listed in column one
1
2
3.......................4
4
6
8.......................9 ,10 ,11 ,12 ,13
[code]...
I want to return invoice 1 with HCC set to 34 since the hierarchy for when any of these HCCs (35 ,36 ,37 ,38) exist on an invoice along with 34 is 34.
i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing.
Can you help me with SQL issue I€™m stuck on?
I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?
View 2 Replies View RelatedI have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
I have a quick question regarding SQL Server Enterprise Manager. I'm looking at setting up a job to automatically create DDL for a user database. This will be done along with our normal nightly backup routine.
I'm very familiar with using EM to create SQL scripts, but is there anyway to schedule this task? I've considered DTS and some type of scheduled package, but can't seem to find anything similar. I'm thinking I may need a custom task.
Could someone please shed some light on the subject? If not from within EM, how about any third party tools? FYI - I already own the Embarcadero suite and am trying it out wwith that.
Thank You.
Anthony Robinson
hi
I want to generate excel file which contain table name , column name,datatype ,size
how we can do in sql server
is there any way
pleases tell the steps
Server:
SQL 2005 SP2 on Win 2003 Ent. SP1
A 3rd part app is requiring that I create a credential, whick in turn requires an SMK be set. When I try to create the credential, I get an error message indicating a decryption error. When I run the alter command to regenerate the key (without force) it throws an error indicating the key cannot be decrypted. According to a KB article I found, this may indicate that a key has never been generated.
My question is, I have a number of production databases in this instance, including SQL Reporting Services. Except for the SRS DB's, all other user db's are simple db's that don't use encryption. If I run the Force command to generate the key, am I going to break anything? I'm really concerned about report servioces.
Thanks.
I need a tool to generate sql code of database including all data like "insert into table values()". Same as sql file in IBuySpy portal. How can I generate a file like this? I tried with enterprise manager but it doesn't generate insert statements and default values of some fileds lost.
Can someone help me?