T-SQL (SS2K8) :: How To Get MIN / MAX Dates Per Grouping

Jul 8, 2015

I have a table with Employee, it lists their department, and has a row for each "position/promotion". (Just the pertinent fields below in the example shown of course and only a single employee for example)

An employee may have a single row in a department, or if they were promoted from line staff to manager they could have many.

Additionally, an employee may move around departments during their employment.

I need to get the min and max dates (basically duration) of their time at each department.

The issue is because they repeated departments when I go to group it loses movement between departments.

Raw data:

Employee DepartmentId StartDt EndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2005-06-30
999 7 2005-07-01 2006-09-30
999 5 2006-10-01 2009-10-31
999 5 2009-11-01 2012-01-01

Result of doing MIN/MAX:

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2012-01-01
999 7 2001-05-01 2006-09-30

However, Ideally I would get 3 rows (Department 5, then the stay at Department 7, then back to Department 5 again).

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2006-09-30
999 5 2006-10-01 2012-01-01

I played with RANK and ROW_NUM hoping I could get it to group / partition on each department and repeat the number for each department so the first department 5 would be RowNum=1, then the next group of Dept 7 would be =2 and the last group of Dept 5 would = 3 and then I could min/max on that grouping, but I didn't have any luck with that approach either.

My table:
CREATE TABLE [dbo].[EmployeeDepartmentHistory](
[Employee] [int] NOT NULL,
[DepartmentId] [int] NOT NULL,
[StartDt] [date] NOT NULL,
[EndDt] [date] NOT NULL
) ON [PRIMARY]

[Code] .....

View 2 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Grouping Dates

Dec 14, 2005

SELECT TOP 100 w.TimeDate AS [date], p.ProdCode, COUNT(w.BoxID) AScases, AVG(w.PrintedWeight / w.PkgsPerBox) AS avgtrayweightFROM dbo.WIPSixtyDay w INNER JOINdbo.Products p ON w.PLUNo = p.PLUNoWHERE (p.PrePack = 1) AND (w.PkgsPerBox > 0)GROUP BY w.TimeDate, p.ProdCodeORDER BY w.TimeDateThis is what I need to do...... sum these by the date(w.timedate) myoriginal I was converting it to a string, but then none of my datequeries would work....Here is my originalSELECT TOP 100 CONVERT(varchar(10), w.TimeDate, 101) AS [date],p.ProdCode, COUNT(w.BoxID) AS cases, AVG(w.PrintedWeight /w.PkgsPerBox)AS avgtrayweightFROM dbo.WIPSixtyDay w INNER JOINdbo.Products p ON w.PLUNo = p.PLUNoWHERE (p.PrePack = 1) AND (w.PkgsPerBox > 0)GROUP BY CONVERT(varchar(10), w.TimeDate, 101), p.ProdCodeORDER BY CONVERT(varchar(10), w.TimeDate, 101)This is in a View....if that matters...

View 3 Replies View Related

Grouping And Ordering Dates

Apr 20, 2000

Hello,
I am trying to solve this....when I run this query
select convert(varchar,date,100) from temp3
where date between convert(varchar,getdate() - 1.0,101) and
convert(varchar,getdate(),101)
result is:
Apr 19 2000 12:45PM
Apr 19 2000 12:46PM
Apr 19 2000 12:47PM
Apr 19 2000 12:48PM
Apr 19 2000 12:49PM
Apr 19 2000 12:49PM
Apr 19 2000 12:52PM
Apr 19 2000 12:53PM
Apr 19 2000 12:56PM
Apr 19 2000 12:57PM
Apr 19 2000 12:57PM
Apr 19 2000 12:58PM
Apr 19 2000 12:59PM
Apr 19 2000 12:59PM
Apr 19 2000 12:59PM
Apr 19 2000 12:59PM
Apr 19 2000 1:00PM
Apr 19 2000 1:01PM
Apr 19 2000 1:02PM
Apr 19 2000 1:02PM
Apr 19 2000 1:03PM
Apr 19 2000 1:04PM
Apr 19 2000 1:05PM
Apr 19 2000 1:06PM
Apr 19 2000 1:06PM
Apr 19 2000 1:10PM
Apr 19 2000 1:11PM
Apr 19 2000 1:11PM
this is what I am expecting. but when I try to group it

select convert(varchar,date,100) from temp3
where date between convert(varchar,getdate() - 1.0,101) and
convert(varchar,getdate(),101)
group by convert(varchar,date,100)

result is:
Apr 19 2000 1:00PM
Apr 19 2000 1:01PM
Apr 19 2000 1:02PM
Apr 19 2000 1:03PM
Apr 19 2000 1:04PM
Apr 19 2000 1:05PM
Apr 19 2000 1:06PM
Apr 19 2000 1:10PM
Apr 19 2000 1:11PM
Apr 19 2000 12:45PM
Apr 19 2000 12:46PM
Apr 19 2000 12:47PM
Apr 19 2000 12:48PM
Apr 19 2000 12:49PM
Apr 19 2000 12:52PM
Apr 19 2000 12:53PM
Apr 19 2000 12:56PM
Apr 19 2000 12:57PM
Apr 19 2000 12:58PM
Apr 19 2000 12:59PM

result is correct but I would like it to be displayed like
the first one. I will really appreciate Any clues,
suggestions, How to at this point. Thanks a lot.
HP

View 5 Replies View Related

T-SQL (SS2K8) :: Dividing Two Values Of Same Column / Grouping

Aug 4, 2014

I'm trying to divide two values from separate rows. Each row is a separate UNION statement.

2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)

In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)

What would be the best way to accomplish this?

View 1 Replies View Related

T-SQL (SS2K8) :: Select Statement That Needs To Use Custom Grouping

Aug 10, 2015

I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:

SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code

The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.

For example:

Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365

Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.

Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.

I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.

People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…

The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.

View 5 Replies View Related

T-SQL (SS2K8) :: Get Particular Day Between Two Dates?

Aug 8, 2014

I want to list Dates for particular day between two dates.

for eg. if user enters 08/01/2014 and 08/31/2014 with value 0 then all the Sundays and related dates should be display.

if above contamination with 1 then all Mondays,

View 9 Replies View Related

T-SQL (SS2K8) :: Grouping Of Data Based On Moving Time Period

Mar 13, 2014

To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.

The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.

The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.

Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style

This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.

Here is some sample data

USE FF_Winning_Together;
IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL
DROP TABLE #AmendTest;
CREATE TABLE #AmendTest
(
AmendmentIDINT IDENTITY(1,1)NOT NULL,
StyleCHAR(1)NOT NULL,
AmendmentStatusVARCHAR(10)NOT NULL,
DTDATETIMENOT NULL

[code]....

View 7 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Gaps In Dates

Mar 25, 2014

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:

CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)

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

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Retrieve Dates For Last 7 Days

Mar 26, 2014

The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7

However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..

I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6

View 5 Replies View Related

T-SQL (SS2K8) :: Get Missing Dates In Table

Aug 18, 2014

I've a request: I've a table with a date column and an if numeric data type.

I've to check from now on 7 days ago if any value is missing in my date field.

So if I've f.i. in my table
ID DAYS
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17

then my output should be:
2014-08-15
2014-08-16
2014-08-18

How to do this?

View 1 Replies View Related

T-SQL (SS2K8) :: Get Earliest Date From Two Dates

Dec 10, 2014

I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null

I tried..

DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Current Year Begin And End Dates

May 6, 2014

I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):

AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'

I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)

SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0

Then, change the CASE statement to read:

AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)

View 8 Replies View Related

T-SQL (SS2K8) :: How To Find Saturday Occurrence Between Two Dates

Jun 17, 2014

I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.

Below is my table, current result and desired results.

My Table:

WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData

Current Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014

Expected Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0002Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0003Saturday05/10/2014

View 9 Replies View Related

T-SQL (SS2K8) :: How Datediff Determine Difference Between Two Dates

Jan 19, 2015

select datediff(wk, '2015-01-11', '2015-01-19') returns 1 (shouldn't it return 2?)
select datediff(wk, '2015-01-10', '2015-01-19') returns 2

View 5 Replies View Related

T-SQL (SS2K8) :: Average Of Time Between Multiple Dates

Oct 7, 2015

I have a dataset as such:

Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1

[Code] ...

So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Select Past 3 Dates And Determine If 1st And 3rd Are Within Timeframe?

Sep 7, 2014

I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.

Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.

View 9 Replies View Related

T-SQL (SS2K8) :: Create Periods Transaction Dates And Make Them Columns

Sep 4, 2014

I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.

I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!

select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

T-SQL (SS2K8) :: Historical Data Where Number Of Records Exists Between Two Dates With Different Years

Jul 10, 2015

Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.

A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.

SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],

[Code] ....

I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).

View 4 Replies View Related

T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?

Apr 22, 2015

following table global_usage

ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01

[Code] .....

Now the problem,per month I need the most recent value so I'm expecting

ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01

I've tried a few thing like group,having even row_number() but I keep getting wrong results

View 6 Replies View Related

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date.  the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

View 1 Replies View Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved