Find middle of bigint - SQL Server Forums

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

Find middle of bigint

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-09-30 : 06:53:22

I have a bigint field.

I current select and group using - left(Transid,8)

How would I select and group by the the values 9 in two long?

Thanks in advance.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-09-30 : 07:00:54

You cannot use the string function LEFT with datatype bigint as first parameter.
Can you give an example of the input and expected output (including data types) to make the question more clear?

Stan1978
Starting Member

31 Posts

Posted - 2011-09-30 : 07:19:52

Here goes I hope this is clearer. I think transid is a bigint. My table is like;

TransID------------------Amount
2011092205000000000-------20
2011092506000000000-------10
2011082104000000000-------15
2011082205000000000-------10

Currently I use left(TransID,6) to group by the first 6 e.g.

TransID--------Sum Amount
201109---------30
201108---------25

Instead of that I want to group by the 9th and 10th numbers;

TransID---Sum Amount
04--------15
05--------30
06--------10

Thank you

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 07:46:23

SELECT
SUBSTRING(CONVERT(VARCHAR(20),transid), 9,2) AS [TransID],
SUM(amount) AS [Sum Amount]
FROM #test
GROUP BY
SUBSTRING(CONVERT(VARCHAR(20),transid), 9,2)

Best Regards
Vadivel

http://vadivel.blogspot.com

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 07:46:50

Select
SUBSTRING(CONVERT(VARCHAR(20),transid), 9,2) AS [TransID],
SUM(amount) AS [Sum Amount]
FROM #test
GROUP BY
SUBSTRING(CONVERT(VARCHAR(20),transid), 9,2)

Best Regards
Vadivel

http://vadivel.blogspot.com

Stan1978
Starting Member

31 Posts

Posted - 2011-09-30 : 07:53:03

Great works perfectly thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 07:57:50

why are you storing dates as int. thats main problem for all your issues. Please remember that not using proper datatypes results in unwanted casting operations and also affects manipulations done using field.
Had you used datetime to store above values you could have easily got value for grouping using datefunctions like DATEPART()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-30 : 08:01:58

Why change datatype?

DECLARE	@Sample TABLE
	(
		ObscureDataTypeUsage BIGINT,
		Data INT
	)

INSERT	@Sample
VALUES	(2011092205000000000, 20),
	(2011092506000000000, 10),
	(2011082104000000000, 15),
	(2011082205000000000, 10)

SELECT		(ObscureDataTypeUsage / 1000000000) % 100,
		SUM(Data)
FROM		@Sample
GROUP BY	(ObscureDataTypeUsage / 1000000000) % 100

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

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 09:08:10

We got to convert it into VARCHAR because the output he was expecting is having a leading zero (04, 05, 06). With INT or BIGINT column the leading zero is not possible to best of my knowledge.

Best Regards
Vadivel

http://vadivel.blogspot.com

Stan1978
Starting Member

31 Posts

Posted - 2011-09-30 : 10:54:49

Thanks for all the responses guys. I am afraid I have inherited the DB and have no access to change table structure, field type etc etc.

As you can tell I am a little rusty on SQL (not used it for 8 years) and have to find work around for problems I haven't encountered before.

Thanks for all the help making my job a little easier!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 10:56:04

quote:
Originally posted by vmvadivel

We got to convert it into VARCHAR because the output he was expecting is having a leading zero (04, 05, 06). With INT or BIGINT column the leading zero is not possible to best of my knowledge.

Best Regards
Vadivel

http://vadivel.blogspot.com

does that really matter here?
all op want is hour based count so i dont think there's a need to show it as 03,04 etc so 3,4 etc should also be fine. If output format is so particular also op can do it at front end. For that one reason alone, converting to varchar is not really necessary I feel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources