Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-17 : 21:43:42
|
Hey all. I am pretty new to SQL programming. I can do the basics, but I have a problem right now, and I really need some help. First in if my overall plan of attack is correct, then how to implement it. I need a table with employee names and an ID.And for each employee I think I need a table. On the table I just need to keep adding dates (and hours). This will not have a certain number, and it won't be the same for every employee. But a row (date/hours) will need to be added during use.Is this the best way to accomplish something like this?CREATE TABLE Temp_Employee_Table( [ID] [int] NOT NULL, [Employee_Name] [nvarchar](max) NULL) ON [PRIMARY]INSERT INTO Temp_Employee_TableVALUES(1,'Pride Grimm'),(2,'Elissa Grimm'),(3, 'Riley Grimm')CREATE TABLE Dates_1( [ID] [int] NOT NULL, [Date1] [date] NULL) ON [PRIMARY]INSERT INTO Dates_1VALUES(1, '3-12-13'),(1,'3-14-13')/*CREATE TABLE Dates_2( [ID] [int] NOT NULL, [Date1] [date] NULL) ON [PRIMARY]INSERT INTO Dates_2VALUES(2, '3-2-13'),(2,'3-4-13'),(2, '4-4-12')CREATE TABLE Dates_3( [ID] [int] NOT NULL, [Date1] [date] NULL) ON [PRIMARY]INSERT INTO Dates_3VALUES(3, '3-22-13'),(3,'3-24-13'),(3, '4-24-12'),(3,'4-17-13')*/I need the data displayed in a datagridview (telerik), and sorted by most recent dates. I am not sure how to go about this. My first thought is to get name and dates in a single row. and then sort by the most recent date. I am trying to read up on dynamic sql, but this needs done very quick. I found some code that starts to do what I want, converting the rows to columns. I am trying to get it working with one employee dates table, then move on. Is this the way to start? Any resources you know dealing closely with this? Thanks a ton!Here is the dynamic sql to convert from rows to columnsDECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Date1_' + cast(rn as varchar(10))) from Dates_1 cross apply ( select row_number() over(partition by ID order by Date1) rn from Dates_1 ) x FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT Employee_Name, ' + @cols + ' from Temp_Employee_Table, ( select ID, Date1, ''Date1_'' + cast(row_number() over(partition by ID order by Date1) as varchar(10)) val from Dates_1 ) x pivot ( max(Date1) for val in (' + @cols + ') ) p ' execute(@query)It yields this. (Not exactly what I want, but I don't want to climb the wrong tree.EMPLOYEE_NAME DATE1_1 DATE1_2Pride Grimm 2013-03-12 2013-03-14Elissa Grimm 2013-03-12 2013-03-14Riley Grimm 2013-03-12 2013-03-14 |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-04-18 : 00:41:12
|
You generally don't need dynamic SQL. You certainly don't want one table per employee!What do the 3 dates represent? Are they related or separate?Will there be more than one date per employee per table?Will there ever be no date per employee per table?I can't tell what you want, but I can tell you you're probably going about it the wrong way!Can you restate your problem in terms of what you are trying to accomplish, not in terms of your solution? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-18 : 02:31:09
|
what i feel is you just need to have two tablesone to store the employee details like id,name etcand other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-18 : 20:42:37
|
quote: Originally posted by LoztInSpace You generally don't need dynamic SQL. You certainly don't want one table per employee!What do the 3 dates represent? Are they related or separate?Will there be more than one date per employee per table?Will there ever be no date per employee per table?I can't tell what you want, but I can tell you you're probably going about it the wrong way!Can you restate your problem in terms of what you are trying to accomplish, not in terms of your solution?
This is for my wife. She runs a hospital unit. they send people home early and she wants a program to track that. Sort who hasn't went home early recently, and make a decision on whos turn it is. It also needs the hours they went home early, but I figured that shouldn't be too hard once I figure the solution. So the dates, I thought, would be when they last went home early. So theoretically there should be similar number of dates per employee, but that won't happen. So having the dates in an "employee" table wasting resources? I thought about all in one table, and have a every growing number of columns (for the dates). And then somehow have a dynamic number of columns? But that seems more difficult.I will probably only want the last 5 or so for the actual report, but will need to keep a good number of dates for a while. |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-18 : 22:15:49
|
quote: Originally posted by visakh16 what i feel is you just need to have two tablesone to store the employee details like id,name etcand other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
And just add a column for each additional date? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-04-19 : 00:12:01
|
quote: Originally posted by pdgrimm
quote: Originally posted by LoztInSpace You generally don't need dynamic SQL. You certainly don't want one table per employee!What do the 3 dates represent? Are they related or separate?Will there be more than one date per employee per table?Will there ever be no date per employee per table?I can't tell what you want, but I can tell you you're probably going about it the wrong way!Can you restate your problem in terms of what you are trying to accomplish, not in terms of your solution?
This is for my wife. She runs a hospital unit. they send people home early and she wants a program to track that. Sort who hasn't went home early recently, and make a decision on whos turn it is. It also needs the hours they went home early, but I figured that shouldn't be too hard once I figure the solution. So the dates, I thought, would be when they last went home early. So theoretically there should be similar number of dates per employee, but that won't happen. So having the dates in an "employee" table wasting resources? I thought about all in one table, and have a every growing number of columns (for the dates). And then somehow have a dynamic number of columns? But that seems more difficult.
No, an ever growing number of columns is wrong.So what's wrong with holding the expected departure time and exit time on the employee table? Is there more to it than that?If you need to track multiple departures then you might need a table with employeeID (FK), expectedDeparture (not null) and actualDeparture (nullable).Your description of the problem still doesn't really tell me what you want but recording the relevant data is a good step. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-19 : 00:16:03
|
Why not just have an employee table and a table with dates they were released early? You don't need to add columns, just add a new row any time someone is released.-Chad |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-19 : 00:16:52
|
With a foreign key of employee id (obviously).quote: Originally posted by chadmat Why not just have an employee table and a table with dates they were released early? You don't need to add columns, just add a new row any time someone is released.-Chad
|
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-19 : 01:21:36
|
Ok. I am sorry to not be clear. I need a list of employees, and the dates which they got to leave early (and how early). So when a person is let leave early, that days date will be added. So tomorrow, she will check the program (a datagrid) and have it sorted by who hasn't went home in a while, to who left most recently. So she can give the one who hasn't went home in the longest period of time the option to go home. And then just go to the next name on grid is they don't want to.Chad- that is what I am thinking (with the code in the first post). The dilema will be displaying the name and dates in a datagrid. I am guessing I will show the most recent 5 dates they went home for each employee in the list (in case someone got to go home for whatever reason two times in a row, even if they are next that may factor in). So I would need to get the name from the info table, and then the 5 most recent dates (instead of all (unknown number) ) in the following columns. LoztInSpace- If I added the dates to the employee table (id, name, date1, date2,date3,.....) then the number of columns would keep growing according to who has left the most often, right?I certainly appreciate everyone's help on this.Not to get sidetracked, but I thought this would be similar to a basic sales/order system. There would be a table to keep all customer information, another table with a list of all that customers orders (with date, total, ..), then a table for each order? So If I wanted to see a gridview of customers, and the dates of their orders, this matches my problem. So I have been looking around for that scenario , but haven't found anything to help me out. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-19 : 01:35:25
|
No, the code in the first post has a table for each employee, which is not a good idea. You need 1 Employee table, and 1 ReleasedEarlyInfo table. Employee table would have all pertinent employee info, the ReleasedEarlyInfo would have info about each time an employee was released early (EmpId, Date, HoursEarly etc...). You can join those 2 tables, and return the top 1 or top 5 or whatever you need instances of an early release for any or all employees.-Chad |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:16:54
|
quote: Originally posted by pdgrimm
quote: Originally posted by visakh16 what i feel is you just need to have two tablesone to store the employee details like id,name etcand other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
And just add a column for each additional date?
not a column but a new row for each new date------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-19 : 19:21:45
|
quote: Originally posted by chadmat No, the code in the first post has a table for each employee, which is not a good idea. You need 1 Employee table, and 1 ReleasedEarlyInfo table. Employee table would have all pertinent employee info, the ReleasedEarlyInfo would have info about each time an employee was released early (EmpId, Date, HoursEarly etc...). You can join those 2 tables, and return the top 1 or top 5 or whatever you need instances of an early release for any or all employees.-Chad
Ok. I think I see what you mean. I'll start working on it that way. So having all the dates in one large table, and then querying it by ID is more efficient than querying all the individual tables? |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-19 : 20:53:33
|
Thanks Chad, I'm working on this way here: http://sqlfiddle.com/#!3/1a7ef/9This does seem easier (more efficient with resources?)CREATE TABLE Employee_Info_Table( [ID] [int] NOT NULL, [Employee_Name] [nvarchar](max) NULL) ON [PRIMARY]INSERT INTO Employee_Info_TableVALUES(1,'Pride Grimm'),(2,'Elissa Grimm'),(3, 'Riley Grimm'),(4,'Joe Biteme')CREATE TABLE ReleasedEarlyDateInfo ( [ID] [int] NOT NULL, [Date] [date] NULL) ON [PRIMARY]INSERT INTO ReleasedEarlyDateInfoVALUES(4,'1-1-08'),(1, '3-12-13'),(4, '1-3-09'),(1,'3-14-13'),(1,'1-1-10'),(1,'2-2-11'),(2, 3-2-13'),(4,'2-21-10'),(3,'1-1-11'),(2,'3-4-13'),(2, '4-4-12'),(3, '3-22-13'),(3,'3-4-13'),(3, '4-24-12'),(3,'4-17-13'),(1,'3-3-12'),(1,'4-4-13')So I am trying to write the query to populate a datagrid that selects the 5 most recent dates of each employee, a displays the data in a row with employee name first, (Employee_name, date1,date2,date3,date4,date5).This query only gives me the dates for the first employeeSELECT TOP 5 Employee_Name, DateFROM Employee_Info_TableJOIN ReleasedEarlyDateInfoON Employee_Info_Table.ID = ReleasedEarlyDateInfo.IDThis yieldsEMPLOYEE_NAME DATEPride Grimm 2013-03-12Pride Grimm 2013-03-14Pride Grimm 2010-01-01Pride Grimm 2011-02-02Pride Grimm 2012-03-03I want it to show (date 5 > date4 > ... > date1)EMPLOYEE_NAME DATE1 DATE2 DATE3 DATE4 DATE5 Pride Grimm date1 date2 date3 date4 date5Elissa Grimm date1 date2 date3 date4 date5Riley Grimm date1 date2 date3 date4 date5Joe Biteme date1 date2 date3 date4 date5I could probably do this in c# quicker. But I want to learn this.Edit:this gets me closerSELECT Employee_Name, DateFROM Employee_Info_Table JOIN ReleasedEarlyDateInfoON Employee_Info_Table.ID = ReleasedEarlyDateInfo.IDGroup by Employee_Name,DateEMPLOYEE_NAME DATEElissa Grimm 2012-04-04Elissa Grimm 2013-03-02Elissa Grimm 2013-03-04Joe Biteme 2008-01-01Joe Biteme 2009-01-03Joe Biteme 2010-02-21Pride Grimm 2010-01-01Pride Grimm 2011-02-02Pride Grimm 2012-03-03Pride Grimm 2013-03-12Pride Grimm 2013-03-14Pride Grimm 2013-04-04Riley Grimm 2011-01-01Riley Grimm 2013-03-22Riley Grimm 2013-03-24Riley Grimm 2013-04-17Riley Grimm 2013-04-24So I need to get the dates into columns, and sort it by date.So I found some code online that I was able to get to produce the results. I don't understand it right now, so I don't know how efficient it is.SELECT Employee_Name, DateINTO #TempTable FROM Employee_Info_Table JOIN ReleasedEarlyDateInfoON Employee_Info_Table.ID = ReleasedEarlyDateInfo.IDGroup by Employee_Name, Date select Employee_Name, pt.[1] as Date1, pt.[2] as Date2, pt.[3] as Date3, pt.[4] as Date4, pt.[5] as Date5from (select Employee_Name, Date, ROW_NUMBER() OVER(PARTITION BY Employee_Name ORDER BY Date) as rnfrom #TempTable as t) as t1pivot (max(t1.Date) for t1.rn in ([1], [2], [3], [4],[5])/* add max number of designations per emp*/) as pt Produces:EMPLOYEE_NAME DATE1 DATE2 DATE3 DATE4 DATE5Elissa Grimm 2012-04-04 2013-03-02 2013-03-04 (null) (null)Joe Biteme 2008-01-01 2009-01-03 2010-02-21 (null) (null)Pride Grimm 2010-01-01 2011-02-02 2012-03-03 2013-03-12 2013-03-14Riley Grimm 2011-01-01 2013-03-22 2013-03-24 2013-04-17 2013-04-24 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-04-19 : 22:35:00
|
Good stuff.Now it gets tricky to explain because I don't have SQL to hand.So you first need to get the top 5 for each one:select * from(SELECT Employee_Name, Date,ROW_NUMBER() over (partition by Employee_name order by Date desc) rownumFROM Employee_Info_Table JOIN ReleasedEarlyDateInfoON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID) xwhere rownum<=5 The you need to look up the PIVOT clause which will allow you to pivot on rownum to give you 1,2,3,4,5 across the screen.http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspxSorry, but it's too hard for me to do without typing it in and with no SQL I'll never get it even close to correct because I rarely use it.Alternatively, and arguably more correctly, do this part in the front end. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-19 : 23:11:52
|
quote: Originally posted by LoztInSpace Alternatively, and arguably more correctly, do this part in the front end.
This was going to be my suggestion.-Chad |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-19 : 23:50:19
|
quote: Originally posted by LoztInSpace Good stuff.Now it gets tricky to explain because I don't have SQL to hand.So you first need to get the top 5 for each one:select * from(SELECT Employee_Name, Date,ROW_NUMBER() over (partition by Employee_name order by Date desc) rownumFROM Employee_Info_Table JOIN ReleasedEarlyDateInfoON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID) xwhere rownum<=5 The you need to look up the PIVOT clause which will allow you to pivot on rownum to give you 1,2,3,4,5 across the screen.http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspxSorry, but it's too hard for me to do without typing it in and with no SQL I'll never get it even close to correct because I rarely use it.Alternatively, and arguably more correctly, do this part in the front end.
I just posted an edit with some code, similar to this, that worked. I have to figure out exactly how it works.I thought about doing this in c#. When I try the code I get to work in sql server management studio, it isn't working as the query in a datasource for a gridview.Would I create a table in c# then bind that to the grid? I guess create the table piecemeal. Thanks for you guys' patience and help. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-04-20 : 02:56:56
|
Not sure about why the C# won't work - it's just a bunch of rows same as anything else. Personally I'd have it in a stored procedure and just use that the same way you'd do anything else.I'd also get rid of the temp table (I know it makes it easier to read but generally you don't need temps and they interfere with the optimiser).Finally, don't forget your where rownum<5 to stop doing more work than is necessary. |
|
|
pdgrimm
Starting Member
8 Posts |
Posted - 2013-04-28 : 01:59:22
|
I finally got back to working on this.I started doing it in c# and will have that done pretty quick.I still really want to figure out a pure sql way of doing this just to do it. I get really close with some temptables like this:SELECT Employee_Dates.Employee_ID, Employee_Name, datesINTO #TempTable FROM Employee_Info JOIN Employee_DatesON Employee_Info.Employee_ID = Employee_Dates.Employee_IDGroup by Employee_Dates.Employee_ID, Employee_Name, dates select Employee_ID,Employee_Name,pt.[1] as Date1, pt.[2] as Date2, pt.[3] as Date3, pt.[4] as Date4, pt.[5] as Date5INTO #TempTabletwo from (select Employee_Name, dates, Employee_ID, ROW_NUMBER() OVER(PARTITION BY Employee_Name ORDER BY dates Desc) as rnfrom #TempTable as t) as t1pivot (max(t1.dates) for t1.rn in ([1], [2], [3], [4],[5])) as ptSelect * from #TempTabletwo http://sqlfiddle.com/#!3/96d0d/8 It returnsID EMPLOYEE_NAME DATE1 DATE2 DATE3 DATE4 DATE52 Elissa Grimm 2013-03-04 2013-03-02 2012-04-04 (null) (null)4 Joe Biteme 2010-02-21 2009-01-03 2008-01-01 (null) (null)1 Pride Grimm 2013-04-04 2013-03-14 2013-03-12 2012-07-11 2012-03-033 Riley Grimm 2013-04-24 2013-04-17 2013-03-24 2013-03-22 2011-01-01Which is perfect, with just one more step to do. I need to sort the table by Date1 with the furthest away first.Is there much time/ resource difference in the temptable way? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-29 : 00:24:45
|
for sorting you just need to make last statement like...Select * from #TempTabletwo order by DATE1 DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|