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

Please HELP

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

Quote:

Originally Posted by gggram2000

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)|||

Quote:

Originally Posted by amitpatel66

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)

Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.

A question would be how to insert a value (eg. Name) into a table row block. Like: Insert Into Table (ClolumnName) Value(@.ColumnName) But I want to enter it into and existing column/row to replace the value of that specific column with "Name". Let's say for that column it has "None"...I want to change it to "Name"...how do i do that?

please check this SP syntax - need more eyes!

when I try to create this SP I get: "incorrect syntax near @.MyResult"
I have tried INT and different variable names, but get same error.

CREATE PROCEDURE sp_IsValidLogon
@.UserName varchar(16),
@.Password varchar(16) ,
@.MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @.UserName
And
Password = @.Password)
begin
@.MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@.UserName, @.Password)

declare @.totalFails int
Select @.totalFails = Count(*) From FailedLogons
Where UserName = @.UserName
And dtFailed > GetDate()-1

if (@.totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @.UserName

@.MyResult = 0
endThe answer is

SET @.MyResult = ...|||Thanks George,
That's sorted it.
I seem to have two threads in this forum now tho' :o sorry guys.|||No worries - I will remove the dupe :)

pk-fk problems

Hallo,

table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

relationship name in B is marked with infinity symbol.

Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.

Relevant record from B is removed properly.

CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end

what is wrong ?

Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser

Its ok, order :)

pluton

pk-fk delete problens

Hallo,
table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
relationship name in B is marked with infinity symbol.
Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.
Relevant record from B is removed properly.
CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end
what is wrong ?
Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser
Its ok, order
pluton

PK on computed column

Maybe I am missing something very obvious, but I couldn't do it:

begin tran
go
create table foo (
f1 int not null,
f2 int not null,
f3 as (f1 + f2) not null primary key clustered)
go
rollback tran
go

This returns:
Server: Msg 8183, Level 16, State 1, Line 8
Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.Never mind, this worked:

begin tran
go
create table foo (
f1 int not null,
f2 int not null,
f3 as isnull((f1 + f2), 0) primary key clustered)
go
rollback tran
go|||My machines don't care for that either, although logically they shouldn't object.

-PatP|||OK...for the life of me....why?|||Actually it sarted with RogerWilco's post (http://www.dbforums.com/t1006214.html (http://www.dbforums.com/t1006214.html)), where I was trying to demonstrate that while having 4 fields instead of 1 does not mean that you will have to perform every join on all 4 fields. And sure enough, you also can make the computed column a PK, and have a UNIQUE constraint defined on it, needless to say create indexes. Of course, when we start talking about doing those things, Brett's belief that "one should leave all connection settings to default" would have to be shattered, because this is exactly the situation where not only you have to change them, but also understand the implications of changing each and one of them...But that may be easily transfered to a dedicated thread...I already see its name...

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.