Count consecutive numbers

I'm trying extract a count of consecutive numbers, or "unbroken" years in
this case, at any particular given time.

For example (simplified):

CREATE TABLE #Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
)

INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')

SELECT * FROM #Customers

CustNo YearNo IsCust
---- ---- --
999 2006 Y
999 2005 Y
999 2004 Y
999 2003 N
999 2002 N
999 2001 Y
999 2000 Y

In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
2, etc. Ideally I'd feed it a single year to lookup

I'm resisting the urge to create cursor here -- anyone have any hints?

...Chris.ChrisD wrote:

> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001
> for 2, etc. Ideally I'd feed it a single year to lookup

This works in Postgres, you'll have to change the "limit 1" to mssql TOP 1
syntax. Also note the hardcoded year on line 4, replace that with a
parameter.

Ironically, this only works if you specify the year. Without the year you
get spurious rows.

select a.yearno,b.yearno,(a.yearno - b.yearno) + 1 as "years"
from customers a join customers b on a.custno = b.custno
where a.yearno > b.yearno
AND a.yearno = 2006
AND a.isCust = 'Y' and b.isCust = 'Y'
and not exists
(
select yearno
FROM customers x
WHERE x.custno = a.custno
AND x.yearno between b.yearno AND a.yearno
AND x.isCust = 'N'
)
order by b.yearno
limit 1

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
> SELECT * FROM #Customers
8<-- Obvious result omitted
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Just two hints ...|||how about this:

select top 1 max(a.yearno)+1 as from_ ,b.yearno as to_ , b.yearno -(
max(a.yearno)+1) as consecutive_time from #customers a join #Customers
b on a.custno = b.custno and
a.iscust='N' and b.iscust='Y' and a.yearno < b.yearno
group by b.yearno
order by consecutive_time desc

i.e. get the max diff between an 'N' and the 'Y' after it|||create view cust as
select custno, yearno, isCust from Customers
union
select custno, min(yearno) - 1, 'N'
from Customers group by custno
go

select custno,yearno, iscust,
case iscust
when 'N' THEN 0
ELSE 1+(select count(*)
from cust a where a.custno = b.custno and
a.yearno < b.yearno and
(a.yearno >
(select max(yearno) from cust c where iscust = 'N' and yearno <
b.yearno and custno = b.custno))
) end as active_for
from cust b
where yearno >= (select min(yearno) from customers x where x.custno =
b.custno )
order by custno, yearno|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:Yfn2e.840846$Xk.593396@.pd7tw3no...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
> SELECT * FROM #Customers
> CustNo YearNo IsCust
> ---- ---- --
> 999 2006 Y
> 999 2005 Y
> 999 2004 Y
> 999 2003 N
> 999 2002 N
> 999 2001 Y
> 999 2000 Y
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

SELECT C.CustNo AS CustNo,
C.YearNo AS YearNo,
C.YearNo - MAX(FY.YearNo) + 1 AS YearTally
FROM #Customers AS C
INNER JOIN
(SELECT C1.CustNo, C1.YearNo
FROM #Customers AS C1
LEFT OUTER JOIN
#Customers AS C2
ON C1.CustNo = C2.CustNo AND
C2.YearNo = C1.YearNo - 1 AND
C2.IsCust = 'Y'
WHERE C1.IsCust = 'Y' AND C2.CustNo IS NULL) AS FY -- 1st year
ON FY.CustNo = C.CustNo AND
C.IsCust = 'Y' AND
FY.YearNo <= C.YearNo
GROUP BY C.CustNo, C.YearNo
ORDER BY CustNo, YearNo

--
JAG|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
8<----Big snip
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

As I said in my previous posting:
The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Chris wanted hints, not complete solutions.
The solutions offered are likely to fail (I didn't test this)
if there is an 'active' year without any 'inactive' predecessor.|||"Theo Peterbroers" <peterbroers@.floron.leidenuniv.nl> wrote in message
news:39bb2c10.0503300659.231f1c7c@.posting.google.c om...
> "ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> > I'm trying extract a count of consecutive numbers, or "unbroken" years in
> > this case, at any particular given time.
> > For example (simplified):
> > CREATE TABLE #Customers
> > (
> > CustNo INT,
> > YearNo INT,
> > IsCust CHAR(1)
> > )
> 8<----Big snip
> > In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> > 2, etc. Ideally I'd feed it a single year to lookup
> > I'm resisting the urge to create cursor here -- anyone have any hints?
> > ...Chris.
> As I said in my previous posting:
> The computation you want to perform is a subtraction.
> There are some caveats concernig your data.
> Chris wanted hints, not complete solutions.

I didn't take that as his literal intention. If it was, a quick glance
will reveal a solution, but probably not lead to comprehension,
and he can choose to ignore it.

> The solutions offered are likely to fail (I didn't test this)
> if there is an 'active' year without any 'inactive' predecessor.

His sample data includes an active year without an inactive
predecessor. As Chris was helpful enough to include DDL
and sample data, I assume all respondents who offered
complete solutions availed themselves of it. As far as I
can tell, my solution solves the problem.

--
JAG|||Yet another version, with a little-used predicate!

SELECT X.cust_nbr, MIN(X.start_date) AS start_date, X.end_date
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_year)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_date;|||Opps! fix my typos:

SELECT X.cust_nbr, MIN(X.start_year) AS start_date, X.end_year
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_year)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_year;|||ChrisD wrote:

> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1,
> 2001 for 2, etc. Ideally I'd feed it a single year to lookup

Thanks all for the nudges!

I was able to make this work using a combination of John's and Kenneth's
samples. Joe's works too.

In practice I will always have a previous year -- but I suppose it's a
always a good idea to check.

...Chris.

Count Attempts!

Hi all,
I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
and determine how many attempts (using COUNT) have IDENTICAL QUESTION LISTS
based on the ATTEMPT_RESULTS table?
IE:
Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
table using the DDL below.
Attempt 1 does not qaulify because it has an extra question 'id = 4'.
Based on the data below the result of this query should be 2.
I would appreciate any help in this, as i am not sure how to implement this
logic in SQL.
Thanks to those who responsd.
DDL:
CREATE TABLE [dbo].[attempts](
[attempt_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[attempt_results](
[attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
[attempt_id] [int] NULL,
[question_id] [int] NOT NULL,
CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)Hi Adam
Thanks for the DDL and example data. Maybe something like the following is
what you require:
SELECT r.[attempt_id]
FROM [dbo].[attempt_results] r
JOIN [dbo].[attempt_results] a ON a.[attempt_id] = 3
AND a.[attempt_id] <> r.[attempt_id]
AND a.[question_id] = r.[question_id]
GROUP BY r.[attempt_id]
HAVING count(*) = ( SELECT COUNT(question_id) as cnt
FROM [dbo].[attempt_results]
WHERE [attempt_id] = 3 )
John
"Adam Knight" wrote:

> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION LIST
S
> based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement thi
s
> logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>|||this should do:
e.g.
create function dbo.getlist(@.attempt_id int)
returns sysname
as
begin
declare @.s sysname
select @.s=isnull(@.s+'|','')+cast(question_id as sysname)
from attempt_results
where attempt_id=@.attempt_id
order by question_id
return @.s
end
go
select x.id,count(*) cnt
from(select max(attempt_id) id
from attempt_results) x join (select distinct attempt_id id
from attempt_results) y
on dbo.getlist(x.id)=dbo.getlist(y.id)
group by x.id
-oj
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:%23WIiFcYxFHA.700@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION
> LISTS based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement
> this logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>sql

Count ( Distinct Case ..) syntax error

I am getting a syntax error for the following piece of code:

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)

I don't understand what's wrong with it. I came across constructs like

Select Count(Distinct Case When ...Then ID End ) ...

and also

Select Distinct Count(Case When ...)...

What I want is the first one, a count of the distinct IDs.

What am I missing?

Magic:

I am not sure what you are getting; could you post your error message? When I run what follows it compiles OK and seems to run OK:

Code Snippet

select Count(Distinct Case When (StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)
and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52)
Then ProjectID end
) as distinctCount
from ( select 1 as startDate, 1 as ProjectStatusId, 1 as projectId) x

/*
distinctCount
-
0

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
*/

|||My code is below. I can't use the insert code feature because whenever I click on that it makes my whole post dissappear. I'm using Firefox.

The error is:
Incorrect syntax near 'distinct'.

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End) Over (Partition By DepartmentDetails.DepartmentName, ProjectCategory) [NewRequests]

From #tempResourceAllocation
Inner join dbo.DepartmentDetails
On (#tempResourceAllocation.ParentDepartmentID = DepartmentDetails.DepartmentID)

Order By ProjectCategory

|||

The problem seems to be when using "distinct" inside an aggregate function and the "over" clause. I haven't be able to find anything related to this in BOL.

AMB

|||

I fully agree with AMB; however, I was able to get my version to work using GROUP BY instead of OVER. Give GROUP BY a try instead. What I have looks like this:

Code Snippet

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)
as [NewRequests]
from ( select 1 as startDate,
1 as projectStatusId,
1 as departmentName,
1 as projectCategory,
1 as projectId
) as departmentDetails
group by DepartmentDetails.DepartmentName, ProjectCategory
order by ProjectCategory

/*
DepartmentName ProjectCategory NewRequests
-- --
1 1 0
*/

|||

I also see a potential problem with your date range. It looks to me like you have the same TO and FROM date if you are trying to get data from the previous month, change

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))

to

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate()), 0))

|||Yes, thanks, I was playing around getting some values from the previous one month intervals and when I switched it back I missed that.

Thanks for the input everyone.

I don't really know what to do since I was using Partition Over as another way to create subtotals based on the category but only for a subset of the columns in the table. I tried with Rollup but I couldn't get this functionality because it forces me to put all the columns in Group By and it messes up my layout giving me summary totals based on different criteria rather than solely on the Category field.

Even so Rollup doesn't work with Distinct aggregates which is a problem because I do have several entries with the same key of interest in my table just because in someother column I have distinct values for the same key and counting will include duplicates also.

|||

hi, did you try this?

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID else 0 End)

|||Whether it counts a 1 or a 0 isn't the result still one? As in one item that got counted?
|||

I think Tolga has a good point NULL does not help you towards a distinct count. Notice for this two-record select that that one of the entries is null. Also, note that the count is "1" and not "2":

Code Snippet

select count(distinct what) as theCount from (
select 1 as what union select null
) a

/*
theCount
--
1
*/

|||

yes you are right, result still one,

okey, try to sum,

sum(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then 1 else 0 End)

Could I use SQL "Select Case .. When..." in ADODB.Recordset().open

Is there any SQL Error?

Or I have to use Select case in VB code to control SQL instead.

Thank you for any ans.

NunoNuno (ranocha@.chula.com) writes:
> Is there any SQL Error?
> Or I have to use Select case in VB code to control SQL instead.
>
> Thank you for any ans.

I'm afraid that the question you have posted provides far too little
of information to be useful. Please post the code you are having problem
with.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
server. Could I try with the following code (I'm not sure that I can use
"SELECT CASE" sql with this or not)
'Dim objADORS_ As New ADODB.Recordset()
'objADORS_.Open("SELECT CASE
DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
sum[tblMiscellenous].[sat]" & _
' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
sum[tblMiscellenous].[sun]" & _
' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
sum[tblMiscellenous].[mon]" & _
' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
sum[tblMiscellenous].[tue]" & _
' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
sum[tblMiscellenous].[wed]" & _
' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
sum[tblMiscellenous].[thu]" & _
' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
sum[tblMiscellenous].[fri]" & _
' " ELSE 0 " & _
'" END as HrsUsed" & _
'" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
'" WHERE(tblHeader.HID = tblTimeSheet.HID and
tblHeader.HID=tblMiscellenous.HID) " & _
'" and ('" & to_date & "' between
[tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
'" and tblHeader.initial = '" & ini & "'" & _
'" GROUP BY [tblHeader].[start_Weekend]")

thank you for your kindness ans.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||A Rugchatjaroen (ranocha@.chula.com) writes:
> Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
> server. Could I try with the following code (I'm not sure that I can use
> "SELECT CASE" sql with this or not)
> 'Dim objADORS_ As New ADODB.Recordset()
> 'objADORS_.Open("SELECT CASE
> DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
> ' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
> sum[tblMiscellenous].[sat]" & _
> ' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
> sum[tblMiscellenous].[sun]" & _
> ' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
> sum[tblMiscellenous].[mon]" & _
> ' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
> sum[tblMiscellenous].[tue]" & _
> ' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
> sum[tblMiscellenous].[wed]" & _
> ' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
> sum[tblMiscellenous].[thu]" & _
> ' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
> sum[tblMiscellenous].[fri]" & _
> ' " ELSE 0 " & _
> ' " END as HrsUsed" & _
> '" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
> '" WHERE(tblHeader.HID = tblTimeSheet.HID and
> tblHeader.HID=tblMiscellenous.HID) " & _
> '" and ('" & to_date & "' between
> [tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
> '" and tblHeader.initial = '" & ini & "'" & _
> '" GROUP BY [tblHeader].[start_Weekend]")

SELECT CASE is OK, but it seems you have an error with to_date. It's
not quoted in the SQL string.

I would suggest that it is better to use a parameterized query instead,
because then you don't have to bother about nested quotes, and the
embedded SQL code becomes cleaner.

I'm only an occassional ADO programmer, so this syntax may not be
entirely correct, but would do something like:

Dim cmd AS new ADODB.Command
cmd.CommandText = _
" SELECT CASE DateDiff('d', h.[start_Weekend], ?)" & _
" WHEN 0 THEN SUM(ts.[sat]) + ? " & _
" WHEN 1 THEN SUM(ts.[sun]) + ? " & _
" WHEN 2 THEN SUM(ts.[mon]) + ? " & _
" WHEN 3 THEN SUM(ts.[tue]) + ? " & _
" WHEN 4 THEN SUM(ts.[wed]) + ? " & _
" WHEN 5 THEN SUM(ts.[thu]) + ? " & _
" WHEN 6 THEN SUM(ts.[fre]) + ? " &_
" ELSE 0 " & _
" END as HrsUsed" & _
" FROM tblHeader h, tblTimeSheet ts, tblMiscellenous m " & _
" WHERE h.HID = ts.HID " & _
" AND h.HID = m.HID " & _
" AND ? between th.[start_weekend] AND th.[end_weekend] " & _
" AND h.initial = ? " & _
" GROUP BY h.[start_Weekend]"
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adInteger,,, & _
sum[tblMiscellenous].[sat])
...
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adChar,, 1, ini)
rs.Open(cmd)

In the SQL I have also introduced aliases to make it less verbose.

I should add that the query looks a little funny, but since I don't
what result you are looking for, I cannot tell whether it returns
the desired result or not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

That's so cool. Thank you so much.... ^_^

Nuno