Report Containing Sequential Dates (removing Date Gaps)
Jul 23, 2005
I have a table containing typed log entries. One log entry is supposed
to be created every twelve hours, but sometimes there are gaps. I need
to create a report showing the time of entry, and the actual log entry.
I can't just list the contents of the log table, because if I do that
there will be dates missing. Instead, when there isn't a log entry for
a date, I need to print the date, and then just leave the log entry
blank.
The SQL bellows shows what the output should look like. HOWEVER, the
code below makes use of a temp table containing all possible dates. My
question is, is there a better way to do this - one that doesn't
involve the temp table? Thanks in advance.
I couldn't find a topic suitable for testing this, so I thought I'd start one.
Here is one way to get the islands without a tally table.declare@test table (symbol char(3), dt smalldatetime)
insert@test select'abc','01/01/1990' union all select'abc','01/02/1990' union all select'abc','01/03/1990' union all select'abc','01/04/1990' union all select'abc','01/05/1990' union all select'def','01/03/1990' union all select'def','01/04/1990' union all select'def','01/05/1990' union all select'def','01/06/1990' union all select'def','01/07/1990' union all select'ghi','01/01/1990' union all select'ghi','01/02/1990' union all select'ghi','01/06/1990' union all select'ghi','01/07/1990' union all select'ghi','01/08/1990'
selectsymbol, min(dt), max(dt2) from( selectt1.symbol, t1.dt, t2.dt as dt2, (select count(distinct t3.symbol) from @test as t3 where t3.symbol < t1.symbol and t3.dt <= t1.dt) AS r from@test as t1 inner join@test as t2 on t2.symbol = t1.symbol wheret2.dt - 1 = t1.dt ) as d group bysymbol, r E 12°55'05.25" N 56°04'39.16"
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
INSERT INTO #SessionTest SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter. The function is below:-
-------------------------------------------------------------- Shared Function GetStartingMonths() as String dim strDefault as string dim CurrentMonth as String Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now) CurrentMonth = "5/1/2002" Do While CDate(CurrentMonth) <= Now SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear" CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then Exit Do else sqlString = SqlString & " Union " end if
Loop
return SqlString
End Function --------------------------------------------------------------- what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression =Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days. it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc
I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated
--my first step is to get the activitys that enter the last 90 days select * from Activity2 where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())
CREATE TABLE [dbo].[InternalUser]( [userid] [int],-- IDENTITY(1,1) NOT NULL, [fullname] [varchar](50) } insert into [InternalUser] values(1,'a') insert into [InternalUser] values(2,'b') insert into [InternalUser] values(3,'c') insert into [InternalUser] values(4,'d'); insert into [InternalUser] values(5,'e'); insert into [InternalUser] values(6,'f');
CREATE TABLE [dbo].[Activity2]( [activityid] [int] NOT NULL, --IDENTITY(1,1) [userid] [int] NOT NULL, [startdate] [datetime] NULL, [enddate] [datetime] NULL )
insert into [Activity2] values(1,1,'2007-02-15 00:00:00.000','2008-03-15 00:00:00.000') insert into [Activity2] values(2,2,'2007-01-01 00:00:00.000','2008-01-02 00:00:00.000') insert into [Activity2] values(3,2,'2008-01-20 00:00:00.000','2008-04-10 00:00:00.000') insert into [Activity2] values(4,3,'2008-02-20 00:00:00.000','2008-10-10 00:00:00.000') insert into [Activity2] values(5,4,'2007-01-01 00:00:00.000','2008-01-16 00:00:00.000')
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.
SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL
[Code] .....
-- expected result
SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1 PoNo :- Po No 1 ShipDate :- Oct 5, 2007
OrderNo : 1 PoNo :- Po No 2 ShipDate :- Dec 5, 2007
OrderNo : 1 PoNo :- Po No 3 ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1 PoQty :- 2000
PoNo :- Po No 2 PoQty :- 3000
PoNo :- Po No 3 PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number 2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3 DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
I have two tables, one contains a list of month end dates and one contains a list of resource requirements. I want to show the resource required by month, where the month end date falls between the start and end of the resource requirement date. So far I have:
Select Sum(tblResRequirements.FTE) As RequiredFTE, vwMonthEnds.MonthEnd From tblResRequirements, vwMonthEnds Inner Join on MonthEnd between StartDate and EndDate Group By vwMonthEnds.MonthEnd
Which isn't working as the Join is not correct
What is the right code to use to get the join to work?
I'm just getting into using sql server 2008 and the report builder 3.0 addon after a long time away from sql and I was wondering if the following was possible.I have a number of records with a start and end date along with a volume i.e.
Essentially this means for each day between the two delivery dates the stated volume is delivered, what i need to try and do is to find the volume for each day between two user entered dates, giving a result along the lines of
Start Date Entered = 01/06/12 End Date Entered = 03/06/12
Date; Total Volume 01/06/12; 80,000 02/06/12; 80,000 03/06/12; 30,000
Is there a way that I can get a resultset that contains unique dates ina given date range without the need to have a temporary table and acursor?perhaps something like:declare @start_date as datetimedeclare @end_date as datetimeset @start_date as '1/1/2005'set @end_date as '1/1/2006'select fn_getuniquedate(@start_date, @end_date)1/1/20051/2/20051/3/2005...12/31/2005
I have a drill down report which need to be exported to excel. But, when exporting to excel it shows many unwanted blank rows for a particular record due to the drill down option.
Is it possible to remove it?
I need to exclude the rows in yellow color when exporting to excel.
Table name Employee =============== emp_id, emp_name, emp_city
Table name EmpStatusReport =================== emp_id, action date
I need to write a sql query to get the emp_name, emp_city and the recent date when the user has sent status report over last 30 days. The user has sent a status report if action field in the empStatusReport is set to 'reported'. This table gets filled everytime user sends report.
I tried to the do the following:
select e.emp_name, e.emp_city, esr.date from employee e, EmpStatusReport esr where e.emp_id and esr.emp_id and /* esr.date = max(esr.date) and esr.date > currentDate - 30 and esr <= currentDate */
I am not able to write a correct login for date part. Any help in this will be highly appreciated.
I have a field called 'LOG_COMMENTS' in a table named T_PRODUCTION_WORK_LOG.
In the 'LOG_COMMENTS' whenever a request is placed on hold comments are added by the application, such as 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' along with a 'LOG_DATESTAMP' field. A request can go on and off Hold multiple times, how do I determine the days a request is On Hold?
I know I can use the sql function DATEDIFF ( datepart , startdate , enddate ), but how do I account for the possiblity that the request was On Hold more than once? And how would I get LOG_DATESTAMP' times for 'LOG_COMMENTS' that contain 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open''?
I am currently working in the sql server 2000 environment and I want towrite a function to pull all dates within a given date range. I havecreated several diferent ways to do this but I am unsatisfied withthem. Here is what I have so far:declare @Sdate as datetimedeclare @Edate as datetimeset @SDate = '07/01/2006'set @EDate = '12/31/2006'select dateadd(dd, count(*) - 1, @SDate)from [atable] vinner join [same table] v2 on v.id < v2.idgroup by v.idhaving count(*) < datediff(dd, @SDate, @EDate)+ 2order by count(*)this works just fine but it is dependent on the size of the table youpull from, and is really more or less a hack job. Can anyone help mewith this?thanks in advance
i have a table like below CREATE TABLE #Test (FromDate DATE,ToDate DATE) insert into #Test VALUES ('2015-08-08','2015-08-11') insert into #Test VALUES ('2015-08-13','2015-08-16') insert into #Test VALUES ('2015-08-19','2015-08-21') SELECT * from #Test drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE) insert into #Result VALUES ('2015-08-08') insert into #Result VALUES ('2015-08-09') insert into #Result VALUES ('2015-08-10') insert into #Result VALUES ('2015-08-11')
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>" SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>
<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1"> <HeaderTemplate> <ul> </HeaderTemplate> <ItemTemplate> <li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li> </ItemTemplate> <FooterTemplate> </ul> </FooterTemplate> </asp:Repeater> This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.
I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
Select datediff(dd,Target_Date,Achv_Date) Now , checks are 1] when target date greater than achv_Date the difference should be greater than 0 means for FileID 77608 Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87%
I'm currently having problems exporting formatted dates from reporting services 2005 to excel.
Basically what I require is a way to format a date in reporting services so that it only shows the date without the time (preferably british format) and when it is exported to excel it is still formatted as a date.
This is so the user can sort the data file via date, I appreciate it is easy to select the column and format the cells but i would prefer to have a 'cleaner' solution to this problem which avoids the need for users to be formatting exported reports.
Originally I was formatting the dates as convert(varchar,@date,103) in the SP which converts it to a character string and excel picks this up as a character as would be expected. So I changed this to a date and set about trying to format the date in Reporting Services, so far i've been unsuccesfull using cdate (brings back the time) and format as it again converts it to a character string.
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE ( InputDate DATE ) GO INSERT INTO TEST_DATE VALUES('01-01-2015') INSERT INTO TEST_DATE VALUES('06-25-2015') INSERT INTO TEST_DATE VALUES('11-23-2014') GO SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then First Quarter End Date would be Previous Quarter End Date Second Quarter End Date would be Current Quarter End Date Third Quarter End Date would be Next Quarter End Date Fourth Quarter End Date would be Next +1 Quarter End Date Like that
I need to calculate the amount of time between each visit. I am pulling the Row Number for my visits and now I need the date span that goes between each day. I also need a new column that returns a Yes or a No if the date span exceeds 3 years.
SELECT ROW_NUMBER ( ) OVER ( PARTITION BY pv.PatientProfileId ORDER BY pv.Visit ASC ) AS RN , CONVERT ( VARCHAR ( 20 ) , pv.Visit , 101 ) AS Visit , pv.TicketNumber , vstatus.Description AS VisitStatus , doc.ListName AS Doctor
I've built a report from a cube that I have had made. After selecting a few dimensions, the columns will be showing a drill down action related to different dates. Problem is, when you preview the report, the dates get alphabetized; they don't show up in an order like dates, days should.
ex: monday, friday, thursday, tuesday, wednesday
or april, august, july, june, may
How can this be changed, or is it related to the dimensions in the way they were made? Possibly something from the tables then? If more information is needed, please specify.