Please help me populate my date fields

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:

Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as xupdate orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||populate only orderDate in this

CREATE TABLE [dbo].[Orders] (
[OrdID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[OrderDateONLY] AS ([orderdate]) ,
[OrderDayName] AS (datename(weekday,[orderdate])) ,
[OrderMonth] AS (datename(month,[orderdate]))
.....
)|||harsal_in,

Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

"The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out :confused:|||it works very well on my machine.
anyways try this:
update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||harshal_in,

I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

Thanks.|||Format 120 (or 121 Brett...) are better for date conversion because they are interpreted unambiguously by SQL Server.

update orders
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
orderDayname=datename(dw,orderdate),
orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),
orderweekofyear=datename(wk,orderdate)

...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.|||Unless it's a warehouse...or for performance reasons...which I can't see it...

NEVER store derived data....

What's the reason..

If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

Which means an additional process..

This is OLTP, right?

in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

OLTP is fluid, and alway in a state of flux...(like the capacitor :D )

So why?sql

Pivotting Data

I have this table:

CREATE TABLE [dbo].[SHIP_HISTORY] (
[SHIPID] [int] IDENTITY (1, 1) NOT NULL ,
[PRODUCT] [nvarchar] (18) NOT NULL ,
[ORDERNUM] [char] (10) NOT NULL ,
[SHIP_DATE] [smalldatetime] NOT NULL ,
[WHSE] [nvarchar] (5) NOT NULL ,
[UNITS] [real] NOT NULL
) ON [PRIMARY]

It contains 4 years of sales history.

I need to pivot the data for a form in my front end (Access). The query was simple in Access, but too much data caused the form to be too slow.
I'm thinking I'll pivot the data in SQL first, and just link my front end to the new table.

I started to write a sproc to do this, and tested it before getting too far. For some reason, this is returning 166 rows, when I expect to seee only 4 (one for each year).
There is one row for each year that contains my totaled data, and the rest of the rows contain zeros. Any ideas what would be causing this?

Here's the SQL I'm using:

SELECT YEAR(sHIP_DATE) AS YEAR,
'1' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN SUM(UNITS)
ELSE 0
END,
'2' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN SUM(UNITS)
ELSE 0
END
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE]), DatePart(ww,[SHIP_DATE])

If anyone has a better idea for how to do this, I'd welcome that, too!

ThanksYou are grouping by DatePart(ww,[SHIP_DATE]), and thus get rows for each of these values, even though you are not displaying DatePart(ww,[SHIP_DATE]) in your SELECT list.|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])|||[edit] posted before reading your second post|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])

That works perfectly.
Thanks, Blindman!

Pivot Query Question - Can I have 2 columns in a Pivot?

I want two columns in Pivot format. Is it possible.

I have the following table:
CREATE TABLE CONDITION DETAILS
(
CONDITIONID INT,
NAME VARCHAR(100),
DESCRIPTION VARCHAR(50),
USERNAME VARCHAR(50),
NOTIFY_FREQUENCY VARCHAR(10))

INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','First Person Notified','JC','Daily')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Second Person Notified','BG','Weekly')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Third Person Notified','BG','Monthly')

Now I have the following statement
SELECT * FROM
(SELECT CONDITIONID, NAME, DESCRIPTION, USERNAME
--, NOTIFY_FREQUENCY
FROM CONDITION) SOURCEQUERY
PIVOT (MIN(USERNAME) FOR [DESCRIPTION] IN ([First Person Notified], [Second Person Notified], [Third Person Notified])
) AS PIVOTTABLE

Output:
CONDITIONID NAME First Person Notified Second Person Notified Third Person Notified
1 Receipt of statements JC BG BG

I also want the Frequency to be displayed, I want output to be like
1 Receipt of Statements Daily JC Weekly BG Monthly BG

Can anyone suggest. Thanks.

The first thing that I want to do is to give kudos for such an outstanding job of perparing your question. Having the table definition and the insert statements for the test data made this much easier to set up for testing. Nice Work!

I have to confess that my work on this might not be as good of an effort as the question. I have never done a double-pivot so I really am not sure what is the best way to go about it. This seems to work. I would really like commentary from Umachandar or someone similar who is familar with a "best approach."

SELECT conditionId,
name,
rtrim(substring([First Person Notified], 11, 50)) as [1st Person],
rtrim(left([First Person Notified], 10)) as [1st Frequency],
rtrim(substring([Second Person Notified],11, 50)) as [2nd Person],
rtrim(left([Second Person Notified],10)) as [2nd Frequency],
rtrim(substring([Third Person Notified], 11, 50)) as [3rd Frequency],
rtrim(left([Third Person Notified],10)) as [3rd Person]
FROM ( SELECT CONDITIONID,
NAME,
DESCRIPTION,
cast (isnull(NOTIFY_FREQUENCY, '') as char(10))
+ username
as pivotStuff
FROM CONDITION
) SOURCEQUERY
PIVOT ( MIN(pivotStuff) FOR [DESCRIPTION]
IN ( [First Person Notified],
[Second Person Notified],
[Third Person Notified]
) ) AS PIVOTTABLE

-- conditionId name 1st Person 1st Frequency 2nd Person 2nd Frequency 3rd Frequency 3rd Person
-- -- - -- - -- - -- -
-- 1 Receipt of statements JC Daily BG Weekly BG Monthly

|||

Thanks. This sure works. Had thought about this solution but was not sure if it was right way to do, so had posted my query on to the forums.

Please let me know if there is a better way to achive the above results.