How I join in this case?

Hello
I would like to join Stats.ClientID with Clients.ClientID using an inner
join to get hold of Clients.Client
but I cant figure out how....... (thanks to Rebecca for providing help
earlier)
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
) vwResultsLike this perhaps:
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,C.ClientID
,Total AS Visitors
FROM
(...
... ) vwResults
JOIN Clients AS C
ON vwResults.client_id = C.client_id ;
David Portas
SQL Server MVP
--|||Try this:
SELECT
Client
,MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
Clients.Client,
CONVERT(DATETIME,CONVERT(CHAR(7),stat.Created,121) + '-01 23:59:59',121
) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats, Client
WHERE
Stats.ClientID = Clients.ClientID
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
, Clients.Client
) vwResults|||ahh thx...
doh, more coffee for me
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128499953.118747.188950@.g44g2000cwa.googlegroups.com...
> Like this perhaps:
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,C.ClientID
> ,Total AS Visitors
> FROM
> (...
> ... ) vwResults
> JOIN Clients AS C
> ON vwResults.client_id = C.client_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||If you want the end date to have the last part of the day,
Take off the time part from the convert, and take away 3ms from the EndDate.
This will give you the full scope of the month and not miss all but 3ms of
the StartDay.
SELECT
MonthAdded AS StartDate
,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
, Stats.ClientID
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hello
> I would like to join Stats.ClientID with Clients.ClientID using an inner
> join to get hold of Clients.Client
> but I cant figure out how....... (thanks to Rebecca for providing help
> earlier)
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 )
AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
> , Stats.ClientID
> ) vwResults
>|||Hi again Rebecca
I got to work now perfectly, but now I need to add another view.... that
shows for "each w" (and works even next year) :S
Tried some but it left a mess......
is it possible to do such grouping with some modifications?
so startdate is (for this w as an example)
2005-10-03 2005-10-09
TIA
/Lasse
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343a346$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want the end date to have the last part of the day,
> Take off the time part from the convert, and take away 3ms from the
EndDate.
> This will give you the full scope of the month and not miss all but 3ms of
> the StartDay.
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
> , Stats.ClientID
> ) vwResults
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
23:59:59',121 )
> AS
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You have been toldthis several times in this
newsgroup.
Instead of computing things like this, why don't you build a reporting
calendar table with date ranges? Instead of storing temporal data in
strings, why don't you use temproal data types? Oh, the standard
syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
data elements.|||easier with "testing-code" i guess
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-10-03 2005-10-09 A 5
2005-10-10 2005-10-16 A 4
2005-10-17 2005-10-23 B 3
or if above is too complicated/impossible:
Year (?) Month (?) Person Count
2005 40 A 5
2005 41 A 4
2005 40 B 3
*/
DROP TABLE #Test
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1128607316.078911.121310@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. You have been toldthis several times in this
> newsgroup.
> Instead of computing things like this, why don't you build a reporting
> calendar table with date ranges? Instead of storing temporal data in
> strings, why don't you use temproal data types? Oh, the standard
> syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
> short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
> data elements.
>|||I don't understand why your first desired result has two rows for the
same month. Going by your second example this seems to be a mistake. I
think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
datecreated). For the dates join to a Calendar table.
As posted your schema and data makes no sense. Why the duplicate rows?
You need a unique constraint on (person,datecreated).
David Portas
SQL Server MVP
--|||David,
since they are different ws. nothing to do with months
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128608758.392918.172520@.g49g2000cwa.googlegroups.com...
> I don't understand why your first desired result has two rows for the
> same month. Going by your second example this seems to be a mistake. I
> think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
> datecreated). For the dates join to a Calendar table.
> As posted your schema and data makes no sense. Why the duplicate rows?
> You need a unique constraint on (person,datecreated).
> --
> David Portas
> SQL Server MVP
> --
>