Needed Help

9 Sep 2005 2:00 AM

Scott

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)

9 Sep 2005 2:56 AM

GregO

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

Show quote

"Scott" <sc***@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)

9 Sep 2005 7:01 AM

R.D

> 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

Show quote

"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" <sc***@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)
>
>
>

9 Sep 2005 8:36 AM

GregO

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

Show quote

"R.D" <R*@discussions.microsoft.com> wrote in message
news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@microsoft.com...
>> 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" <sc***@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)
>>
>>
>>

9 Sep 2005 9:04 AM

R.D

GregO
what abt feb to august between 2005 and 2006. I did not mean jan. It was an
instance.
Regards
R.D

Show quote

"GregO" wrote:

> 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" <R*@discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@microsoft.com...
> >> 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" <sc***@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)
> >>
> >>
> >>
>
>
>

9 Sep 2005 9:42 AM

GregO

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

Show quote

"R.D" <R*@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:
>
>> 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" <R*@discussions.microsoft.com> wrote in message
>> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@microsoft.com...
>> >> 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" <sc***@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)
>> >>
>> >>
>> >>
>>
>>
>>

9 Sep 2005 11:10 AM

R.D

Good

Show quote

"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 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" <R*@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:
> >
> >> 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" <R*@discussions.microsoft.com> wrote in message
> >> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@microsoft.com...
> >> >> 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" <sc***@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)
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

9 Sep 2005 12:07 PM

R.D

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

Show quote

"GregO" wrote:

> 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" <R*@discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@microsoft.com...
> >> 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" <sc***@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)
> >>
> >>
> >>
>
>
>