tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
I am trying to determine the amount of days in a month to prorate a month end estimate. We measure service calls and need to approximate how many we will have at month end. I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart. However , I don't know how to create a datetime variable this way.
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment) ,( ((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
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 three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year FROM EMP_DATE DTE join EMP_INFO EMP on EMP.ID = DTE.ID join YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
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 .
CREATE TABLE OrderRanking ( OrderID INT IDENTITY(1,1) NOT NULL, CustomerID INT, OrderDate date ) INSERT OrderRanking (CustomerID, OrderDate)
[Code] ...
Looks fine but what I need is DRP with this:
CustomerID OrderDate 'DRP taking care of the gap in the days' 1 '01-01-2015' 1 1 '01-01-2015' 1 2 '02-01-2015' 1 2 '02-01-2015' 1 2 '05-01-2015' 4 2 '05-01-2015' 4
Hi, I just want to know, is there anything more better solution than this one to find out the no of days in a month ? I have done this but I am not satisfied,anybody has a smarter solution? Plz comment..
My Solution :
/* check leap year*/ if year(getdate())%4<>0 set @noofdays=(select case month(getdate())-1 when 1 then 31 when 2 then 28 when 3 then 31 when 4 then 30 when 5 then 31 when 6 then 30 when 7 then 31 when 8 then 31 when 9 then 30 when 10 then 31 when 11 then 30 when 12 then 31 end ) else set @noofdays=(select case month(getdate())-1 when 1 then 31 when 2 then 29 when 3 then 31 when 4 then 30 when 5 then 31 when 6 then 30 when 7 then 31 when 8 then 31 when 9 then 30 when 10 then 31 when 11 then 30 when 12 then 31 end)
So Im trying to find the number of days in a certain month I know how I want to do but cant figure out how to code it
i want to take the first date of the getadate() and then ad a month then subtract 1 day and ill have the number of days in a month how would i code that?
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
How can I calculate the no. of days in a month.I know it is a simple question but I am not able to find any code to calculate that. Can someone help me?
I want to retreive users that had their birthdays 7days before today and will have their birthday 7 days from now (so 14 days in total).What would the SQL for such a thing look like?I now have ths, but that doesnt work when you are at the beginning of a month (march 1). So there must be a more clever way :)select firstname from userswhere (month(ud.birthdate)=month(getdate())) and (month(ud.birthdate) between month(getdate())-1 and month(getdate())+1 ) and (day(ud.birthdate) between day(getdate())-7 and day(getdate())+3 )
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
SQL QUERY SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
Hi all, I have a table in which I have two fields in my DB. FromDate and ToDate. Both are stored as Varchar(MAX). I would like to have an SP which gives me the Days in Between them. Regards, Naveen.
Hello, I have a table with a field Expiration of smalldatetime. I want to only display the data that has 60 days or more between Expiration and todays date. Any help would be highly appreciated.
I wish to schedule a DTS package so that it runs on the penultimate day of each month. Is there an advanced option which allows you to choose a different date for each month? If not, is there a way this can be done programatically/
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 )
with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
