Dynamic CASE Statement Based On List Of Dates
I have the following table of data. I need to take a date from a large table and do the following case:
CASE
When date < date(0)
Then '0'
When date between date(0) and date(1)
Then '1'
When date between date(1) and date(2)
Then '2'
When date >= date(3)
Then '3'
What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)
I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.
thanks
Milton
Dates Table
Date
4/1/2003
1/1/2006
4/2/2007
Fee Table
Date
Period
Class
Fee
1
Daily
True
329
1
Half Day
True
178
1
OT
True
49
1
Hourly
True
41
1
Daily
False
156
1
Half Day
False
86
1
OT
False
27
1
Hourly
False
19
2
Daily
True
355
2
Half Day
True
192
2
OT
True
50
2
Hourly
True
44
2
Daily
False
171
2
Half Day
False
92
2
OT
False
28
2
Hourly
False
21
3
Daily
True
364
3
Half Day
True
197
3
OT
True
51
3
Hourly
True
45
3
Daily
False
175
3
Half Day
False
94
3
OT
False
29
3
Hourly
False
21
View Complete Forum Thread with Replies
Related Forum Messages:
Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far: SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated. Thanks!
View Replies !
Need CASE Statement(s) To Populate 5 Fields Based On Values Of 2
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile. What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces): 1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E == == == == == == == == == == == == == == == == == == == == Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL. After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x. CASE HB.PLAN_TYPE WHEN '10' THEN 'Y' ELSE 'N' END AS 1A, CASE HB.PLAN_TYPE WHEN '10' THEN 'N' ELSE ' ' END AS 1B, ' ' AS 1C, CASE HB.PLAN_TYPE WHEN '10' THEN HB.BENEFIT_PLAN ELSE ' ' END AS 1D, CASE HB.PLAN_TYPE WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX' WHEN HB.COVRG_CD ='2' THEN 'YY' WHEN HB.COVRG_CD ='3' THEN 'ZZ' ELSE 'WW' END) ELSE ' ' END AS 1E, It works to an extent. While the desires/required output it above, the output this produces (same scenario) is: 1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E == == == == == == == == == == == == == == == == == == == == Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __ 1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E == == == == == == == == == == == == == == == == == == == == N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __ 1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E == == == == == == == == == == == == == == == == == == == == N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL. Any assistance in this matter would be greatly appreciated.
View Replies !
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly. So, is this possible using data driven subscriptions? Scenario is: 1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user. Any tips on how to get this working? Thanks Mark Smith
View Replies !
Problem Using Result From CASE In Another CASE Statement
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code. Previously, I had been duplicating the CASE logic for both columns, like so: Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view... This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise. This is what I'd like to do: Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE shipment_status WHEN 1 THEN 'Condition 1 text' WHEN 2 THEN 'Condition 2 text' WHEN 3 THEN 'Condition 3 text' WHEN 4 THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view... This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text. Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result? Thanks, Jason
View Replies !
List Parameterized Dates
Hello, For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter? Thanks!
View Replies !
Returning Results Based On Dates
I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this Select InformationDate From thisTAble Where If @dateValue IsNot @Null Informationdate = @dateValue Else Where In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go InfomationDate IsBettween @daterangeFrom @dateRangeTwo Any help on this i hope i was clear...thank you!
View Replies !
How Do Order The List By Date To Show For New Dates.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>" SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource> <asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1"> <HeaderTemplate> <ul> </HeaderTemplate> <ItemTemplate> <li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li> </ItemTemplate> <FooterTemplate> </ul> </FooterTemplate> </asp:Repeater> This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.
View Replies !
CREATING TABLE USING SQL BASED ON SEQUENTIAL DATES
I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Is There An Easier Way? Two Aggregate Fields From One Based On Dates
When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose. I have a table like this: Balance Date 1 200 2/14/2000 2 350 2/14/2000 3 32 2/14/2000 2 723 2/14/1998 3 354 2/14/1998 1 321 2/14/2000 2 673 2/14/1998 3 581 2/14/2000 2 574 2/14/1998 3 50 2/14/2000 1 10 2/14/2000 And essentially need this. Total Balance Before 1/1/2000 Total Balance After 1/1/2000 1 0 531 2 1970 350 3 354 663 Right now I'm splitting it into two temp tables and then joining them together. Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria. Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in. Utimately this will be used in a SPROC for a Multivalued SSRS report.
View Replies !
Between Dynamic Dates
Hello, To determine if the current date is between Monday 6AM and Friday 5PM you may want to consider the following T-SQL code. DECLARE @FromDay int, @ToDay int DECLARE @FromTime varchar(10), @ToTime varchar(10) DECLARE @Day int DECLARE @RangeFrom datetime, @RangeTo datetime SET @Day = DatePart (dw, getDate()) SET @FromDay = 2 SET @ToDay = 6 SET @FromTime = '06:00' SET @ToTime = '17:00' IF (@Day >= @FromDay) AND (@Day <= @ToDay) BEGIN PRINT 'Day ' + CAST(@Day AS varchar) PRINT 'FromDay ' + CAST(@FromDay as varchar) PRINT 'ToDay ' + cast(@ToDay as varchar) SET @RangeFrom = CONVERT(varchar, GetDate() - (@Day - @FromDay), 110) + ' ' + @FromTime SET @RangeTo = CONVERT(varchar, GetDate() + (@ToDay - @Day), 110) + ' ' + @ToTime PRINT @RangeFrom PRINT GetDate() PRINT @RangeTo IF (GetDate() >= @RangeFrom AND GetDate() <= @RangeTo) BEGIN PRINT 'Between Dates' END END
View Replies !
Populate Dates For Parameters Based On A String Parameter
Hello, My basic goal is to try to simplify inputs for the user. I have 3 parameters: Begin Date, End DAte and Duration. Duration will contain 3 choices: All, 2 Years and Range and is meant to give them a shortcut to dates as described below: All - Would automatically populate the start date to 10/01/2005 and an end date to current date 2 Years - Would automatically populate the start date to current date minus 2 years, and the end date to current date. Range - Would allow the user to select any dates as desired. I'm able to get the dates to populate based on the duration field using non-queried values based on the Duration value, but the problem is that if I want to allow them to select Range the calendar control is not available and a text box is displayed. I've tried to create some code in the properties that would populate, but I keep getting that this item is Read Only. The code I've created is as follows: public function populateDates(Duration) as String Select Case Duration Case = "Range" Report.Parameters!pBeginDate = Report.Parameters!pBeginDate Report.Parameters!pEndDAte = Report.Parameters!pEndDAte Case = "All" Report.Parameters!pBeginDate = #10/01/2005# Report.Parameters!pEndDAte = Now().Today case = "Two" Report.Parameters!pBeginDate = DateAdd("yyyy", -2, Now().Today) Report.Parameters!pEndDAte = Now().Today end select end sub My only goal is to give the User the 3 choices, but still keep the calendar control available, and I can't seem to do this? suggestion please! Thanks! Maureen
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Dynamic SQL - Compage Dates
Hi, I have a stored procedure that is all dynamic SQL... here is the portion that I have a question about: 1 -- Check for date 2 IF @DateSearch > 03 BEGIN4 IF @whereFlag = 'F' 5 BEGIN6 SET @strSQL = @strSQL + ' WHERE ' 7 SET @whereFlag = 'T' 8 END9 ELSE10 BEGIN11 SET @strSQL = @strSQL + ' AND ' 12 END13 14 SET @strSQL = @strSQL + ' (BL.ApprovedDate >= ' + @From + ') AND (BL.ApprovedDate <= ' + @To + ')' 15 END BL.ApprovedDate is a 'DateTime' format.@From and @To are also datetimeI'm getting the following error: "Syntax error converting datetime from character string."Any solutions? Thanks in advance.
View Replies !
Calculating Length Of Service Categories Based On Start Dates
Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :) Basically, I have the following table structure Employee(employee_number, continuous_start_date, ...) The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+). I can work out the length of service in months with the following code SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months' FROM employee So the first stage is to summarise the length of service into groups as mentioned above. Then the final stage is working out how many people are in each group! Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;) Thanks later, and in advance :p -GeorgeV
View Replies !
Calculating %ages Based On A List...
Hey all! First post-- I apologize for the newbness of the question, but I'm having an issue with a basic query... here goes: I want to calculate the percentages of statuses found from two tables. Let say T1 is my list of statuses: T1 Passed Failed Not Completed and T2 is the table where I derive my total # of statues for each id and how many in each status. T2 ID, Desc, Status , numtimesinstatus 1, Test1, Failed, 3 1, Test1, Passed, 1 3, Blah, Failed, 5 3, Blah, Not Completed, 1 What I'd like to do is get the percentages for each available status in T1, from T2-- I can do this; however, I'm having trouble in the cases where a particular ID hasn't been in one of the status (in other words, calculating a 0%). What I'd like to get from these two tables is: Result ID, Desc, Status, %age 1, Test1, Failed, 75.0 1, Test1, Passed, 25.0 1, Test1, Not Completed, 0.0 3, Blah, Failed, 83.3 3, Blah, Passed, 16.67 3, Blah, Not Completed, 0.0 Can anyone shed some light? Thanks!
View Replies !
Trying To Get - List Of Brokers With One Case Only In Last 9 Months
I have a problem where I need to return a list of Brokers that have had only one case and that case was in the last nine months, the script below was returning appropriate numbers until I amended it to add the date of that case (cdateReceived) and the related join. There were 470 rows returned before the addition of the cdateReceived and 1780 rows after. Ordinarily you would expect the dataset to grow with the addition of a join on a one-to-many realtionship but the criteria should be limiting the resuilts to be brokers with only one case!!! It should not then increase the number of rows. What am I mis-understanding? SELECT TOP 100 PERCENT dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker, dbo.brokerCompany.companyName, dbo.brokerCompany.cTown, dbo.brokerCompany.cPostCode, ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network, dbo.cases.cDateReceived FROM dbo.brokerDetails INNER JOIN dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID INNER JOIN dbo.cases ON dbo.brokerDetails.BrokerID = dbo.cases.BrokerID WHERE (dbo.brokerDetails.brokerID IN (SELECT TOP 100 PERCENT brokerID FROM dbo.cases WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0) GROUP BY brokerID ORDER BY brokerID ) ) AND (dbo.brokerDetails.brokerID IN (SELECT TOP 100 PERCENT brokerID FROM dbo.cases WHERE (spiked <> 1) AND (IsDirect = 0) GROUP BY brokerID HAVING count(caseID) =1 ORDER BY brokerID ) ) ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname
View Replies !
Trying To Get - List Of Brokers With One Case Only In Last 9 Months
I have a problem where I need to return a list of Brokers that have had only one case and that case was in the last nine months, the script below was returning appropriate numbers until I amended it to add the date of that case (cdateReceived) and the related join. There were 470 rows returned before the addition of the cdateReceived and 1780 rows after. Ordinarily you would expect the dataset to grow with the addition of a join on a one-to-many realtionship but the criteria should be limiting the resuilts to be brokers with only one case!!! It should not then increase the number of rows. What am I mis-understanding? SELECT TOP 100 PERCENT dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker, dbo.brokerCompany.companyName, dbo.brokerCompany.cTown, dbo.brokerCompany.cPostCode, ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network, dbo.cases.cDateReceived FROM dbo.brokerDetails INNER JOIN dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID INNER JOIN dbo.cases ON dbo.brokerDetails.BrokerID = dbo.cases.BrokerID WHERE (dbo.brokerDetails.brokerID IN (SELECT TOP 100 PERCENT brokerID FROM dbo.cases WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0) GROUP BY brokerID ORDER BY brokerID ) ) AND (dbo.brokerDetails.brokerID IN (SELECT TOP 100 PERCENT brokerID FROM dbo.cases WHERE (spiked <> 1) AND (IsDirect = 0) GROUP BY brokerID HAVING count(caseID) =1 ORDER BY brokerID ) ) ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname
View Replies !
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
Why Can't I Use Columns Based On Case Down In My Where Clause
I am writing a fairly simple sql, and I would like to write something like Code Snippet select firstname as firstname, case when firstname = 'Peter' then 'yes' else 'no' end as whatever from MyTable where whatever = 'yes' And this should then select out the rows where column number 2 is 'yes'. It doesn't work, and I have to copy the firstname = 'Peter' into the where clause. But why?
View Replies !
Dynamic Dates In Columns On Matrix Report?
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12. For example a report that would run on today's MM, yy (5/2007) would look something like this: MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08 (Col 1 - Col 12 separated by commas) How do I do this in a matrix report column?
View Replies !
Get Shopping List Based On Selected Recipes
Dear all, I am trying to create a recipe database and one of the funtions is to generate shopping list based on the selected recipes. at the moment, I have a table --recipe, a table--ingredient, and a cross over table between these two, reIng. Does anyone know how can I write a procedure with cursor that no matter how many recipes the user selected, a list of ingredients will be created based on that? Another things I can't solve is if an ingredient appears twice, how can I make them sum together.. I can write a simple procedure with one parameter so that I can get the ingredients for a certain recipe.( the parameter is the recipeID ). But I get struck when the possibility is that user will select more than one recipe and wish to get a list of the ingredients. should I use a cursor to do that? Or I should create a temporary table to store the selected recipe? THanks for your help!
View Replies !
Page 2 - Trying To Get - List Of Brokers With One Case Only In Last 9 Months
Well I agree with you about "Order By" having no value in the subquery, its a silly slip from clarity caused after ordering the subquery when it was run in isolation as an individual query to verify the dataset. However removing the ORDER BY from the subquery allows the removal of TOP from the subquery but not the highest level query as I need to keep some order in the madness. Quote: Originally Posted by r937 ORDER BY makes absolutely no sense in a subquery take it out and i'll bet EM doesn't stick TOP 100 PERCENT in there any more what it's trying to do is overcome the silliness of ORDER BY in a subquery with its own silliness kind of like acid/base, yin/yang, ...
View Replies !
Dynamic List
Is there a way to create a dynamic list? I have two variables I want to combine with two constants to create a list of 4 entries that I could run through a loop.
View Replies !
Selecting Only The List Of Columns From A Table Based On Input
Hi, i have a table like below table:- id col2 col3 col4 col5 col6 1 2 3 4 5 6 2 5 8 4 7 6 3 4 8 2 6 9 4 2 5 8 6 3 5 6 9 5 5 9 i want to write a stored procedure where i pass column names a parameters and i want to get result based on that For ex:- if i pass the parameters as col3 and col5 where id =1 then i should the result as id col3 col4 col5 1 3 4 5 and if i pass input as col2and col6 where id =3, the result should be id col2 col3 col4 col5 col6 3 4 8 2 6 9 can anyone help on this??
View Replies !
Problem With Group By When Using Case Statements In The Select List.
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice : Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Below is the my sql statement: SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item, ItemRef = Case when RH.Quantity < 0 then Sum(RH.Quantity) when RH.Quantity >= 0 then Sum(0) end FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16)) Group by dbo.DateOnly(TH.TransactionDate), RH.Item The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day. Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
View Replies !
Creating WHERE Clauses Based On IF (or CASE) STATEMENTS Transact-SQL
I am trying to create a stored procedure whose where clause isdependent on a parameter.If the parameter @myparam is null or '' then I want the where clause tobe one thing, else I want it to be a completely different thing. I cando it easily using iif but obviously that is not an option here...Here is what I have:Select @ FROM my table WHEREIF @myparam = '' or @myparam is null[MyField] = like '%'ELSEcharindex(@myparam,[MyField]) > 0)I have also tried variations of this and tried it using CASE. I keepgetting syntax errors. Do I have to write multiple SELECT statementsfor every possibility. If I can get this working the query willactually be a bit more complex, although it will follow this basicstructure...in other words, I'd rather not have to write multiplequeries when the only thing that is changing is the WHERE portion...Thanks!Ryan
View Replies !
Using CASE .. WHEN To Have 'dynamic' Sort
Hi,I'm trying to create a Stored Procedure that returns a recordset, but Iwant to be able to choose the ORDER BY clause in mijn parameter list ofthe Stored Procedure. Since CASE .. WHEN can only be used in the SELECTclause, I came up with the following:-- BEGIN SCRIPT --DECLARE @blah AS VARCHAR(20)SET @blah = 'DOSSIER_CODE'SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)NrOfNotes,CASE @blahWHEN 'DOSSIER_CODE'THEN DOSSIER_CODEWHEN 'SCAN_DATE'THEN SCAN_DATEELSESCAN_DATEEND AS ORDERFIELDFROM MR_DOCSLEFT OUTER JOIN MR_USERSON MR_DOCS.USER_FID = USER_PIDLEFT OUTER JOIN MR_DOSSIERSON DOSSIER_FID = DOSSIER_PIDLEFT OUTER JOIN MR_NOTESON DOC_PID = MR_NOTES.DOC_FIDWHERE MR_DOCS.USER_FID = 1AND DOC_STATE IN (1, 3, 4)AND REMINDER_DATE <= getdate()AND MR_DOCS.isVisible = 1AND TREE_FID IS NULL-- Added by Tim Derdelinckx - 2005.06.20AND TODO_FID IS NULL-- Select documents that are scanned for this user (1),-- or moved to this user (3),-- or forwarded to this user (4),GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODEUNIONSELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)NrOfNotes,CASE @blahWHEN 'DOSSIER_CODE'THEN DOSSIER_CODEWHEN 'SCAN_DATE'THEN SCAN_DATEELSESCAN_DATEEND AS ORDERFIELDFROM MR_DOCSLEFT OUTER JOIN MR_USERSON USER_FID = USER_PIDLEFT OUTER JOIN MR_DOSSIERSON DOSSIER_FID = DOSSIER_PIDLEFT OUTER JOIN MR_NOTESON DOC_PID = MR_NOTES.DOC_FIDWHERE BORROW_USER_FID = 1AND DOC_STATE = 5AND REMINDER_DATE <= getdate()AND MR_DOCS.isVisible = 1AND TREE_FID IS NULL-- Added by Tim Derdelinckx - 2005.06.20AND TODO_FID IS NULL-- or borrowed to this userGROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODEORDER BY ORDERFIELD DESC-- END SCRIPT --But it doesn't seem to work correctly:When SET @blah = 'SCAN_DATE', it works just fine!When SET @blah = 'DOSSIER_CODE':I get an error: Server: Msg 242, Level 16, State 3, Line 3The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.Warning: Null value is eliminated by an aggregate or other SEToperation.Anyone any ideas about this? Or maybe another way of handling this (notwith CASE .. WHEN)?Thanks a lot,Tim@Allgeier*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
Dynamic Where Clause With If..else Or Case
Hello all... I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select i.e if...@where = @where + ' llll ' if...@where = @where + ' llll ' select @statement = @statement + @where exec(@statement) I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement. Is it possible to use either statement inside a where clause?? i.e where if a = 1 then d=e else contains(.....) thanks
View Replies !
Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement. Using the examples given in Books Online returns compilation errors. See below. Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement? James -- SQL --------------- EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE author_cursor CURSOR FOR select_statement; EXEC SQL PREPARE select_statement FROM :szCommand; EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName; --Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
View Replies !
Move Files From Directory Based On Names On A Static List.
I have a scenario where I need to move a series of files from within a directory of many files. The files follow no nameing convention and are more of less random. However the file names never change from week to week. I tried various different options in a 'file system task', no go. Any ideas on how to move only a list of files? or can I load only specific files into a 'Foreach Loop container' from a certain directory. I tried delimiting file names in the file source, that did not work. Either way can work, Thanks
View Replies !
Set Up Permission Based On The Selection Made From The Drop-down (Parameter) List
I created a report with a school parameter for all our schools in SQL report. The school parameter gets it's value from a query. The report is generated based on the selection made by the user. If user choose school "A", report is generated for school "A" and if user choose school "B" report is generated for school "B". I am using a centralized user id and password for all users. A report URL (report link) is sent to user to access the report. Currently, from the school parameter (school drop-down list) users from any school may/can choose report for other schools other than theirs schoos. The content of the report is sensitive and we do not want anyone to see anyone else report. I want to create a user based security, so based on the user id for school "A", he/she will only be able to see school "A" report but nothing else. I was trying to accomplish this by creating linked report but it does not look realistic to create a folder for each school on the report server (which means about 180 or more folder for all our schools ). Does anyone else have better suggestions what can/should I do?
View Replies !
Building A Dynamic Mailing List
Okay, this has kept me busy for the past couple of days and just about had enough of it. What I'm trying to do is build Mailing Lists using logic instead of just assigning a contact to a mailing list. I've tried and tried but got no where as I think the solution is outside my knowledge scope of SQL. The reason why I need this is because my client would sometimes like to target a subset of all their contacts and it needs to be dynamic so new contacts can be automatically included/excluded. So here are the tables ... tblCountry CountryId CountryName ------------------------------------- 1 United Kingdom 2 United States of America 3 Canada 4 Austria tblReligion ReligionId ReligionName -------------------------------------- 1 Christian 2 Sikhism 3 Athiest tblContact ContactId ContactName CountryId ReligionId ------------------------------------------------------ 1 John Smith 1 NULL 2 Jane Roberts 1 1 3 Tim Williams 1 1 4 John Doe 2 2 5 Janice McBride 2 3 6 Eddie Lewis 3 NULL 7 Hans Murdoch 4 1 tblRecipientGroup RecipientGroupId RecipientGroupName -------------------------------------- 1 European Christians 2 All Athiests 3 No Christians tblRecipientItem RecipientItemId RecipientGroupId CountryID ReligionID IsExclude --------------------------------------------------------------------------- 1 1 1 NULL 0 2 1 4 NULL 0 3 1 NULL 1 0 4 2 NULL 3 0 5 2 NULL 1 1 Note: IsExclude detemines if the country is excluded instead of the default (included) ... and in brief, If I wanted to obtain all European Christians then 3 items are added to tblRecipientItem to represent the countries and religion to filter on. So the results would look like so ... tblContact ContactId ContactName CountryId ReligionId ------------------------------------------------------ 2 Jane Roberts 1 1 3 Tim Williams 1 1 7 Hans Murdoch 4 1 ... but bear in mind that that we may want to exclude filters like the No Chistians mailing list in tblRecipientGroup. How would I go about building a query for this?
View Replies !
Dynamic Join Based On Column Value??
Hi, I have three tables. The "Master table has a recordid, a masterID, a "IsSubField" and other stuff. I need to do a join to a second table based on the MasterID... However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B. Make sense? Anyone got any pointers? Thanks in advance James
View Replies !
Between Statement Used With Dates
I have a Client Side SQL Statement That Is asking For all Dates Between Two dates. Select * from table1 where Datefield1 between `1998-07-01` and `1998-07-31` datefield1 is a standard datetime type The result set returns everthing except 1998-07-31 When I use the statement Select * from Table1 where datefield1 <= `1998-07-01` and datefield1 => `1998-07-31` Sql server returns everything including 7/31/98 Sql Server is evaluating 1998-07-31 as Jul 31 1998 12:00am Is there a setting that I can toggle that will change this? Also, I have tested this on another Server and the Between Statement is evaluated correctly. Thank you
View Replies !
Build SQL Query From Dynamic Checkbox List
Not sure if this is the place to post this, but here goes. I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query. Synopsis: Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause. Thanks.
View Replies !
Dynamic List Of Tables In Integration Services
I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this: tblLookupData_Customer1 tblLookupParseData_Customer1 tblLookupData_Customer2 tblLookupParseData_Customer2 tblLookupData_Customer3 tblLookupParseData_Customer3 I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system. NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.
View Replies !
Dynamic WHERE Operator Based On User Input
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements: DropDownList1: This drop down list contains 3 choices- "=", ">", and "<". Age: This text box is where someone would enter a number. Button1: This is the form's submit button. I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1. The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error: "The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure." Any help would be appreciated. Here is what I'm using in my code behind: protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
View Replies !
|
TRACKER
