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 |
|
jbphoenix
Yak Posting Veteran
68 Posts |
 Posted - 2009-11-16 : 16:06:43
|
| I am working on a report and need to get yesterday's DayDel and put it in a field for today. Here's an example - Cust Rec DayDel Date YstrdayCu12 315 42123 1115 Cu25 299 48321 1114 Cu19 141 62987 1113So if you look at the 1st line - I would need Ystrday to equal 48321. How would I go about this? Thanks |
|
|
knox203
Starting Member
31 Posts |
Posted - 2009-11-16 : 16:15:55
|
| This can definitely be done but, it really depends on how you're pulling the data and the structure of the database. Can we see your query, and the datatype for each column, please?Thanks, - Adam |
 |
|
|
jbphoenix
Yak Posting Veteran
68 Posts |
Posted - 2009-11-16 : 16:42:53
|
| select Cust, Rec, DayDel, Datefrom Table1where Cust like 'Cu%' and date >=20091001I shortened the date in the previous post to make it easier to read.Datatypes Cust varchar(25)Rec decimal(38,2)DayDel decimal(38,2)Today decimal(18,2)ystrday decimal(38,2) |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-16 : 18:36:33
|
| If I am reading your sample data right, which I don't think that I am, then you have five columns (Cust, Rec, DayDel, Date, and Ystrday) but only four data items. Also, as Adam has already requested, we need the data types of the columns. "1114" is not a date.Help us to help you...=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
jbphoenix
Yak Posting Veteran
68 Posts |
Posted - 2009-11-17 : 10:41:04
|
| You are correct I have 5 columns but only 4 have data in them right now. I'm trying to figure out how to get the correct data in the 5th column (Ystrday). The date column is an int. I did not design the table and realize having the date column as an int isn't ideal but I cannot change the table structure. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-17 : 19:52:25
|
| If "Yesterday" is (Date - 1) then can't you do some arithmetic to derive the value for yesterday? Does the Date represent a MMDD date value?Yesterday = DateAdd(day, -1, cast(cast(Year(GetDate()) * 10000 + Date as varchar) as datetime))=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-17 : 20:02:59
|
| What dates do 48321 and 1114 represent?JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Display yesterday's data today