I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month FROM CPTable where year(ReportDate) = '2015' GROUP BY Standard, Standard , month(ReportDate)
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',
[Code] ....
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.
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
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)
[code]....
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.
I need to get the sum of sales for the last date of each month group by custom and by month(fecha)
for example, for custom ='q' in month=8 I have 3 items in the last day of the month ='2014-08-15' totalling 13 and so on
DECLARE @sales TABLE (custom VARCHAR(10) NOT NULL, fecha DATE NOT NULL, sales NUMERIC(10, 2) NOT NULL); INSERT INTO @sales(custom, fecha, sales) VALUES ('q', '20140708', 51),
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:
Code Block
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:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
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 am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.
Client,Month,Year and Comments are columns in that table.
For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.
If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.
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.
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As ( SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
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.
Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName from (SELECT Standard, COUNT(Standard) AS Total, datename(month, ReportDate) as [MonthName] FROM CPTable where ReportDate >= @ReportDate
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 ?
On SQL Server 2005 at least, this works efficiently where we have an indexed row number.
It does seem to be very sensitive to the join condition in the recursive part of the CTE: changing it to the equivalent "ON T.rn - 1 = RT.rn" results in a scan of T each time instead of a seek!
DROP TABLE dbo.T
-- rn must have contiguous values for this to work: CREATE TABLE dbo.T (rn int PRIMARY KEY, f float NOT NULL)
-- 100000 random floats between 0 and 1: INSERT INTO dbo.T SELECT n+1 AS rn, RAND(CAST(NEWID() AS binary(4))) AS f FROM dbo.Numbers GO
;WITH RT AS ( SELECT rn, f AS rt FROM dbo.T WHERE rn = 1
UNION ALL
SELECT T.rn, RT.rt + T.f FROM RT INNER JOIN dbo.T AS T ON T.rn = RT.rn + 1 ) SELECT * INTO dbo.TRT FROM RT --ORDER BY rn OPTION (MAXRECURSION 0)
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
Right Join
(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
Left Join
(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
Item Number
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?
The below query is for november month: (Apr to Nov)
select * from maintable where period between '01-04-2014' and '01-11-2014' then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable where period between '01-04-2014' and '01-12-2014' ..... .... select * from maintable where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
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?
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year 25,000 July 2014 25,000 August 2014 25,000 September 2014 25,000 October 2014 25,000 November 2014 25,000 December 2014 25,000 January 2015 25,000 February 2015 . . . .