Display The Records For This Month

Apr 15, 2008

hi friends,

how to display the records for current month?

View 7 Replies


ADVERTISEMENT

Transact SQL :: Get Query By Selecting Month From Dropdown List And Display Records?

Oct 8, 2015

Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output

select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate

[Code] .....

View 5 Replies View Related

Month Display

Jun 5, 2008

This my procedure

DECLARE @Months table
(MonthName varchar(20)

)

INSERT INTO @Months
SELECT 'Jan' UNION ALL SELECT 'Feb' UNION ALL SELECT 'Mar' UNION ALL SELECT 'Apr' UNION ALL
SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT 'Jul' UNION ALL SELECT 'Aug' UNION ALL
SELECT 'Sep' UNION ALL SELECT 'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dec'


SELECT m.MonthName,ISNULL(CallOutCharge,0) As 'CallOutCharge'
FROM @Months m
LEFT JOIN (SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC
INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1'
WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate)t
ON t.month=m.MonthName

This my Result

MonthName CallOutCharge intMonth
-------------------- ------------- -----------
Jan 0 NULL
Feb 0 NULL
Mar 0 NULL
Apr 0 NULL
May 767 5
Jun 0 NULL
Jul 0 NULL
Aug 0 NULL
Sep 0 NULL
Oct 0 NULL
Nov 0 NULL
Dec 0 NULL


But i need to display


MonthName CallOutCharge intMonth
-------------------- ------------- -----------
Jan 0 1
Feb 0 2
Mar 0 3
Apr 0 4
May 767 5
Jun 0 6
Jul 0 7
Aug 0 8
Sep 0 9
Oct 0 10
Nov 0 11
Dec 0 12


Any one help me

Regards,
Prabu R

View 2 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

Display ONLY Month, Year From SQL2k

Jan 15, 2007

Tried this: SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM .....That returns: Apr 01, 2006What I need is this: April 2006 or even Apr 2006. But no date for the day.Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!bs. 

View 3 Replies View Related

Create Query To Display Month Between?

May 3, 2008

Hello everybody,I want to ask how to create query use the solve the problem of this..Example : I want to display the month january between february... Sorry if my english is not good...Thank you... 

View 3 Replies View Related

Calculate The SUM For 1 Month & 5 Days -display

Dec 18, 2007

Hi All,

I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions-

I have a Table1 which contains
C1-acountid
C2-date
C3-grossamount(postivie and negative decimal values)
C4-netamount

Table2
C1-groupid
C2-accountid

Table 3
C1-groupid
C2-groupname


I need create a store procedure to retrieve the following on a single table

1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day
NOTE:These 10 AccountIDs may be sam or differing each day
2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month.
NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date.
3.Sum of Netamount for the last 5 days for each accountids in STEP1


The result set must contain the columns as below

C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the month
C4-Sum of Net for last 5 days
C5-groupname


Please help me.

Below is the script that i have written, without calculating the sum

(
select top 10 a.date, a.accountid, a.gross, a.net, c.groupname
from GBSys_Sum_EOD a

join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id
join server2.dbname.dbo.Table3 c on b.groupid=c.groupid

where date> getdate()-1
and gross< (floor(-00.00)) order by gross
)

Thanks in advance.

View 2 Replies View Related

Display Just The Month Of The Date Parameter

Jan 29, 2008



Hello,

My report has a parameter called ToDate. Say the user enters 01/29/2008. How do I display this in the header:

Year to Date Jan 2008. ????

Is there a month and year function that I could use to extract the three letter month and the 4 number date?

Thanks!

View 1 Replies View Related

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

Display First Date If Split Into A Year And Month Column?

Mar 27, 2012

How can I find the first date if the date is split into a year and month column?

I thought of using the Min function, which would work for the year column, but it would probably just return a 1 everytime in the month column.

View 2 Replies View Related

Reporting Services :: How To Display Month Column As Varchar

Jun 1, 2015

I have a doubt ,while doing report, I have month column, I have passed 11 in  month parameter, but I want to display month column as NOV not 11. 

View 2 Replies View Related

Analysis :: Query To Display Count Of Products When Month Is Primary

Sep 18, 2015

I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..

With Member ProductRank as
Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]))
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]

Output:
 
It runs for 13 seconds on my laptop. I  have modified the query to get the results fast like With

Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])
Member ProductRank as
Rank([Date Due].[Calendar].currentmember,MySet)
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]

Output:

This query runs instantly and result is not the same. Am I missing something here?

View 2 Replies View Related

To Display Month Name And Year Instaed Of Complete Date...in SSRS Reports

May 6, 2008

Hi All,
I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this...
312 KRISTI WHITE 865400 2008-04-30 2
312 KRISTI WHITE 1000264311 2008-04-30 3
312 KRISTI WHITE 1000430815 2008-04-30 1
312 KRISTI WHITE 1000660614 2008-04-30 1
312 KRISTI WHITE 1002371318 2008-04-30 2
312 KRISTI WHITE 2003722520 2008-04-30 4


Thanks
Chaitanya.

View 10 Replies View Related

Records Last Month

Sep 27, 2006

Today's month is 9 as in September. How can I retrieve records entered last month?This is what I got so far... WHERE     (Classes.CLWhenDt >= DATEADD([MONTH], - 1, GETDATE()))  

View 4 Replies View Related

How To Select Data For A Given Month Or All Records

Mar 29, 2007

I posted this question last night and thought I had an answer.  I have a date field.  I want to be able to filter records by the month of the date.  To do this, I pass the integer of the month.  But I also want to be able to return all the records if no month integer is passed.  So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty.  The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned.  I was focused on another aspect of the page when this was posted.  It worked in the designer, so I thought I was set.  This morning I realized I don't know how to pass a null variable in a querystring.  Since a querystring is a string, it probably can't be done.  Another suggestions was to change this programmatically.   But is there a way to do this in the dataset?  I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane  The answer i got last night was to  

View 4 Replies View Related

Get Records From Last 3 Months But Without Current Month

Apr 3, 2014

I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it

(dbo.frhkrg.fakdat >= DATEADD(MM, - 3, GETDATE()))

View 2 Replies View Related

How To Select All The Dates Of Month Even Though There Are No Records?

Nov 28, 2007



I have one SQL Table with 2 columns as below


Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL

Now There are 2 Records in Table

1-1-2007, 5
1-3-2007, 7


Output of Result should be as below

1-1-2007 5
1-2-2007 0
1-3-2007 7
1-4-2007 0
1-5-2007 0
1-6-2007 0
.
.
.
1-31-2007 0

Means Query should return all the dates of Month with Quantity and if no entry in Table then 0 for Quantity.

How to Do it? Please suggest with Query

View 5 Replies View Related

How To Return Records Based On Previous Month

Jan 11, 2005

I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.

I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.

I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"

'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"

These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).

Any ideas?

View 7 Replies View Related

Problem Query For Records For Previous Month

May 23, 2008

I'm working on project for school that involves building a query in a video store database. The query is suppose to pull the total number of movies rented the previous month. I can get it to work if I physically put in the dates. However, part of the requirements is to set it up so the date range is auto calculated. The following is the code I have

SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN (YEAR(getdate()), MONTH(getdate()), 1)
AND (YEAR(getdate()), MONTH(getdate())+1, 0)


I get the following error message when I try to run it:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.


Anyone have an idea where my mistake is within the date range

View 1 Replies View Related

Transact SQL :: Count Of Records By Month Wise

Oct 7, 2015

I am trying to get count of  records by month wise when they select year .It was showing  the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...

SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)

Output

Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139

View 4 Replies View Related

Get Count And Average Number Of Records Per Month

Sep 12, 2006

Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime

Sample data:
Id Name  AddDate
1  John  01/15/2005
2  Jane  01/18/2005
.
.
.
101  Jack  01/10/2006
102  Mary  02/20/2006

First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?

select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
  from dbo.sampletable
  group by datename(mm, AddDate), datepart(yy, AddDate)
  order by count(id) desc

Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?

View 3 Replies View Related

Display DB Records In Label?

Oct 16, 2006

A SQL Server 2005 stored procedure expects a parameter UserID depending upon which it retrieves the no. of records & OrderIDs corresponding to the UserID from a DB table (note that OrderID & UserID are two of the columns in the DB table). So for e.g. consider a user whose UserID=6 & the DB table has 3 records where UserID=6. In other words, there are 3 OrderID records of the user whose UserID=6, say, OrderID=8, OrderID=17 & OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 for UserID=6) & the OrderID (which will be 8, 17 & 29 for UserID=6). This is the stored procedure:ALTER PROCEDURE dbo.OrderCount    @UserID intASDECLARE    @OrderCount intSET @OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @UserID)SELECT @OrderCount AS OrderCount, OrderIDFROMNETOrdersWHEREUserID = @UserIDIn a VB class file, I am invoking the stored procedure in a function named GetOrderCount which returns a SqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs of UserID=6 in a Label control. Unlike the DataBinding controls like DataList, DataGrid, Repeater controls, the Label control will not automatically loop through the recordset. So I tried to accomplish this using a For....Next loopDim i As IntegerDim sqlReader As SqlDataReaderiUserID = Request.Cookies("UserID").Value'ShopCart is the name of the class in the VB class fileboShopCart = New ShopCartsqlReader = boShopCart.GetOrderCount(iUserID)While (sqlReader.Read)    If (sqlReader.GetValue(0) > 1) Then        pnlLinks.Visible = True        For i = 1 To sqlReader.GetValue(0)            lblLinks.Text = sqlReader.GetValue(1)(i)        Next i    Else        pnlLinks.Visible = False    End IfEnd WhileBut this generates the following error:No default member found for type 'Integer'.pointing to the linelblLinks.Text = sqlReader.GetValue(1)(i)in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in a Label control?

View 8 Replies View Related

Display Grouped Records

Apr 11, 2008

I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID     StoryTitle       StoryAuthor      Rating     StoryID    Comments
1      About Me       goodyone            6          20           Great Story
2      About Me       goodyone            5          20           Love your work
3      Hello World    magicme             6          26           What a Story
4      Hello World    magicme             7          26           This Reminds me of...
I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>"
SelectCommand="SELECT * FROM [iaw.comments]">
</asp:SqlDataSource>
 

View 5 Replies View Related

Display Distinct Records?

Dec 7, 2011

TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar

I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'

File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:

Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

View 3 Replies View Related

Display Records For This Week

Apr 15, 2008

hi friends,

how to display the records for this week?

for today i used this query to dispaly the values

select count(*) from tbl_voting v,tbl_lkvotefor l where v.voteforid=l.voteforid and v.creationdatetime=CONVERT(VARCHAR(10), GETDATE(), 101)

View 4 Replies View Related

Display Repeated Records

Mar 5, 2014

finding the solution for the below query?It displays repeated records.

select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from

[code]....

View 2 Replies View Related

How To Display Records Only Once Using Groupby In

Mar 31, 2008

in my sql query,when it run then it displays records as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

i want to display records only once like as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is

not contained in either an aggregate function or the GROUP BY clause.

so how can i design query so records display only once not repeated,or is any
alternative,to solve this .


my sql query as follows

SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as

dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,

dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,


op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type

,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,

m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')


uday

View 3 Replies View Related

How To Display Records In Ms Sql Using Pivot

Mar 13, 2008

hi i want to display records as per data in ms sql 2005
my data is as follows


Table name & fields are as follows :--

1) Ab_Corporate_Project

Fields are :

Ab_crp_id- int
Contract_no varchar
Project_no varchar
Field_order_no varchar
Ab_type varchar
Owner_id int

Records in Table Ab_Corporate_Project is as follows :

Ab_crp_id Contract_no Project_no Field_order_no Ab_type Owner_id

1 123 pr123 fd222 LS 2
2 323 pr323 fo122 GMS 3
3 243 pr243 fo233 EMC 1

2) Ab_Plant_Project

Fields are :

Ab_plant_id int
Service_order varchar
p_type varchar
Owner_id int

Records in Ab_Plant_Project is as follows

Ab_plant_id Service_order p_type Owner_id
1 so111 LS 1
2 so123 T&M 3
3 so110 EMC 2


3)Other_Project

Fields are

Other_proj_id int
Po varchar
Otype varchar
Owner_id int
Records in Other_Project is as follows

Other_proj_id Po Otype Owner_id
1 p123 LS 3
2 p22 EMC 3
3 p231 GMS 2

4)Owner_representative

Fields are
Owner_id int
Owner_names varchar

Records in Owner_representative are as follows

Owner_id Owner_names
<!--[if !supportLists]-->1 <!--[endif]-->john
<!--[if !supportLists]-->2 <!--[endif]-->amey
<!--[if !supportLists]-->3 <!--[endif]-->nickols

5)Employee_table

Fields are
emp_id int
Emp_name varchar

Records in Employee_table are as follows

Emp_id Emp_name
1 andrue
2 handy
3 sims
4 sandy

6)Daily_Time_Entry

Fields are
Dt_id int
Daily_type varchar
Dt_date datetime
Emp_id int
Project_type varchar
Project_id int
Time_st int
Time_ot int
Time_dt int
Records in Daily_Time_Entry are as follows

Dt_id Daily_type Dt_date Emp_id Project_type Project_id Time_st Time_ot
1 Elect 12/03/2008 1 AB Corporate 2 10 8
2 Mech 13/03/2008 2 AB Corporate 1 10 10
3 Mech 13/03/2008 1 AB Plant 2 5 6
4 Elect 13/03/2008 3 AB Plant 3 7 8
5 Mech 13/03/2008 2 Other Project 2 5 6
6 Elect 13/03/2008 4 Other Project 3 10 8

Where dt_date is in dd/mm/yyyy format
Also in project_type field -- AB Corporate is taken for Ab_Corporate_Project ,
AB Plant for Ab_Plant_Project, Other Project for Other_Project
This means if Project_type = AB Corporate then search into Ab_Corporate_Project
Else if Project_type = AB Plant then search into Ab_Plant_Project
Else if Project_type = Other Project then search into Other_Project

Now I want to display records as per datewise suppose I want to view records by date 13/03/2008

Record display as follows

Dt_id,Dt_date,emp_id,Emp_name, Project_type ,project_id, contract_no, service_order,ab_type,p_type,otype,owner_id,owner_name,time_st,time_ot

So how can I write query to display records as per datewise display
Plz send me a query for this solution

View 1 Replies View Related

Display TODAY's Records...

Sep 1, 2006

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.



I tried this code but didn't work..


[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

View 12 Replies View Related

Display Count Of Records From The Same Table &> 1

Dec 27, 2007

How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State   UserName     First          Last         ID         City         CountTX       Kkeaton         Kathryn     Keaton     1001     Dallas      2TX       KakiKeaton    Kathryn     Keaton     1001     Dallas      2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig

View 4 Replies View Related

Query To Display Most Active Records

Feb 11, 2005

Hello,

I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

For example:

PromoID StartDate EndDate
------- --------- -------
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

Here's a query I am using to display all active promotions:

select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()

Thanks for your help.

D

View 4 Replies View Related

How To Display Unmatched Records In Two Tables

Feb 28, 2008

I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.

SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.

Only field3 is populated everything else is null.

select field3 from tbl1 group by field2 - record count = 48



SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2

-------------------------------------

Result Record count = 0

------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48

I used the left join and it didn't work.


I would like to build a query that will display all 3000 records.


Any help will be appreciated.

thank you!

View 4 Replies View Related

How Not To Display Number Of Records In SQLCMD

Jan 26, 2008

I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution. How do I do that?

Thanks in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved