Needed Help

I need to write a query which should calculate the Month and year part of
the Date that is stored in the database. Please note that the query also has
a group by column. Here Date is stored in table c. Please note that the quer
y
works fine and able able to retrieve the Month part. What am interested is
"Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number 6,
which I am getting now.
Apprecite your help.
Query is given below.
SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, MONTH(c.Date)Hi Scott
SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date)
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Scott" <scott@.gmail.com> wrote in message
news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
> I need to write a query which should calculate the Month and year part of
> the Date that is stored in the database. Please note that the query also
> has
> a group by column. Here Date is stored in table c. Please note that the
> query
> works fine and able able to retrieve the Month part. What am interested is
> "Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number
> 6,
> which I am getting now.
> Apprecite your help.
> Query is given below.
> SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, MONTH(c.Date)|||> GROUP BY a.Col1, dataname(month,c.Date)
oops!
Jan2005 and Jan2006 are same as dataname(month,c.Date) results same january
for both if col1 is same.
You need derived table to group by c.date
Regards
R.D
"GregO" wrote:

> Hi Scott
> SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date)
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Scott" <scott@.gmail.com> wrote in message
> news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
>
>|||Hi R.D.
Spot on mate I was a bit quick on that./ He was after Jan 2006 not just Jan
SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
[MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
convert(varchar(4),year(c.Date))
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
> oops!
> Jan2005 and Jan2006 are same as dataname(month,c.Date) results same
> january
> for both if col1 is same.
> You need derived table to group by c.date
> Regards
> R.D
>
> "GregO" wrote:
>|||GregO
what abt feb to august between 2005 and 2006. I did not mean jan. It was an
instance.
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>|||Hi R.D.
I must not be understanding you correctly. The query belwo will result in
group by Maonthname + Year so the months will not be the same. Hard to show
without the data but say the .Date col have
2005-01-01
2005-02-02
2006-01-01
2006-02-02
the results would be
January 2005
Febuary 2005
January 2006
Febuary 2006
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
> GregO
> what abt feb to august between 2005 and 2006. I did not mean jan. It was
> an
> instance.
> Regards
> R.D
> "GregO" wrote:
>|||Good
"GregO" wrote:

> Hi R.D.
> I must not be understanding you correctly. The query belwo will result in
> group by Maonthname + Year so the months will not be the same. Hard to sh
ow
> without the data but say the .Date col have
> 2005-01-01
> 2005-02-02
> 2006-01-01
> 2006-02-02
> the results would be
> January 2005
> Febuary 2005
> January 2006
> Febuary 2006
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
>
>|||Hi Chandra
Yes you can
declare @.myvar nvarchar(10)
declare @.myint bigint
select @.myvar = '9584'
select @.myint = cast(@.myvar as bigint)
print @.myint
--Result
9584
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>