Transact SQL :: Combining Multiple Similar Queries
Nov 20, 2015
I have to run this 3 times for similar but slightly different parameters. I only change the very top peice of code slightly each time:
1. Partition by Forename, Surname, DOB, Postcode
2. Partition by Forename, DOB, Postcode
3. Forename, Surname, DOB.
As you can see very subtle change, ideally I'd like to find a way to run just one report where all of the above occur, the issue I face running separately is one person may appear on 1 or more giving duplicates.
USE HealthBI_Views;this bit below is basically grouping my output on Forename, Surname, DOB & Postcode. What the whole process is trying to achieve is to show where a patient which has all of the above fields identical but has a different patient identifier suggesting that the service has allocated to unique identifiers to the same person which will result in both records needing to be merged.
WITH cte
AS (SELECT *,
COUNT(HEYNo) OVER (
PARTITION BY Forename, Surname,
DOB
[code]...
--- this bit below is simply showing all instances where the above criteria is met and there are more then one instances.WHERE countOfHeyNo > 1
--- The final output display all patient identifiable information from the MF_PATIENT table so that the report can be created in SSRS and run routinely by the Data Quality Team who can then investigate each occurance.
View 5 Replies
ADVERTISEMENT
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
Apr 29, 2015
I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
View 4 Replies
View Related
May 20, 2015
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
[Code] ....
How I could combine the results?
View 9 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 Replies
View Related
Oct 19, 2015
As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.
I am looking to automate the process, like a single click to execute all the .SQL files.
The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.
My design for this requirement is, say each .SQL file need to contain a template like
@DBName = 'your Database name'
@Executeon = 'When to execute'
In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.
Is it feasible ? Or any other alternate easier way to proceed.
View 3 Replies
View Related
Feb 21, 2008
Hi All,
Is there any difference between the two queries given below..I am not able to find any but am not sure. Kindly help.
A)
select a.* from( select top 1 hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008' order by hs.last_modified desc)a
order by a.revision_date desc
B)
select hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008'
order by hs.last_modified desc, hs.revision_date desc
Thanks,
Pradeep.D
View 4 Replies
View Related
Sep 22, 2015
create table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
View 3 Replies
View Related
Nov 22, 2003
hello everyone
there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :
select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5
and the other query
select x1, x2 from Table1 where table1.anything = 5
is there anyway????
Thank you
View 2 Replies
View Related
Jan 23, 2006
I have a transactions table that stores prices for products bought and sold.
If I want average buying prices I use:
SELECT AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product
and for selling prices:
SELECT AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product
Is there a way to combine this into one SQL query, to create one bindable dataset ?
View 2 Replies
View Related
Apr 12, 2006
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
View 3 Replies
View Related
Jun 19, 2008
I need to combine two queries into one.
Query 1 (main query)
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours
fromdbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26(material)
1569.26 (production)
NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
Any clues? Thank you.
View 4 Replies
View Related
Dec 18, 2006
Hi All,I have a problem with a table that I want to get nice data out of in asingle query. The guys here reckon it can't be done in a single querybut I wanted to prove them wrong !! Essentially, I want to get the samecolumn out of the single table, but in one case the column must have awhere clause associated with it, and the other case it does not have awhere clause...Lets say have a table like this :-date || user || transaction type || Amountso, each row contains a transaction type, and a corresponding amountfor this transaction.There can be any number of transactions (and transaction types) peruser per day.Here is what I want :I want to get one particular transaction type as a percentage of thetotal transactions : for example, a list of the % amount that Debittransactions have occurred for a user for a day, with respect to alltransactions that the user has done that day, so :Debits Jim performed on day 1 are 50% of all transactions he performedDebits Jim performed on day 2 are 55% of all transactions he performed... and so on.At the moment, I do this :select date, user, sum(amount) as debit_Amount where transaction_type='debit'group by date, userand dump that into tmp table Debitsthen I doselect date, user, sum(amount) as total_Amountgroup by date, userand dump this into tmp table Totalsand then I have to do a :SELECT Debits.User, (Debits.debit_Amount / Totals.total_Amount) asperc_Debit , Debits.dateFROM Debits, TotalsWHERE Debits.date = Totals.date AND Debits.User = Totals.UserCan anyone suggest a way of doing this without the need for thesetemporary tables???Thanks!
View 7 Replies
View Related
May 13, 2008
can someone assist me in joining the two queries together into 1
so that I can get one result set
---------------------------------------------------------
SELECT count(ee_occup_levels) AS OcpLvl, ee_occup_levels AS [Occupation Levels], headcount
FROM headcountdec
WHERE period = 'March 2008' AND headcount ='NewHires' AND staffno IS NOT NULL AND ee_occup_levels is not null
GROUP BY ee_occup_levels, race, gender, headcount
ORDER BY gender, Race, ee_occup_levels, headcount
select count(headcount + headcount) AS Transfares
from headcountdec
where Period = 'March 2008' AND headcount IN ('TransferOut', 'TransferIn')
View 1 Replies
View Related
May 4, 2005
I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
View 1 Replies
View Related
Nov 21, 2007
Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...
SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantID, QuestionID, Answer FROM [3_Temp]
WHERE SurveyID=1) AS preData PIVOT
( MAX(Answer) FOR QuestionID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )
AS data
ORDER BY RespondantID
Thanks,
Martin
View 1 Replies
View Related
Apr 3, 2008
I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal.
The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.
I will post my feable attempt at merging them. No matter how I order the code, I continue to get the same error, pertaining to the last line of code ... Line 73: Incorrect syntax near 'vpi'.
Any help would be greatly appreciated! Thank you! Jena
select
RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL vpi.FirstName,''))) Employee,
convert(varchar,vpi.FromEffectiveDate,101) PositionStart,
convert(varchar,vpi.ToEffectiveDate,101) PositionChange,
convert(varchar,vpi.PositionStartDate,101) PositionStartDate,
datediff(hour,vpi.FromEffectiveDate,vpi.ToEffectiveDate) as time_diff,
vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescription
from
(
select [Age] = convert(varchar, [Years]) + ' Years ' +
convert(varchar, [Months]) + ' Months ' +
convert(varchar, [Days]) + ' Days',
*
from
(
select
[Years] = casewhen BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= casewhen BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= casewhen BirthDayThisMonth <= Today
then datediff(day, BirthDayThisMonth, Today)
else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
end,
Birth = convert(varchar(10) ,Birth, 121),
Today = convert(varchar(10), Today, 121)
from
(
selectBirthDayThisYear =
casewhenday(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth))
elsedateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)
end,
BirthDayThisMonth =
case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth))
elsedateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)
end,
*
from
(
selectBirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
CurrYearStart = dateadd(year, datediff(year, 0, Today), 0),
BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0),
*
from
(
select
birth = convert(datetime, fromeffectivedate) ,
Today = case when convert(datetime, toeffectivedate) = '3000-01-01'
THEN convert(datetime, convert(int,getdate()))
else toeffectivedate
end, *
from vHRL_PositionInfo
) aaaa
) aaa
) aa
)a
) vHRL_PositionInfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode
View 2 Replies
View Related
Mar 5, 2008
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
View 7 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialID SBOI OwnerBOI TimeOfEvent
1 A D 5
1 A D 2000
[code]....
View 8 Replies
View Related
Jun 9, 2015
How can I add a group number to the following query?
For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):
RN ReportDate Category Division TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1 2015-06-08 Field Sales CA BDM 299743154.3912
2 2015-06-07 Field Sales CA BDM 299765954.0354
3 2015-06-01 Field Sales CA BDM 297902654.4172
1 2015-06-08 Key Accounts Life Office 49954981.74
2 2015-06-07 Key Accounts Life Office 50016989.22
3 2015-06-01 Key Accounts Life Office 50169967.26
4 2015-05-31 Key Accounts Life Office 50169918.01
Becomes
GN RN ReportDate Category Division TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1 1 2015-06-08 Field Sales CA BDM 299743154.3912
1 2 2015-06-07 Field Sales CA BDM 299765954.0354
1 3 2015-06-01 Field Sales CA BDM 297902654.4172
2 1 2015-06-08 Key Accounts Life Office 49954981.74
2 2 2015-06-07 Key Accounts Life Office 50016989.22
2 3 2015-06-01 Key Accounts Life Office 50169967.26
2 4 2015-05-31 Key Accounts Life Office 50169918.01
i.e. each combination of Category+Division results in a new GN.
The query is:
selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN
View 2 Replies
View Related
Sep 23, 2015
I've unpivoted some data and stored it in a temp table variable
idNumFreqDtFreqrn
16100120120101M2
16100120120101M3
16100120100101M4
16100120100101M5
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
Using the above data, if two rows have the same FreqDt, I want to see the record with the lowest row number.
So it should look like the below
idNumFreqDtFreqrn
16100120120101M2
16100120100101M4
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
I've used the below code to accomplish it
SELECT DISTINCT
CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END,
CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END,
CASE WHEN t2.freq is null then t1.freq else t2.freq end
FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2
ON t1.idNum = T2.idNum
AND t1.FreqDt = t2.FreqDt
AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
idNumFreqDtFreq
16100120060101M
16100120000929Q
16100119990101A
16100119970101M
answer is below:
SELECT T1.*
FROM @t as t1 LEFT JOIN @t as t2
ON t1.idnum = T2.idnum
AND t1.freq = t2.freq
AND t1.rn = (t2.rn-1)
WHERE t2.idnum IS NULL
View 5 Replies
View Related
Jun 17, 2015
I have a table which contains single legs. E.g.
Row Leg-ID From To On DateFrom DateTo DOW
1 ABC123 AAA BBB CCC 01JAN15 01JAN15 1
2 ABC123 AAA BBB CCC 07JAN15 07JAN15 1
3 ABC123 AAA BBB CCC 14JAN15 14JAN15 1
4 ABC123 XXX YYY ZZZ 21JAN15 21JAN15 1
I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:
Row Leg-ID
From To
On DateFrom
DateTo DOW
1 ABC123
AAA BBB
CCC 01JAN15
14JAN15 1
3 ABC123
XXX YYY
ZZZ 14JAN15
14JAN15 1
The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:
check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..
View 4 Replies
View Related
May 16, 2008
Hi Guys
I have 4 databases in the same space.My users use all of them and use the same username and password to log into these 4 databases.In each of these databases,i have put a control table to allow me to keep track of all users that have to reset their passwords.
The control table consists of the username and flag fields.When the flag is ON(1) the user is forced to reset thier password and if the flag is OFF(0) they are not.
When a user logs into any one of these databases and they have to reset thier password,how do i make sure that all the other tables in other databases are also updated to make sure that the user is not forced to reset their password again when they log into those other databases later since they are using the same username and password for all databases.
I am planning to use a stored procedure which i will put into all the four databases and when a user logs in and has to reset their password,that sproc is called and automatically updates all the other 3 tables in other 3 databases.
Some SQL examples will be very appreciated.
Thanks.
View 5 Replies
View Related
Oct 23, 2001
How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).
Thank you,
Andrew
View 2 Replies
View Related
May 7, 2015
In a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.
Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'
I have been messing around with datepart and dateadd with no luck.
View 3 Replies
View Related
Apr 18, 2015
Would it be possible to handle multiple file systems (e.g. xlsx, csv, mdb) with different /similar headers in single package without Script Component?
View 1 Replies
View Related
Nov 17, 2015
My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE
[Code] ....
I would expect the groups to look something like below:
Is this where a recursive CTE may be used?
View 6 Replies
View Related
Mar 6, 2006
Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:
create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
View 5 Replies
View Related
Feb 13, 2007
I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you
View 8 Replies
View Related
Dec 19, 2014
I have data that looks like this:
Cus_no Inv_No Trans_type Amt_Inv Amt_Paid
100 12345 Other 0 -21.76
100 12345 Discount 0 -6.39
100 12345 Discount 6.39 6.39
100 12345 Discount 21.76 21.76
100 12345 Sales Inv 218.99 218.99
What I would like to do is a select statement that returns:
Cus_no Inv_no Amt_Inv Amt_Paid Disc_amt Other
100 12345 247.14 218.99 -6.39 -21.76
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt,
case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no
View 2 Replies
View Related
May 4, 2007
Hii all.
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
any help is appreciated.
Neil
View 7 Replies
View Related
May 16, 2008
Hi,
I'm trying to do this in T-SQL. I have a query that returns, for this matter, only 2 rows. The table output looks like this:
AdjType
AdjNbrStart
AdjNbrEnd
AdjTotalAmt
1
9
9
-134180
2
8
10
104981.42
How do you do it to bring back only one row that will look like so:
1
9
9
-134180
2
8
10
104981.4
Is it possible? Like I said, I only have 2 rows. I'm not concatenating these columns, just want to bring it back as one row. Any ideas? Thanks.
View 1 Replies
View Related
Apr 7, 2008
OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component. Each source returns 1 row of data with 1 column each except the Script Component. It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry. I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component? Please if anyone can help that would be wonderful.
View 10 Replies
View Related
