T-SQL (SS2K8) :: How To GROUP BY With Shortest Distance By Account Number

Mar 11, 2014

Given the following example;

declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),

How can I show the shortest Distance by AccountID and StoreID. Results would look like this;

AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300

View 7 Replies


T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
create table dim_lead
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: How To Divide Amount In Joint Account

Mar 6, 2014

I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07

If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.

select cast((35622.15/2) as decimal(15,2))

Is there any way i can achieve this.

View 4 Replies View Related

Help: Shortest Path In Sql

Jun 4, 2007

Hello to all,
help, help,...
i have with this problem since 3 weeks, until now i cann't resolve this problem. Maybe can somebody help me. I am hopeless.
i have a data table ValidRelationship, i will check if there is a relationship between two members by this table.
datas in the table ValidRelationship:
IDMember                 IDOtherMember           IDType










I will give two member and check their Relationship with a sql query. but it can be that this two member have no relationship. So i define here that man should search processor <= 6 . To better describe i use a example: max. Result of this query is: 1-2-3-4-5-6. If this is a relationship between 1-7 is 1-2-3-4-5-6-7, but i will give a answer that  this is no relationship between 1-7. because processor > 6.
But my problem is: this query executing is too slow. if i habe two member no relationship, the time of this complete sql query to execute is more than 1 minutes. Is my algorithm wrong, or where is the problem, why this executing is so slow? How can i quickly get the relationships between two member, if my algorithms is not right? The following Query is only to processor = 3, but it works too slowly, so i don't write remaining processors.
declare @IDM int;
declare @IDO int;
set @IDM = 3418;
set @IDO = 4270
select top 1 IDMember
from v_ValidRelationships
where IDMember = @IDM
and @IDO in (select a.IDOtherMember from v_ValidRelationships as a where a.IDMember = @IDM)
select top 1 a.IDMember, b.IDMember
from v_ValidRelationships as a, v_ValidRelationships as b
where a.IDMember = @IDM
and b.IDMember in (select c.IDOtherMember from v_ValidRelationships as c where c.IDMember = @IDM)
and @IDO in (select d.IDOtherMember from v_ValidRelationships as d where d.IDMember = b.IDMember )
select top 1 a.IDMember, b.IDMember, e.IDMember
from v_ValidRelationships as a, v_ValidRelationships as b, v_ValidRelationships as e
where a.IDMember = @IDM
and b.IDMember in (select c.IDOtherMember from v_ValidRelationships as c where c.IDMember = @IDM) and e.IDMember in (select f.IDOtherMember from v_ValidRelationships as f where f.IDMember = b.IDMember)
and @IDO in (select d.IDOtherMember from v_ValidRelationships as d where d.IDMembe = e.IDMember)
If someone has a idea, please help me. Thank a million
Best Regards

View 6 Replies View Related

Domain Group Account Won't Work

Sep 17, 2007


I am trying to configure Reporting Services to allow a domain group access to reports. I am able to configure the domain and group (mydomaingrpname) in both Report Manager and BIDS. I'm sure I entered the correct name because I purposely misspelled it and received an error. I think this tells me it is finding the group correctly.

However, when my test user goes to Report Manager, there are no folders displayed. I checked and he is in the domain group I am using. If I explicitly add him (mydomainandy) to the folders, he can see them and execute the reports.

After searching the forums and other websites, I have checked IIS is using Windows Integrated Security and not anonymous access.

Any ideas?


View 7 Replies View Related

Unable To Add Domain Group Account

Oct 24, 2007

Hi am
i am facing problem adding a domain group to the reporting services.
while setting the security of a report, i am getting the rsUnknownUserName error while adding a domain group.
the group is valid and it does exists. i tried creating a windows group on the machine running reporting services and tried adding the domain group and it accepted. but the reporting services is not accepting.
can somebody tell me whats the problem with this.
i am able to add other domain group belonging to the same domain and the SSRS accepts but not this particular domain group which is like any other domain group.

View 3 Replies View Related

Guest Account Enabled Via Group Membership

Jul 30, 2007


I have a situation (on SQL Server 2000 SP3a) where the guest account appears in the list of database users despite the account being removed via sp_dropuser.

The guest account appears in the list of users with Database Access set as 'Via Group Membership'

Once in this state it cannot be removed as sp_drop user will now report:

Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'guest' does not exist in the current database.

My conerns are:

(a) Does this imply any security risks ?
(b) How can I remove all reference to the guest account?

Any ideas or suggestions would be appreciated



View 7 Replies View Related

Reporting Services :: Space Between Every Account Number

May 8, 2015

How could I enter finger space between every account numbers ?

View 4 Replies View Related

How To Handle Invisible Characters On An Account Number

Dec 6, 2007

I am joining on table A AND Table B ON the accountnumber field. The accountnumber on the tblB has some invisble characters at the end, ie "12344 ". The Table A does not have these invisble chars and is "12344".
Due to the extra spaces at the end, the join fails and does not find a match for that acctno. I have used ltrim(rtrim) hoping that will elimante the extra spaces and find a match.
Heres my qry:

select * from Tbla a
inner join tblb B
on ltrim(rtrim(ACCTNO))=ltrim(rtrim(ACCT))

How do I do this, so that it finds a match and returns the acctno.

View 2 Replies View Related

Dijkstra's Shortest Path Algorithm

Jan 8, 2007

Here it is, the long lasted algorithm I promised.., -- delete previous map
exec dbo.uspdijkstrainitializemap

-- create a new map
exec dbo.uspdijkstraaddpath 'a', 'b', 4
exec dbo.uspdijkstraaddpath 'a', 'd', 1
exec dbo.uspdijkstraaddpath 'b', 'a', 74
exec dbo.uspdijkstraaddpath 'b', 'c', 2
exec dbo.uspdijkstraaddpath 'b', 'e', 12
exec dbo.uspdijkstraaddpath 'c', 'b', 12
exec dbo.uspdijkstraaddpath 'c', 'f', 74
exec dbo.uspdijkstraaddpath 'c', 'j', 12
exec dbo.uspdijkstraaddpath 'd', 'e', 32
exec dbo.uspdijkstraaddpath 'd', 'g', 22
exec dbo.uspdijkstraaddpath 'e', 'd', 66
exec dbo.uspdijkstraaddpath 'e', 'f', 76
exec dbo.uspdijkstraaddpath 'e', 'h', 33
exec dbo.uspdijkstraaddpath 'f', 'i', 11
exec dbo.uspdijkstraaddpath 'f', 'j', 21
exec dbo.uspdijkstraaddpath 'g', 'd', 12
exec dbo.uspdijkstraaddpath 'g', 'h', 10
exec dbo.uspdijkstraaddpath 'h', 'g', 2
exec dbo.uspdijkstraaddpath 'h', 'i', 72
exec dbo.uspdijkstraaddpath 'i', 'f', 31
exec dbo.uspdijkstraaddpath 'i', 'j', 7
exec dbo.uspdijkstraaddpath 'i', 'h', 18
exec dbo.uspdijkstraaddpath 'j', 'f', 8

-- resolve route
exec dbo.uspdijkstraresolve 'a', 'i'This is the outputFromToCost
ab 4
bc 6

Peter Larsson
Helsingborg, Sweden

View 20 Replies View Related

Error After Service Account Removed From SQLServer2005MSSQLUser Group

May 1, 2007

I am attempting to configure my SQL Server instance to use a service account with the minimum privileges. I thought I had everything configured correctly, when I realized that having the service account as a member of the "SQLServer2005MSSQLUser" Windows Group meant that the service account was now in the "sysadmin" fixed server role. This was not the configuration I wanted.

I went through the Books Online article "Setting Up Windows Service Accounts" and made sure the login had access to the appropriate folders used by SQL Server. Then I stopped the SQL Server service and tried to restart it, without success. These are the error messages:

Code Snippet

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

FCB::Open failed: Could not open file E:MSSQL$STAGINGDatamodel.mdf for file number 1. OS error: 5(Access is denied.).

TDSSNIClient initialization failed with error 0x5, status code 0x1.

TDSSNIClient initialization failed with error 0x5, status code 0x90.

I checked some other posts on this board, and they suggested the problem might be that the "VIA" protocol was enabled. I checked for this protocol in the Configuration Manager, and it is DISABLED in both the SQL Server 2005 Network Configuration and the SQL Native Client Configuration. What else could be causing this error?

The errors do not occur when I add the service account back to the "SQLServer2005MSSQLUser" Windows Group. The SQL Server service starts successfully when the account is back in this group.

Here are my answers to the questions posted at the top of this board:

What is the MS SQL version? - SQL Server 2005 SP2 (9.00.3054.00)

What is the SKU of MS SQL? - Enterprise Edition (SKU ID: 1804890536)

What is the SQL Server Protocol enabled? - TCPIP, Named Pipes

Does the server start successfully? - NO

If not what is the error messages in the SQL server ERRORLOG? - See above.

If SQL Server is a named instance, is the SQL browser enabled? - YES

What is the account that the SQL Server is running under? - Domain Account

Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not applicable, Windows Firewall is not used

Do you make firewall exception for SQL Browser UDP port 1434?
Not Applicable, Windows Firewall is not used

View 7 Replies View Related

Unable To Run SQL Agent With A Service Account Not In Admin Group

Jul 26, 2007

Am trying to run SQL Server Agent with a service account which is not in the Administrators group. Have done the following -
1. Removed the service account from the Administrators group on the machine
2. Assigned sysadmin privileges to the service account
3. Added it to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER role
4. Through SQL Configuration Manager assigned this account to the SQL Server Agent service
However, this does not start the Agent as a service. What is it that is missing?

View 4 Replies View Related

Write A Report That Includes Different Lab Values For Account Number Depending On The Test?

Dec 10, 2012

I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.

Here is an example of what I'm getting:

Acct Number MR # AdDateDDateCode CDateCTime Result Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0831110515 4.7 WBC
E00000000000 MR00000000 0824110902111012 0827110525 5.1 WBC
E00000000000 MR00000000 0824110902111012 0826110455 5.3 WBC
E00000000000 MR00000000 0824110902111012 0828110525 5.7 WBC
E00000000000 MR00000000 0824110902111012 0829110500 5.7 WBC
E00000000000 MR00000000 0824110902111012 0901110500 6.6 WBC
E00000000000 MR00000000 0824110902111012 0825110609 6.8 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB
E00000000000 MR00000000 0824110902111032 0831110515 10.2 HB
E00000000000 MR00000000 0824110902111032 0826110957 10.2 HB
E00000000000 MR00000000 0824110902111032 0827110525 10.4 HB
E00000000000 MR00000000 0824110902111032 0826110455 10.5 HB
E00000000000 MR00000000 0824110902111032 0901110500 10.7 HB

Her is what I need:

Acct Number MR # AdDateDDateCode CDateCTimeResult Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB

View 7 Replies View Related

SQL Server 2014 :: Count Duplicate Records Based Upon Account Number?

Jul 16, 2014

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
SELECT @Status = status_cd from status-table where status_id = 10

View 9 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Replica Ends Up With User Account As Owner

Sep 5, 2014

Setting up a test AlwaysOn Availability Group for one database.

However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.

Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.

I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.

View 2 Replies View Related

Whether To Use Local System Account Or Domain Account For Service Account

Jan 5, 2006

During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services.  I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.

View 6 Replies View Related

T-SQL (SS2K8) :: Group By Performance FK / PK

Jul 10, 2014

We have a medium sized database with the next tables:

- PA: 525000 records
- PR: 780000 records
- R: 1000 records
- B: 45 records





When I group by B.BOEKCODE the query lasts: 10 (or more when 'where' option is added) seconds
When I group by R.BOEKCODE the query lasts less than 2 seconds.

ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?

View 9 Replies View Related

T-SQL (SS2K8) :: Group By With Nulls?

Jun 12, 2015

Table Clients is master table with all records, joining against Assets table that may or may not have a matching entry.

Trying to sum an asset type against table and no matter what kind of join I do I cannot get SQL to return a NULL match against the Clients ID value. All I get back are matching rows.

Here's the SQL:

select c.cindex,SUM(a.value) AS 'Total Assets' from Clients c
"the join" Assets2012 a on a.clientid=c.cindex
where (c.ClientClass<=7 AND c.ClientClass<>6) AND a.assettype = 2
group by c.cindex

But no matter what type of join I do, left, right, left outer, inner, I am not getting back NULL values for client records that have no matching asset records.

Strange thing is, by removing the "and assettype=2" part I get the whole database back, with NULL's but not the range I'm looking for.

View 2 Replies View Related

T-SQL (SS2K8) :: Select MAX Per Group?

Jul 22, 2015

I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.

I am able to get one MAX row per Group but not all the Rows which has MAX value.

Here is my Table sample.



In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.

View 7 Replies View Related

T-SQL (SS2K8) :: List From 1 To Another Number?

Sep 12, 2015

@BoxesNeeded will change every time it runs. This method works.

declare @i as int,
@BoxesNeeded as int
select @i = 0,
@BoxesNeeded = 15

--drop table #temp

select @i as DDL
into #temp where 1=2
while @BoxesNeeded > @i begin
set @i = @i + 1
insert into #temp
select @i
select DDL
from #temp

View 3 Replies View Related

T-SQL (SS2K8) :: Group Columns To One Row In A Table

Apr 1, 2014

I have a table with the following columns

74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo

I am trying to get the result as follows.

Num Id Final Value

74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo

I tried to use Stuff and XMLpath but it is not giving me distinct results.

View 4 Replies View Related

T-SQL (SS2K8) :: MIN Across Multiple Columns With GROUP BY

Jan 13, 2015

How to get the lowest U.Price along with FX and Rate.


1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00

Required output.


1280 6.32 RM 1.00

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

T-SQL (SS2K8) :: Skipping Group With FOR XML Path

May 18, 2015

I have a table with a text field and a grouping number.

1, A
1, B
1, C
1, D
1, E

2, A
2, C
2, D
2, E

3, A
3, B
3, C
3, F

4, A
4, B
4, D
4, F
4, G

I assemble the text fields into strings by group, using the FOR XML PATH('') function, so that I end up with

1, A-B-C-D-E
2, A-C-D-E
3, A-B-C-F
4, A-B-D-F-G

This all works fine. I now have a requirement to leave out some groups, based on whether the GROUP does or does not contain a certain text. For instance, if I want only GROUPS with 'E', my result set should be

1, A-B-C-D-E
2, A-C-D-E

If I want only those GROUPS that DO NOT have 'E', my result set should be

3, A-B-C-F
4, A-B-D-F-G

I can (and have) put a condition on the assembled string, but it seems to me that it should be possible to make the cut earlier. That is, rather than assembling the string, scanning it with a Like '%E%' condition and discarding what doesn't meet my needs, I would like to stop scanning the incoming GROUP as soon as a mismatch is detected.

If I want all groups that will not contain an 'A', for instance, it is pointless for the query engine to read in 'A', 'B', 'C', 'D', 'E', put them all together into a string, compare it with a wildcard and throw it away. As soon as it sees the 'A', it has enough information to completely skip over the rest of that group. The abbreviated examples here are trivial, but the real code is not.

All I've been able to dream up so far results in skipping the RECORD that contains the 'A', but not the entire group.

View 9 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:




View 3 Replies View Related

T-SQL (SS2K8) :: Fill In The Gap Between 2 Number Range?

Apr 2, 2014

In my SQL Server database, I have table with the following records

counter, value1
12345, 10.1
12370, 10.5
12390, 9.7

Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.

For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.

1234510.1 --> Record 1
1237010.5 --> Record 2
123909.7 --> Record 3

Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.

why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.

View 9 Replies View Related

T-SQL (SS2K8) :: Dividing A Number With Remainders

Jan 29, 2015

SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...

Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...

Month1 = 71.62
Month2 = 71.61

Another example...Same amount but have to divide by 6

Month1 = 23.88
Month2 = 23.87
Month3 = 23.87
Month4 = 23.87
Month5 = 23.87
Month6 = 23.87

View 9 Replies View Related

T-SQL (SS2K8) :: Counting Number Of Occurrences?

Sep 23, 2015

I'm trying to figure out how to do the following:

Number of People receiving their second speeding ticket during this time frame

4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12

The table would contain historical data and look something like this

CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL

View 9 Replies View Related

T-SQL (SS2K8) :: Update Records Separated By Group

Jun 25, 2014

I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:

Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
Select 11, 2
Select 14, 3;

I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.

1 1 619 1

I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".


CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,

[Code] ......

View 3 Replies View Related

T-SQL (SS2K8) :: Group By And Order By - Location Starting With A And B

Jul 7, 2014

I am having below schema.

CREATE TABLE #Turnover (
location varchar(50),
Total int

insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)

Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.

View 5 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,


I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,


View 1 Replies View Related

T-SQL (SS2K8) :: Group Sum LEN On VARCHAR Column And Concatenate

Jul 3, 2015

I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?

View 1 Replies View Related

T-SQL (SS2K8) :: Formatting Date Of Birth Using RSA ID Number

Nov 8, 2012

I need creating date of birth using ID number the ouput that im looking is a follows

e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01

The desired format I need is to take the above and create date of birth with the below format as required by the application used.

01 Jan 1980
01 Jan 2000

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved