Data To Get From 3 Different Tables Depending On Some Conditions
Nov 2, 2007
Hi,
I know it sounds very simple with the subject line but for me it's not.
BACK GROUND
My Client is using Crystal Reports 8.5 and SQL Server.
a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.
now there are 3 tables in Question
Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision
and the Fields in Question are Interstate_Revenue and International_Revenue.
Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History.
Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.
Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period.
they do it every month. But sometimes they need to generate Invoices for the OLD Periods.
if we put the problem in simple words,
they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle),
else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there
else
look into History Table and if the filer is found there for that cycle than get the data from there
here's the requirement given to me by my system Analyst.
User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID
Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle
Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID
Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle
For each USER_FILER_ID
If USER_CYCLE_ID=CURRENT_CYCLE
Pull revenue from BILLING_REVENUE /*Process stops here*/
ELSE
{
If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID
Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/
ELSE
IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID
Pull revenue information
}
here's the View being used which is the First Case which is happening currently
SELECT dbo.BILLING_PROVIDER.CONT_NAME_FIRST, dbo.BILLING_PROVIDER.CONT_NAME_LAST, dbo.BILLING_PROVIDER.ADD_STREET_LINE2,
dbo.BILLING_PROVIDER.ADD_STREET_LINE1, dbo.BILLING_PROVIDER.ADD_STREET_LINE3, dbo.BILLING_PROVIDER.ADD_CITY,
dbo.BILLING_PROVIDER.ADD_STATE, dbo.BILLING_PROVIDER.ADD_ZIP, dbo.BILLING_PROVIDER.CONTACT_TEL,
dbo.BILLING_SUMMARY.INVOICE_NUM, dbo.BILLING_SUMMARY.BALANCE_DUE, dbo.BILLING_SUMMARY.PREVIOUS_BALANCE,
dbo.BILLING_SUMMARY.SUM_TRANS, dbo.BILLING_SUMMARY.M_BASE, dbo.BILLING_SUMMARY.M_CONTRIBUTION,
dbo.BILLING_SUMMARY.ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.EST_Q_CONTRIBUTION,
dbo.BILLING_SUMMARY.PRE_DM_ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.Q_CONTRIBUTION_BASE,
dbo.BILLING_SUMMARY.Q_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.Q_LIRE_STATUS,
dbo.BILLING_SUMMARY.A_DM_STATUS, dbo.BILLING_SUMMARY.A_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_DM_STATUS,
dbo.BILLING_SUMMARY.A_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.A_LIRE_STATUS, dbo.BILLING_DETAILS.TRANSACTION_TYPE,
dbo.BILLING_DETAILS.LINE_ITEM_TYPE, dbo.BILLING_DETAILS.SUPPORT_MECHANISM_TYPE, dbo.BILLING_DETAILS.LINE_ITEM_NUMBER,
dbo.BILLING_DETAILS.LINE_ITEM_AMT, dbo.BILLING_DETAILS.TRANSACTION_DATE, dbo.BILLING_DETAILS.REASON_CODE,
dbo.BILLING_CYCLE.STATEMENT_DT, dbo.BILLING_CYCLE.PAYMENT_DUE_DT, dbo.BILLING_CYCLE.MAILING_DT,
dbo.BILLING_REVENUE.INTERSTATE_REVENUE, dbo.BILLING_REVENUE.INTERNATIONAL_REVENUE, dbo.BILLING_SUMMARY.CYCLE_ID,
dbo.BILLING_SUMMARY.FILER_ID, dbo.BILLING_PROVIDER.CARRIER_NAME, dbo.BILLING_CYCLE.PERIOD_ID,
dbo.BILLING_PERIOD.PERIOD_TYPE_ID, dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_DESCR, dbo.BILLING_PERIOD.FCC_PERCENTAGE_ACTUAL,
dbo.BILLING_PERIOD.FCC_PERCENTAGE_EST, dbo.BILLING_PERIOD.HC_PERCENTAGE, dbo.BILLING_PERIOD.SL_PERCENTAGE,
dbo.BILLING_PERIOD.RHC_PERCENTAGE, dbo.BILLING_PERIOD.LI_PERCENTAGE, dbo.BILLING_SUMMARY.U_CONTRIBUTION_BASE,
dbo.BILLING_PERIOD.FCC_CIRCULARITY_FACTOR, dbo.BILLING_SUMMARY.A_LIRE_REVENUE, dbo.BILLING_SUMMARY.ANNUAL_BASE,
dbo.BILLING_REVENUE.RECEIVED_DT
FROM dbo.ACCT_LINE_ITEM_TYPE RIGHT OUTER JOIN
dbo.BILLING_DETAILS RIGHT OUTER JOIN
dbo.BILLING_PERIOD INNER JOIN
dbo.BILLING_CYCLE INNER JOIN
dbo.BILLING_SUMMARY ON dbo.BILLING_CYCLE.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON
dbo.BILLING_PERIOD.PERIOD_ID = dbo.BILLING_CYCLE.PERIOD_ID ON dbo.BILLING_DETAILS.FILER_ID = dbo.BILLING_SUMMARY.FILER_ID AND
dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON
dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_TYPE = dbo.BILLING_DETAILS.LINE_ITEM_TYPE LEFT OUTER JOIN
dbo.BILLING_PROVIDER LEFT OUTER JOIN
dbo.BILLING_REVENUE ON dbo.BILLING_PROVIDER.FILER_ID = dbo.BILLING_REVENUE.FILER_ID ON
dbo.BILLING_SUMMARY.FILER_ID = dbo.BILLING_PROVIDER.FILER_ID
WHERE (dbo.BILLING_SUMMARY.FILER_ID NOT IN
(SELECT DISTINCT BILLING_DETAILS.FILER_ID
FROM dbo.BILLING_DETAILS
WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',
'RHCDC', 'RHCDADJ'))))
Current Situation.
1. They use CR 8.5 IDE to enter the parameters
and this version of CR doesn't allow using Stored PRocedures.
2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere.
But i really really need to do this
3. please give me some clue.. some idea how to resolve this.
here's the primary database diagram
http://deepak.palkar.googlepages.com/Billing2.jpg
thanks a lot..
Deepak
View 3 Replies
ADVERTISEMENT
Dec 7, 2006
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies
View Related
Jul 27, 2004
Hi there,
Is there a quick way to list all the tables in a DB that contain a certain column name?
Thanks
S
View 5 Replies
View Related
Mar 19, 2008
Hi,
I have a tbl called BANK.
This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.
I also have two other tables called Suppliers and SubContractors.
For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)
I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?
So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.
How would I best write that?
thanks for any help!
View 5 Replies
View Related
Jan 6, 2004
I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.
next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.
My code works as long as there isn't an existing record in the tables. Can someone give me a hand?
Here's what I have (and it doesn't work)
CREATE PROCEDURE b4b_sw_request
@FName as varchar(50)= NULL,
@LName as varchar(50)=NULL,
@Address1 as varchar(100) = NULL,
@Address2 as varchar(100) = NULL,
@City as varchar(50) = NULL,
@State as char(2) = NULL,
@Zip as char(5) = NULL,
@Email as varchar(100) = NULL,
@Send_Updates as smallint = '0'
AS
IF EXISTS
(SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @FName AND LName = @LName AND Zip = @Zip)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
Values
(@FName, @LName, @Address1, @Address2, @City, @State, @Zip, @Email)
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @@Identity)
BEGIN
RETURN
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@@Identity, @Send_Updates)
END
GO
View 2 Replies
View Related
Jun 13, 2004
The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!
CREATE PROCEDURE [insert_vwMusic]
(@Artist [nvarchar](50),
@Genre [nvarchar](50),
@NLink [nvarchar](50),
@Album[nvarchar](50),
@Song[nvarchar](50),
@ArtistID[nvarchar](50),
@AlbumID[nvarchar](50),
@SLink[nvarchar](50))
AS
DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)
IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Artist]
( [Artist],
[Genre],
[NLink])
VALUES
( @Artist,
@Genre,
@NLink)
SET @NewArtistID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
ELSE
BEGIN
IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
END
ELSE
BEGIN
SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
GO
View 5 Replies
View Related
Jan 10, 2008
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Thanks for whatever information you can provide.
View 7 Replies
View Related
Jul 20, 2005
Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper
View 3 Replies
View Related
Sep 10, 2007
Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
View 4 Replies
View Related
Jul 15, 2004
Hi there,
Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.
E.g. present query:
Insert Prices
select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C
where M.asset = 'money'
and C.zDate not in (select zdate from price where sid >=15)
So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)
Any ideas would be appreciated!!
Thanks
S
View 3 Replies
View Related
Feb 14, 2008
Hello,
I have a matrix report where I drill down from a business' divisional level to an office level. A subreport is generated by clicking on the relevant data field. However, when at divisional level, clicking on the data field simply generates the data from the first office row. I want it so that when the column is collapsed to divisional level, it shows the data for the entire division.
So... is there any way I can write an expression that creates different subreports based on whether a matrix column is expanded or collapsed?
Cheers,
Peter Marshall
View 4 Replies
View Related
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Sep 24, 2014
I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586
I want result on the basis of first 2 digit the repeatation of code is count as 1.
code1 code2 count
12 34 2
21 34 1
45 78 1
View 2 Replies
View Related
Nov 22, 2007
Hi,
I am attempting to explain my probelm again. Please read it:
I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType
For eg:
For Eg: ( Assume Call Detail id is changing for all the days)
Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls
Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls
Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee
Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.
So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because :
3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls
2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.
Here is what I wrote with the help of a mod over here:
Code Block
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA023', 12, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 1, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 2, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 1, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '12/5/2007 12:12:34 PM'
-- Query written with help of a helpful person here
UPDATE r
SET r.UniqueNo = dt.CallGroup
FROM @Request r
JOIN @Call c
ON r.CallID = c.CallID
JOIN (SELECT
CallDetailID,
EntryDt,EmpID,
CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt )
FROM @CallDetail
) dt
ON c.CallDetailID = dt.CallDetailID
select * from @Request
OUTPUT IS
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 2
Where as it should be
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 1
as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.
Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid.
Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )
thanks for any help.
View 4 Replies
View Related
Dec 21, 2006
Hi:
I have a data flow task in which i have multiple OLEDB sources going to a one OLEDB destination via UNION component. I also have a derived column component sitting behind the union, because there are some columns, which exist in the destination table, but are not coming from source, but I am responsible of providing values for those fields. The question is that some of those values are conditional, for eg. Lets say we have a column called RecStatusCode in the derived column component. The value for that column depends on a condition/criteria, so I need to query a db and based on the value of another field, I set the value of the recstatus code. How do i accomplish that? I am thinking expressions, but I need clear direction in steps in how to set a value of a derived column based on a criteria(sql statement). Thanks.
MA
View 10 Replies
View Related
Feb 11, 2008
I have a requirement wherein PDF files are being rendered from an .rdl (report definition language), through the use of a SSRS scheduler automatically. The generated PDF file is further emailed to a mailing list, through the same scheduler. However, there are situations where the PDF is generated as an empty file (under certain specified circumstances) [through the automatic scheduler run]. In this situation, it is required not to email the PDF at all.
I would appreciate an input which lets me know how to prevent the generation of the PDF file, when there are no records in the dataset that binds to the .rdl.Alternatively, is there any indicator via which the scheduler can be alerted NOT to pick up files 0 KB in size?
View 3 Replies
View Related
Aug 14, 2015
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Jul 13, 2007
Please give me advise ครับ
View 1 Replies
View Related
Dec 9, 2007
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
View 1 Replies
View Related
Oct 8, 2007
Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)
What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:
SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));
But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.
I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy
I really appreciate your help
View 8 Replies
View Related
Feb 25, 2008
hello
sorry to distub you again
in fact i'm a beginner and i don't really see how i can do some tasks
when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client
so i have 3 datasets
one in which i get all my data needed
and i have a filter in where part of query
Code Snippet
where client=@client
and mission=@mission
and i filter on these 2 values
in the second dataset , i gather all the clients i have by doing this query
Code Snippet
select distinct name from client
and i have this third dataset where i gather all the missions corresponding to one client
and here i write
Code Snippet
select mission_label from client
where name=@client
the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value
but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values
how can i do this please?
thanks a lot in advance
and sorry or bothering with such questions
View 4 Replies
View Related
Apr 30, 2004
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.
IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable
View 1 Replies
View Related
Apr 23, 2008
Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT
At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View 13 Replies
View Related
Jun 4, 2008
I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.
Is it possible in TSQL?
View 13 Replies
View Related
Oct 5, 2007
Anybody knows how to apply an inflation factor depending on the date to a measure
The Inflation Factor is (Current Month Rate / X Month Rate)
So lets say for current year the rates are
Jan 121.64
Feb 121.98
March 122.244
April 122.171
For example
If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)
But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)
Im using SSAS 2005
Thanks in advance
Isaac
View 1 Replies
View Related
Jul 23, 2005
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
View 7 Replies
View Related
Dec 3, 2007
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
UPDATE t1
SET t1.name = t2.name,
t1.age = t2.age
FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Any ideas?
TIA,
View 7 Replies
View Related
Apr 14, 2008
Is it possible to merge cells within several columns depending on the row number?
e.g.
colA colB colC colD
1 x a b c
2 y -------------------------
3 z -------------------------
for row # 2, #3, colB colC colD are merged.
View 7 Replies
View Related
May 20, 2006
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example
DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)
SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount
What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3
Thanks for the help
@puy
View 5 Replies
View Related
Sep 26, 2007
Hi all,
I have a table with artikels and count, sample:
Art Count
------------
12A 3
54G 2
54A 4
I would like to query this table and for each 'count' retrieve one row:
query result:
Art Count
------------
12A 3
12A 3
12A 3
54G 2
54G 2
54A 4
54A 4
54A 4
54A 4
Is this possible?
Thanks, Perry
View 3 Replies
View Related
Apr 17, 2008
Hello All,
I have an Execute SQL Task and a SendMail in the control flow of my package.
If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.
But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.
How can I achieve this...
Thanks,
Kapadia Shalin P.
View 1 Replies
View Related
Apr 6, 2006
Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks
View 2 Replies
View Related