Transact SQL :: Query To Show Totals

Aug 28, 2015

This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008

Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)

[code]....

Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to.  How does this need to be written so the data is displayed in the proper formatting?

Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc

[code]....

View 4 Replies


ADVERTISEMENT

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Transact SQL :: Query To Show Most Recent Value Based On Date Field

Jun 18, 2015

I simply need to list the most recent value for each employee here... the value at each person's maximum date

Sample Detail Data:

EmpID                   Date                      Value
1                              1/1/14                  27
1                              2/12/15                333
2                              5/5/15                   255
3                              5/4/15                   110  
3                              1/1/13                   67                          
3                              3/2/14                   80

[Code] ....

What is the most efficient way to display the most recent value for each employee ID via MS SQL.?

View 4 Replies View Related

Transact SQL :: Running Totals In A View

Sep 14, 2015

I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.

There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.

I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.

Table: deposit    This table will hold all deposits

customerId    (string)
balanceType    (string)
transactionDate   (date) 
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)

The data set for the deposit table could be:

1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100

Table: withdrawal  This table will hold all withdrawals

customerId    (string)
balanceType    (string)
transactionDate   (date) 
amount     (decimal)
transactionText   (string)

The data set for the withdrawal table could be:

2 Bonus2 01-04-2015 -100  Needed 100
2 Bonus2 02-04-2015 -100  Needed 100
2 Bonus2 03-01-2015 -100  Needed 100
3 Bonus2 10-04-2015 -50   Took out 50
3 Bonus2 11-04-2015 -100  Took out 100

[Code] .....

Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate

customerId
balanceType
transactionDate
amount
Total
Text

The view must show this, when selecting all records:

1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100

[Code] ....

And a last view that will show distinct totals for each customer and balanceType

customerId
balanceType
totalBalance

1 Bonus -200
2 Bonus -300
2 Bonus2 -200
3 Bonus2 -550

View 8 Replies View Related

Transact SQL :: Cumulative Totals - Rolling Window

Aug 14, 2015

I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..

View 7 Replies View Related

Transact SQL :: Converting Union Statements Into Census Row Totals

Aug 20, 2015

I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code.  Each block is the same except for the date.  How could I rewrite these 30 union statements into a more concise query?

Each client (people_id) has a distinct agency_id_number.  So the code below totals the number of distinct clients/day and groups them by programs and facilities.

Sample data that you can use to test this report:

IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census

create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)

[Code] ....

View 3 Replies View Related

Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Example:
...
....
Group1              Value
                           10
                            20
Sub Total 1:         30

Group2                 Value
                              15
                              25
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies View Related

How To Wrap A UNION Query In A Totals Query?

Jul 20, 2005

I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell

View 9 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!

 

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

Transact SQL :: How To Show Value In Only One Group

Nov 3, 2015

I have a table with "Project", "Demo" and "Value". Here is a sample. Is there a way to show the "Value" in just one Demo after a I use group by?

Project  Demo Value

Project1 Adult 100
Project1 Kids 100
Project1 Adult 100
Project2 Adult 200
Project2 Adult 200
Project2 Kids 200
Project2 Kids 200

That is after I ran the SQL: I will get only: (see Project1's Kids's value is null now.

Project  Demo Value

Project1 Adult 100
Project1 Kids
Project2 Adult 200
Project2 Kids 200

View 12 Replies View Related

Transact SQL :: Show Total For Each Name

Sep 23, 2015

I tried my syntax below, but it said that the field were not in the group by.  This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:

Total, 29, 150, 2400/60
Total, 25, 143, 2400/60

Here is syntax:

Create Table #Farquard
(
empID varchar(50),
fullname varchar(500),
break1 int,
break2 int,
dailytotal int,
workday date
)

[Code] ......

View 8 Replies View Related

Totals Of Categorys Query

Feb 26, 2008

 Been trying to wrap my head around this one, I'm sure its quite possible but can't figure it.I've got a massive table of Items, each of which has a category. I want to bring back a list of categories and a total number of items in each category, so a result set which would look like:Category      NoOfItemsInCategory--------------------------------------------------Category1                    15Category2                    1287Category3                    25How would I write this query? I've been messing around with Group By and Distinct but can't get the effect I'm wanting.Thanks! 

View 1 Replies View Related

Totals In Query From Two Tables

Jun 5, 2007

Hi all,

I hope someone can help me with this problem, cos I'm a bit lost.

We have a system and its needs to check whether an employee is assigned to a Group or not. With one table, its not a problem.

Select Count(CardID) from Table Where GroupID is Null.

Now we created a different table for different Employees.

I basically need to do the following
Select Count(CardID) as HRCount from Table1 Where etc, etc
Select Count(CardID) as FPCount From Table2 Where etc, etc

However I need ONE total, if possible at once.

There is no relation between the tables.

To SUM it up, I need ONE total from TWO separate tables.

Is it possible?

Thanks and Regards

Claudio

View 2 Replies View Related

Transact SQL :: Show Particular Records Within Same Group?

Aug 13, 2015

I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty  and source data looks like this

ID   PersonID    Date            Type       Qty  

1      1        01/01/2011       Accept      5          
2      1        01/01/2011       Accept      5  
3      2        02/01/2010       Accept      10             
4      2        02/01/2010       Deny        20  
5      3        02/01/2012       Accept      15

[Code] .....

Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.

ID  PersonID    Date            Type         Qty
1    1        01/01/2011       Accept        5      (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3    2        02/01/2010       Accept        10     (show Accept row=3,ignore deny row)

5    3        02/01/2012       Accept        15     (show Accept row=5)

6    4        05/05/2012       Accept        25     (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)

7    4        07/08/2012       Accept        20
        
11   6        01/01/2011       Accept        5      (show Accept rows=11,12 because Qty is different)  

12   6        01/01/2011       Accept        15

Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)

Insert into sample values (1 ,1,'01/01/2011','Accept',5),

(2,1,'01/01/2011','Accept',5),  
(3,2,'02/01/2010','Accept',10),             
(4,2,'02/01/2010','Deny',20),  
(5,3,'02/01/2012','Accept',15),  
(6,4,'05/05/2012','Accept',25),  
(7,4,'07/08/2012','Accept',20), 
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),          
(12,6,'01/01/2011','Accept',15)

View 4 Replies View Related

Transact SQL :: Hours To Show In A Column?

Jun 19, 2015

i'm trying to capture data for every hour and would like to display the hours of the day in a column.

select
case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM],
case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM]
from table1
where cast(calldate as date) = cast(calldate as date)

My desired result should display

12-7 am
8 am
9 am
10 am

View 4 Replies View Related

Adapting Running Totals To Query

Jul 8, 2013

I'm trying to adapt a formula for running totals to my Query, have some difficulties in declaring the tables correctly.

Formula: SELECT Ordinal, QTY, QTY+COALESCE((SELECT SUM(QTY)
FROM Table b
WHERE b.Ordinal < a.Ordinal),0) AS RunningTotal
FROM Table a
ORDER BY Ordinal

Now my Table is rather lengthy and is described by this query:

Select X.Ord, X.QTY
From (Select.... )X

Now in order to adapt the formula for running totals I did:Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(X.QTY)

FROM X b
WHERE b.Ord < a.Ord),0) as RunningTotal
From (Select....)X a
Order by X.Ord

It gives my an ncorrect syntax error near a. I guess I miss the (double) declaration of the Table.

View 4 Replies View Related

Transact SQL :: How To Show Sum Of Columns To New Column In Server

Jul 8, 2015

I made a select query which shows following output as shown in picture . 

Now I want to add one more column in this query to show current bags  and Bags in these 2 columns i want to show calculation like    in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and  697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image

There is one more column common date according to which i have to make calculation like

rid     commondate   recdate   recbags        recweight             loss gain   delbags    delwght

101215109    01/01/2015   07/01/2015       1400                  697.5                0                  0            0             0
101215110   02/01/2015      08/01/2015         560                    279.64             0              0         0               0

View 7 Replies View Related

Transact SQL :: Show Customized Error To Clients?

Jun 16, 2015

I don't want show the DBMS error to my clients. I want show a simple error instead of SQL error.

View 3 Replies View Related

Using SUM To Give Interim Totals Every 'x' Rows From A Query

Jun 29, 2007

First off, here is the application.

An airport baggage handling system distributes bags using multiple conveyors.
Bag counts are logged every 15 minutes. There is a count for each conveyor.
Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)

Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6

i 3 2 3 4 2 1

i+15min 2 3 4 2 2 2

i+30min etc.....................

i+45min

i+60min

etc...



The management team wants a throughput report which will take the following parameters in order to filter the results:

Begin Date
End Date
Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)

My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?



Thanks for your help and let me know if I need to clarify anything

View 3 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Query To Show ID That Is Not In DB?

Oct 24, 2013

I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.

Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots

And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')

You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?

View 1 Replies View Related

How To Show Boolian Value In Sql Query?

Apr 1, 2008

i have one table completed tabletask_id   task_due_datetime   task_completed_on  1                    03/21/2008        03/25/20082                    03/12/2008        03/10/20083                    03/10/2008        03/18/2008 i want the output:task_id    on time        late 1                  0             1 2                  0             0 3                  0             1  how i get this output in sql query without the use of cursor...  

View 5 Replies View Related

Query To Show Duplicates

Aug 16, 2005

mytable      fld1 int     primkey                  fld2 varchar(20),                  fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2.  I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.     select fld1, fld2, fld3 from mytable                

View 1 Replies View Related

SHOW Query For MSSQL

Sep 18, 2004

What would the MySQL equivalent for "SHOW TABLES" and "SHOW FIELDS" be in MSSQL?

View 8 Replies View Related

Query To Show All Data According To ID

Feb 1, 2015

I have a table i need to show data according to id. distinct id and all name should be comma separated against each id.

DECLARE @TBL TABLE (ID NUMERIC(10), NAME VARCHAR(10))

INSERT INTO @TBL (ID,NAME)
VALUES(1, 'A'), (1,'B') ,(1,'C') ,(2,'E') ,(2,'B') , (3,'F') , (3,'G')

output :

id name

1 a,b,c
2 e,b
3 f,g

View 2 Replies View Related

Show Me Example Of Dynamic SQL Query

Mar 5, 2008

Show me example of dynamic SQL query if possible

thank you

View 5 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

Transact SQL :: Table Row Data Show In Column Data

Oct 22, 2015

I have a simple table data i want want to show row data in to column data.
      
SELECT clblcode,mlblmsg
FROM warninglabels

My expected result will be 

0001                 0002  0003                      0004
-------                ------- --------                    --------
May Cause....       Important.....  Take madi...........         Do Not Take.......

View 16 Replies View Related

Show The Decimal Part In Query

Jun 19, 2008

Hi,In the following query the calculator gives value 3.3  but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks 

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show ED / CESS In Query

Nov 5, 2014

Detail:

create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)

insert into detail values

('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')

i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?

View 4 Replies View Related

How To Query And Show The Result In A Form

May 12, 2006

I am an beginner in VC#2005,

my question is:

I connect a table and transtorm to WM5.0 (PDAphone),

I don't know how to show the result of my query,

EX:

table1,(two column: name , phoneNo)

private void button1_Click(object sender, EventArgs e)
{
SqlCeConnection cn = null;
try
{
cn = new SqlCeConnection("Data source=\Programs files\sqltest1\sqlPDA.sdf");


SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Table1 where phoneNo='0922123456'";


SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;

}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}

}
}

there is nothing happen, please help me.

thanks

View 3 Replies View Related







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