Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end ) from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014' groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
CREATE TABLE #Attendance( [ID] [int] IDENTITY(1,1) NOT NULL, [StudentID] [int] NOT NULL, [ClassID] [int] NOT NULL, [DateAdded] [datetime] default getdate() NOT NULL ) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count] 1 15 2 5 3 6 4 10 (until 52)
Result:
[Week] [Count] 1 15 2 20 3 26 4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/ CREATE TABLE Test( [Week] [int] NOT NULL, [Count] [int] NOT NULL ) GO INSERT INTO Test ([Week], [Count]) VALUES (1, 15) INSERT INTO Test ([Week], [Count]) VALUES (2, 5) INSERT INTO Test ([Week], [Count]) VALUES (3, 6) INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
I have been trying to calculate age and the results either round the age up one year or down one year. I have tried CASE, DATEDIFF, FLOOR functions but nothing works.
I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns
I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.
Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.
SELECT 1, 10 UNION ALL SELECT 2, 10 UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date) SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
How can I calculate the combined table size for the following: The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
INSERT INTO [Table] SELECT '100','1','DD',1000000,'999999000', '2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502', '101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'
EXECUTE sp_spaceused 'Table'
name | rows |reserved | data |index_size | unused [Table] | 1 |16KB | 8KB | 8KB | 0KB
i couldnot understand i) how the system calculate this 16 KB and 8 KB ? ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ? iii) how can i see the change in size of table by reducing the size of datatype ?
I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".
In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)
How to calculate the values from a underlying table?
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
electric: id_electric indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_electric money, status bit
Water: id_Water indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_water money, status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year' , Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes' From lodgings_Contract a , electric b , Water c Where a.id_room = b.id_room And a.id_room = c.id_room And b.status = 1 And c.status = 1 Group by Year(day_register)
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
I am trying to move data from one table to the another (staging to real time) in a stored procedure.
There are no indexes or primary keys on the target table and it is still taking ages to execute it (30 minutes approx.). There are no defaults, no constraints as well. There is one identity int column though.
There are some 500000 odd rows in the target table.
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000 ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?
when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.
I have table like below
Custid, department, location 1 Fin NY 2 Acc NY 3 HR MI 4 5 6
In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field. Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.
I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists. Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.
I know I have done this type of change before with no issues. What may have changed?I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.
What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.
CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100)); INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES ('# Ready To Work Inventory','606'), ('OB Dial Attempts','255');
