![]() Table1Line.Column8 - ROUND(. Column7 = 0 THEN 'PVM14'ĬAST(.Column7 AS int) AS TaxPercentage, Column4 AS Name,ĬAST(.Column5 AS date) AS InvoiceDate, Additional resources: Payables SQL views. To test above queries you need to have records of last 15 days or last 6 months ( or 12 Months ). Please advise if I need to create indexes or if there is another way to optimise this query. If you would like to use different aging buckets, just follow the examples in my code and change the labels and number of days. 90 Days should be a sum of invoices raised. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. ![]() The Current value should be a sum of invoice totals from February. Right now it seems that I would need to index datetime column. basically, Say I print the report on 15th Feb. I would like to optimise this query so that it wont go through the entire tables looking at the datetime and would only search from the latest entries. This query takes about 5 seconds to complete. The problem is that when I run the select query the sql server throws a timeout while the query is running and new records are not created during this time frame. So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently.I have a query that does a select with joins from multiple tables that contains in total about 90 million rows. For example, if the current period is Last 28 days, the comparison is to the previous 28. So it does: selects any matching rows, deletes the table from the DB, and ignores anything else. To setup date ranges in your reports, youll do the following. SELECT * FROM MyTable WHERE StreetAddress = 'x' A perfectly valid SELECTĭROP TABLE MyTable A perfectly valid "delete the table" command SELECT * FROM MyTable WHERE StreetAddress = 'x' DROP TABLE MyTable -'Which SQL sees as three separate commands: If I come along and type this instead: "x' DROP TABLE MyTable -" Then SQL receives a very different command: SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'The quote the user added terminates the string as far as SQL is concerned and you get problems. Open a Query Analyzer window and run the following command (s) CODE Select GetDate () Select DateAdd (Day, DateDiff (Day, 0, GetDate ()), 0) Notice that the first one will show you a date AND a time. When you concatenate strings, you cause problems because SQL receives commands like: There are various ways to remove the time component from a datetime variable/column. ![]() It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. ![]() Never concatenate strings to build a SQL command. Never do that - it's extremely dangerous. At the moment, if a client changes his mobile, how do you know which row to UPDATE? You should have an ID column - IDENTITY is fine - to give you an absolute reference to the column when you need to change it. Start by looking at your data - until you CONVERT it it has no formatting info at all, it's stored as "ticks since a specific point in time" rather than years, months, and days - and get that right (I'd use C# or VB to read the row, make sure it's right, and UPDATE sql where necessary)īut looking at your database definition from your comments below, there are other problems here.ĭon't store TIME info separately from DATE, and always store DATETIME info in UCT, not local time - that way when the local "summer" or "daylight saving" time comes in or out, your DB doesn't get filled with inconsistent data.ĭon't store "client type" as a string, create a second table, and use a foreign key to index it.ġ00 chars is not a good idea for Mobile numbers, or email - in one case it's too big, in the other it's too small! But suppose if I choose any date from the date filter the End Date. By default the above should work from Today's date (End Date). This function is useful for formulas where you are concerned with how many days have passed since a previous date, the date of a certain number of days in the future, or if you just want to display the current date. Dear Techie, I have a requirement where I need to calcuate 'Accounts Receivable' for last 30 days, 60 days and above 90 days. TODAY(), NOW() and TIMENOW() The TODAY() function returns the current day, month, and year as a Date data type. So when you changed your NVARCHAR column to DATETIME, SQL had to try and work out what each date was. DAX formula to calculate last 30 days, 60 days and above 90 days. That's why you can't store dates as NVARCHAR - when SQL has to convert them back, it will guess what format it is, and most likely get it wrong, in some cases if not all.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |