COUNT of datediff

Ok... it's stupid but I'm getting crazy...
SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
WHERE ORD_DTRSC > 0
how can I have the COUNT() of the days grouped by positive and
negative?
suppose:
DAYS
5
3
1
11
-2
-3
0
I'd like to get
POSITIVE NEGATIVE
5 2
...it should be not difficult... I know...
...but...
thnx in advanceWorst...
I must get something like:
PERIOD POSITIVE NEGATIVE
1st quarter 2004 3 0
2nd quarter 2004 1 1
3rd quarter 2004 0 0
4th quarter 2004 1 1
...
...
:(|||hi
CREATE TABLE #Test
(
col INT NOT NULL
)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (12)
INSERT INTO #Test VALUES (-1)
INSERT INTO #Test VALUES (-10)
SELECT COUNT(CASE WHEN col>=0 THEN 1 END ) AS Positive,
COUNT(CASE WHEN col<0 THEN 1 END ) AS Negative
FROM #Test
"ugom" <ugomangini@.tiscali.it> wrote in message
news:1132760283.256957.100830@.o13g2000cwo.googlegroups.com...
> Ok... it's stupid but I'm getting crazy...
> SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
> FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
> BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
> WHERE ORD_DTRSC > 0
> how can I have the COUNT() of the days grouped by positive and
> negative?
> suppose:
> DAYS
> 5
> 3
> 1
> 11
> -2
> -3
> 0
> I'd like to get
> POSITIVE NEGATIVE
> 5 2
> ...it should be not difficult... I know...
> ...but...
> thnx in advance
>|||Try
SELECT
COUNT(CASE WHEN DATEDIFF("D",ORD_DTRSC,BLT_DATA) > 0 Then 1 END) as
Positive,
COUNT(CASE WHEN DATEDIFF("D",ORD_DTRSC,BLT_DATA)< 0 Then 1 END) as
Negative
FROM YourTable
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ugom" <ugomangini@.tiscali.it> wrote in message
news:1132760283.256957.100830@.o13g2000cwo.googlegroups.com...
> Ok... it's stupid but I'm getting crazy...
> SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS
> FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
> BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
> WHERE ORD_DTRSC > 0
> how can I have the COUNT() of the days grouped by positive and
> negative?
> suppose:
> DAYS
> 5
> 3
> 1
> 11
> -2
> -3
> 0
> I'd like to get
> POSITIVE NEGATIVE
> 5 2
> ...it should be not difficult... I know...
> ...but...
> thnx in advance
>|||Wonderful!
...but, what about grouping by period...?
thnx again|||I've Found!
SELECT
MYYEAR, MYQUARTER,
COUNT(CASE WHEN query1.MYDAYS>=0 THEN 1 END ) AS Positive,
COUNT(CASE WHEN query1.MYDAYS<0 THEN 1 END ) AS Negative
FROM
(SELECT YEAR(BOLCLI.BLT_DATA) AS MYYEAR,
DATEPART("qq",BOLCLI.BLT_DATA) AS MYQUARTER,
DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS MYDAYS
FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND
BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG
WHERE ORD_DTRSC > 0) AS QUERY1
GROUP BY MYYEAR,MYQUARTER
thnx again to all of you!