I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123 Children: Sale ID # 456, Sale ID #789, Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage ( saleid int ,projectname varchar(200) ,typeofsale varchar(200)
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select projectname ,Count(saleID) As [Total Sales] ,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales] ,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales] FROM SanitationGarbage GROUP BY projectname order by projectname asc
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.
For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:
Month 1: 50$
Month 2: 100$
Month 3: 100$
Month 4: 100$
Month 5: 100$
Month 6: 50$
I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss 06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57 01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table ( PropCode VARCHAR(10) ,VoidStartDate date ,LetDate date ,Market_Rent Money
I have a situation where I want to get running totals of a potentionally very large table. Say I have a million records with a table with few fields. Table structure like this UID value 1 3 2 9 3 4 4 7 5 2
I want to return a result set of something like this select uid, value, (rtotal???) from table
This is to be used for sort of a lotery. Say I have 1 million users with a variety of points tward the lotery. I total the points, is say 5 million, determined the winner to be say 3,234,563 Now I have to determine which uid is the winner. So I planned to to do a running total till the winning value is less then or equal to the running total and I have my winner. I am looking for a fast way to do this on a large database.
I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......
Have a table :
create table test_registrations ( date_maint smalldatetime, user_name1 varchar (255), user_address1 varchar (255), total_users int ) go
If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :
select date_maint , count (1) from test_registrations group by date_maint order by date_maint desc go
The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want the total users for the users on 1st to read 10 and total users on the 3rd to read 25.
I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?
Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.
Declare @Brand as varchar(10) DECLARE @StartDate datetime Declare @EndDAte as Datetime
Set @Brand = 'business' SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime) SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales From
/**********Finds projection per day****************/ (Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS From (Select StoreID, Projection as Projection, Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth From Reporting.dbo.RetailSalesComparison_ProjectionsView Where StoreID between 12000 and 12999 )ttTemp
(SELECT DATEADD(dd,number,@StartDate) as DAYS FROM ( select number from master..spt_values WHERE type='p' union all select number+256 from master..spt_values WHERE type='p' ) as s where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection
(Select Date, StoreID, Sum(Sales) as Sales From Reporting.dbo.RetailSales_byStore_byDay Group By Date, StoreID)ttSales on ttProjection.StoreID = ttSales.StoreID and ttProjection.DAYS = ttSales.Date Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID Where Month(Days) = Month(getdate()) Order By Days, ttProjection.StoreID
We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date
Week to Date Month to Date Year to Date
I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID Branch Group Date Value 1 A C 2015-06-01 10 2 A C 2015-06-02 15 3 A C 2015-06-03 25 4 A C 2015-06-04 20 5 B D 2015-06-01 20 6 B D 2015-06-02 25 7 B D 2015-06-03 10 8 B D 2015-06-04 20
I want the Output like below with a Running value difference in comparison to previous day.
ID Branch Group Date Value Running Value 1 A C 2015-06-01 10 10 2 A C 2015-06-02 15 05 3 A C 2015-06-03 25 10 4 A C 2015-06-04 20 -5 5 B D 2015-06-01 20 20 6 B D 2015-06-02 25 05 7 B D 2015-06-03 10 -15 8 B D 2015-06-04 20 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?