SQL group by issues - 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

General SQL Server Forums

New to SQL Server Programming

SQL group by issues

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2011-12-22 : 07:32:42

I am having some real trouble with this,('

') below is the code, I am try to get counts from two different tables then group them by date.

declare @fromdate as date
declare @todate as date
set @fromdate = CONVERT(date,GETDATE()-3,101)
set @todate = CONVERT(date,GETDATE(),101)

USE database
SELECT
CONVERT(date,created_date,101) as OrdDate,
col1= (SELECT COUNT(distinct cust_id)
FROM table1
WHERE created_date BETWEEN @fromdate and @todate),
col2= (SELECT COUNT(distinct cust_id)
FROM table1
WHERE [status] LIKE 'P%' and created_date BETWEEN @fromdate and @todate),
col3= (SELECT COUNT(distinct cust_id)
FROM table2
WHERE created_date BETWEEN @fromdate and @todate),
col4= (SELECT COUNT(distinct cust_id)
FROM table2
WHERE created_date between @fromdate and @todate and result_error like 'FAIL%')

FROM table1
WHERE created_date BETWEEN @fromdate and @todate

GROUP BY CONVERT(date,created_date,101)
ORDER BY CONVERT(date,created_date,101)

It is counting all of the days together, I have tried adding Group by to each expression, no go. Any suggestions?('

')

OrdDate col1 col2 col3 col4
2011-12-19 5000 4000 3000 2000
2011-12-20 5000 4000 3000 2000
2011-12-21 5000 4000 3000 2000

What I want is something like this

OrdDate col1 col2 col3 col4
2011-12-19 1500 1500 500 750
2011-12-20 2500 750 1000 1000
2011-12-21 1000 1750 1500 250

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-12-22 : 08:14:09

[code]
DECLARE @fromdate AS date = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP - 3), 0)
,@todate AS date = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0);

WITH T1Counts
AS
(
SELECT DATEADD(day, DATEDIFF(day, 0, created_date), 0) AS OrdDate
,COUNT(DISTINCT cust_id) As Col1
,COUNT(DISTINCT CASE WHEN [status] LIKE 'P%' THEN cust_id END) AS Col2
FROM table1
WHERE created_date BETWEEN @fromdate and @todate
GROUP BY DATEADD(day, DATEDIFF(day, 0, created_date), 0)
)
, T2Counts
AS
(
SELECT DATEADD(day, DATEDIFF(day, 0, created_date), 0) AS OrdDate
,COUNT(DISTINCT cust_id) As Col3
,COUNT(DISTINCT CASE WHEN result_error like 'FAIL%' THEN cust_id END) AS Col4
FROM table2
WHERE created_date BETWEEN @fromdate and @todate
GROUP BY DATEADD(day, DATEDIFF(day, 0, created_date), 0)
)
SELECT COALESCE(T1.OrdDate, T2.OrdDate) AS OrdDate
,T1.Col1, T1.Col2, T2.Col3, T2.Col4
FROM T1Counts T1
FULL JOIN T2Counts T2
ON T1.OrdDate = T2.OrdDate
ORDER BY OrdDate;
[/code]

rcp
Starting Member

32 Posts

Posted - 2011-12-22 : 08:51:58

Absolutely perfect, thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources