SQL Server 2012 :: Filling Gaps In Date Ranges?
Dec 10, 2014
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
[code].....
View 9 Replies
ADVERTISEMENT
Sep 13, 2013
I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.
Example dataset SQL below:
CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'
[Code] ....
I would expect a dataset to be returned consisting of:
1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013
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...
View 9 Replies
View Related
Feb 5, 2014
I have a question regarding windowing functions. I have a sales order table with the columns "orderid", "customerid", "order_date" and "amount". I use the following query to get the amount of every customer as a additional column:
Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid)
FROM sales_orders
My question is if there is a good way to add another column, which includes the SUM(amount) of the customerid, where the order_date > 2012-01-15 , something like this:
Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid),
SUM(amount) OVER (PARTITION BY customerid WHERE order_date > 2012-01-15)
FROM sales_orders
I know, this is not a valid method, so do you know a way to achieve this? Can I maybe use CROSS APPLY or something like this? I know that I could use a subquery to get this, but is there maybe a way / a better way via window functions?
View 9 Replies
View Related
Dec 13, 2004
hello, i have quite a challenge on my hands here and would appreciate any help. :confused:
I have a table variable that stores integer ranges representing times of the day:
select * from @reservations
room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000
I need to be able to return the minutes that are open for each room. The @reservations table shows me the times that are blocked.
I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.
Based on the @reservations table above, I'd like to write something that returns:
room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)
View 3 Replies
View Related
Jun 19, 2012
Create a table with an Identity column, insert data / restart the server / insert more data / restart the server/ insert some more data.
My data looks like this :
Identity column
1
2
3
1002
1003
1004
1005
2002
2004
It looks like the indentity value gain +~1000 after most server restart (sometimes identity stay the same). This can be very dangerous for some datatype! The only thing google told me was this : URL...but microsoft did not comment on it yet!
View 9 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Aug 31, 2014
I have a system log with NULL gaps between a sequence of numbers...see "BEFORE" sample below.
The number of gaps between the Sequence_ID's are arbitrary, but generally less then 50 records.
I'd like enumerate the gaps to produce the "AFTER" result, but do it with a single query or view, not through procedures.
I've been playing with windowed functions and groupings with no success. I'm guessing it'll need some recursive CTE logic, but I haven't been able to figure it out the correct loop.
BEFORE:
PK_IDSequence_ID
1035586935587
3035586234 NULL
8355585 NULL
1235584 NULL
4675583 35583
4035582 NULL
6035382 NULL
1435581 NULL
2035580 NULL
3435553 35563
6603589 NULL
9475559 35552
AFTER:
PK_IDSequence_ID
1035586935587
3035586234 3
8355585 2
1235584 1
4675583 35583
4035582 4
6035382 3
1435581 2
2035580 1
3435553 35563
6603589 1
9475559 35552
View 9 Replies
View Related
Dec 19, 2013
Consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
[code]....
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges.
View 8 Replies
View Related
Sep 28, 2005
The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html
View 3 Replies
View Related
Aug 21, 2015
What I am trying to accomplish is to make a few extra columns with specified date ranges.
I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.and also I need to add 2 more columns Prior year current month and This year, current month.
<code>
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14
[code]....
View 2 Replies
View Related
Feb 13, 2014
The identity int column in my sql2012 db is not incrementing properly.
I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:
1, 2, 3, 4, 5, 1004, 1005
This is happening on a random number of tables at very random times...
View 5 Replies
View Related
Jan 30, 2005
I need to write a procedure to check if I have any gaps in my dates...
Start Date............End Date
10/6/2004............10/6/2005
10/6/2003............10/6/2004
7/10/2003..........10/6/2003
7/10/2002.............7/10/2003
What I need to do is test the End Date to the next row under the Start Date Column. (in bold to clarify) (moving upward from bottom)
sSQL011$ = "SELECT * FROM TableName " & _
"WHERE ID = '" & ID & "'"
Set rs011 = DB.OpenRecordset(sSQL011$, dbOpenDynaset)
Set Data1.Recordset = rs011
rs011.FindFirst "ID = " & ID
With rs011
do while .eof = False
if .eof then
exit sub
end if
if rs011("StartDate") 'from one record, if it is not equal to the Start
Date in the next record then
MsgBox "Gap in Dates for: " + ID
loop
end with
pseudo...
if the end date in one consecutive row is not the same as the start date in the next row then there is a date gap
thanks...
View 3 Replies
View Related
Mar 5, 2008
Hi,
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')
View 5 Replies
View Related
Jul 23, 2005
I have a table containing typed log entries. One log entry is supposedto be created every twelve hours, but sometimes there are gaps. I needto 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 thatthere will be dates missing. Instead, when there isn't a log entry fora date, I need to print the date, and then just leave the log entryblank.The SQL bellows shows what the output should look like. HOWEVER, thecode below makes use of a temp table containing all possible dates. Myquestion is, is there a better way to do this - one that doesn'tinvolve the temp table? Thanks in advance.create table StationLog (LogDate datetime, LogText char(11))insert StationLog values ('1/1/2005 00:00:00','entry one')insert StationLog values ('1/1/2005 12:00:00','entry two')insert StationLog values ('1/2/2005 00:00:00','entry three')insert StationLog values ('1/3/2005 00:00:00','entry four')create table Date_List (TempDate datetime)insert Date_List values ('1/1/2005 00:00:00')insert Date_List values ('1/1/2005 12:00:00')insert Date_List values ('1/2/2005 00:00:00')insert Date_List values ('1/2/2005 12:00:00')insert Date_List values ('1/3/2005 00:00:00')insert Date_List values ('1/3/2005 12:00:00')select TempDate, LogTextfrom Date_Listleft outer join StationLog on Date_List.TempDate = StationLog.LogDatedrop table StationLogdrop table Date_List
View 6 Replies
View Related
Jan 9, 2015
We just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns "When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).
"For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."
After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept
1. Use sequence object -
a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.
b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.
2. Use Trace Flag 272 (-T272)
I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.
I don't want to do any changes in my database structure.
how to use this -T272 or why it is not working.
I don't want to use this new identity feature how to suppress it. Why -T272 is not working.
View 4 Replies
View Related
Dec 17, 2006
Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
View 6 Replies
View Related
Mar 18, 2004
I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.
Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.
View 3 Replies
View Related
Oct 25, 2005
I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.
Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.
The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.
I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.
At my current state, I'm just done.. i can't maintain the perspective... how do I do this?
View 5 Replies
View Related
May 22, 2008
Hey,
I woudl like to take an age range (Say 22-27) and determine the min and max year, and then take those two DateTime Year values and do a SELECT from a column titled "Birthday Year", which is a an int value like '1984'. Could someone give me a hand with this?
/* Value 1: 1978
Value 2: 1988
*/
SELECT(???) FROM User WHERE ['BirthdayYear'] <> (?Age Function?)
GO
Thanks.
View 3 Replies
View Related
Apr 16, 2015
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT
DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH,
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE()))
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM
tbLogTimeValues
WHERE
DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
View 5 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Dec 11, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Joshi
View 1 Replies
View Related
Mar 15, 2007
I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?
View 1 Replies
View Related
Mar 23, 2015
How can I make partitions on a table for a particular value and ranges together?
For example, for customer id 12345 i need a separate partition, then for 56789 i need a separate partition, and if i have range of values like 1000 to 1020 then a separate partition for this.
For certain ids i need unique partition, and for certain ids i need Ranges.
is it possible in SQL 2012?
View 6 Replies
View Related
Jun 1, 2015
I need to remove some IP addresses from a total list of IP addresses. The ones I need to remove are in certain IP ranges. I could create a temp table with all these IP addresses I need to exclude.
Example IP Ranges:
128.134.2.100 to 128.134.2.200
135.234.12.60 to 135.234.12.120
210.10.140.150 to 210.10.140.200
View 7 Replies
View Related
Jun 2, 2006
We have some seasonal products that we would like to turn on and off automatically on our website. To accomplish this, we've added StartDate and EndDate fields to the product table. See simplified table:
--------------Simple Table:--------------ID int identity (PK)Value varchar(50)StartDate datetimeEndDate datetime
Normally I could just do something like SELECT ID, Value FROM PRODUCTS WHERE StartDate <= @MyDate AND EndDate >= @MyDate
The catch is that we want to ignore the year part of the dates so that we don't have to go back through and update all the records every year. I'm pretty sure I've done this in the past, but I'm having a brain fart right now and can't remember how. One of the issues is that the start month could be > the end month (e.g. - October 15 - January 1). Using code in the application I could work around this (see below), but I would like to handle this in SQL if possible so I don't have to return more records than are needed and weed out the bad records.
'dtStart and dtEnd are Date Objects, StartDate and EndDate are strings containing the MM/DD partIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(myDate) < 0 Then dtEnd = Date.Parse(EndDate & "/" & (myDate.Year + 1))ElseIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(objDate) > 0 Then dtStart = Date.Parse(StartDate & "/" & (myDate.Year - 1))End If
I appreciate any help you can provide.
Thanks,Sam
View 1 Replies
View Related
Apr 23, 2008
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
View 11 Replies
View Related
May 31, 2008
I have a table with date like this.
InstId--Date----Readings
--1---10/12/2008--10
--1---11/12/2008--10
--1---12/12/2008--10
--1---13/12/2008--9
--1---14/12/2008--10
--1---15/12/2008--10
--1---16/12/2008--10
--1---17/12/2008--10
--2---05/03/2008--8
--2---06/03/2008--6
--2---07/03/2008--8
--2---08/03/2008--8
--2---09/03/2008--8
--2---20/03/2008--8
Guys I want to get the date ranges instrument wise for which the instrument readings are constant.
For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.
StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8
Thanks for any help.
View 10 Replies
View Related
Nov 6, 2013
Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap as I can't have two prices on one day.
For example if in a table there was:
Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00
The top two overlap.I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:
SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]
[code]....
View 5 Replies
View Related
Jul 23, 2005
I'm trying to create a query that will tell me which requeststook longer than 10 days to move one from particular state to anotherstate. The query I've created returns the correct requests,but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.I'm sure I'm missing something easy, but I can't figure out what itmight be. Any help is appreciated! Thanks,Myron-- remove SPAM-KILL from address to reply by email --DDL for table creation and data population:CREATE TABLE [dbo].[ReqHistory] ([Id] [int] NOT NULL ,[ReqId] [int] NOT NULL ,[ReqIDStateId] [tinyint] NOT NULL ,[ActionDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[RequestStates] ([ID] [tinyint] NOT NULL ,[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Requests] ([ID] [int] NOT NULL ,[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StatusChangeDate] [datetime] NULL ,[Status] [tinyint] NULL) ON [PRIMARY]GOinsert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))insert into RequestStates values(1,'Awaiting CSMC')insert into RequestStates values(2,'CSMC Review')insert into RequestStates values(3,'Reject Awaiting CSMC')insert into RequestStates values(10,'Awaiting MA Review')insert into RequestStates values(12,'Awaiting FO Review')insert into RequestStates values(13,'Awaiting IS Review')insert into RequestStates values(20,'MA Review')insert into RequestStates values(22,'FO Review')insert into RequestStates values(23,'IS Review')insert into RequestStates values(30,'Func Approval')insert into RequestStates values(31,'Func Approval Complete')insert into RequestStates values(96,'Resolved')insert into RequestStates values(97,'Planning')insert into RequestStates values(98,'Open')insert into RequestStates values(99,'Closed')The query that almost works:select irh.ReqID, irh.MAactiondate, irh.reviewstate,irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextStatefrom (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,irh.ReqIDStateID As IRHState, irs.statetext as ReviewStatefrom ReqHistory IRHjoin requeststates irs on irs.id = irh.ReqIDStateIDwhere irh.ReqIDStateID in (20, 23)group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irhjoin (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetextfrom (select min(actiondate) as actiondate, ReqID,min(ReqIDStateID) as IRH2Statefrom ReqHistory--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,--but I couldn't make it workwhere ReqIDStateID > 23group by ReqID) as irh2join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqIDjoin requests ir on ir.id = irh.ReqIDwhere irh.MAactiondate + 10 < irh2.Nextactiondateorder by irh.ReqIDThe data being returned is:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 96 ResolvedThe data that should have been returned:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMC
View 3 Replies
View Related
Jun 27, 2006
Hi there, I'm trying to generate a report for an old database and I'mhaving trouble coming up with an elegant way of going about it. Usingcursors and other 'ugly' tools I could get the job done but 1) I don'twant the report to take ages to run, 2) I'm not a big fan of cursors!Basically there are tables that track history and each table tends totrack only a specific value housed within a date range. I'm trying tocombine the tables to get a snap-shot of the complete history. I'mhaving problems dealing with the Start/End Dates from the two tablesand building the dates in the final table to be broken down by 'historytype'.Here are a few sample records and the results I'm trying to achieve:Table 1:CAgyHist (ProdID,AgyID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 5, 20061 2 Jan 5, 2006 Jan 25, 20061 1 Jan 25, 2006 NULLTable 2:CInvHist (ProdID, InvID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 23, 20061 2 Jan 23, 2006 Jan 15, 20061 1 Jan 15, 2006 NULLDesired End Result:CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)1 1 1 Jan 1, 2006 Jan 5, 20061 2 1 Jan 5, 2006 Jan 15, 20061 2 2 Jan 15, 2006 Jan 23, 20061 2 1 Jan 23, 2006 Jan 25, 20061 1 1 Jan 25, 2006 NULLMy challenge thus far has been dealing with the dates as they don'tnecessarily correspond - from one table to the other.I am by no means a database expert of any level and any help would begreatly appreciated.Thanks,Frank.
View 7 Replies
View Related
Aug 2, 2004
Masters,
The below queries return the data that I seek, but I have no idea how to combine them into a single query.
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [0 to 30]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '0' and '30'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [31 to 60]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '31' and '60'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [61 to 90]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '61' and '90'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [90+]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) > '90'
Any assistance that can be provided will be greatly appreciated.
Grasshopper
View 1 Replies
View Related
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
View Related
