I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.
I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.
Is this a possibility? Or am I going about this all wrong?
helloi need to write an sql which calculates # of billing cycles for anorder for which ive order start and end date. the first invoice willbe cut on the order start date. Also if i apply a coupon for lets sayone month (30 days) to that order i need to calculate how many billingcycles that coupon will apply to.ive start and end date of coupon ina diff table for that orderfor eg: if an order was started on 1/15 and ended on 4/14 i have 3billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and3/15)lets say on 3/15 if a users applied one month coupon which means hedidnt have to pay for billing cycle 3/15.can someone help me with this logicthank uml
Table & Columns: ID field is identity. Currency is either CDN or USD. Order: ID, DateOrder, Currency, AmountOrder Invoice: ID, NumOrder, DateInvoice, AmountInvoice Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit Rate: YYYY, MM, US
Relationship among tables: Order & Invoice : on Order.ID = Invoice.NumOrder, one to many Order & Credit: on Order.ID = Credit.NumOrder, one to many Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many
Other conditions: For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN. The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN. A credit always uses the rate of DateInvoice of its associated invoice.
Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN
Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)] WHERE the DateInvoice and DateCredit must be in the specified period. AND associated Order.Devise = USD
SQL: 1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling 2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder 3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice 4 WHERE Order.Devise = ‘USD’ 5 AND DateInvoice >= startDate AND DateInvoice <= endDate 6 AND DateCredit >= startDate AND DateCredit <= endDate
Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged. Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period. This is where I am getting stuck. Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.
In trying to solve the problem, I tried to frame a calculated field formula but could not do so. Therefore, I tried solving it via a calculated column formula in the PowerPivot window. My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result. I am getting an error when I write this formula (see Billing data tab of PowerPivot window).
hai, iam new to ssrs pease help me below calculations.iam taking the parameters as begin date,Enddate,region,year,site,
My report having outstanding bills information, i need to calculate, here resters are nothing but students, you take any relavent input data.
1)Rosters Outstanding 6-10 Days (Rosters expected, but not received for up to 10 days in the rears), 2)Rosters Outstanding > 30 Days 3)Rosters Current (Rosters expected as of the report end date) 4)Total # Rosters Outstanding (Total rosters expected, but not received) 5)Rosters Queued For Entry Into 3rd Party Systems (Total rosters entered into the SIS System and expected to be entered into, but not already in 3rd party systems) 6)Rosters Entered Into 3rd Party Systems (Total rosters entered into 3rd party systems) 7)Display Total Billable Dollar Amounts Associated With All Outstanding Rosters For All Aging Columns At Bottom Of Report By Daily, Weekly, Monthly, SYTD
I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.
Input values:
Starting Date: 09/14/2007 End Date: 12/06/2007
Monthly Report :
Start Date - End Date - Number of companies 09/14/2007 - 09/30/2007 1 10/01/2007 - 10/31/2007 0 11/01/2007 - 11/30/2007 4 12/01/2007 - 12/06/2007 0
Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(
I need to run a script on a monthly basis (e.g at midnight on the last day of the month) which selects all the records from that calendar month. I have the below so far however as each month has a different number of days in it this will not work well. Are there any date/time criteria I can use in my tsql statement which will only select records from that month no matter how many days are in the month? I obviously need to move away from manually updating the script each month depending upon how many days are in the month! SELECT *FROM UserLogwhere logtime >= (getdate() - 30)order by logtime asc cheers marco
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productive that keeps track of the current bookings.
I would have access to the enterprise edition if that changes anything at all.
I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:
Name StartEmp EndEmp
tom 1/jan/05 15/mrt/05
dirk 1/jan/05 31/mrt/06
jan 1/feb/05 NULL
In order to get the right information for the report the information should be represented in the following way:
January 2005 February 2005 March 2005 "April" 2005
Tom 1 1 0,5 1
Dirk 1 1 1 1
Jan 0 1 1 1
HeadCount 2 3 2,5 3 (there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )
I need some help about building up the query to aggregate the info from table1 to the format of table2.
I am looking for a script whichs exports data (by DTS?) into a flat file and store the files (according their date stamp in the transactions) with a name like 05_2002.txt, 06_2002.txt etc. The data in the table Transactions will be deleted after some time to prevent fast growing of this particular table.
I have created a database table in MSSQL 2000 like this
[empcode] [leave_date] [type] [reason] 100 2008-12-29 00:00:00.000 T Tour 100 2008-12-30 00:00:00.000 T Tour 101 2008-12-31 00:00:00.000 CL Casual Leave 102 2009-01-01 00:00:00.000 R Restricted holiday 100 2009-01-02 00:00:00.000 T Tour
This table contains only leave details.... but i need to create monthly attendance report such as below
empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ............. 100 P P P P S P T CL P P P S P P T ............ 101 P T R R S R R T CL P P S P P P.............. 102 P P P P S P P P P T T S CL P P P............
I want to display the reports in weekly format suppose
today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...
my report looks like Weekly Report (09/27 - 10/12) Week====09/27-09/30======10/01-10/07======10/08-10/12 Sales======50===============100===============80
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column. When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.
My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'
I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this
Maybe a case?
Thanks for your help guys. Let me know if any more info would help.
I would like to run a report for each month over two years. I am currentlyusing a date range like this. Then manually substitute the error_timebounds for each month and rerun the query. How can I script this so I canprogrammatically perform the substitution in a loop. Thanx in advance.select count(*) from application_errorswhere error_message like 'Time%'and error_time >= '1Apr2004' and error_time < '1May2004'
I'm scheduling monthly subscriptions in reporting services. I would like to receive subscriptions every 2nd Monday of the month. For instance, if the 1st is a Friday, then report should be delivered Monday, the 11th.
So my question is: in reporting services:
if I set the subscription for Monday - 2nd week, and the first day of the month is a Friday
- does it consider the week from 4 -> 10 as the second week (because month began the week before)
- or as the first week (because it takes into account only full weeks)?
My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'
I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this
Maybe a case?
Thanks for your help guys. Let me know if any more info would help.
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
Hi Guys, I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen (or the stored procedure) so i need some help for SQL Gurus. Following are the details: 1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month) 2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day 3. There are around 500 employees data im dealing with 4. The user will select any given department and employee's data for the respective department has to be displayed for any given month 5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen 6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee 7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in the attendance table, we are dealing with approximately half a million attendance records. Thanks and waiting for your suggestions...
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
I have a table based around requisitions, and each requisition has a number of positions. That number can change over time through updates to pertinent rows rather than through transaction-like records that record an entire history, and I'm only able to get a monthly snapshot of the table. What I decided to do is still use one table for OLAP (fact_requisitions) but add a column called period_key that refers to the month the data comes from. So if I have two months of data then the table has each requisition twice, possibly with differing position counts, and new requisitions from the second month are only present once. Then I tried to filter the MDX query like so:
SELECT { ([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Requisitions].[Period].[Period Key].&[200801]) } ON COLUMNS, NON EMPTY { ([Dim Location].[Region Name].MEMBERS, [Dim Location].[Period Key].&[200801]) } ON ROWS FROM [Requisitions] WHERE [Measures].[Request Closed Date Count]
This query doesn't work even though the data is there, it just returns nulls. Am I going about this all wrong? If not, what might I be doing wrong, and how would I get the query to return more than one period (e.g. tell Dim Requisition to match up with Dim Location on the period key)?
How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.
We have two tables:
ITEM_SALES which consists of: Item_ID Name Store Sales_Date Sales_Price Quantity
And then
ITEM_DISCOUNT which consists of: Item_ID Name Store Sales_Price Date_From Date_To
Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.
To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.
Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.
I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..
Im having big trouble designing the WHEN part of the procedure.
CASE ( when Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then.. when Isnull(dp.rabatt, Isnull(x.rabatt, id.rabatt)) then.. when Isnull(ds.rabatt, Isnull(y.rabatt, id.rabatt)) then.. end) AS 'Discount',
The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here:
LEFT OUTER JOIN discount AS id ON id.identifiers = isa.identifiers AND id.store = BV.name AND id.from_date <= isa.sales_date AND id.to_date >= isa.sales_date AND id.to_date < '2999-01-01' LEFT OUTER JOIN discount AS u ON u.identifiers = isa.identifiers AND u.to_date = '2999-01-01'
The two others tables are designed in similar ways
but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY, dd.month_number FROM sales_fact sf, date_dim dd WHERE dd.date_id = sf.date_id AND dd.month_number = 1 GROUP BY dd.month_number;
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY, dd.month_number FROM sales_fact sf, date_dim dd WHERE dd.date_id = sf.date_id AND dd.month_number = 1 GROUP BY dd.month_number; Error at Command Line : 1 Column : 53
Error report - SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"
