I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @.gdo and @.gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @.biui, 'Updated booking, ID of booking updated: ' & @.biui & ', Booking date and time before/after update: ' & @.gdo & '/' & @.gd & ', Room number before/after update: ' & @.rno & '/' & @.rn & ' and Customer ID before/after update: ' & @.cio & '/' & @.ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:Another trouble, Now I also figure even I cancel the date variable out, I also get the Syntax error trying to convert varchar 'blah blah blah' into an int column.
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @.biui, 'Updated booking, ID of booking updated: ' & @.biui & ', Booking date and time before/after update: ' & @.gdo & '/' & @.gd & ', Room number before/after update: ' & @.rno & '/' & @.rn & ' and Customer ID before/after update: ' & @.cio & '/' & @.ci)
I means the & @.biui & between Strings. @.biui, @.rno, @.rn are all int
Can someone give me some help pls?|||If I see it right you want to convert date and int into string. Try CAST or CONVERT, and use '+' to concatenate strings instead of '&' which is bitwise AND operation.|||Thanks!
It Works great!!
I have created a procedure which display all the record in a table.
How can I execute that in Access please? (I mean show the table in access)sql
Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Invalid dates passed to an SP
Hi,
We have a number of SP's which have a couple of datetime params.
We expect the format to be such as 'YYYYMMDD' to get around the issue of US
vs UK dates. Unfortunately if an invalid date is passed, it errors straight
away with (e.g.)
Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
Error converting data type varchar to datetime.
Is there any way of getting around this? The error occurs BEFORE I can check
whether the dates are valid with the isdate() function.
I know that I can change all the params to varchar, and check them before
putting the values into datetime variables, but this seems a bit of a fudge.
Any help would be most appreciated.
Cheers!
AndyWell, you pass a invalid parameter to a stored procedure. Of course the
parameters are checked _before_ the stored procedure itself is executed.
And what is the exact use of checking the parameter in the stored procedure
itself (if you changed the parameter to a character datatype)? The only
thing you could do is raise a more custom error message, but I hope the
programmers that code against this stored procedure are smart enough to work
out the meaning the error message they get now, and implement error handling
or fix bugs on their side accordingly.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy|||I agree with what you're saying, but just wanted to write a more robust SP s
o
that i could provide users with a simple error message.
regards,
Andy
"Jacco Schalkwijk" wrote:
> Well, you pass a invalid parameter to a stored procedure. Of course the
> parameters are checked _before_ the stored procedure itself is executed.
> And what is the exact use of checking the parameter in the stored procedur
e
> itself (if you changed the parameter to a character datatype)? The only
> thing you could do is raise a more custom error message, but I hope the
> programmers that code against this stored procedure are smart enough to wo
rk
> out the meaning the error message they get now, and implement error handli
ng
> or fix bugs on their side accordingly.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
>
>|||The applications that access the database should provide the users with a
simple error message. Users really shouldn't call stored procedures directly
IMO.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:DDAC2AB1-4794-4F79-B467-04EB4E36CDA7@.microsoft.com...
>I agree with what you're saying, but just wanted to write a more robust SP
>so
> that i could provide users with a simple error message.
> regards,
> Andy
> "Jacco Schalkwijk" wrote:
>|||Nothing reasonable you can do except make sure that when you build an
application you don't allow bad dates. There are lots of date controls that
can be purchased, or easily written.
You "could" change your parms to text and check them, but that is not a
great use of processing time. Checking universally known domain values like
this should always be done in the client where the possible 10 milliseconds
to perform the task will not be added to multiple other operations, causing
greater waiting all around. Doing it as the user leaves the date control
puts this work in a better place.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy
We have a number of SP's which have a couple of datetime params.
We expect the format to be such as 'YYYYMMDD' to get around the issue of US
vs UK dates. Unfortunately if an invalid date is passed, it errors straight
away with (e.g.)
Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
Error converting data type varchar to datetime.
Is there any way of getting around this? The error occurs BEFORE I can check
whether the dates are valid with the isdate() function.
I know that I can change all the params to varchar, and check them before
putting the values into datetime variables, but this seems a bit of a fudge.
Any help would be most appreciated.
Cheers!
AndyWell, you pass a invalid parameter to a stored procedure. Of course the
parameters are checked _before_ the stored procedure itself is executed.
And what is the exact use of checking the parameter in the stored procedure
itself (if you changed the parameter to a character datatype)? The only
thing you could do is raise a more custom error message, but I hope the
programmers that code against this stored procedure are smart enough to work
out the meaning the error message they get now, and implement error handling
or fix bugs on their side accordingly.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy|||I agree with what you're saying, but just wanted to write a more robust SP s
o
that i could provide users with a simple error message.
regards,
Andy
"Jacco Schalkwijk" wrote:
> Well, you pass a invalid parameter to a stored procedure. Of course the
> parameters are checked _before_ the stored procedure itself is executed.
> And what is the exact use of checking the parameter in the stored procedur
e
> itself (if you changed the parameter to a character datatype)? The only
> thing you could do is raise a more custom error message, but I hope the
> programmers that code against this stored procedure are smart enough to wo
rk
> out the meaning the error message they get now, and implement error handli
ng
> or fix bugs on their side accordingly.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
>
>|||The applications that access the database should provide the users with a
simple error message. Users really shouldn't call stored procedures directly
IMO.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:DDAC2AB1-4794-4F79-B467-04EB4E36CDA7@.microsoft.com...
>I agree with what you're saying, but just wanted to write a more robust SP
>so
> that i could provide users with a simple error message.
> regards,
> Andy
> "Jacco Schalkwijk" wrote:
>|||Nothing reasonable you can do except make sure that when you build an
application you don't allow bad dates. There are lots of date controls that
can be purchased, or easily written.
You "could" change your parms to text and check them, but that is not a
great use of processing time. Checking universally known domain values like
this should always be done in the client where the possible 10 milliseconds
to perform the task will not be added to multiple other operations, causing
greater waiting all around. Doing it as the user leaves the date control
puts this work in a better place.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy
Monday, March 12, 2012
Invalid Column Name error,
Hi,
I'm having trouble with the following query.
select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name
Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).
I keep getting the error,
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.
Any help appreciated.First, use this to truncate your datetime values:
cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate
Second, you can't reference AuditDate by name; you have to reference it by the formula:
where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name
It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).
blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.
It's working now!
I'm having trouble with the following query.
select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name
Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).
I keep getting the error,
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.
Any help appreciated.First, use this to truncate your datetime values:
cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate
Second, you can't reference AuditDate by name; you have to reference it by the formula:
where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name
It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).
blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.
It's working now!
Friday, March 9, 2012
Invalid character value for cast specification
Hi: gurus,
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
Wednesday, March 7, 2012
Intricate SQL Statement
Hi there at the forum,
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)