Need Help On this Query & Subquery

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

Need Help On this Query & Subquery

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2010-12-07 : 18:13:55

Hi all,

i am looking to retrieve data and compare current daily counts with the the count of the same day last ..i am writing this query but having no result...one thin is my dates are in epoch time and i have no issue to have them in human being format..I am getting the following error :

SELECT
(CONVERT(varchar(10), DATEADD(ss, CAST(t1.BEGIN_DATE AS int), CONVERT(DATETIME, '1970-01-01 00:00:00',102)), 101) ) As Current_Week_Date,
COUNT(DISTINCT t1.TID) AS Current_W_Count,

(
SELECT
COUNT(DISTINCT t2.TID)
FROM TABLE t2
WHERE
(
(CONVERT(varchar(10), DATEADD(ss, CAST(t2.BEGIN_DATE AS int), CONVERT(DATETIME, '1970-01-01 00:00:00',102)), 101) ) = (CONVERT(varchar(10), DATEADD(ss, CAST(t1.BEGIN_DATE - 604800 AS int), CONVERT(DATETIME, '1970-01-01 00:00:00',102)), 101) )
)
GROUP BY
(CONVERT(varchar(10), DATEADD(ss, CAST(t2.BEGIN_DATE AS int), CONVERT(DATETIME, '1970-01-01 00:00:00',102)), 101) )

) AS Last_Week_Count

FROM TABLE t1
GROUP BY
(CONVERT(varchar(10), DATEADD(ss, CAST(t1.BEGIN_DATE AS int), CONVERT(DATETIME, '1970-01-01 00:00:00',102)), 101) )

I AM GETTING THE FOLLOWING ERROR :

Column 'BEGIN_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

THANKS FOR THE HELP

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-08 : 02:11:37

you have use join in your sub query.
SELECT
COUNT(DISTINCT t2.TID)
FROM TABLE t2
left join TABLE T1
ON T1.TID=T2.TID.
if iam not wrong

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-08 : 03:33:55

Can you provide some sample data and expected output. It will help us in understanding your requirement.

korssane
Posting Yak Master

104 Posts

Posted - 2010-12-08 : 08:33:42

Hi and thanks for the answers..what i want is a result like this :

week# / Current_weekdate / Currrent_Week_Date_Count / Last_Week / Last_weekdate_sameday / Last_Week_Date_Count
45 / 12/08/2010 / 630 / 44 / 12/01/2010 / 610
...etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources