Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
How to pick only distinct bill id's ..
I have some bills keep circulating in our BillWorkflow table. I have to pick only those Bills.
Almost every bill will hit this ProcessorId, ProcessorBinId and StateId.
But i want the bills which are keep circulating (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38.
select distinct BillId from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
How should i pick only those distinct bill id's
Almost every bill will hit this ProcessorId, ProcessorBinId and StateId.
But i want the bills which are keep circulating (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38.
select distinct BillId from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
How should i pick only those distinct bill id's
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi
why dont you try this to find which billid's appear more than once
select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
and count(BillId)>1
Group By BillId
why dont you try this to find which billid's appear more than once
select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
and count(BillId)>1
Group By BillId
When you say "... it is on top 1 record ...", Top requires order by. Do you want to order by TimeStmp?
Also, BillId is the same for all repeating bills. If not, how can we recognize they are repeat bills?
Also, BillId is the same for all repeating bills. If not, how can we recognize they are repeat bills?
Msg 147, Level 15, State 1, Line 6
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Query returns ...
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I am sorry
select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
Group By BillId
Having count(BillId)>1
select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >= '08/30/2015'
Group By BillId
Having count(BillId)>1
>>"How to pick only distinct bill id's
>>"... (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38."
I assume from this you want ONLY the bill id, you are able to use the having clause without also needing to include the aggregate calculation in the select clause. To correctly using TOP there should be an ORDER BY clause.
Please also note that MM/DD/YYYY is NOT a safe date format, but YYYYMMDD is a safe date literal format in SQL Server
>>"... (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38."
I assume from this you want ONLY the bill id, you are able to use the having clause without also needing to include the aggregate calculation in the select clause. To correctly using TOP there should be an ORDER BY clause.
SELECT TOP(1)
BillId
FROM Billworkflow
WHERE ProcessorID = 300
AND ProcessorBinID = 50
AND StateID = 38
AND TimeStmp >= '20150830'
HAVING COUNT(BillId) >= 5
ORDER BY
TimeStmp DESC
Please also note that MM/DD/YYYY is NOT a safe date format, but YYYYMMDD is a safe date literal format in SQL Server
mmm, there are errors in my previous comment - sorry
This might work instead:
details:
This might work instead:
SELECT TOP (1)
BillId
FROM (
SELECT
BillId
, TimeStmp
, COUNT(*) OVER (PARTITION BY BillId) AS UsedTimes
FROM Billworkflow
WHERE ProcessorID = 300
AND ProcessorBinID = 50
AND StateID = 38
AND TimeStmp >= '20150830'
) X
WHERE X.UsedTimes >= 5
ORDER BY X.TimeStmp DESC
details:
CREATE TABLE Billworkflow
([BillId] int, [ProcessorID] int, [ProcessorBinID] int, [StateID] int, [TimeStmp] datetime)
;
INSERT INTO Billworkflow
([BillId], [ProcessorID], [ProcessorBinID], [StateID], [TimeStmp])
VALUES
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(1, 300, 50, 38, '2015-08-30 00:00:00'),
(2, 300, 50, 38, '2015-08-30 00:00:00'),
(2, 300, 50, 38, '2015-08-30 00:00:00'),
(2, 300, 50, 38, '2015-08-30 00:00:00')
;
**Query 1**:
SELECT TOP (1)
BillId
FROM (
SELECT
BillId
, TimeStmp
, COUNT(*) OVER (PARTITION BY BillId) AS UsedTimes
FROM Billworkflow
WHERE ProcessorID = 300
AND ProcessorBinID = 50
AND StateID = 38
AND TimeStmp >= '20150830'
) X
WHERE X.UsedTimes >= 5
ORDER BY X.TimeStmp DESC
**[Results][2]**:
| BillId |
|--------|
| 1 |
[1]: http://sqlfiddle.com/#!3/8cad2/2
Premium Content
You need an Expert Office subscription to comment.Start Free Trial