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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 First Complex SQL Project

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_Table
VALUES
(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_1
VALUES
(1, '3-12-13'),
(1,'3-14-13')

/*

CREATE TABLE Dates_2(
[ID] [int] NOT NULL,
[Date1] [date] NULL
) ON [PRIMARY]

INSERT INTO Dates_2
VALUES
(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_3
VALUES
(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 columns

DECLARE @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_2
Pride Grimm 2013-03-12 2013-03-14
Elissa Grimm 2013-03-12 2013-03-14
Riley 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?
Go to Top of Page

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 tables

one to store the employee details like id,name etc

and other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 tables

one to store the employee details like id,name etc

and other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




And just add a column for each additional date?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 tables

one to store the employee details like id,name etc

and other one to store the date time values. It will have EmployeeID as a foreign key reference from employee table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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?
Go to Top of Page

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/9

This 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_Table
VALUES
(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 ReleasedEarlyDateInfo
VALUES
(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 employee


SELECT TOP 5 Employee_Name, Date
FROM Employee_Info_Table
JOIN ReleasedEarlyDateInfo
ON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID


This yields

EMPLOYEE_NAME DATE
Pride Grimm 2013-03-12
Pride Grimm 2013-03-14
Pride Grimm 2010-01-01
Pride Grimm 2011-02-02
Pride Grimm 2012-03-03

I want it to show (date 5 > date4 > ... > date1)

EMPLOYEE_NAME DATE1 DATE2 DATE3 DATE4 DATE5
Pride Grimm date1 date2 date3 date4 date5
Elissa Grimm date1 date2 date3 date4 date5
Riley Grimm date1 date2 date3 date4 date5
Joe Biteme date1 date2 date3 date4 date5


I could probably do this in c# quicker. But I want to learn this.


Edit:
this gets me closer


SELECT Employee_Name, Date
FROM Employee_Info_Table
JOIN ReleasedEarlyDateInfo
ON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID
Group by Employee_Name,Date

EMPLOYEE_NAME DATE
Elissa Grimm 2012-04-04
Elissa Grimm 2013-03-02
Elissa Grimm 2013-03-04
Joe Biteme 2008-01-01
Joe Biteme 2009-01-03
Joe Biteme 2010-02-21
Pride Grimm 2010-01-01
Pride Grimm 2011-02-02
Pride Grimm 2012-03-03
Pride Grimm 2013-03-12
Pride Grimm 2013-03-14
Pride Grimm 2013-04-04
Riley Grimm 2011-01-01
Riley Grimm 2013-03-22
Riley Grimm 2013-03-24
Riley Grimm 2013-04-17
Riley Grimm 2013-04-24


So 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, Date
INTO #TempTable
FROM Employee_Info_Table
JOIN ReleasedEarlyDateInfo
ON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID
Group 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 Date5
from (
select Employee_Name, Date
, ROW_NUMBER() OVER(PARTITION BY Employee_Name ORDER BY Date) as rn
from #TempTable as t
) as t1
pivot (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 DATE5
Elissa 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-14
Riley Grimm 2011-01-01 2013-03-22 2013-03-24 2013-04-17 2013-04-24
Go to Top of Page

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) rownum
FROM Employee_Info_Table
JOIN ReleasedEarlyDateInfo
ON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID
) x
where 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).aspx
Sorry, 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.
Go to Top of Page

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
Go to Top of Page

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) rownum
FROM Employee_Info_Table
JOIN ReleasedEarlyDateInfo
ON Employee_Info_Table.ID = ReleasedEarlyDateInfo.ID
) x
where 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).aspx
Sorry, 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.
Go to Top of Page

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.
Go to Top of Page

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, dates
INTO #TempTable
FROM Employee_Info
JOIN Employee_Dates
ON Employee_Info.Employee_ID = Employee_Dates.Employee_ID
Group 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 Date5
INTO #TempTabletwo
from (
select Employee_Name, dates, Employee_ID
, ROW_NUMBER() OVER(PARTITION BY Employee_Name ORDER BY dates Desc) as rn
from #TempTable as t
) as t1
pivot (max(t1.dates) for t1.rn in ([1], [2], [3], [4],[5])) as pt

Select * from #TempTabletwo


http://sqlfiddle.com/#!3/96d0d/8

It returns

ID EMPLOYEE_NAME DATE1 DATE2 DATE3 DATE4 DATE5
2 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-03
3 Riley Grimm 2013-04-24 2013-04-17 2013-03-24 2013-03-22 2011-01-01

Which 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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -