Combining Fields to create unique ID

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

Combining Fields to create unique ID

Author

Topic

sqlzepher
Starting Member

5 Posts

Posted - 2011-03-14 : 16:12:52

Hello,

I am trying to combine a client ID field and date field together to create a unique id. Since the date has to be converted I can get it to add with the Client field. Here is the code ...

Select
Cliente as AccountNumber,
Convert (varchar(10),Fecha,103) AS HistoricalDate,
Sum(Segundos)/60 As Minutes,
CONVERT(varchar(100), CAST(SUM(Total) AS decimal(38,4))) as Charges,
CONVERT(varchar(100), CAST(Sum(Coste) AS decimal(38,4))) as Cost,
Convert (varchar(10),Fecha,112) + Cliente AS AutoId

From
Historico
Where
Fecha between '01-01-2011' AND '03-01-2011'

Group By
Cliente,Convert (varchar(10),Fecha,103), Convert (varchar(10),Fecha,112)
Order By
Cliente

When I run it I always get an error message that the Fecha.Historico is not a function or in the Group By section.

The final should look this ....

AccountNumber HistoricalDate Minutes Charges Cost AutoId
10003 01/01/2011 75 12.1055 1.5202 1000320110101
10003 02/01/2011 17 1.6892 0.3392 1000320110102
10014 01/01/2011 0 0.0639 0.0069 1001420110101
10014 02/01/2011 0 0.0639 0.0069 1001420110102
10033 01/01/2011 9 0.5173 0.2980 1003320110101

Is this possible?

Any and all help is appreciated.

Sam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-14 : 16:40:24

Are you trying to group by ClientID and Date (without the time portion)?

If so, you shouldn't convert date values into strings. It just makes everything harder and it makes things harder to order. For exmaple, here is one way to remove the time portion from a datetime:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

More specific to your code:

...
Group By
    Cliente,
    DATEADD(DAY, DATEDIFF(DAY, 0, Fecha), 0)
Order By 
    Cliente

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-14 : 16:45:58

Ahh, I missed the last conversionin your select statement: Convert (varchar(10),Fecha,112) + Cliente

You need to add that to your GROUP BY clause. SO replace: Convert (varchar(10),Fecha,112)

with Convert (varchar(10),Fecha,112) + Cliente

Also, assuming that Cliente is a number, you might want to CAST it to the approprite type to combine with a date string.

sqlzepher
Starting Member

5 Posts

Posted - 2011-03-14 : 17:28:55

Hello Lamprey,

Thank you for your help. I have changed the code but I am not sure what you mean by CAST the Cliente field to the right type so that I can add it to the Date field.

My goal is to have a unique id like this ...

Cliente + Date = ClienteDate

10003 + 20110101 = 1000320110101

As I am new to SQL any help is greatly appreciated.

sqlzepher
Starting Member

5 Posts

Posted - 2011-03-14 : 17:38:26

Lamprey,

Got it!! Thank you for your help.

Here is the final code.

Select
Cliente as AccountNumber,
Convert (varchar(10),Fecha,103) AS HistoricalDate,
Sum(Segundos)/60 As Minutes,
CONVERT(varchar(100), CAST(SUM(Total) AS decimal(38,4))) as Charges,
CONVERT(varchar(100), CAST(Sum(Coste) AS decimal(38,4))) as Cost,
CAST (Cliente as varchar) + Convert (varchar(10),Fecha,112) AS AutoID

From
Historico
Where
Fecha between '01-01-2011' AND '03-01-2011'

Group By
Cliente,Convert (varchar(10),Fecha,103),Convert (varchar(10),Fecha,112),Cast (Cliente as varchar) + Convert (varchar(10),Fecha,112)
Order By
Cliente

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources