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'),
('1','46','8.4325654'),('2','44','7.8754345'),('2','45','1.54654323'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),
('3','46','17.4325654'),('4','44','7.8754345'),('4','45','1.54654323'),
('4','46','11.5436543')
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
ADVERTISEMENT
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
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
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
3700
3726
10000
3726
3700
10000
3742
3672
10000
3672
3742
10000
3422
3548
10000
3548
3422
10000
3548
3717
10000
3717
3548
10000
3675
3695
10000
3695
3675
10000
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
Shasha
View 6 Replies
View Related
Sep 17, 2007
Greetings,
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?
Rob
View 7 Replies
View Related
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
Jul 30, 2007
Hi
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
Regards
Nadreck
View 7 Replies
View Related
May 8, 2015
How could I enter finger space between every account numbers ?
View 4 Replies
View Related
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
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
cj18
jf26
fi37
Peter Larsson
Helsingborg, Sweden
View 20 Replies
View Related
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
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
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
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)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 9 Replies
View Related
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
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
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
PA: PK = PAARDCODE
PR: PK = PAARDREGISTERCODE
PR: FK = PAARDCODE
PR: FK = REGISTERCODE
R: PK = REGISTERCODE
R: FK = BOEKCODE
B: PK = BOEKCODE
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.
SELECT B.BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
FROM PA
INNER JOIN PR ON PA.PAARDCODE = PR.PAARDCODE
INNER JOIN R ON R.REGISTERCODE = PR.REGISTERCODE
INNER JOIN B ON R.BOEKCODE = B.BOEKCODE
GROUP BY BOEKOMSCHRIJVING, B.BOEKCODE -- or R.BOEKCODE
ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?
View 9 Replies
View Related
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
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.
CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT
[code]....
In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.
View 7 Replies
View Related
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
end
select DDL
from #temp
View 3 Replies
View Related
Apr 1, 2014
I have a table with the following columns
Num,ID,Pos,Value
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
Jan 13, 2015
How to get the lowest U.Price along with FX and Rate.
ID-U.Price-FX-Rate
1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00
Required output.
ID-U.Price-FX-Rate
1280 6.32 RM 1.00
View 4 Replies
View Related
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
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
Aug 3, 2015
I would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445
View 3 Replies
View Related
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
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
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
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...
143.23:
Month1 = 71.62
Month2 = 71.61
Another example...Same amount but have to divide by 6
143.23
Month1 = 23.88
Month2 = 23.87
Month3 = 23.87
Month4 = 23.87
Month5 = 23.87
Month6 = 23.87
View 9 Replies
View Related
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
) ON [PRIMARY]
GO
View 9 Replies
View Related
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
UNION ALL
Select 11, 2
UNION ALL
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.
InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450
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 STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Code] ......
View 3 Replies
View Related
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
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,
[code]....
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,
[code]....
View 1 Replies
View Related
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
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
