If i pass a date(03/01/2008) i need to get the startdate(31/12/2007) and enddate(05/01/2007) of the week. Is there any functionallity in SSRS 2005 to do this.
Now say I want to filter on this data by startDate and endDate, however I have to return any offer that is valid for a specific period. E.g. if I filter by startDate = 2005-09-10 and endDate = 2005-10-01 then I want it to return the offers with the ID of 1 and 2 as both of these are valid for the dates I'm filtering on.
I can't for the life of me figure out how to do this at the moment, maybe it's too early in the morning to be thinking about this.
I want to display 2 weeks Events from Table 1 depends on repeat count
(if event scheduled on Tuesday(2014-11-04) and repeat count is 4 then it should repeat the 2 row of data)
We have to display only upcomming events, if the repeat count is 0 and the startdate and enddate falls by todays date then it should display as a single row.Data's Should be ordered by recent date
For some of my calculations it is mandatory to get the first and the last date, filtered by the slicer type "timeline".
This works well as long as the date is not used on rows or columns ...
As soon as I'm using theDate within the rows this very simplistic dax measure doesn't work ...
I'm wondering how to determine the StartDate (from the timeline) and the EndDate (from the timeline) when "thedate" is actively contributing to the filtercontext.
In my opinion it is not another example of the "event in progress" situation, due to the fact that start and end dates are actively selected by the user and can not be derived from within the date model.
Hi, I have created a report in SQL reporting using serveral parameters including a date parameters called StartDate EndDate
Now my requirement is, in ReportViewer I need to validate in such a way where "StartDate" should not be greater than "EndDate". So kindly help me out by giving me possible solution for the above issue.
I'm using some files to show certain pages on certain date for an example
File name : aa.doc start date: 10/02/00 end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page Here is my stored procedure:
" SELECT startdate, enddate,archivedate and (startdate is null or (getdate() >= startdate and getdate() <= enddate)) and (archivedate is null or (getdate() <= archivedate)) group by startdate, enddate order by startdate desc "
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?
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 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 am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable ( Name varchar(50), GA int, GB int, startdate DATETIME, enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate Pavan 261/1/20151/1/2015 Hema 561/1/20151/1/2015 Surya 501/1/20151/1/2015 Pavan 811/2/20151/8/2015 Hema 311/2/20151/8/2015 Surya 121/2/20151/8/2015 Pavan 1041/9/20151/15/2015 Hema 301/9/20151/15/2015 Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal Total 8 11 5 24 GA 2 5 5 12 GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal Total 9 4 3 16 GA 8 3 1 12 GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal Total 14 3 19 36 GA 10 3 6 19 GB 4 0 13 17
I can fetch the counts for total present and absent
Query i have tried is
Declare @StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458' Begin select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000 System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000 System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000 System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000 System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000 System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I have a pretty large DB and a fairly complex query. If I drop buffers and clear cache the query runs in 20 seconds returning 25K rows. Subsequent runs are 2 seconds. Is this the result of the results being cached, execution being cached, other? Are there good ways to close the gap between the initial and later runs? Does the cache stay present until the service restarts or does SQL recycle the memory and if so, based on what criteria?
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 would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference ( id INT ,start_date INT ,end_date INT ) INSERT INTO datedifference VALUES (10,20091202,20100629) INSERT INTO datedifference VALUES (20,20071202,20090330) INSERT INTO datedifference VALUES (30,20051202,20101031)
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB) 279938496 Version Generation rate (KB/s) 53681040 Version Cleanup rate (KB/s) 53422320 Version Store Size (KB) 258720 Version Store unit count 22 Version Store unit creation 774 Version Store unit truncation 752
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I was trying to use inline views, then sub-query etc. Still can't get quite what I want.
So for example take a personID where they are registered, processed and deregistered. They can have one or more registrations, one or more related processes (for each registration) and then deregistration, the processes are always tied to a registration and deregistration period.
for a given time-period I want to find the first registration and use that first registration to find subsequent registration records within 20 days - I don't want to return processes related to the next registration record, just the next registration record.
I already have the query built that returns all the raw data for those registrations, processes and deregistrations but need to set up something that finds subsequent registrations. So here is a sample of raw data:
firstly need to use all first registrations - that would be row where reg-date = process first date and also reg time and process first time are equal - and use those for finding the next reg_date of that personID. PersonID can have more than one registration in a period so need to do something like partition, which although it worked on it's own, seemed to become a tangled mess when I started using the subset in a inline view or sub-query. Not sure how to return earliest reg date in period for certain PersonID for multiple IDs.
I have made the dates the same in last 4 rows, this isn't intentional and doesn't mean anything it's because I copied and pasted the first 4 rows.
How to tie this together using first reg of each personID and then comparing with other records to find subsequent first registrations. I'm not too fussed about finding them within a certain time period from the first registration, more that I want to just get some sort of query set up then I can put in specifics.