Transact SQL :: How To Calculate Number Of Employees In Each Month
Jun 17, 2015
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
need help how to check in table if all the employees have Full month
if it double days fix it
if the employees have less days ! > move to temp table
for eny problem with this employee (check continuity or error) move to temp table ! i have stored procedure that insert employees for next month "Full month" from start of the month until end of the month how to check continuity and if i don't give less days OR double days in month like if the month is 29 days the employee must have 29 not more
for eny problem with this employee move to temp table !
situation 1 employees have less days ! sn empid ShiftDate day ------------------------------------------------- 1 111111 2008-02-01 Tuesday 2 111111 2008-03-02 Wednesday 3 111111 2008-04-03 Thursday 4 111111 2008-03-04 Friday 5 111111 2008-03-05 Saturday 6 111111 2008-03-06 Sunday 7 111111 2008-03-07 Monday . ---------------------------------------------- 8 111111 2008-03-09 Wednesday 9 111111 2008-03-10 Thursday .......................................................................until end of the month 99 222222 2008-02-01 Tuesday 100 222222 2008-03-02 Wednesday 101 222222 2008-04-03 Thursday 102 222222 2008-03-04 Friday . ---------------------------------------------- 104 222222 2008-03-06 Sunday 105 222222 2008-03-07 Monday 106 22222 2008-03-09 Wednesday 108 22222 2008-03-09 Wednesday 109 22222 2008-03-10 Thursday .......................................................................until end of the month
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 have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.
How can I correct this so that it shows the right % for each of the flags for each month?
Select sk.Period Period, sk.[Invoice Flag], count(*) * 100.0 / Sum(count(*)) Over () Percentage, count(*) Total1 From (Select wh.worknumber [Work Order],
[code].....
Output at present:
Period Invoice Flag Percentage Total1 --------------------------------- ------------------------------- --------------------------------------- ----------- 2015-02 Invoiced after 7 days 5.704697986577 136 2015-03 Invoiced after 7 days 2.097315436241 50 2015-02 Invoiced with Customer Approval 59.563758389261 1420 2015-03 Invoiced with Customer Approval 32.634228187919 778
I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions-
I have a Table1 which contains C1-acountid C2-date C3-grossamount(postivie and negative decimal values) C4-netamount
Table2 C1-groupid C2-accountid
Table 3 C1-groupid C2-groupname
I need create a store procedure to retrieve the following on a single table
1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day NOTE:These 10 AccountIDs may be sam or differing each day 2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month. NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date. 3.Sum of Netamount for the last 5 days for each accountids in STEP1
The result set must contain the columns as below
C1-accountid C2-date C3-net loss for 10 losers on the current date since the beginning of the month C4-Sum of Net for last 5 days C5-groupname
Please help me.
Below is the script that i have written, without calculating the sum
( select top 10 a.date, a.accountid, a.gross, a.net, c.groupname from GBSys_Sum_EOD a
join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id join server2.dbname.dbo.Table3 c on b.groupid=c.groupid
where date> getdate()-1 and gross< (floor(-00.00)) order by gross )
i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number , concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year , cast(fh.transaction_date as date) as transaction_date , sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
I have written two programs; One which sum the total cost for services provided and one which pulls in the projected budgets for the services. The budgets can be renewed once a year or every six months. I’m having trouble joining to the programs together so I can calculate the % of budgets used for each waver service by month.
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'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE (YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month) OR (YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
I need to add days to a date field, my date field is as varchar(20041030 for example) and I need to add 4 days to it, my result should be 20041103, result field is also in varchar,how would I do that, can anyone pls help?
What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?
How do I output a row number for a table solely for the purpose ofquerying for a unique row?In my problem, the table from a legacy system does not have a primarykey, so it limits various querying I'd like to do that identifiesuniqueness in the table.The problem is that since I'm using DTS to simply copy the table toSQL, I don't want to create identity rows.
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
There are three methods to consider when calculating the days to pay logic.
Method 1 - Simple : Look for Document Type 2 (Invoice), if "closed at date" > "posting date" then number of days = ("closed at date" - "posting date")
Method 2 - A Document Type 1 (payment) closes a Document Type 2 (Invoice) For this method the formula would be: Payment Record (1) "posting date" - Invoice Record (2) "posting date"
Method 3 - An Invoice closes the payment.
On a payment entry “closed by entry no.” refers to an Invoice entry.
a. In our code we are not on the payment looking for the invoice, we are on the invoice. i. Because of this we need to find the entry that our current invoiced has closed.
I am taking this from a page that has the pascal code that I need to translate to SQL.
IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND NOT CustLedgEntry2.Open THEN IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")
[Code] ....
I am also including create table and insert data scripts ...
I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .
I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]
This is my query so far
SELECT [CreatedDate] ,[StageName] ,[CloseDate] ,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age FROM [FDMS].[Dan].[Raz_Reporting_LCS]
I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.
I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.
Hi, I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt FROM tblCalendar AS c WHERE (@WorkingDays = (SELECT COUNT(*) AS Expr1 FROM tblCalendar AS c2 WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1) AND (IsHoliday = 0)
-- Return the result of the function RETURN convert(varchar(12),@WorkingDate,106)
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime ) INSERT INTO @EventLog SELECT '100','Inplace','01/05/2015 05:00' UNION ALL SELECT '100','Removed','03/05/2015 10:00' UNION ALL SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK") FROM ruleResults;
I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:- Wednesday 1st Thursday 2nd Friday 3rd ... etc
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?
