I have a procedure that requires picking up the Rate based on Effective Date. This is what I have so far:
SELECT
SHPD.ProductID,
SHPD.ReceivedDate,
SHPD.Shipper,
SHIP.UnitRate
FROM tblShipmentDet SHPD
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper AND Max???(SHIP.Effectivedate) <= SHPD.ReceivedDate
Because there can be more than 1 Shipper record, I would somehow need to pickup the Maximum EffectiveDate in each case. I realize I cannot use the Max aggregate in the JOIN. Not sure where to go from here. On the Mainframe I used a LOOKUP function that would return the correct EffectiveDate. Help would be appreciated.
I need to pickup a tax rate, that is stored on a 1 record file. I would like to avoid using the CROSS JOIN. Is there a way to SELECT the record and set a Variable = to the tax rate so I can pickup the rate in another SELECT statement on each record?
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status( ID varchar(50), Status varchar(50), EffectiveStartDate datetime, EffectiveEndDate datetime, Is_Current bit
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great. Example…. pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.
Can someone help me? USE [Impact_PROD] GO /****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]
AS BEGIN SET NOCOUNT ON
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME SET @STARTTIME = GetDate()
PRINT 'SP started on: ' + CAST(@StartTIME as varchar) PRINT ''
IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL DROP TABLE #pcsiData IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL DROP TABLE #HoldKey
IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL DROP TABLE #HoldKey2 SET DATEFORMAT mdy;
SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO #pcsiData FROM pcsi p WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh' ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC
--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000 --(IMPACT XSQL process uses that date in place of null!) UPDATE #pcsiData SET pcsi_trm1 = null WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'
SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount INTO #holdkey FROM #pcsiData GROUP BY pcsi_id1 + pcsi_id2 HAVING count(*) = 1
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1) DROP TABLE NonDuppcsiDataForMalPracticeReport
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport FROM #pcsiData WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)
DELETE FROM #pcsiData WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)
DROP TABLE #HoldKey
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup INTO #HoldKey2 FROM #pcsiData GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 HAVING count(*) > 1
SET NOCOUNT OFF DELETE #pcsiData FROM #pcsiData, #holdkey2 WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1 AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2
drop table #holdkey2
SET NOCOUNT ON
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1) DROP TABLE pcsiDataForMalPracticeReport
SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO pcsiDataForMalPracticeReport FROM #pcsidata p WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)
DECLARE cur CURSOR FAST_FORWARD FOR SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 FROM #pcsiData --group by pcsi_pkey Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC
OPEN Cur FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @lplID = @pcsi_id1 + @pcsi_id2 SET @LEff = @Eff SET @Ltrm = @Trm
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm SET @i = 2
DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging
WHILE @@FETCH_STATUS = 0 BEGIN --Begin While Loop
IF @pcsi_id1 + @pcsi_id2 = @lplID BEGIN --If current record is for the same provider location as the last then...
SET @Gap = DATEDIFF(day, @Ltrm, @Eff) IF @Gap > 2 BEGIN --If there is a gap greater than 1 day... PRINT '' PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! ' PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar) PRINT '' --IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2) --IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps) --BEGIN --Begin if effective date was not already updated --IF @Leff > @Eff UPDATE pcsiDataForMalPracticeReport SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2 --ELSE --UPDATE pcsiDataForMalPracticeReport --SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm --WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff) --END --End if effective date was not already updated END --End if there is a gap greater than 1 day END --End if the provider location is different than the last row
--Set current rows data in last rows variables...
SET @lplID = @pcsi_id1 + @pcsi_id2 SET @LEff = @Eff SET @Ltrm = @Trm
--Get next row of data
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm SET @i = @i + 1 --increment i PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)
END --End While Loop
INSERT INTO pcsiDataForMalPracticeReport SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 FROM NonDuppcsiDataForMalPracticeReport /*** UPDATE #pcsidata SET pcsi_trm1 = '20470101' WHERE pcsi_trm1 is null OR pcsi_trm1 = '' /** SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1) INTO #HoldKey2 FROM #pcsidata p group by p.pcsi_id1, p.pcsi_id2 ORDER BY p.pcsi_id1
UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R INNER JOIN #HoldKey I ON r.pcsi_id1 = I.pcsi_id1 AND r.pcsi_id2 = I.pcsi_id2 **/
Print '' Print '' Print '' Print 'SETTING MAX TERM VALUES NOW....(This may take a while)' Print '' Print '' UPDATE pcsiDataForMalPracticeReport set pcsi_trm1 = jp.MaxTrm FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2 --WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2 GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)
DECLARE @NotTermed int SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101') PRINT'' PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)
PRINT '' PRINT 'STEP TWO.......................' PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates' PRINT'' --This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date --if there are no current records with a termination date = NULL
TRUNCATE TABLE tmpMalPracticeEffectiveDates
SET NOCOUNT OFF
PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'
INSERT INTO tmpMalPracticeEffectiveDates (pcsi_id1, pcsi_id2, pcsi_eff1) SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1 FROM pcsi p WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2 WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2) ORDER BY pcsi_id1, pcsi_id2 ------------------------------ --Set temp bogus date to distinguis which records are current in --subsequent statement PRINT 'Setting bogus date to distinguish pcsi records that are not termed' UPDATE tmpMalPracticeEffectiveDates SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049' WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2) ------------------------------- PRINT 'Setting most recent term date for pcsi records that are not currently active' UPDATE tmpMalPracticeEffectiveDates SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2) WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL ------------------------------- PRINT 'Setting bogus dates back to NULL' UPDATE tmpMalPracticeEffectiveDates SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL WHERE pcsi_trm1 = '12/21/2049' ------------------------------- --CORRECT EFFECTIVE DATES WITH GAPS... PRINT 'Correcting Effective Dates for those records with gaps in credentialing records' UPDATE tmpMalPracticeEffectiveDates SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate FROM tmppcsiDatesWithGaps t WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1 AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2
----END OF SP---
DECLARE @Diff decimal SET @ENDTIME = getdate() PRINT '' PRINT '' DECLARE @GapCount int SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps) PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.' PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.' PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar) SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar) PRINT '' PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.' PRINT ' = ' + str(@Diff/60) + ' Minutes!'
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date) insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE ) SELECT 'chandu', GETDATE(), NULL union all SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem, PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row :
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())? WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think.
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
I have created a File System task which is contained in a Foreach Loop Container. I have .bak files that are populating a directory from a maintenance backup plan.
There is a point where I need to delete the .bak file's after I've zipped them all up.
How do I set the SourceVariable to read through the directory and pick up just the .bak file's in the directory to delete.
I have a Primary Key column that I would like to have formatted as follows: 'yyyymmdd[auto increment]' . I would also like the auto increment to restart at the beginning of a new month. Any idea's on how to achieve this? Thanks
I have three INT columns in a table that record the users birth year, month, and day.
BDAY_DAY (INT) BDAY_YEAR (INT) BDAY_MONTH (INT)
I'd like to include a function in my query that will return their Age in years based on these three columns.
I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.
Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int AS Begin Declare @Age As Int Set @Age = Year(@Today) - Year(@DOB) If Month(@Today) < Month(@DOB) Set @Age = @Age -1 If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB) Set @Age = @Age - 1 Return @AGE End
Usage (how do i pass the three columns into this function??)
SELECT Last_Name, First_Name, ssn, dob FROM Employee_Data e (nolock) WHERE Cust_Id = 'Customer1' and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21
Payment Order_Number|Card_Type|Card_#|Merchant 10001 Gift Card1234null 10001 Gift Card1235null 10001 Gift Card 1236null 10001 Credit Cardxxxxprd 10002 Credit Cardxxxxprd 10003 Credit Card xxxxprd 10004 Credit Card xxxx prod
I have to populate th below table to track last gift card used for each cust_number. 1. last gift card used for each customer, each order 2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order. 3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod 4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order.
Please help me with the sql. I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1.
SELECT RN_TEST_ID AS 'Test ID', MAX(RN_EXECUTION_DATE) AS 'Last Execution Date', MAX(RN_EXECUTION_TIME) AS 'Execution Time', RN_DURATION AS 'Run Duration' FROM RUN
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
I have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
I am having a table where i have the following columns where the date format is dd/mm/yyyy Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008 My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure? Please provide me with full stored procedure Thanx in advance
SELECT * FROM bookkeep RIGHT OUTER JOIN acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
