Time Ranges
Sep 24, 2007
Hello -
Building a small app for a school nurse......
Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etc
What I need to do is then check to see:
1. What period did they login in?
2. What period did they logout in?
3. How much time in a period did they actually miss?
The ranges look like:
Set @Period1Start='08:20 AM'
Set @Period1End='09:10 AM'
Set @Period2Start='09:15 AM'
Set @Period2End='10:00 AM'
...
...
...
So.....
if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1
If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2
Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.
Thanks!
Dan B
View 5 Replies
ADVERTISEMENT
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 4 Replies
View Related
May 25, 2008
Find Time Ranges
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View 3 Replies
View Related
May 25, 2008
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 2 Replies
View Related
Jun 3, 2008
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] (
[TIME_ADDED_FIS] datetime NULL,
[PERSON_ID] int NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2
(TIME_ADDED_FIS, PERSON_ID) VALUES
('3/23/2008 9:12:29 PM', '163634')
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] (
[PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_ADDED_FIS] datetime NULL,
[LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLOYEE_KEY] int NULL)
ON [PRIMARY]
GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196)
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
GO
View 2 Replies
View Related
Mar 31, 2008
Hi, I have the following query that is not working correctly
Basically I have a fields called TimeLeave (datetime) and I want to classify every record according to that field
The multiplication by the field b.Zulu is just to convert to local time, once converted I want to classify the local time with those numbers, but for example nothing falls in the first category and I get a bunch of records with a wrong ID
Any idea what should bet the correct sql statement to classify the records??
Thanks in Advance
CASE
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '22:30' AND '05:59' THEN 1
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '06:00' AND '08:59' THEN 2
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '09:00' AND '11:59' THEN 3
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '12:00' AND '14:59' THEN 4
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '15:00' AND '16:59' THEN 5
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '17:00' AND '19:59' THEN 6
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '20:00' AND '22:29' THEN 7
END
View 3 Replies
View Related
May 8, 2008
I'm looking for a way to link records that would be related to an employee that worked during that shift.
we have 2 shifts days and nights going from 6:00AM to 6:00PM
we have records that are written to a table as events happen on equipment on site.
"Equip_Trans"
"Start_time" "Equip_ID" "Shift_date" "Status" "End_Time"
Date-Time, int, Date, varchar(3), Date-time
5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03
5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I have another transaction table "Operator_Trans"
"Operator" "Date" "Shift"
jane Doe, 5/6/2008, 1
Pete Who, 5/6/2008, 2
Sam What, 5/7/2008, 1
Pete Who, 5/7/2008, 2
john Doe, 5/8/2008, 1
jane Doe, 5/8/2008, 2
and last a third table "Shift"
Shift, Start_Time, End_Time
1, 6:00AM, 6:00PM
2, 6:00PM, 6:00AM
I need out put that will show all Equipment status's that started during the shifts worked by an operator
Pete Who, 5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
Pete Who, 5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03 AM
Pete Who, 5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
Pete Who, 5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
john Doe, 5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
john Doe, 5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I am not completely sure the direction I should start off with. I have some SQL knowlege but I would still consider myself new.
Thanks
View 1 Replies
View Related
Aug 31, 2004
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
View 3 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Aug 10, 2006
Im trying to get a table where the columns are a range of dollars spent...
Code:
$1 - $24 $25 - $49 $50 - $74
01-12 Month 5,000 6,000 6,200
13-24 Month 7,000 8,800 9,120
-------------------------
This is what i have so far. It gives me one value. Of course I can run it a bunch of times changing the values, but im just curious if there is a way to do it all at once.
---------------------
Code:
SELECT COUNT(transaction_header.transaction_id) AS CustID
FROM transaction_header INNER JOIN
Northwind.dbo.Ttype ON
transaction_header.transaction_type =
Northwind.dbo.Ttype.transaction_type
WHERE (Northwind.dbo.Ttype.Cat2 = 'CATALOG' OR
Northwind.dbo.Ttype.Cat2 = 'ECOM') AND (DATEDIFF(mm, transaction_header.transaction_date, { fn NOW() }) < 13)
AND
(transaction_header.total_net_retail BETWEEN 1 AND 24)
Thanks,
Dynasty
View 5 Replies
View Related
Jul 1, 2004
Table:
SomeFKID <pk>
StartDateTime <pk>
EndDateTime
SomeValue
Example Scenario:
Data is stored hourly. So there would be 24 records for today for each SomeFKID. I need to be able to pass a TimeSpan (in minutes), a StartDateTime, and an EndDateTime to a stored procedure and return totals in the date range grouped by the TimeSpan. So if I want all records today grouped by 2 hour intervals I would need to pass:
7/1/2004 00:00:00, 7/1/2004 23:59:59, 120 --> and return 12 records one for hours 0-2, one for hours 2-4, etc.
Any advice would be greatly appreciated!
Thanks in advance,
Wheatster
View 8 Replies
View Related
May 27, 2008
after i create a paratiotion, i want to see the ranges.
when i tried to do :
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
i got the paratition data but not the ranges themselfs.
how can i do this?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
View 1 Replies
View Related
Oct 27, 2014
I'm trying to write a statement that will return all records within a range. The only problem is that the records can have modifiers on them.For example, a sample range I would want would be all records between 1008 and 1120. I can't use a between statement though because these records can end in modifiers, like 1009X or 1117B. How can I search for records within a range when they have modifiers such as above?
View 1 Replies
View Related
Sep 21, 2005
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
View 1 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
Oct 3, 2007
Hi All,
I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
View 1 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
Jul 26, 2004
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30--------------------240
Row 2: 40--------------120
Row 3: 50----------------------260
Row 4: ...1300---------1440
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
Thank you for the help!
View 14 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
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
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 7 Replies
View Related
Jun 14, 2012
I'll keep the analogy as basic as possible, but lets say we sell vouchers. The vouchers are individually numbered.
We sell the vouchers in batches, and are stored in the DB as one record per sale with a range of the vouchers sold (so they scan the first and last voucher number to get the range)
E.g.
Table1
SalesID Description From To
SalesID0001 Batch of paper 100001 100015
SalesID0002 More paper 100016 100150
The vouchers come back in individually however, so we might get voucher 100011 back and is stored in another table as an individual item.
I'm trying to write a report which shows which vouchers have not come back yet and I'm struggling since the data is stored in ranges not on an individual basis...?
My thinking is leading me to maybe create a temp table where I can convert those ranges into real numbers....which I'll be able to more easily compare - but how I might do that.
(Example output from above example
TempTable
SalesID Description Voucher
SalesID0001 Batch of paper 100001
SalesID0001 Batch of paper 100002
SalesID0001 Batch of paper 100003
SalesID0001 Batch of paper 100004
SalesID0001 Batch of paper 100005
etc, etc)
View 2 Replies
View Related
Mar 17, 2004
I've got a linked server setup to DB2, and some of the
date fields in the DB contain 1/1/0001 values.
I've got views created in SQL2000 against the DB2 linked server.
When I run a query against a particular table that contains multiple field of datetime type.
I get the below error
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
This only happens when I include in the select the field that contains 1/1/0001 values.
I assume since valid dates ranges in SQL are from
January 1, 1753 through December 31, 9999, this would be
what's causing this.
I tried to covert in the select but that failed as well. The only thing that I've been able to do, is to use a DTS to pull the data from the DB2 to a local SQL2000 table, with that fields type set as varchar. This works.
Using a DTS to pull the data to a local table in production isn't a viable workaround, since this table contains 1.8 million rows.
How is DTS converting this field, when convert fails in the select?
How do I get around this?
Thanks
View 13 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
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 2 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
Jul 4, 2006
Hi All,
I have the following table:
Name Age
A 15
B 18
C 22
D 27
E 33
F 42
I need to have the following output:
Range Count
0-10 0
11-20 2
21-30 2
31-40 1
41-50 1
Could anyone let me know how to achieve this in SQL Server 2005?
Thanks,
Varun
View 3 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
Feb 6, 2007
Hello
I have the following problem:
- I have a transactional replication between a publisher and a few suscribers,
- The servers are SQL 2003.
- I have Identities columns in some of the tables
- I'm using the Automatic Identity Range Handling
The problems happens when the publisher or one of the suscriber goes down, (it happens relativly often and we can't do anything against it), so when the connection is restablished, the merge agent assign a new identity range to the publisher or
the suscriber(the one that went down).
Everytime a server goes down, it "eats" a idetity range, doesnt matter how many idenities have been used, and i am running out of identity ranges.
I want to keep using the Automatic Identity Range Handling to manage the replication activity because
changing it to manual would be really hard for us.
Im new at this but I see two ways to solution it:
1) avoid that the marge agent assign a new identity range when a server goes down
2) let the merge agent assign a new identity range, and the reestablish the identity to the heigest id value (using CHECKIDENTITY()), but i think i would have to do some extra things to make the publisher to be sincronized with the suscribers (maybe modify a table on the publisher or something)
Could someone please tell me what is the easier way to solution it, if there is another easier way and how to implement it, or the other two?
thanks for your attention
View 3 Replies
View Related
Nov 23, 2007
Trying to return actual values that will look at dates after it and return the total actuals for week 1, week 1 and 2, week 1, 2 and 3, etc. Here is a sample of data that is in a table:
CREATE TABLE #tmpactuals
(caid int IDENTITY(1,1) NOT NULL,
prediciton_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
actuals money NULL
) ON [PRIMARY]
INSERT into #tmpactuals VALUES('4/22/2007 0:00', '4/28/2007 0:00', 193113)
INSERT into #tmpactuals VALUES('4/29/2007 0:00', '5/5/2007 0:00', 153320)
INSERT into #tmpactuals VALUES('5/6/2007 0:00', '5/12/2007 0:00', 122271)
INSERT into #tmpactuals VALUES('5/13/2007 0:00', '5/19/2007 0:00', 80846)
INSERT into #tmpactuals VALUES('5/20/2007 0:00', '5/26/2007 0:00', 110603)
INSERT into #tmpactuals VALUES('5/27/2007 0:00', '6/2/2007 0:00', 89748)
INSERT into #tmpactuals VALUES('6/3/2007 0:00', '6/9/2007 0:00', 116675)
INSERT into #tmpactuals VALUES('6/10/2007 0:00', '6/16/2007 0:00', 140567)
INSERT into #tmpactuals VALUES('6/17/2007 0:00', '6/23/2007 0:00', 555827)
INSERT into #tmpactuals VALUES('6/24/2007 0:00', '6/30/2007 0:00', 119377)
INSERT into #tmpactuals VALUES('7/1/2007 0:00', '7/7/2007 0:00', 73529)
INSERT into #tmpactuals VALUES('7/8/2007 0:00', '7/14/2007 0:00', 101982)
INSERT into #tmpactuals VALUES('7/15/2007 0:00', '7/21/2007 0:00', 129099)
INSERT into #tmpactuals VALUES('7/22/2007 0:00', '7/28/2007 0:00', 178279)
INSERT into #tmpactuals VALUES('7/29/2007 0:00', '8/4/2007 0:00', 178279)
Again, what I want to return is for each prediction_date_enddate is the total actuals for that date plus the running totals for the next 13 weeks. So for the first date you would have the actuals for that week. Prediction week two you would have the actuals for that week plus the next week, etc. Want to do this for each end date. The results should look something like this: (Well not exactly like this. I transposed the info for readability on the post. Obviously it would be the columns with 2 rows of data based on the test data)
Columns: Row 1, Row 2
prediction_date_enddate 4/28/2007 0:00, 5/5/2007 0:00
Prediction Week 1: 74140, 123535
Actuals Week 1: 193113, 153320
Difference Week 1: 118973, 29785
Percent Week 1: 260%, 124%
Prediction Week 2: 237762, 245906
Actuals Week 2: 346433, 275591
Difference Week 2: 108671, 29685
Percent Week 2: 146%, 112%
Prediction Week 3: 413159, 517435
Actuals Week 3: 468704, 356437
Difference Week 3: 55545, -160998
Percent Week 3: 113%, 69%
Prediction Week 4: 691786, 738029
Actuals Week 4: 549550, 467040
Difference Week 4: -142236, -270989
Percent Week 4: 79%, 63%
Prediction Week 5: 911249, 846405
Actuals Week 5: 660153, 556788
Difference Week 5: -251096, -289617
Percent Week 5: 72%, 66%
Prediction Week 6: 989056, 939253
Actuals Week 6: 749901, 673463
Difference Week 6: -239155, -265790
Percent Week 6: 76%, 72%
Prediction Week 7: 1089510, 1056769
Actuals Week 7: 866576, 814030
Difference Week 7: -222934, -242739
Percent Week 7: 80%, 77%
Prediction Week 8: 1191440, 1151155
Actuals Week 8: 1007143, 1369857
Difference Week 8: -184297, 218702
Percent Week 8: 85%, 119%
Prediction Week 9: 1292520, 1383868
Actuals Week 9: 1562970, 1489234
Difference Week 9: 270450, 105366
Percent Week 9: 121%, 108%
Prediction Week 10: 1534959, 1505965
Actuals Week 10: 1682347, 1562763
Difference Week 10: 147388, 56798
Percent Week 10: 110%, 104%
Prediction Week 11: 1677370, 1683000
Actuals Week 11: 1755876, 1664745
Difference Week 11: 78506, -18255
Percent Week 11: 105%, 99%
Prediction Week 12: 1872707, 1895996
Actuals Week 12: 1857858, 1793844
Difference Week 12: -14849, -102152
Percent Week 12: 99%, 95%
Prediction Week 13: 2299290, 2108978
Actuals Week 13: 1986957, 1972123
Difference Week 13: -312333, -136855
Percent Week 13: 86%, 94%
View 6 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
