Select First Few Characters of VARCHAR

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select First Few Characters of VARCHAR

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 11:16:05

Hey friends.

I want to do a GROUPBY on a column called date. Whoever created this table used a VARCHAR to record the date, and not datetime or anything like that. So a date of today would just be '110408'
Does anyone know how i can group by month?? so group all of the 01**** together and 02**** together etc...
I really appreciate any responses and help. I am still quite new to SQL but love learning about it...teching myself however, so I certainly need some outside help sometimes. Thanks again!
-Skott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 11:28:40

Is 110408 "November 4th 2008?

If so, just GROUP BY CAST(Date AS INT) / 10000
Or simpler

GROUP BY LEFT(Date, 2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:32:00

[code]GROUP BY DATEADD(mm,DATEDIFF(mm,0,CONVERT(datetime,STUFF(STUFF(yourcol,3,0,'/'),6,0,'/'),1)),0)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 11:51:35

quote:

Originally posted by visakh16

GROUP BY DATEADD(mm,DATEDIFF(mm,0,CONVERT(datetime,STUFF(STUFF(yourcol,3,0,'/'),6,0,'/'),1)),0)

Busy today, Visakh?

GROUP BY LEFT(Date, 2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 12:01:06

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

GROUP BY DATEADD(mm,DATEDIFF(mm,0,CONVERT(datetime,STUFF(STUFF(yourcol,3,0,'/'),6,0,'/'),1)),0)

Busy today, Visakh?
GROUP BY LEFT(Date, 2)

E 12°55'05.63"
N 56°04'39.26"

Obviously

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 12:08:44

Hey Thanks a lot for the responses guys!
I have a question for each...Sorry.
Peso: when i do either of your options i get that i cannot select any of the columns bc not contained in group by or aggregate??
visakh16: umm...i am sorry i just dont understand your response, i am too new still! ha. I do not know what to put in for dateadd, datediff, STUFF or STUFF. if you can please claify what i need to replace with what, and what i leave alone i would love to try that!
Thanks again to both of you and your quick responses!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 12:10:15

Post your current query...

SELECT		LEFT(Date, 2) AS theMonth,
		COUNT(*) AS theCount
FROM		Table1
GROUP BY	LEFT(Date, 2)
ORDER BY	LEFT(Date, 2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 12:11:03

post you query please? Peso's suggestion is better. If you've difficulty in joining it to current query we will help you out.

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 12:15:11

Hm I will keey trying, i think i was doing it a little wrong. THanks guys!
Here is my query! - DatePurchased is the one i want to group by

SELECT "DatePurchased", "DealerNumber", "CompanyName", "ModelNumber"
FROM "BESRegistrations"
ORDER BY "DatePurchased", "DealerNumber", "ModelNumber"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 12:19:52

after grouping which values you want to retrieve for others? MIN or MAX?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 12:24:23

[code]
SELECT b.DatePurchased, b.DealerNumber, b.CompanyName, b.ModelNumber
FROM BESRegistrations b
INNER JOIN (SELECT DatePurchased,MIN(DealerNumber) AS Earliest
FROM BESRegistrations
GROUP BY DatePurchased)b1
ON b1.DatePurchased=b.DatePurchased
AND b1.Earliest=b.DealerNumber
ORDER BY b.DatePurchased, b.DealerNumber, b.ModelNumber

or

SELECT b.DatePurchased, b.DealerNumber, b.CompanyName, b.ModelNumber
FROM BESRegistrations b
INNER JOIN (SELECT DatePurchased,MAX(DealerNumber) AS Latest
FROM BESRegistrations
GROUP BY DatePurchased)b1
ON b1.DatePurchased=b.DatePurchased
AND b1.Latest=b.DealerNumber
ORDER BY b.DatePurchased, b.DealerNumber, b.ModelNumber

[/code]
Also dont put "" around column and table names

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 12:32:36

Yea the "" i dont usually put, it was the way i created the query from a report or something, but thanks still!
Thanks for those queries, they work...but i dont think it is what i need. Let me explain my full goals.
From the query i posted to you. I want the 'DealerNumber' and 'CompanyName' With with a GROUP and COUNT of Model number for each month. I hope that i clear..let me know what you think please.
And seriously, Thanks AGain!

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 12:40:01

So i Guess i want a final result to look something like this:
{code}
01(month) | Dealer Number | Company Name | ModelNumber
MS55
20 (count or qty)

MS290
50 (count or qty)

HS55
30 (count or qty)
01(month) | Dealer Number | Company Name | ModelNumber
MS55
20 (count or qty)

MS290
50

HS55
30
02(month) | Dealer Number | Company Name | ModelNumber
MS55
20

MS290
50

HS55
30
03(month) | Dealer Number | Company Name | ModelNumber
MS55
20

MS290
50

HS55
30
{/code}

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 12:59:55

Hey..this one has worked the best guys i used this query:
SELECT LEFT(DatePurchased, 2) AS theMonth,
COUNT(*) AS theCount
FROM BESRegistrations
GROUP BY LEFT(DatePurchased, 2)
ORDER BY LEFT(DatePurchased, 2)

and got these results:

theMonth theCount
01 57
02 97
03 188
04 686
05 2013
06 7270
07 17235
08 13479
09 18421
10 13553
11 200
12 54

That is definatley what i want, but now i want to break those results down so the 57 lines from 01 can be grouped by dealer, and then by modelnumber

so it will be 01, dealernumber & company name, model number
for each of the 12 months...
i hope that is clear.
Thanks again for all of the help you have provided!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-04 : 13:12:04

Is this what you looking for

SELECT left(DatePurchased,2)as DatePurchased,DealerNumber, CompanyName, Count(ModelNumber) as Count
FROM BESRegistrations
Group by left(DatePurchased,2),DealerNumber, CompanyName
order by left(DatePurchased,2),DealerNumber, CompanyName

besadmin
Posting Yak Master

116 Posts

Posted - 2008-11-04 : 13:17:44

Yup thats almost exactly how i just did it. WE GOT IT!
Thanks a ton to everyone who helped me and the quick responses. You are all very much appreciated.
Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources