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.
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?
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?
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)
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
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)
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.
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?
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.
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
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?
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.
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?
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! :)
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?
- 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?
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)
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?
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
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."
- 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?
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.
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.
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?
- 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?
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)
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
