I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:
> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:
> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>
Showing posts with label null. Show all posts
Showing posts with label null. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Query filtering problem
I do not want results with combinations like this:
d.workphone is null and m.workphone = '0'
m.workphone is null and d.workphone = '0'
but my query below still brining in records like this, how can I fix this:
select m.number, m.workphone as master_workphone, d.workphone as debtor_workphone
FROM master m
INNER JOIN debtors d ON d.number = m.number
where (d.workphone <> m.workphone)
AND NOT (d.workphone = '' AND m.workphone = '')
AND NOT ((d.workphone = '0' AND m.workphone = '') OR (d.workphone = '' AND m.workphone = '0'))
Some thoughts...
You mention null as something you want to filter on, but your query does not filter on nulls, it only uses '' (empty string)
Could you provide some sample data that your query treats in the 'wrong' way and also what result you want from that example?
/Kenneth
|||sorry, I mean't ''|||No problem, that's cool. =:o)
Can you provide with an example that your query does 'wrong'..?
A small script generating tables with just the necessary columns along with a few rows of data that illustrates your problem is enough.
/Kenneth
Friday, March 9, 2012
Query Design
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***Please do not multi-post. See my reply in comp.databases.ms-sqlserver.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bill Bob" <nospam@.devdex.com> wrote in message
news:uMGK1tFNGHA.1032@.TK2MSFTNGP11.phx.gbl...
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00
But, I am getting More than One row from the transactions table. I just
need the first matching row
ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00
The Query I am using is
SELECT dbo.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.examnotes.net ***
Monday, February 20, 2012
Query Assistance
Hi,
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>
Query and GROUP BY
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODEint NOT NULL,
PRODUCT_QTYint NOT NULL,
SALE_TIMESTAMPdatetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
Assuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegr oups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODEint NOT NULL,
PRODUCT_QTYint NOT NULL,
SALE_TIMESTAMPdatetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
Assuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegr oups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
Query and GROUP BY
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=
@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
Query and GROUP BY
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <= @.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <= @.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
NeilAssuming that if two sales occur at exactly the same, you want just one of
them:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
HISTORY
WHERE
(PRODUCT_CODE = @.product_code)
AND (SALE_TIMESTAMP <= @.sale_timestamp)
ORDER BY
SALE_TIMESTAMP DESC
END
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go
I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:
CREATE PROCEDURE sp_get_sales @.product_code int, @.sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @.product_code) AND (SALE_TIMESTAMP <=@.sale_timestamp)
GROUP BY PRODUCT_CODE
END
go
However, this fails to create with:
Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?
thanks,
Neil
Subscribe to:
Posts (Atom)