Click here to Skip to main content
15,122,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Iam using Sql server 2008 , i want to display the records for every day based on date.
but when iam running my querry it is counting the repeated values in status column value ie stored,delivered ,I dont wnat count the value for Status Column value more than once
can you correct my querry

below is my table
Histories
==========
Id -uniqueidentifier
Code -nvarchar(50)
Status- nvarchar(50)
StatusDate- smalldatetime
Name -varchar(50)


CSS
Id  Code          Status           StatusDate            Name
===================================================================================

70947cd3-998f   EP515859956SA   Delivered   4/8/2013 12:50:00 PM        PSRUH20300

2fcd1ea0-6740-  EP827144739SA   Delivered   4/8/2013 12:52:00 PM        PSRUH20300
9794c3de-37cc   EP827144739SA   Stored          4/8/2013 12:10:00 PM    PSRUH20300
4b344945-cbb8   EP827144739SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
                ======================
d1b13be5-ee6b   EH000278540SA   Stored          4/8/2013 12:03:00 PM    PSRUH20300
9f4c2cc2-05ac   EQ131017851SA   Stored          4/8/2013 12:03:00 PM    PSRUH20300
2ece4352-1e02   EQ131017851SA   Stored          4/8/2013 11:53:00 AM    PSRUH20300
                ======================
ca53c856-47b7   EH001475961SA   Stored          4/8/2013 12:02:00 PM    PSRUH20300
37aff000-a7df   EQ010022561SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
434e57be-81f8   EH001475961SA   Stored          4/8/2013 11:52:00 AM    PSRUH20300
                =======================

f5689228-6905   EH001479941SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
b7126c1d-0307   EH001479941SA   Stored          4/8/2013 12:10:00 PM    PSRUH20300
                =======================


queryy iam using as below


SQL
SELECT        COUNT(Id) AS Actions, Status, Name
FROM            Histories AS his
WHERE        (DATEADD(D, 0, DATEDIFF(D, 0, StatusDate)) BETWEEN '2013-04-09' AND '2013-

04-09')
GROUP BY Status, Name
ORDER BY Name
Posted
Comments
RedDk 10-Apr-13 13:01pm
   
Not sure I can make this out ... first things first though;

DECLARE @strStatusDate [smalldatetime]
SET @strStatusDate = '4/8/2013 11:52:00 AM'
PRINT DATEADD(hour,0,DATEDIFF(hour,0,@strStatusDate))

On my box gives me a return of nonesense: "Jun 12 4618 12:00AM"
Without understanding what you want when you SELECT using Status and Name alone then say in the preamble you want ALL records ... meaning StatusDate ...

Isn't this enough?

SELECT DISTINCT [StatusDate], [Status], [Name]
FROM [cpqaAnswers].[cpqa].[tbl_IT_Histories]

1 solution

See this:
SQL
DECLARE @dateFrom SMALLDATETIME
DECLARE @dateTo SMALLDATETIME

SET @dateFrom = '2013-04-09'
SET @dateTo = '2013-04-09'

SELECT [Status], [Name], COUNT([Status]) AS CountOfStatusForName
FROM Histories 
WHERE StatusDate BETWEEN @dateFrom AND @dateTo
GROUP BY [Status], [Name]
ORDER BY [Name], [Status]

SELECT [Status], COUNT([Status]) AS CountOfStatus 
FROM Histories 
WHERE StatusDate BETWEEN @dateFrom AND @dateTo
GROUP BY [Status]
ORDER BY [Status]


Do you see the difference?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900