Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
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
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
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 ' '
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?
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.
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.
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.
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.
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
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.
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.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
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.
We 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.
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
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.
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I have a company that sells fruit and vegetables to the catering industry. I take orders in the evening for the next day, and buy my fruit and vegetables from wholesale market to deliver to my customers on the next day. I have sage simply accounting. I have to enter invoices day before I print them, so I can get a list of items i should buy next day (and also sort them into different routes) My question is, is there a way to update all the prices on the active invoices (i.e. not printed or posted) for the previous day, after I enter new prices for the fruit and vegetables??? I need this as at the moment I hava to go into individual invoices and enter the products and quantities again.
