Conditional count help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional count help

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-10-03 : 10:58:07

Dear all,
Cound we do the Max between two date (date format: yyyy-mm) and Conditional count in SQL 2000. I have a table:

Effective Process Item A Item B
2007-08 2007-06 3 7
2007-08 2007-07 30 53mon
2007-08 2007-08 280 669
2007-08 2007-09 1 5
2007-09 2007-03 0 1
2007-09 2007-06 0 1
2007-09 2007-07 4 4
2007-09 2007-08 44 70
2007-09 2007-09 321 753
2007-09 2007-10 10 5
2007-10 2007-09 3 2
2007-10 2007-10 6 11

If we can do, we will have one column named "Final date" (is the Max between Effective and Process)

Effective Process Item A Item B Final date
2007-08 2007-06 3 7 2007-08
2007-08 2007-07 30 53 2007-08
2007-08 2007-08 280 669 2007-08
2007-08 2007-09 1 5 2007-09
2007-09 2007-03 0 1 2007-09
2007-09 2007-06 0 1 2007-09
2007-09 2007-07 4 4 2007-09
2007-09 2007-08 44 70 2007-09
2007-09 2007-09 321 753 2007-09
2007-09 2007-10 10 5 2007-10
2007-10 2007-09 3 2 2007-10
2007-10 2007-10 6 11 2007-10

Then, we will do the count for each item group by the final date:
Final date Item A Item B
2007-08 313 729
2007-09 370 834
2007-10 19 18

To me, this is a very complex count.

Thank you!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-03 : 11:05:47

is this what your after...?

select (case when effective >= process then effective else process end),
sum([item a]),
sum([item b])
from YourTable
group by (case when effective >= process then effective else process end)

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 11:07:38

[code]Select Final_Date, Sum(Item_A) Item_A, Sum(Item_B) Item_B
From
(Select
(Case when Effective > Process then Effective else Process end) as Final_Date,Item_A, Item_B
From Table
) as t
Group by Final_Date[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

webforyou
Starting Member

15 Posts

Posted - 2007-10-03 : 11:39:38

The first table is the result of my pivot script. I must convert all date type to the varchar like 'yyyy-mm' because I want to group by month in each year.

Thanks to your solutions, I can not make comparison effective >= process because of varchar type. Could we re-convert to datetime to run your scripts. Or any suggestions?.

Kindly Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 11:45:09

Yes you can and in fact, you should not store dates in varchar column in first place.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 12:04:05

[code]-- Prepare sample data
DECLARE @Sample TABLE (Effective DATETIME, Process DATETIME, ItemA INT, ItemB INT)

INSERT @Sample
SELECT '20070801', '20070618', 3, 7 UNION ALL
SELECT '20070803', '20070709', 30, 53 UNION ALL
SELECT '20070805', '20070817', 280, 669 UNION ALL
SELECT '20070809', '20070906', 1, 5 UNION ALL
SELECT '20070922', '20070321', 0, 1 UNION ALL
SELECT '20070915', '20070601', 0, 1 UNION ALL
SELECT '20070930', '20070730', 4, 4 UNION ALL
SELECT '20070901', '20070830', 44, 70 UNION ALL
SELECT '20070916', '20070927', 321, 753 UNION ALL
SELECT '20070911', '20071011', 10, 5 UNION ALL
SELECT '20071003', '20070908', 3, 2 UNION ALL
SELECT '20071002', '20071004', 6, 11

-- Show the expected output
SELECT Final_Date,
SUM(ItemA) AS ItemA,
SUM(ItemB) AS ItemB
FROM (
SELECT CASE
WHEN Effective > Process THEN DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Effective), '19000101')
ELSE DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Process), '19000101')
END AS Final_Date,
ItemA,
ItemB
FROM @Sample
) AS d
GROUP BY Final_Date
ORDER BY Final_Date[/code]

E 12°55'05.25"
N 56°04'39.16"

webforyou
Starting Member

15 Posts

Posted - 2007-10-03 : 12:44:48

In fact, I should not store the varchar data, But if there are the table like that:

DECLARE @Sample TABLE (Effective varchar(50), Process varchar(50), ItemA INT, ItemB INT)

INSERT @Sample
SELECT '200708', '200706', 3, 7 UNION ALL
SELECT '200708', '200707', 30, 53 UNION ALL
SELECT '200708', '200708', 280, 669 UNION ALL
SELECT '200708', '200709', 1, 5 UNION ALL
SELECT '200709', '200703', 0, 1 UNION ALL
SELECT '200709', '200706', 0, 1 UNION ALL
SELECT '200709', '200707', 4, 4 UNION ALL
SELECT '200709', '200708', 44, 70 UNION ALL
SELECT '200709', '200709', 321, 753 UNION ALL
SELECT '200709', '200710', 10, 5 UNION ALL
SELECT '200710', '200709', 3, 2 UNION ALL
SELECT '200710', '200710', 6, 11

Could we make comparison?. I edit the script below but It doesn't work:
-- Show the expected output

SELECT Final_Date,
SUM(ItemA) AS ItemA,
SUM(ItemB) AS ItemB
FROM (
SELECT CASE
WHEN Effective + '01' > Process + '01' THEN DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Effective), '19000101')
ELSE DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Process), '19000101')
END AS Final_Date,
ItemA,
ItemB
FROM @Sample
) AS d
GROUP BY Final_Date
ORDER BY Final_Date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 12:48:40

[code]SELECT CAST(Final_Date + '01' AS SMALLDATETIME) AS Final_Date,
SUM(ItemA) AS ItemA,
SUM(ItemB) AS ItemB
FROM (
SELECT CASE
WHEN Effective > Process THEN Effective
ELSE Process
END AS Final_Date,
ItemA,
ItemB
FROM @Sample
) AS d
GROUP BY Final_Date
ORDER BY Final_Date[/code]

E 12°55'05.25"
N 56°04'39.16"

webforyou
Starting Member

15 Posts

Posted - 2007-10-03 : 12:57:34

Thanks to Peso, Elancaster and Harsh_athalye!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources