Excluding Based On Specific Criteria
Jan 2, 2007
I am trying to accomplish the following.
There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.
The first and second table have one field in common - the Account number.(ACCT_NUM)
the exclusion is based in steps..
1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.
how do i do this... pls suggest
Thanks
View 1 Replies
ADVERTISEMENT
Feb 6, 2012
I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
Right now my SQL is this:
Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF
INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
WHERE PR04PTF.EmployeeID=441
View 3 Replies
View Related
May 7, 2008
Hi,
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
Thanks for any help.
View 1 Replies
View Related
Aug 25, 2005
Hi,
I was wondering if it is possible to order a recordset by specific values.
That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.
For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.
Can you do something like
Code:
SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)
...etc
Thanks
e
View 2 Replies
View Related
Feb 20, 2012
I have to find the number of orders sent to two different postal codes but to get all of the right columns, I have to use 2 different tables.
So, I used WHERE to connect the two tables with the primary keys, but where do I use the specific criteria for the exact postal codes?
View 1 Replies
View Related
Mar 5, 2007
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
RegionalID pbxID userID
0 1012 17
0 543 2
0 10961 6
0 16499 26
0 14061 36
0 15882 2
4 15101 6
4 15101 26
6 16499 2
6 16012 26
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup
838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554
5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554
12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554
10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554
13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554
19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554
19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554
15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249
3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249
32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc
79731 PRETORIA 0123620NORTH EASTERN REGION
79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION
79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION
79761 PRETORIA 01730140NORTH EASTERN REGION
79771 PRETORIA 01230150NORTH EASTERN REGION
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaIDRegionalIDDongleAreaCodeAreaDescUpdateStatus
121UAOumashoop0
131UBPietersburg0
141UCWarmbad01
151UDNylstroom0
161UEPotgietersrus0
271UFLouis Trichardt0
281UGMessina0
291UHEllisras0
301UIThabazimbi0
311UJPhalaborwa0
321UKTzaneen0
331UTStanderton0
341UMMeyerton0
351UNNelspruit0
361UOWitrivier0
371UPLydenburg0
381UQMiddelburg0
391URWitbank0
401USBronkhorstspruit0
461UZOlifantsfontein0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialupRegionUpdateStatusRegionCodeLocalRegion
10800003554North Eastern010
20800005027Gauteng020
30800006194Eastern030
40800004249Central040
50800201859Southern050
60800201989Western060
70800113515HO101
80800222204Tellumat070
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
View 7 Replies
View Related
May 31, 2008
Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
Code:
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
View 1 Replies
View Related
May 31, 2008
Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
View 2 Replies
View Related
Sep 23, 2013
I have to produce a report, but not sure of the best way to get the required results
Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]
E.g.
10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>
My query is
SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'
View 5 Replies
View Related
Sep 21, 2005
Currently I'm trying to add a column based on certain criteria based on the following data:
CallID GroupName CustomerPending
------ ----------- ------------
00500588FollowupN
00500588FollowupN
00500588Server N
00500588Service DeskN
00500588Service DeskN
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------
Any assistance appreciated.
View 4 Replies
View Related
Sep 13, 2006
Hi
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage
adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg
View 12 Replies
View Related
Jul 10, 2006
Hi,
I want to delete all files in a folder where creation date is an year older. I created a For Each Loop container to go over the whole folder and then execute the following Script Task
Dim FileArchive As Int16
Dim dteArchive As Date
Dim FilePath As String
FileArchive = CType(Dts.Variables("FileArchive").Value, Int16) ' One Year FileArchive = 365
dteArchive = DateAdd("d", -FileArchive, Now.Date)
FilePath = Dts.Variables("FilePath").Value.ToString
If File.GetCreationTime(FilePath) <= dteArchive Then
Try
File.Delete(FilePath)
Catch ex As Exception
'Do Nothing
End Try
End If
Dts.TaskResult = Dts.Results.Success
Is there a better approach to this? As most of the time job may have to delete 10 or 15 files but he has to read each file and then check the date if it matches the criteria and it delete.
View 1 Replies
View Related
Mar 21, 2006
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
View 1 Replies
View Related
May 9, 2007
***** SQL Server 2005 ********
I have a table that needs to be updated with a sequential number based on criteria.
I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.
SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8
This is what the table should look like after the update in complete.
SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1
Can anyone HELP me please?
View 4 Replies
View Related
Apr 17, 2014
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
[code]....
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'
View 9 Replies
View Related
Jan 31, 2014
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
View 1 Replies
View Related
Apr 25, 2008
Hello,
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
12-Jan
19-Jan
26-Jan
2-Feb
9-Feb
16-Feb
MTS
New Cases Received
85
84
79
98
79
95
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
44
29
26
35
55
54
CreditB
6
12
9
5
7
13
CreditS
-
-
-
-
3
-
CreditP
10
11
11
24
17
7
MTS Subtotal
140
125
110
144
151
150
BNB
New Cases Received
12
13
14
14
6
11
Sales
-
-
-
-
-
-
SalesD
-
-
-
-
-
-
CreditR
12
11
12
10
5
9
CreditB
8
13
9
17
16
6
CreditS
-
-
2
-
-
-
CreditP
1
1
1
1
4
3
BNB Subtotal
21
25
24
28
26
19
Total
New Cases Received
97
97
93
112
85
106
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
56
40
38
45
60
63
CreditB
14
25
18
22
23
19
CreditS
-
-
2
-
3
-
CreditP
11
12
12
25
21
10
Grand Total
161
150
134
172
177
169
This is just a very brief bit of code
SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.
FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...
Can you please show me how to accomplish this?
Thank you in advance for your effort,
Rhonda
View 2 Replies
View Related
Sep 7, 2005
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
View 2 Replies
View Related
Mar 11, 2014
I have an SSRS Report that I created. I'd like to set up a job that will run every hour, and what the job will do is send an SSRS report to a select group of recipients when a certain criteria is met. How do I go about doing this? My SSRS knowledge is very limited.
Additional Info:I created the report with a one time schedule to a certain email address. I do see the report schedule in the reportserver DB. Because I already have the schedule and the subscription id, I think all I have to do is call that subscription, but that's where I'm lost.
View 3 Replies
View Related
Apr 17, 2008
Hi! I'm new to SQL and have a question...
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress
If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If
RecordSet.Update
RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
View 5 Replies
View Related
Feb 11, 2015
I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.
create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.
View 2 Replies
View Related
Feb 14, 2008
Hi,
I would like to get some advises on how creating some kind of reports with Report builder
Lets say i have these two tables with these columns
Table Product
ProductName
Table ProductInstance
Product ID
CreationDate
CompletedDate
I would like to be able to create with report builder a report that looks this
Start Date : 2008/10/01 - End Date : 2008/10/30
Total Total Total
In Out OutStanding
ProductName1 10 0 3
ProductName2 5 5 2
ProductName3 8 8 5
ProductName4 12 5 6
Total In : total number of product created between StartDate and EndDate
Total Out : Total number of product completed between Start Date and End Date
Total OutStanding : Total number of product that have not yet been completed.
Any suggestion and advise on how to achieve this ?
View 2 Replies
View Related
Feb 13, 2008
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
View 11 Replies
View Related
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 Replies
View Related
Apr 30, 2015
We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.
Low Qty    High Qty    Fee       Actual Qty       Price
0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7500Â Â Â Â Â Â Â 0.025Â Â Â Â Â Â Â Â Â Â Â 7500Â Â Â Â Â Â Â Â Â Â 187.50
7501Â Â Â Â Â Â Â Â Â Â Â 15000Â Â Â Â Â Â Â 0.020Â Â Â Â Â Â Â Â Â Â Â 2221Â Â Â Â Â Â Â Â Â 44.42
Below is the table definition that we have to work with (ugghh).
CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,
[Code] ....
What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628
The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.Â
ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)
[Code] ....
View 9 Replies
View Related
Jan 11, 2008
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
View 3 Replies
View Related
Jul 20, 2007
Hi,
DONT KNOW IF IT IS POSSIBLE.. PLEASE SUGGEST.
Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.
Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.
Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.
Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.
The scenario which I am facing is given below with 2 columns as an example.
1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2
2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2
Scenario -1
---------------
tbl_Source Sample Data (after excel import to the temporary table)
------------------------
src_Col1 src_Col2
------------------
50 NULL
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 NULL
Scenario -2
---------------
tbl_Source Sample Data
------------------------
src_Col1 src_Col2
------------------
NULL 100
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 100
One update query which handles both scenarios.
Thanking you in anticipation.
Regards
View 4 Replies
View Related
May 26, 2007
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.
Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).
Any suggestions/comments would be appreciated. Thank you in advance.
View 1 Replies
View Related
Jun 28, 2012
I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:
Table - Vehicle_Event
Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3
[code].....
OUTPUT_Required:
Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
[Code] .....
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
View 6 Replies
View Related
Jun 22, 2015
I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.Â
SELECT
   IR.Id                     as 'ID'
 , CAST(IR.Priority as varchar)   as 'Priority'
 , IRSupportGroupDN.DisplayName   as 'Support Group'
 , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate)   as 'Created Date'
 , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate)   as 'Resolved Date'
 , SLOConfig.DisplayName         as 'SLO'
 , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate)   as 'SLO Target'
 , SLOStatusDN.DisplayName         as 'SLO Status'
 , SLOMetric.DisplayName         as 'SLO Metric'
 , SLOFact.UpdatedBatchId         as 'UpdatedBatchID'
View 11 Replies
View Related
Mar 16, 2007
Hi
I am very new to SQL so please excuse me if my question seems too easy to answer.
Basically I need to populate a form based with records based on the criteria that the next mot date and todays are +/- 10 days.
i.e if todays date is 13/05/07 and the next mot date is 3/05/07 or later OR 23/05/07 or less then various fields will be shown in the form.
Can you please help.
Thanks
Paul
View 2 Replies
View Related
Jan 22, 2008
Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
View 1 Replies
View Related
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
View Related
