Data from different columns for one column

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

Data from different columns for one column

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-07 : 00:03:05

Hi,

I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta".
The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester".
Data for columns "Activity" and "Hours Used" taken from table called ALD.
"Delta" column is the difference between Hours Given and Hours Used.
For "Hours Given" in table "Daily", the data source should change every 12 hours.
For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .

How to do that? Please Help. Thank you.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 00:22:33

Can you clarify?

I recall the Delta column from your post the other day..

So if I understand this question correctly, you need to update this "Daily" table, but the source of this update needs to change every 12 hours?

You would just have 4 separate procedures as one option...

doing this in one query would be a little nasty...

What does ONE of the necessary queries look like now? and does it work as needed?

Edit...if you just need to change which column the source value comes from, you can do that with Case WHEN

Case When [condition] then [Column1]
When [Condition] then [Column2]
ELSE
END

Poor planning on your part does not constitute an emergency on my part.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-07 : 01:22:12

Do you mean that the recordset should be comprised of those particular subsets for the defined periods?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 02:02:51

jacky...related to this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98440

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 20:14:50

Thanks for the reply. You are right. The source of this update needs to change every 12 hours. I know that i have to use the if else statements or case statements but how to specify the condition that it should change every 12 hours? How to make it detect the days. I dont know. Please help me.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 20:23:04

quote:
Originally posted by shan1430

Thanks for the reply. You are right. The source of this update needs to change every 12 hours. I know that i have to use the if else statements or case statements but how to specify the condition that it should change every 12 hours? How to make it detect the days. I dont know. Please help me.

Where are the 12 hour breaks?

You can test for various components of a datetime field pretty easily with the DATETIME, DATEDIFF and DATEADD functions available.

FOR example, try these in Query Analyzer

Select DATENAME(DAY,getdate())

Select DATEDIFF(DAY,'20080306','20080307')

Select DATEDIFF(HOUR,'3/6/2007 8:35:00.000 AM', '3/6/2007 12:55:00.00 PM')

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 22:08:04

the 12 break shoud be from 7 to 7. So every 7 hours the data source for the column should change. These codes tells the difference between hours but how to apply it in my case? Help..

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 22:12:23

Sorry, the 12 hours break should occur at 7am and 7 pm. So the data source changes whenever it is 7 am and 7 pm.
For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" . Do you get the situation? Help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 22:28:03

The structure above is a little different than what was in your other post..is there a datetime column that is to be used as the comparison?

Give me some more context on this one...

When you say "every 12 hours source column changes", and this change occurs at 7AM and 7PM, it sounds like you also have to check for the day of the week?

Is the "changing of the source column" based on a column of data with a datetime for comparison? or will it be based on a datetime field that represents the Tester's clock in and clock out time?

For clarification, you say the source column changes at 7AM Monday to be the Monday-Friday Mornings source column. Prior to that the source column would be the WeekEnd_Night column.

So my question I guess is, am I basing the comparison on running this at 7AM monday? or am I basing this comparison to a datetime value that exists?

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 22:51:36

Can you give me your email address so that i can attach some tables for better understanding?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 23:11:01

See if this gives you some insight..

just a quick sample to show you how to possible do this





Create Table #testtimes (testdate datetime not null)

insert into #testtimes(testDate)
Select dateadd(hh,number,getdate())
FROM master..spt_values
Where Type = 'P' and number in (5,15,20,48,60)

Select TestDate,
	   Datepart(weekday,testDate) as dayINT,
	   datename(weekday,testdate) as WeekdayName,
			Case When  DatePart(hour,testdate) >=7 and 
				 DatePart(hour,testdate) <=19 and
				 DatePart(weekday,testdate) >=2 and DatePart(weekday,testdate) <=6 then 'Wkday_AM'

				 When  DatePart(hour,testdate) <7 or
				 DatePart(hour,testdate) >19 and
				 DatePart(weekday,testdate) >=2 and DatePart(weekday,testdate) <=6 then  'Wkday_PM'
		END as EvalResult
FROM #testtimes

Drop Table #testtimes

quote:

Can you give me your email address so that i can attach some tables for better understanding?

Maybe ;)....just give me the address where I can mail my invoice? (just kidding)

No need to send me tables...if you can't explain it logically, then it the design itself may be illogical.

answer this:

Is there a date to use as a basis for the 12 hour switch, particularly..one in the tester table that identifies which DATE the hours were given?

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 23:15:57

the hours are given every mondays.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 23:19:47

you are missing my point.

How is the logic supposed to identify the 12 hour switch point?

Yes, I understand that 7AM and 7PM require different columns.

Yes, I understand that weekdays and weekends are different.

If they submit their hours on Monday AM. How does the Tester Table know which column to put them in?

My main point is, you can't do the logic for using different source columns based on switching every 12 hours without a frame of reference to base that time comparison on.

You can't say if [nothing] is between 7 AM and 7PM then [Do Something]

You have to say if [date of service] is between 7AM and 7PM then [Do Something] Else [Do Something Else]

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-09 : 23:29:13

They will fill up all the columns manually in the tester table. So tester table dont need to identify anything. Only the Hours given in daily table must identify which column from tester table to be read every 12 hours. And How to set the logic to identify the 12 hour switch point, thats what i dont know.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 23:38:45

Then how would I, or anyone else?

There HAS to be a basis for the time evaluation?

is it the time the daily table is updated? (so if the daily table "grabs" the information at Monday morning 7AM, it looks in the appropriate column?)

Perhaps a different approach..from what you have said:

The testers enter their hours into the appropriate columns for the hours they worked during the past week. So if they worked Monday morning shift they would enter their total hours in the appropriate column, and would theoretically not enter time in another column.

The other angle is, assuming some miscommunication is that the information is being READ FROM the DAILY table and DELIVERED to the Testers during that time frame....which means... A particular tester reports to work at 8 AM monday. The Daily table delivers to them "something" based on the information in the Weekday_AM column?

Show me data for one tester and the desired result for that tester?

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-10 : 00:12:53

I think tere is a misunderstanding. Let me explain the situation to you.
The “Hours Given” column in daily table is the one to be updated now not the Tester tabe.
The Tester table will be updated manually by Admin every Mondays. Weekday_day,Weekday_night,Weekend_day,Weekend_night in the Tester Table are the allocation for users to use the testers for that particular week.
Users should use the testers less or equal to the hours allocated for them on the respective days. But users are using more that allocated. So I created a table called Daily with columns “ TesterID”,”Activities”,”Hours_Given”,”Hours_Used” and “Delta”. Delta should have the substracted values of Hours_given and Hours_Used as you helped me on that day. Once there is a negative in Delta I should trigger a mail to the admin.

The Hours_used are grabbed from another table (ALD) which has the hours that user used for every past 12 hours. Let say now is Monday morning 7am, ALD will have the hours used for 7pm Sunday night to Monday morning 7am. And when the day reaches 7pm today, ALD will have the data for 7am – 7pm for Monday.
So ill use that data in the daily table for column Hours_used. So there is no entering of hours used by users. All the record of hours user used will be detected by ALD software.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-10 : 00:32:48

That makes it a little clearer. I knew that Tester was not being updated by this, and it was the Daily table you wished updated. Remember, with no table structures of any kind provided, no sample data provided, and no context provided (until now) it was challenging to try and offer a solution.

as I said, i can't propose a solution or alternatives without certain information.

So you intend to have something trigger at 7AM and 7PM each day to pull information from ALD. The intent is to show how many hours a user has left in a given allocation of hours, and that specific allocation of hours is based on the 4 columns you noted in the original post.

So, at 7AM Monday,
1.ALD gets updated with last nights hours used from 7PM to 7AM,
2.you need to get the information for the 12 hour period from 7PM Sunday night through 7AM monday for hours used from ALD
3.you also need to get the information for the 12 hour period from 7PM Sunday through 7AM monday for hours given from a specific column in the tester table.
4. This hours_given-hours_used gets put into the daily table as the hours remaining?

If that is incorrect, please lay it out in that type of orderly fashion.

Keep in mind that I am still looking for a time to use as a comparison. In theory, if this runs at 7AM and 7PM each day, that makes it easier because the time for deciding which column would be just that...but absent of a SQL server agent schedule executing at precisely 7AM and 7PM each day...where would the comparison expression find a time to look at that would be applicable?

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-10 : 02:32:54

Daily Table
TesterID, Hours Given, Hours Used, Activity, Delta,
1, 2, 4, Ab, -2,
2, 4, 3, Cd, 1,
3, 6, 6, Ef, 0,

Tester Table
TesterID Weekday_day Weekday_Night Weekend_day Weekend_Night
1, 2, 3, 8, 6,
2, 4, 5, 5, 5,
3, 6, 6, 6, 9,

ALD table
TesterID Activity Hours_used
1, Ab, 4,
2, Cd, 3,
3, Ef, 6,

So

If it is Monday – Friday mornings,
the Hours Given column in Daily table should get the data from Weekday_day from Tester table for the respective TesterID for 7am-7pm.

Else if it is Monday- Frida nights,
the Hours Given column in Daily table should get the data from Weekday_night from Tester table for the respective TesterID for 7pm-7am.

Else if it is Saturday and Sunday morning,
the Hours Given column in Daily table should get the data from Weekend_day from Tester table for the respective TesterID for 7am-7-pm.

Else if it is Saturday and Sunday nights,
the Hours Given column in Daily table should get the data from Weekend_night from Tester table for the respective TesterID for 7pm-7-am.

I dont understand what time you wanted.Sorry im new for programming. Can you tell me what are the things that you want me to explain to you.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-10 : 13:05:56

I am not sure how to ask it any clearer.

I am looking to fill in the [ThisTime] in the following, with some type of meaningful value.

You are the only one who can tell me what [ThisTime] should be.

Case when [ThisTime] between 7AM and 7PM then.....
When [ThisTime] between 7PM and 7AM then....

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-10 : 21:44:45

If [This Time] is the time where it have to swith the data source then it is at 7am and 7pm daily.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-10 : 22:38:01

Okay, this is getting frustrating. I understand where you are trying to get...but you are not understanding what i am asking you, repeatedly and different ways.

You still haven't answered my question, you simply rephrased it!

where does [THIS TIME] Come from? it HAS to come from somewhere? is the time the procedure runs? is it in a field in a table somewhere?

Honestly, I think the data stucture is bad for what you want to do. You should have all your hours given in ONE column, and merely assign them based on another column with a code indicating the shift assignment for that tester.

That way you have 1 row for a testerID, hoursgiven and shift id.

EDIT:

Endless Loop ;n .see Loop,Endless
Loop, Endless ;n .see Endless Loop

Poor planning on your part does not constitute an emergency on my part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources