Saturday, March 9, 2013

[T-SQL] In Need of some expert query help

[T-SQL] In Need of some expert query help

In Need of some expert query help

Posted: 08 Mar 2013 10:52 AM PST

The below query I have been piecing together is really slow. I was wondering if any of you might be able to give me some ideas with regard to optimizing this. My goal is to create a view then leverage the view for an SSIS project. I'm thinking there MUST be a better solution. Thanks[code="sql"]SELECT 'MySource' AS SourceDB, 'MySite' AS 'SiteName', Rtrim(apibh.idvend) AS VendorNumber, Rtrim(apibh.idinvc) AS VoucherNumber, Sum(apibh.amtgrosdst) AS VoucherTotalDomestic, Sum(apibh.amtgrosdst) * ( apibh.exchratehc ) AS VoucherTotalUSD, (SELECT DISTINCT ( Sum(p.amtpaym) ) FROM aptcr r WITH(NOLOCK) INNER JOIN aptcp p ON r.btchtype = p.batchtype AND r.cntbtch = p.cntbtch AND r.cntentr = p.cntrmit INNER JOIN apibh H ON p.idvend = h.idvend AND p.idinvc = H.idinvc WHERE H.idinvc = apibh.idinvc GROUP BY H.idinvc) AS PayAmt, (Sum(apibh.amtgrosdst) - (SELECT DISTINCT ( Sum(p.amtpaym) ) FROM aptcr r WITH(NOLOCK) INNER JOIN aptcp p ON r.btchtype = p.batchtype AND r.cntbtch = p.cntbtch AND r.cntentr = p.cntrmit INNER JOIN apibh H ON p.idvend = h.idvend AND p.idinvc = H.idinvc WHERE H.idinvc = apibh.idinvc GROUP BY H.idinvc) ) AS OpenAmountDomestic, (Sum(apibh.amtgrosdst) - ((SELECT DISTINCT ( Sum(p.amtpaym) ) FROM aptcr r WITH(NOLOCK) INNER JOIN aptcp p ON r.btchtype = p.batchtype AND r.cntbtch = p.cntbtch AND r.cntentr = p.cntrmit INNER JOIN apibh H ON p.idvend = h.idvend AND p.idinvc = H.idinvc WHERE H.idinvc = apibh.idinvc GROUP BY H.idinvc) ) * ( apibh.exchratehc )) AS OpenAmountUSD, ( Select DATEDIFF(day,(SELECT DISTINCT ( Max(dbo.Udf_convert_int_date(r.datermit)) ) FROM aptcr r WITH(NOLOCK) INNER JOIN aptcp p ON r.btchtype = p.batchtype AND r.cntbtch = p.cntbtch AND r.cntentr = p.cntrmit INNER JOIN apibh H ON p.idvend = h.idvend AND p.idinvc = H.idinvc WHERE H.idinvc = apibh.idinvc GROUP BY H.idinvc),GETDATE()))AS DueDays, '' As AgeDays , (SELECT DISTINCT ( Max(dbo.Udf_convert_int_date(r.datermit)) ) FROM aptcr r WITH(NOLOCK) INNER JOIN aptcp p ON r.btchtype = p.batchtype AND r.cntbtch = p.cntbtch AND r.cntentr = p.cntrmit INNER JOIN apibh H ON p.idvend = h.idvend AND p.idinvc = H.idinvc WHERE H.idinvc = apibh.idinvc GROUP BY H.idinvc) AS PaidDate, dbo.Udf_convert_int_date(apibh.datebus) AS PostedDate, dbo.Udf_convert_int_date(apibh.datebus) AS AppliedDate, dbo.Udf_convert_int_date(apibh.dateinvc) AS AgingDate, dbo.Udf_convert_int_date(apibh.datedue) AS DueDate, dbo.Udf_convert_int_date(apibh.dateinvc) AS DocumentDate, NULL AS ReceivedDate, CASE WHEN ( apibh.datedisc ) = 0 THEN NULL ELSE dbo.Udf_convert_int_date(apibh.datedisc) END AS DiscountDate, apibh.codecurn AS CurrencyCode, apibh.exchratehc AS EffectiveExchangeRateFROM apibh AS APIBH WITH(NOLOCK)WHERE 1 = 1--AND APIBH.IDINVC = '22036'--AND APIBH.IDVEND = 'ABE129'GROUP BY ( apibh.idvend ), ( apibh.idinvc ), ( apibh.amtgrosdst ), ( apibh.amtgrosdst - Isnull(apibh.amtgrosdst, 0) - apibh.amtdiscavl ), ( ( apibh.amtgrosdst * apibh.exchratehc ) - Isnull(apibh.amtgrosdst, 0) * apibh.exchratehc ) - ( apibh.amtdiscavl * apibh.exchratehc ), ( apibh.datebus ), apibh.datebus, apibh.dateinvc, apibh.datedue, CASE WHEN ( apibh.datedisc ) = 0 THEN NULL ELSE dbo.Udf_convert_int_date(apibh.datedisc) END, apibh.amtdiscavl, apibh.codecurn, apibh.exchratehc, apibh.idtrx, Isnull(apibh.amtgrosdst, 0), apibh.amttotdist [/code]

Need guidance on how to Insert a new record between existing records

Posted: 08 Mar 2013 07:09 AM PST

Here is a the table structure:MemberNbr Varchar(11)MemberCardNumber Varchar(10)EffectiveDate IntTermDate IntSample data is:Membernbr MemberCardNumber EffectiveDate TermDate12345678909 A020129091 20120101 2012043012345678909 A020129091 20120501 2012063012345678909 A020129091 20120701 2012083112345678909 A020129091 20120901 0I receive an incoming file which indicates that this member updated his CardNumber toB020129091 on 20120516 and I need to update the table to reflect this change in all his recordsgoing forward from 20120516, overwriting, if necessary records which exist in the table and whichhave effective dates > than 20120516 while also maintaining the TermDates.Does anyone have some guidance on the SQL to do this?

Single value MIN and MAX dates from multiple rows

Posted: 08 Mar 2013 07:34 AM PST

This post has the code and an Excel Workbook with results and expected results (Red text)My query works for the desired results, except for the last two columns (they were added at the 11th hour).What I need is a query to capture is a single MIN and MAX date based from the values found for each PATIENT_ID. If you look at the excel sheet, you can see there are 7 records. I need MIN date from DATE_WRITTEN and MAX date from EXPIRES_ON. It's OK that they repeat, because the query is moving into Crystal Reports and Crystal is forgiving with duplicates. Here is the query[code="sql"]SELECT DISTINCT [Rxo].[SYS_ID] , [Rxo].[PATIENT_ID] , [Rxo].[DESCRIPTION] , [Rxo].[RX_NUMBER] , [JRxf].[MAX_FILL_NUM] , [Rxo].[PT_CASE_PHYSICIAN_SYS_ID] , CONVERT(VARCHAR(12) , [Rxo].[DATE_WRITTEN] , 110) AS DATE_WRITTEN , CONVERT(VARCHAR(12) , [Rxo].[EXPIRES] , 110) AS EXPIRES_ON , CONVERT(VARCHAR(12) , [Rx3].[MIN_START] , 110) AS MIN_START FROM [dbo].[RX_ORDER] AS Rxo LEFT JOIN ( SELECT MAX([Rxf].[REFILL_NUMBER]) AS [MAX_FILL_NUM] , [Rxf].[RX_ORDER_SYS_ID] FROM [dbo].[RX_FILL] AS Rxf GROUP BY [Rxf].[RX_ORDER_SYS_ID] ) JRxf ON [Rxo].[SYS_ID] = [JRxf].[RX_ORDER_SYS_ID] LEFT JOIN [dbo].[PATIENT] AS Pat ON [Rxo].[PATIENT_ID] = [Pat].[PATIENT_ID] LEFT JOIN [dbo].[PT_CASE] AS Ptc ON [Pat].[PATIENT_ID] = [Ptc].[PATIENT_ID] /*Not working*/ LEFT JOIN ( SELECT DISTINCT MIN([Rxo2].[DATE_WRITTEN]) AS MIN_START , [Rxo2].[PATIENT_ID] , [Rxo2].[RX_NUMBER] FROM [dbo].[RX_ORDER] AS Rxo2 GROUP BY [Rxo2].[PATIENT_ID] , [Rxo2].[RX_NUMBER] ) Rx3 ON [Rxo].[PATIENT_ID] = [Rx3].[PATIENT_ID] AND [Rxo].[RX_NUMBER] = [Rx3].[RX_NUMBER] WHERE [Ptc].[SITE_ID] = '0001' AND [Ptc].[CASE_STATUS_CODE] = 'A' AND [Rxo].[STATUS] = 'A' AND [Ptc].[PATIENT_ID] = 2000000000 ORDER BY [Rxo].[PATIENT_ID] , [Rxo].[RX_NUMBER][/code]

Need to find out the latest STATUS of each Consumers on a given date from their Activities

Posted: 08 Mar 2013 04:22 AM PST

Hi there,I've a ConsumerActivity table that records all the activities. Now, I need to find out the latest status for each Consumers on a given day. For example, I need to write a query that gives me ConsumerID and latest Status on '2012-04-01 00:00:00'.In this table, there is no activity on this date and as such I won't get any result. However, I want to get the result as: ConsumerID Status 101 ACTIVE 102 PREMIUMBelow is my simple scenario and query:USE [Sample]GO-- Create TableCREATE TABLE [dbo].[ConsumerActivity]( [ConsumerID] [varchar](10) NOT NULL, [ActivityDate] [datetime] NULL, [Status] [varchar](10) NULL) ON [PRIMARY]GO-- Insert Some Data into this tableGOINSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, Status)SELECT 101, '2012-01-10 00:00:00', 'INACTIVE'UNION ALLSELECT 101, '2012-05-20 00:00:00', 'ACTIVE'UNION ALLSELECT 102, '2012-02-10 00:00:00', 'BASE'UNION ALLSELECT 102, '2012-03-15 00:00:00', 'PREMIUM'GOThanks.

Strategy for n-level approvals

Posted: 08 Mar 2013 02:50 AM PST

I have a TimesheetMaster table which requires 1 or more "approvals". I've created an eventlog table to keep track of approval events. Now, if any approver rejects a timesheet, the approval process must begin again, but I don't want to lose track of all the events. If I didn't care about keeping track of all the events, I could just purge the approval events whenever there was a rejection event. What would your strategy be? One idea I had was to code every approval as "current", or "expired", so a rejection event would, instead of deleting all related events, would just mark them as "expired". My program would seek out only "current" approvals to see if the timesheet is finally approved. Other cool ideas? You guys always have the neatest tricks....

Query help.....

Posted: 08 Mar 2013 03:42 AM PST

Hi All,can any one help on below query declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)insert @tbl(ID,patientName,age,city,Mobile) select 1,'Ramesh',20,'HYD',12345678 union allselect 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union allselect 1,'Ramesh new',20,'HYDERABAD ' ,87654321select * from @tbl TBLID ID patientName age city Mobile1 1 Ramesh 20 HYD 123456782 1 Ramesh 24 HYD 123456783 1 Ramesh new 20 HYDERABAD 87654321i want output as mentioned below format which columns data got changed Columns OLDDATA NEWDATApatientName Ramesh Ramesh newCity HYD HYDERABAD Mobile 12345678 87654321can any one help on this

Update master from detail

Posted: 08 Mar 2013 03:13 AM PST

I have a timesheet master table and a related timesheet detail table. Each detail row could trigger need for an override approval of the whole timesheet. Currently, I have an ASP.Net application that checks for 1 or more detail rows needing override approval, and updates the master column "RequiresOV" (int). I'm thinking of setting a trigger on the detail table to do the update on the master automatically anytime a row requiring override permission is added, updated, or deleted. It would simply add up all the detail rows requiring Override, and update the master appropriately. Alternatively, I could make the column in the master a computed column that does roughly the same thing. Override rows are uncommon, say 5% of timesheets contain an override row. The average timesheet has maybe 300 rows, and there are maybe a dozen timesheets submitted per week. Some are submitted in bulk (uploaded from a workbook...) and some are hand-entered in an ASP.Net app, one row at a time.What do you guys think?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog